Which webservices do you use?

Which of the Bank Recon webservices currently offered do you use? We are planning a revamp of the existing webservices to provide better interfaces for exception management and integration with different systems. A full list of the available webservices is below.

For Imports:

• DomainGroupBankStatementImport
• DomainGroupCashbookImport
• DomainSingleBankStatementImport
• CashbookImport
• SingleBankStatementImport
• BankDetailsImport

For Recons:

• DomainGroupAutoRecon
• AutoRecon
• CancelRecon

Other Bank Recon Processes:

• DomainGroupCashbookTransfer
• DomainGroupGLExport
• GLExport
• DomainGroupJournalRule
• SingleJournalRule

Client Specific Processes:

• GetDetail
• GetExceptions
• GetHistory
• UpdateException
• DeleteDetail
• AddDetail

Any changes we make will affect the Framework 4 version only. Scheduling has been included in Bank Recon since Version 2.4.758.2344 released in January 2010. This has reduced the dependency on the existing webservices to automate imports, recons and other processes.

If you use the built in Bank Recon scheduling but still have reason to occasionally use the webservices let me know.
If you are currently on a version that does not have scheduling and use the webservices are you planning to switch over once you upgrade?

Email me or post a comment and tell me.

Version 4.2.348.1775 is available for download (bug fixes)

A few small bugs were identified in the previous version and we have worked to fix them and get the new build out to you quickly. Thank you to Anand and the team for the great work. Please use this build if you are doing an upgrade to the Framework 4 version instead of the build released last week. Version 4.2.348.1775 is available from the Twenty57 website.

• Install with Windows Authentication selected works. You don't have to install with Forms Authentication and then update your site and config files later.
• Amount Filter work correctly with IN, NOT IN and BETWEEN conditions. Please note that using a formatted amount value with these three filter conditions will not work. IN, NOT IN and BETWEEN still work as normal for all other columns (ID, Date, Details etc).

If this is your first install of a .net Framework 4 version please follow the instructions in the previous blog post. 

Let me know what your experiences with Bank Recon are and what you think we could do to make it better. All feedback is wanted and welcome.

Bank Recon is on Framework 4.

Bank Recon is officially on Framework 4. You can download Bank Reconciliation 4.2.344.1766 from http://www.twenty57.com/twenty57/BankRecon/Download.aspx?product=BankRecon.The 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.

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
4:00:30	2:01:16	49.5%

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.

The Bench-marking journey and results

We have finished bench-marking and it has been an interesting journey into the world of Amazon servers and  'cloud computing'. I will give the highlights (and low lights) and the final results. You are more than welcome to contact me if you want more details as to our findings and the procedure followed. Please note that all results and recommendations are subjective. We ran Bank Recon on one server (Application, service and database) and we were only running Bank Recon, no other applications. Amazon servers are also virtual so the performance will most likely differ to a real hardware setup with similar specs.

We also only tested the recon rules and cashbook imports. Testing of the manual screen as well as load testing may be conducted at a later stage.

We tested two different database setups. Both databases are real world databases with actual cashbook and bank transactions in them. The recon rules are in use at clients and were not changed in anyway for the testing.
1. 170 GB database where the Cashbook Transaction table has been partitioned and the Recon ID is the clustered index. Ran the rules on one domain for all transactions matched on one day.
2. 7 GB database with the standard Bank Recon schema. Ran the rules for all the domains for all transactions in the database.

The m1.Large instance has 7.5 GB memory and 2 virtual cores with 2EC2 units each. The underlying processor is an Intel Xeon E5430 (according to the Windows Server 2008 System information)
The m2.xLarge instance has 17.1 GB memory and 2 virtual cores with 3.25 EC2 units each. The underlying processor is an Intel Xeon X5550 (according to the Windows Server 2008 System information)
1 EC2 unit is approximately equal to the CPU capacity of a 1-1.2 GHz 2007 Opteron or 2007 Xeon Processor. More information on the instances is available here.
All instances are running Windows Server 2008 R2 64 bit.

And the results:

Cashbook Imports:
Only imported data into the 170 GB database, using the SQL BCP process. There were no code changes made.

Import File size	No of Txns Imported	m1.Large Time (hh:mm:ss)	m2.xLarge Time (hh:mm:ss)	% Improvement
777,039 KB	1 033 395	00:53:34	00:38:18	29%
779,603 KB	1 003 104	00:52:02	00:39:22	25%

Recon Rules

			m1.Large (old vs New code)	m2.xLarge (new code)	m2.xLarge (new code) best time
Database	Domain	Recon Count for all rules (approx)	% Improvement code change	% Improvement: increased hardware as compared to m1.Large new code	Time taken (hh:mm:ss)
170 GB	1	24 600	85%	20%	00:16:28
7 GB	1	37 000	14%	44%	01:37:44
	2	315 600	69%	47%	02:46:22
	3	35 900	35%	26%	02:59:39
	4	12 680	74%	32%	00:03:15

What really jumps out from the above results is the wide swing in recon performance improvement. All domains tested show an improvement but it varies between 14% and 85% when looking at the code changes and between 20% and 47% when looking at the hardware scaling. 

What this seems to indicates, is the effect that rule design has on the performance. We didn't change the matching logic, only the reconciling logic. The matching code has to work harder to find matches when the data isn't fully filtered or the sorting isn't consistent, and the reconciling portion is then a smaller percentage of the overall process. We may make changes to the matching logic in the future but you can get further rule improvements now by looking at your rules and tweaking them where necessary. But rule design is a subject for another blog post. 

Why these instances?

We tested on the m1.Large, m1.xLarge, m2.xLarge and m2.2xLarge instances. m1.Large is the smallest instance which made sense in our case - it allowed us to scale the 'hardware' and provided enough power to test on. We then upgraded to the m1.xLarge instance. On paper this is double the m1.Large instance and very similar to the m2.xLarge instance. But to our surprise we didn't get the expected improvements. Research led us to the conclusion that not all Amazon instances are created equal. It is likely you are sharing the underlying hardware when using a m1 instance. So you can find yourself in a 'noisy neighbourhood' and suffering from 'CPU steal cycle'. The m2 instances do not seem to experience the same issues to as large a degree.

We tested the scalability of Bank Recon by running the rules on the m2.2xLarge (double the m2.xLarge instance) instance but did not see noticeable improvements. Bank Recon does not make use of parallelism so the additional cores were of no benefit. There is also a limit to the amount of memory Bank Recon can currently utilise.

Summary and Guidelines

The results indicate that the optimum configuration for Bank Recon is 18 GB RAM and a 2-3 GHz dual core processor. This is required for both the Application and Database server. Your hard disk size we be determined by the size of your database and application. Please note that this guideline is based on running Bank Recon on one server with NO other applications. If you run multiple applications or databases on the same server you will benefit from more cores and memory which allow for parallel execution of processes and multiple threading.

A big thank you to the development team for the code improvements and all the time spent in bench-marking and analysing the results. We are busy with some small tweaks to the Manual Recon screen to make it more user friendly and then we will release everything in one bumper release

Benchmarking Performance on Amazon Servers

Bank Recon benchmarking is underway with the goal of providing measurable information as to what the optimal setup is, as well as to quantify the improvements gained from the recon rule changes that have been made. Remember, we are testing in a very specific environment so we can only give guidelines to maximise performance. Factors such as the number of applications running on your servers and the number of users will affect performance in your environment in ways we haven't tested. 

We decided to use the Amazon EC2 servers for the benchmarking exercise. The benefit of the Amazon setup is that hardware can be easily scaled to measure the effect of upgrading the processors, memory and disk space without the physical cost of buying and housing the servers . Amazon provides a number of 'instances' which have differing CPU's, cores and memory. Amazon uses the EC2 Compute Unit (ECU) to measure CPU output. One ECU unit is roughly equivalent to a 1.0-1.2 GHz 2007 Opteron or 2007 Xeon processor. More information about the Amazon EC2 servers can be found here.

Thus far we are testing on the following configurations

Instances Used

EC2 Large: 4 ECUs, 2 cores, 7.5 GB memory

EC2 Extra Large: 8 ECU's, 4 cores, 15 GB Memory

Server Setup

Windows Server 2008 R2 64 bit.

SQL Server 2005. 

Database and application running on the same server.

Databases:

160 GB with the Cashbook Transaction Table partitioned on Recon Id

6 GB with a standard schema

I will detail the testing done and the results in a later blog post when it is all completed, which should be in the next week or so. We have tested the recon performance improvement of the new code vs the old code as well as benchmarked the Import process on the Large instance. We are now testing how scaling the hardware affects the performance by running the recon rules and imports on the Extra Large Instance using the new code only. We may test on other instances as well to get a better idea of scalability. Every time we release a new version going forward we will benchmark it to ensure the performance is as good or better. 

And as soon as the testing is complete we will release the new build - watch this space!

Bank Recon Release 4.1.303.1673

Bank Recon 4.1.303.1673 can be downloaded from http://www.twenty57.com/Twenty57/BankRecon/Download.aspx?product=BankRecon

It is the last version to be released on .net Framework 3.5. All new features are being developed on .net Framework 4. We will continue to support the older versions, limiting development to bug fixes.

What is in this version?

• Bug Fix: Edit tag screen: CB transaction frame size has been increased to make viewing easier
• Features: Added setup information, guidelines and a test button to the Cashbook and Bank Details Import Definition screens. 
• Reports: Updates to the Age Analysis of Outstanding Deposits, Unreconciled Item Summary Report and Unreconciled Transaction Detail with Summary reports. Improve the layout and ensure meaningful data is returned in all situations.

As always, all feedback is wanted - let me know what you think. I hope you are using Suggest and getting great value out of it.