Great North Computing

NxBRE - Invoking multiple rules on one business object

2008-07-02T10:37:00.003-04:00

In my business rules I have a business object that contains multiple fields relating to employees. These fields are populated by data from an external data source and need to be validated before storing them into a new database on SQL Server. Rather than looping through the data multiple times (once for each field that needed validation) I wanted to loop once and check/fix each field. In order to do this I had to put my rules in Sets and invoke them from another set. I tried putting them in sets and then doing an InvokeSet from a main logic block, but that ended up causing an infinite loop with a stack overflow at the end.

The end result looks like this. I use the NxBRE native format for my rule files instead of ruleML (my comments are in bold):

<xbusinessrules nonamespaceschemalocation="xBusinessRules.xsd" xsi="http://www.w3.org/2001/XMLSchema-instance">

<set id="sMain">
<true id="sMain">
<invokeset id="sP_EXEMPT">
<invokeset id="sP_ACTIVE">
<invokeset id="sP_LOCATION">
<invokeset id="sp_illaccr">
<invokeset id="sp_illtaken">
<invokeset id="sp_vacaccr">
</set>

<set id="sP_EXEMPT">
...some variables I needed for this set of logic;
<string id="Ys" value="Y">
<string id="1s" value="1">
<string id="0s" value="0">
<objectlookup id="oP_EXEMPT" objectid="AbraData" member="P_EXEMPT">
<logic>
...put your if statement here with all the do's and elseif's and other stuff
</logic>
</set>


<set id="sP_ACTIVE">
...some variables I needed for this set of logic;
<string id="As" value="A">
<objectlookup id="oP_ACTIVE" objectid="AbraData" member="P_ACTIVE">
<logic>
...put your if statement here with all the do's and elseif's and other stuff
</logic>
</set>


<set id="sP_LOCATION">
...some variables I needed for this set of logic
<objectlookup id="oP_LOCATION" objectid="AbraData" member="P_LOCATION">
<objectlookup id="LocationParse" type="System.Int32" member="TryParse">
<argument valueid="oP_LOCATION">
<argument value="-1" type="Integer">
</objectlookup>
<logic>
...put your if statement here with all the do's and elseif's and other stuff
</logic>
</set>


<set id="sp_illaccr">
...some variables I needed for this set of logic;
<string id="As" value="A">
<objectlookup id="op_illaccr" objectid="AbraData" member="p_illaccr">
<logic>
...put your if statement here with all the do's and elseif's and other stuff
</logic>
</set>


<set id="sp_illtaken">
...some variables I needed for this set of logic;
<string id="As" value="A">
<objectlookup id="op_illtaken" objectid="AbraData" member="p_illtaken">
<logic>
...put your if statement here with all the do's and elseif's and other stuff
</logic>
</set>


<set id="sp_vacaccr">
...some variables I needed for this set of logic
<string id="As" value="A">
<objectlookup id="op_vacaccr" objectid="AbraData" member="p_vacaccr">
<logic>
...put your if statement here with all the do's and elseif's and other stuff
</logic>
</set>

</xbusinessrules>

I'll post more real world scenarios with snippets of code as we go on.

NxBRE - A .NET based business rules engine

2008-07-02T10:28:00.003-04:00

I'm working on an internal application that needs to make use of business rules. This will provide flexibility in the application and require less recoding when management needs changes made. To that end I started researching products and ran across an opensource product called NxBRE.

NxBRE is a .NET based business rules engine that provides both a flow based engine and an Inference engine. It supports multi-threading if you structure your code according to the documentation. It supports both the ruleml specification and its own syntax for rule file design. There is also a plugin to allow the design of business rules through Microsoft Visio. Support for this project also seems to be excellent. I had an issue with the product and within 24 hours a new feature request was implemented to support my need. Now I cannot say that will happen for all feature requests, but the fact that it was looked at and evaluated that quickly speaks volumes of the quality of the people working on the project.

You can get to the project here.

I'll post more on this product as I use it.

Remove Failed DCPromo server from Active Directory

2008-06-12T13:58:00.000-04:00

I’m building a server right now for a remote office. And due to some network issues, the DC it tried to use to get information during the DCPromo process failed to correctly get all the settings. As a result we have a new DC that thinks it’s an Active Directory controller but cannot actually process anything since it cannot talk to the domain. In order to start over I did the following:

1) Using the steps provided in this article remove all entries related to this server from the regular active directory domain controllers.

2) Delete the computer account for the offending server from Active Directory.

3) Unplug the borked domain controller from the network.

4) Using the same article from step 1. Remove all metadata related to the existing working domain controllers. This makes the bad serve think it’s the only one in the domain.

5) Run dcpromo to demote the server and check the box specifying that it’s the last server in the domain.

6) Remove the server from the domain.

7) Wait for AD replication to complete before rejoining the server to the domain.

8) Rerun dcpromo.

Voila, you should now have a working DC.

Note: Some people have experienced problems re-promoting the server with the same name. You may need to rename the server prior to adding it back into the domain. This is usually due to some leftover information stuck in Active Directory.

Later,

Gary

Setting datagridview programmatically and tuning column widths

2008-05-09T10:56:00.000-04:00

I’m working on an application right now that require datagridviews to be built programmatically instead of using the designer. Some of these datagridviews have a large number of columns and it’s a painful trial and error method to get the width right. To simplify the process I added a bit of temporary code to the class where the datagridview lived. The code basically fires the ColumnWidthChanged event of the datagridview and then displays the width in a messagebox. I know it’s not fancy, but its temporary and it makes life very simple when you have to configure widths for a bunch of columns. All you have to do is add this code, run the program to populate the datagridview with data and tune the width till your happy and note the width. Then you can reset it in code to the final widths.

Here’s the code:

Add to your class constructor:

this.<datagridviewname>.ColumnWidthChanged += new DataGridViewColumnEventHandler(mydgrid_ColumnWidthChanged);

Add the event handler code to the class:

Void mydgrid_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e)

{

MessageBox.Show(e.Column.Width.ToString());

}

When your all finished just delete or comment out the event and eventhandler.

Microsoft Access missing menu bars

2008-04-21T11:22:00.000-04:00

I had a problem where all my menu bars disappeared in access. I didn’t have any place to click so that I could customize the menu. In order to reset the interface back to default I did the following:

NOTE: Edit the registry at your own risk. I am not responsible if you break your machine by applying these modifications correctly, or incorrectly.

1) Set a restore point if your running XP or Vista. This gives you a safe point in case you bork your registry.

2) Open regedit.

3) Navigate to HKCU/Software/Microsoft/Office/<version your using>/settings

4) Export the key for backup purposes.

5) Delete the “Command Bars” sub-key.

6) Open Microsoft Access and your toolbars should be there.

Slow Nortel Contivity VPN downloads

2008-04-17T17:13:00.000-04:00

We have some employees that access remote networks using Nortel Contivity VPN client. During the course of their duties they download large files and have issues with painfully slow (1-2 kb/s) download speeds in the application they are using. After much head banging and support technician stumping we finally found the cause.

Our firewall is a cisco based product and for some reason the Nortel Contivity VPN client did not like using the dynamic NAT address pool we had set aside for use by outbound connections. To resolve the issue we had to configure each computer that used the Nortel Contivity VPN client with a static NAT entry in the firewall. That caused their download speeds to jump from 2 kb/s to over 150 kb/s.

Just as a note, there were no errors reported in the firewall logs and no errors found by the Cisco engineers in the packet traces. Everything appeared to be fine. They are doing a little research to see if they can tell me why this was happening.

I'll let you know what I find out.

Blank data when filling datagridview from dataset

2008-04-11T17:19:00.002-04:00

If your working with disconnected datasets that get bound to DataGridViews at runtime and you don't use the designer to preset the columns you may find yourself with empty rows or duplicate columns.

To fix the duplicate columns issue:

Add the following line to your code where you setup your datagridview(Example using DataGridView called dgrid).

this.dgrid.AutoGenerateColumns = false;

To fix the issue where you have blank rows and you know your dataset has the data:

Add the following line to your code where you set the properties of each column (Example using a DataGridViewColumn called dcol).

dcol.DataPropertyName = "name of column in datatable"

Blocking Drag-and-Drop on Network File Server

2008-04-09T10:31:00.000-04:00

We've been wrestling with users who regularly drag one project folder into another on accident and don't even realize it. After scouring the internet I had a forhead smacking moment when someone pointed out that all you need to do is deny Delete writes for the folder in order to prevent it from being moved. It should work, I'll try it and confirm the results here.

Using FreeRadius with both EAP-PEAP authentication and PAM authentication

2008-03-25T13:55:00.000-04:00

G1I recently implemented FreeRadius on Fedora Core 7 to provide authentication for our Cisco Wireless Network. I then had to extend that to provide PAM authentication to our RSA Authentication Manager for our remote office VPN because their router didn’t support direct RSA authentication. While that sounded simple it actually was quite painful at first.

For some reason, probably configuration errors on my part, I could not get the remote Cisco 2801 router to successfully authenticate to the RSA device through PAM on the freeradius server. It always returned an error stating “No Auth-Type set.” After trying several modifications I found that I could successfully do pam authentication by adding:

Update control{

Auth-Type:=PAM

}

into the Authorize{} section. However, this forced ALL authentication to be PAM and that broke wireless authentication. After more fiddling I added a second IP address to the radius server and configured freeradius to use virtual servers listening on two separate IP addresses. The first virtual server handles normal EAP-PEAP authentication and the second virtual server handles PAM authentication.

Here are the important parts of my config:

The listen section:

listen {

type = auth

ipaddr = 1.2.3.1

port = 1642

virtual_server = one

}

listen {

ipaddr = 1.2.3.1

port = 1652

type = acct

virtual_server = one

}

listen {

type = auth

ipaddr = 1.2.3.2

port = 1642

virtual_server = two

}

listen {

ipaddr = 1.2.3.2

port = 1652

type = acct

virtual_server = two

}

The virtual server section:

server one{

authenticate {

eap

pam

Auth-Type CHAP {

chap

}

Auth-Type MS-CHAP {

mschap

}

authorize {

mschap

eap

}

server two {

authenticate {

eap

pam

Auth-Type CHAP {

chap

}

Auth-Type MS-CHAP {

mschap

}

authorize {

mschap

eap

update control {

Auth-Type:=PAM

}

There are probably some things I can remove from the second virtual server since I am forcing it to use pam, but it works so I’ll leave it alone for now.

Later,

Gary

Cleanup and Migration of Access Database to Microsoft SQL - Part 5

2008-03-18T23:40:00.005-04:00

The Saga So Far:

In episodes one we covered the basic pains of cleaning up the database tables, queries, macros, etc in order to get a clearer picture of the project scope. In episode two we covered business process analysis. In episode three we covered the process I am using to normalize the data. In episode four we covered basic design criteria for the basic interface.

Access Strikes Back:

I started writing the application to synchronize the access database with the new SQL database in realtime. It used an XML file to control how data changed formats, and even include dynamic sql calls where needed to do lookups (example: converting employee names into employee Id numbers to link into the normalized tables on SQL).

The application worked great. I had added boolean fields to the tables on both access and sql to notify the app when a record was changed and that limited the number of rows that were being returned in the select statements. I added the hooks in the access forms to update these fields since I didn't have triggers available for use. Everything looked fine....until I started running it in production.

I started out simple, adding one table at a time to the live sync. I had narrowed it down to a necessary 15 tables. Unfortunately some of these tables had over 90 fields. Anyway, after I started synchronizing the third table, the access database began to get corrupt and I had to do a weekly "compact and repair" to fix the issue.

As a result of the regular database corruption I switched back to using the nightly push from access to sql and modified the plan for rolling out the new application. Instead of migrating users one job function at a time, we are developing the new system in parallel and will let them "play" with it until they sign-off on the app and we put it into production.

During this process I got together with the guys who wrote our web base time entry system and we decided to design a common middleware piece for our two applications to interface with. I will be using .NET Remoting for my accounting users and they will be using XML Web Services to access their data. This will allow us to develop common classes for the different business objects and apply business rules that will be consistent across all the applications.

We'll see how it goes. We have the diagram for the middleware piece done and are starting the coding of the base object classes (timesheet,employee, blah blah blah).

Check back later for the next update to the saga!!!

Some days I hate computers ;)

Cleanup and Migration of Access Database to Microsoft SQL - Part 4

2008-03-18T23:34:00.001-04:00

The Saga So Far:

The Next Step:

Now we have to determine whether to keep the front end reports and forms that users currently use in their day to day operations or to design a new front end application from scratch. In order to determine this I first looked at form design.

In our case the forms used very little code, and the code they did use was autogenerated for buttons. This meant that there was no data validation going on and most of the actions performed by the buttons were handled by macros. This did not lend itself to an easy upgrade for use by SQL server if I wanted to increase the reliability and functionality of the application. The forms also made heavy use of text boxes where combo or drop down boxes should have been used to limit the choices to valid ones for the field. This has caused me no end of grief in trying to normalize the data as it is uploaded to the SQL Server while maintaining the current level of functionality.

Our reports were mainly done outside of Access in Crystal Reports. During the migration saga the company also decided to move to a new reporting software so all the reports were being redesigned anyway by another individual. This person was rewriting them to work with the SQL Server so this became a non-issue.

After weighing the time required to overhaul the myriad of forms and macros and turn them into an efficient reliable user interface, I decided to develop a new front end application in parallel to the current Access Application. Since there are several pieces to this application I needed to devise a way to keep both databases synchronized as I migrated functions from the Access application to the new application that used SQL Server to prevent issues for the various users doing the different functions (TimeKeeping, Accounts Receivable, Accounts Payable, Project Management, and Payroll). This process needs to be in place and working before I begin to design the new program or I will never get anyone to use it.

The next part will go into more detail about the synchronization application and the pitfalls I experienced. I decided to do that in the next segment because I am writing the app as this is being posted to the site. Check back in a couple of weeks for the next part.

Cleanup and Migration of Access Database to Microsoft SQL Server - Part 3

2008-03-18T23:31:00.001-04:00

In the Beginning

Now that you have removed everything obvious, its time to move on to normalizing. You can do this one of two ways: either normalize the data in the existing database; or create normalized tables in the SQL database and force the data into the proper format using VBA code when you push it into the database.

Which Method

In my case there is a second database that receives nightly data dumps from the main accounting database. This second database is used by other employees solely for the purpose of running reports. One of their chief complaints is that the data is only up-to-date as of the previous business day. This second database uses duplicates of many of the tables in the main database. For this reason I chose to migrate the secondary database to SQL first since it is less mission critical and I can get the kinks worked out of the process.

The Surgery

I began by listing all the necessary tables for the reporting database. I then looked at each table and determined what needed to be done to normalize the data. This took quite a bit of time and research, not to mention a lot of scratch paper.

Once I had determined the best table design I then proceeded to create the tables and relationships necessary. The original access databases had absolutely no table relationships, and because of this, data was constantly being corrupted by people directly manipulating tables without updating the tables containing related data.

The next step was to create an interface for getting the data into the sql database. Since I am using stored procedures this is a two step process. The first step was to create the actual stored procedure that will do appropriate data checks and lookups before writing the records into the database. The second step is to create the VBA procedures to actually push the data using ADODB. There are two vba functions: The first is to do bulk uploads of all records and the second is to update records when they are modified in the acccess database.

Function number one is pretty straight forward. Create the ADODB objects necessary. Get a recordset from access containing all the records to upload. And finally, loop through the recordset and call the stored procedure for each record. The stored procedure will return the record index of the record and write it back into a newly created field in the access table. This will provide the link between the data in the access database and the sql database for future updates.

Function number two will be called from forms whenever they create or modify a record. If they create a new record the RecIndex field will be empty and the code will know to add a new record and return the record index so it can be placed back in that field. If they modify a record that already has a RecIndex then it will call another stored procedure that updates the corresponding field.

The Work Begins

This process will take some time to complete since I have to do this for over 10 tables as well as creating the hooking functions in the forms to keep the data up-to-date. The next article will look at modifications to the existing database and reports so it can use the data in the database.

Cleanup and Migration of Access Database to Microsoft SQL Server - Part 2

2008-03-18T23:29:00.001-04:00

The second step of this process is to take a hard look at the each business process that involves the application. We need to determine which program functions logically follow the business processes and which ones actually slow down the business processes by being incorrectly designed. Throughout this entire process I need to remember that these people are the customers and its in my best interest to give them a robust and stable product. That will minimize the amount of rework that I need to do in the future.

Where To Start

I approach this in a top down fashion. I start by interviewing the managment that oversees the people using the application. I need to determine at a high level how each business process should work (in an ideal world) and which users are involved.

Follow the Chain

Now that I have a high level understanding of each of the processes I can now interview the users involved in the day to day execution of the processes. I need to take detailed notes on how they perform that portion of the process and also find out if the current application lacks any features, or has any flaws, that impact their jobs.

Put it All Together

Now that we have interviewed all the necessary people we need to get a roadmap of the process. Personally I use visio, but you can use pen and paper or whatever other method you desire. The end goal is to generate a flow-chart detailing the steps of each business process and the pieces of the existing application that is used. I also note on the map which pieces of the application will need to be redesigned. In the end it gives you a clear understanding of the scope of the project and you will know what pieces can be ported directly to SQL server and which ones need to be redesigned before being ported over. Remember, our end goal is to get all of the data off of Access and possibly move the front end interface to a platform that meets the business needs better.

Cleanup and Migration of Access Database to Microsoft SQL Server - Part 1

2008-03-18T23:24:00.000-04:00

Introduction

Everyone who does anything with databases, particularly Microsoft products, will end up at a job or client where Microsoft Access is being used for some mission critical database and it has not been properly designed or maintained. Don't get me wrong, I like Microsoft Access and find it quite powerful and stable to use when the database is designed correctly. But I also believe the database application reaches a certain size or complexity where it could benefit from migration to SQL server.

I am currently facing this problem. I inherited an accounting database application that was full of garbage and bugs. The data was suspect and everyone was afraid to touch it. Just to give you an idea of what we are dealing with, the database contains: 349 tables (no table relationships at all), 1569 queries, 427 macros, 312 reports, and over 50 switchboards. This is a database application for processing accounts payable, receivable, and payroll, not something used to decode DNA!

Now that you know the garbage we are dealing with you may not consider your cesspool of a database quite so bad. So on to the good stuff.

Part 1 - Where Do I Begin???

The first thing you need to do is document every object in the database and how in relates to every other. This sounds daunting, and it can be, but it is necessary for you to understand how each piece works before you can start to remove the garbage. If your database uses a switchboard and has a complicated one, I would also recommend generating a diagram that shows all of the menus that the users actually access.

I recommend using a product by FMS incorporated called the Total Access Analyzer. It analyzes a database and creates documentation on all of the objects. This documentation includes cross referencing objects with other objects that use them. The version I used had one problem. It didn't seem to check the switchboard items table when it was building cross references, so some items may not be cross referenced correctly. That may have been a problem of my own creation, or it may not, I am not sure. Anyway, using that product it got me 90% of the way documented. The other 10 % I did by hand by creating the diagram of the switchboard items that people actually used.

Now that I had the doumentation I could start to generate a list of potentially unused items. I started by listing all of the items that were in the diagram of the used switchboard items as "production" items. I then checked the Total Access Analyzer generated documentation and added any items that cross referenced the items from the switchboard. For example, if the switchboard opened a specific report, the Total Access Analyzer would also list the query that supplies the data to that report under the cross reference. If you start following all of the cross references you can find a chain of items that are all related to each other, but have no relation to the rest of the application anymore. Write down all of these items. Don't worry if you don't get them all the first time, you will make a more fine grained pass over the database as you analyze the business processes that use the application and how they work together.

Make a development copy of your database. Remove these items from your development copy and verify that your application still works as expected. Have one of the actual application users test your database. Repeat this process until you feel satisfied that you have removed everything that can currently be removed.

In the next part we will begin looking at business process analysis and making sure the application functionality fits the business process...not the other way around.

Please be patient

2008-03-17T21:35:00.003-04:00

Please be patient as I recover my articles from my old site and place them here. My previous hosting provider glitched and deleted my site and they couldn't get it working again. As a result I cancelled my hosting and decided to give this a try instead. Hopefully I will have the temporary file deletion script back up in a couple of days, as well as the defprint.exe download.

Thanks,
Gary