Thursday 29 September 2011

Bank Recon is on Framework 4.

Bank Recon is officially on Framework 4. You can download Bank Reconciliation 4.2.344.1766 from reconciliation changes are released in this version and there should be measurable improvements in auto recon times for everyone. There are no changes to the rule design or matching logic so the effectiveness of your rules has not been affected in any way.

Thank you to all the developers for the great work done.

Additional features included in this build
1. Copy and Paste from the Amount column to the filter and apply it without getting an error - any formatting is now ignored.
2. Clicking in any row of the grid will bold it and select it so you always know where you are, even when you have scrolled across the grid.
3. Export to Excel now includes the transactions in the matched section - no matter what your applied filters.
4. Clicking Clear will remove the ticks from the checkbox - no need to page through your transactions to find and remove them.
5. 'Group by' has been simplified. You will no longer see the three additional columns in the filter when you check the group by box. Select your required columns, amount will always be selected and when you Apply the filters, Bank Recon will automatically group by the columns and sum the amounts.
6. The ABSA ASCII bank statement format now supports import of multiple accounts in one file.
7. Suggest Bug - Display error when html special characters are included in the text has been fixed.
8. A view of the Current Log.txt file written by the Process Service has been added. Admin>Process Log
9. Suggest matches made are no longer logged in the audit tables

Installing this version
Before installing this version please ensure you have .Net Framework 4 installed on the application server. It is advisable to do a clean install of this build. If you must do an upgrade please contact me and I will help you with the necessary changes.

1. Stop the service and uninstall your existing Bank Recon installation.
2. Clean up the install folder (delete or rename the web.config and processservice.config files).
3. Run the msi
4. Select Custom install. Specify the correct install folder and select all features EXCEPT the database and Virtual Directory. You can use the same Virtual Directory and you will need to update the existing database using the database updater after install.
5. Select Forms authentication. Even if you use windows authentication please run the install with Forms authentication and update the web.config file afterwards. There is a windows authentication install error. We are working on a fix for it.
6. Install Bank Recon. The service may fail to start automatically and will need to be manually restarted once the Database has been updated.
7. Use the Database Updater located in the Install Tools folder to update your database.
8. Update the connection strings in your config files to the correct value and change your authentication type to Windows if required.
9. Change the Framework version of your IIS directory. In IIS 6 you change it on the site itself. In IIS 7 update the Framework version of the application pool. If you are running IIS7 please ensure your app pool is running in Classic Mode.
10. Restart your service.

And you are good to go.

We are still investigating ways to improve the performance and scalability of Bank Recon. Currently we are testing various options, some of which are showing great results. These improvements will be included in future releases.

We are also including 'in-house' exception handling. On the cards now is better webservices to integrate to other systems and giving the user the power to send an email directly from Bank Recon when tagged transactions need investigation. We are looking at other exception management functionality and will extend it over time. Watch this space for more details.

As always I would love to hear what you think and what you want to see in Bank Recon.

Thursday 1 September 2011

Best practices for good Recon Rule design

I have been focusing on recon rules and how they perform for the last few months. Some great dev work has been done to improve the auto matching, but it is also possible to get significant performance gains by implementing good rule design.

To give you an idea of how impressive the gains can be:
The 'worst' performing Domain in our testing only showed an improvement of 14% between the old and new code. After applying some of the tips below to the rules, there was a 50% improvement in the time taken for the rules to execute. These rules were run on large data sets, but the gains are equally noticeable on recon counts of 1000 and less.

New Code, original rules
(hh:mm:ss) m1.Large Instance
New code, tweaked rules
(hh:mm:ss) m1.Large Instance
% Improvement

General Guidelines (in no particular order):
  • Specify exact matches in the Command SortOrder for both LHS and RHS rather than in the match Section of the rule.
  • Try include at least one exact match criteria - even just the AccountId match
  • Sort your SQL queries in the same order you specify the matches.
  • Always sort the SQL queries and always sort LHS and RHS in the same way.
  • Use (noLock) in the SQL.
  • Only return the columns you need, not everything.
  • Filter your data sets to exclude blank values, zero amounts and other records which won't produce a match for that rule.
  • Use @CurrentDomainID (case-sensitive).
  • Limit the data sets to recent transactions. If a record hasn't auto-matched in the last 90 days (or less) it is unlikely to recon now.
  • When working with grouped data sets exclude 'groups' of 1 transaction. Grouped data sets make use of the GetKeys command whether the group is one or many and this takes more time. Rather do a separate rule for 1-1 matches.
  • Set the priority of your rules - this helps ensure the best matches are made first.
  • If values have changed fields ( eg from Details to Reference1), after an initial phase in period, remove the old matching criteria. 'Or' command in the regex will slow down the rule.
  • Try get the matching value in SQL and use SortOrder to make the match rather than use Regex in a match definition.
  • Limit the number of Recon Rules.
  • Review the rule performance periodically. I can provide scripts to do this.
  • Make use of Helper Rules.
  • Make use of the TemporaryTableCommand.
Not all of the above can be applied to all rules and sometimes a change may make things worse. Use your discretion and always test a tweak first to make sure that you are still matching the correct stuff and that there is actually a performance gain. There are those rules where very specific matching criteria may mean a trade off with performance but try limit them!

If you have other tips please let me know about them. I am always trying to grow the Bank Recon knowledge base.