Bank Recon 4.16.1117.3525 is available

Version 4.16.1117.3525 (28 June 2018)
Improvements
 - Changed deprecated database column types (Text, NText, Image) to appropriate supported types (varchar, nvarchar, varbinary)
 - Added 'Page Timeout' to 'Site Configuration' page (default value: 90 sec). This allows pages that take long to load, like when doing large volumes of group by queries on the manual recon screen to complete in the time set, before giving a timeout.
 - Long running open cashbook batches query tied to 'Command Timeout' value on 'Site Configuration' page to allow large volumes to load in the time set, before giving a timeout.

Bank Recon 4.16.1111.3511 is available

Bank Recon 4.16.1111.3511 is available for download from the Twenty57 Partner Portal. Thank you to Russell and Shivkant.

The following has been included in this version.

Version 4.16.1111.3511 (21 June 2017)

Performance
 - Journal Rule Speed Improvement (Underlying query simplification and performance improvement)

Fixed
 - Service Recovery Improvements. Upon starting the service,
           o process items that have been killed (in the busy state) will be updated to Error, and
           o items in the Pending State will be re-initiated.
         Previously these status updates would not occur automatically and pending processes would not automatically be re-initiated.

Bank Recon 4.16 is available

Bank Recon 4.16.1110.3508 is available for download from the Twenty57 Partner Portal. Thank you to Shivkant for the development work.

The following has been included since the previous blog post. All these versions are available for download.

Version 4.16.1110.3508 (7 June 2017)

Feature

 - Suggest Functionality now includes a configuration option to disable the MOD (10) rounding exclusion for exact matches.

Version 4.16.1109.3506 (9 April 2017)

Fixes

 - Last Key Value can now be larger than 2^32 - 1

 - Using OR conditions in journal rules no longer allows selecting transactions from accounts other than the currently selected account

Version 4.15.1108.3502 (24 February 2017)

Features

 - When manually creating a bank batch, there is an option to use the last statement type of the account

 o When selected, the specific statement validations for the statement number will be enforced

 o When unselected, the standard validations will apply (Opening/Closing balance)

 - Tag transaction removal

 o Cashbook/Bank transactions can be removed from Tags by any user with the "Save/Edit" tags right for the related domain

- SWIFT MT940: The SWIFT MT940 parser has been extended to parse the new 2016 Account identification tag (:25P:)

 o Existing documents are parsed as per usual

Fixes

 - Object reference error on the Recon Viewer screen has been resolved 

o The error would occur when a user without the ReconciliationDelete right navigated to the ReconViewer screen by selecting

  a ReconID link from a cashbook / bank transaction

 - Object reference error occurring when deleting a bank batch no longer occurs and the audit process log is created successfully

Version 4.14.1101.3480 (19 January 2017)

Feature

 - A new process type has been added, 'Bank Batch Delete'

           o Manually deleting a bank batch creates a process log with a message showing the original batch details

  o This allows the event to be audited

  o Even though this event is audited, it should only be used for deleting manually created bank batches

  o Bank batches created by way of bank statement import should be rolled back, since multiple batches can be created for a single bank statement

Fixes

 - Manual Recon totals bar displays correctly when using comma as decimal separator

 - Configurable columns on the Domain Group/Domain/Account selection screen are now correctly aligned and consistent with the standard columns

Version 4.13.1098.3469 (15 December 2016)

Features

 - Bank enrichment amount condition

            o When applying Bank enrichments, an optional condition can be specified for the amount which will determine whether to apply the enrichment

o Supported conditions include None, Amount zero, Amount negative and Amount positive

 - Process Log

           o The Process Log can now be filtered by Process Type, StartTime, EndTime and/or Process Status

Improvements

 - Import definition test function

  o When executing import definitions, numeric parameters used with a greater than condition are not incremented before applying the query

  o Date parameters used with the equals condition or the greater than or equal to condition are incremented before applying the query

  o The "Test" function on the import definition page was not applying the logic above leading to a difference in the data displayed by the test function

    and the data which would be returned on the next run of the import definition

  o The actual value of the @keyValue parameter is now displayed when using the "Test" function

  o The results returned by the "Test" function will match the results returned by the import definition on the next run

 - Bank batches

  o  Statement Date has been added to the Bank batches grid

Version 4.12.1093.3456 (05 December 2016)

Fixes

 - Cashbook Import error: "invalid column length from the bcp client for colid..."

o Truncating the Cashbook transactions details (300 -> 100) for insertion in the Allocation table has been fixed

o When a cashbook import returns data larger than the cashbook table can accommodate, the value is truncated to the maximum length

  prior to inserting (200 characters for reference fields and 300 characters for the description)

 - Cashbook imports ending in a warning now appear on the Rollback cashbook screen

o The process cannot end in a warning unless transactions were committed

 - Bank Statements list

   o There is a new menu item available to Site Administrators

   o Setup > Site Configuration > Bank Statements

   o Individual Bank statements can be selected/deselected for use by the application

   o The goal for the future is to allow installation of plugins without requiring a new version of the application

 - Swift Parser

   o There is a new plugin 'Swift'

   o This plugin will parse MT940 and MT950 files and ignore other swift messages (900,910,...) without failing

   o The main benefit is that the plugin will parse a file containing a mixture of message types

   o It is off by default

   o To activate, navigate to the Bank Statements list, deactivate both of 'Twenty57.BankRecon.BankStatements.SWIFTMT940.dll' and

 'Twenty57.BankRecon.BankStatements.SWIFTMT950.dll'. Then activate 'Twenty57.BankRecon.BankStatements.SWIFT.dll'

   o Without deactivating the SWIFTMT940 and SWIFTMT950 plugins, they will always detect and attempt to parse the Swift file first

 - AutoRecon transfer consolidation

   o There are 2 new configuration options on the ReconRule editor:

 o 'Consolidate Transfers':

- When selected and the rule is run across accounts, transactions will be consolidated and reconciled per account prior to transfer

- This may avoid the creation of many transactions, since only 2 transactions will be created for the transfer

  per account instead of 2 transactions created for each transferred transaction

- The default setting is off

 o 'Transfer Account' = [Use first Bank Account] or [Use Account with most transactions]

- When reconciling across accounts, Bank Recon must choose an account to transfer transactions to

- A new option is available to limit the number of transferred transactions by selecting the transfer account as the account

  with most transactions meaning that fewer transactions need to be transferred

- The default is [User first Bank Account]

   o The major gain in reducing the number of transactions created when transferring across accounts is by selecting the 'Consolidate' option

Improvements

 - MSDTC is no longer required on the TagEditor screen

 - Unreconciled table maintenance

  o The maintenance of the unreconciled tables (CashbookTransactionUnreconciled and BankTransactionUnreconciled) can now be switched off

  o The process has been adjusted so as not to interfere with other processes and to ensure that only one update of the tables is running at any one time

    - This will reduce load on the server as well as resolve some concurrency issues associated with updating the tables

  o The unreconciled tables are used by the Suggest process and may be used by the Recon rules themselves

  o To turn off the unreconciled table maintenance, you must have the Suggest process switched off and ensure that none of your Recon rules

    reference the unreconciled tables directly (since they will then not match anything)

- Any recon rules using the unreconciled tables directly can be changed as follows:

  1. Any reference to CashbookTransactionUnreconciled should be changed to CashbookTransactionUnreconciledView

  2. Any reference to BankTransactionUnreconciled should be changed to BankTransactionUnreconciledView

- Then add the following application setting to the process service configuration file:

  1. add key="bankrecon.unreconciledtables.active" value="false"

Version 4.11.1088.3433 (26 October 2016)

Features

 - Flexcube transaction reversals are now parsed and reflected on the BankTransaction as Reference3 = 'Reversal'

 - MT940 / MT950 reversals are imported as BankTransaction.Reference9 = 'Reversal'

 - Individual reconciliations created when reconciling across accounts can be individually unreconciled or the entire batch can be unreconciled

   o These are created when transferring transactions from source accounts to the Recon account

Fixes

 - "Reconciliation done successfully" message displays immediately after reconciling directly without a balancing entry

o Previously, the message would only show on returning to the manual reconciliation screen

o This message still display when returning to the Manual Reconciliation screen after creating a balancing entry

 - Bank transaction - Bulk import bug for bankstatements with no statement number has been resolved

         o When multiple statements are found in the import directory, Bank Recon needs to order the statements to import correctly.

  It does this by using the associated Statement number / Statement date

o In the case that a bank format does not use a statement number and the statement dates are equal, Bank Recon will use the

  ordering of the selected files as shown on the Bank Transaction import screen (top to bottom).

 - When reconciling across accounts and using Helper data, the ReconID's attached to Helper data rows were incorrectly assigned to the transfer Recons

    instead of the Recon in the working account

         o This has been resolved and the ReconID assigned to matched helper data will match the BankTransaction account (Recon account)

Improvement  

 - GL Account names are displayed on the Journal rule list and editor screens in addition to the GL account number

      o <Account Name> - <Account Number>

  

Version 4.10.1086.3422 (28 September 2016)

Features

 - Auto journal entries can now be consolidated in the same way as with manual entries

           o To use this feature, the Consolidate option should be selected on the Journal rule

 - When testing a classic Recon rule with Helper data, the matched Helper data can be viewed along with the cashbook / bank data

Fixes

 - Manual Reconciliation balancing entry error (Cashbook Transaction ID 0 does not exist)

o Bank Recon can fail to create a balancing entry due a number of reasons including:

   i.  the user does not have create cashbook batch / auto journal permissions

   ii. there are no open batches

o Previously, the actual error was being swallowed and "Cashbook Transaction ID 0 does not exist" was displayed

 - "One of the Recons does not balance.". A scenario was discovered when a rule returns matches using multiple ReconHelpers, BankRecon was

         not mapping the correct ReconHelpers to the correct match. This meant that importing the missing Helpers from the data set

would retrieve helpers from the wrong data set and the result would not balance. This has been resolved

Improvement

 - Oracle cashbook imports were failing on first connection and subsequently succeeding due to an as yet unknown cause

               o To mitigate this, the Oracle cashbook import definition supports retrying the connection a configurable number of times

    using a configurable interval between retries

  o A cashbook import will show a warning on the process log when the connection needed to be retried and fail if the number

    of retries exceeds the configured setting

 

Bank Recon 4.9 is available

Bank Recon 4.9.1082.3412 is available for download from the Twenty57 website. Thank you to Shivkant and Shaun for the development work.

New Bank Statements

- SAP
- FlexCube
- Montran

For more information on these formats, contact the Digiata office for assistance.

Performance

Unreconcile performance improvements have been made to support unreconciling a larger number of transactions. This will be most noticeable when unreconciling a recon involving many transferred transactions from different source accounts.

The account save performance has been improved and will be more noticeable in domains with a large number of GL accounts.

Manual Recon transaction selection performance has been improved. This mainly affected IE 8 users.

GL Account usability

When an account has many linked GL accounts, asides from being difficult to locate GL accounts, the application may become slow or even exceed the limitations of the web server as specified in the web.config file (maxRequestLength).

The GL Account list includes a search box with autocomplete:

In the case that the number of GL accounts for the domain is larger than the web.config setting "bankrecon.glaccount.dropdownlimit", the Account editor will show an search control from which GL accounts may be added to the list:

The selected GL accounts may be associated with the account be unchecking the "ALL" checkbox. In this case, only the GL accounts displayed are are associated with the account.

The selected GL accounts may be disassociated with the account by checking the "ALL" checkbox. In this case, all GL accounts are associated with the account except for the selection.

Fixes    

• The MT940 / MT950 parsers now compare statement numbers correctly when importing a batch of statements with alternate formats of the Statement number tag (:28C)

• Statement number only:

• Statement number and sequence number:

• The IE status bar dependency has been removed. The text previously shown on the status bar is now shown as HTML.

• This avoids having to allow javascript to update the status bar in IE (as well as display it in the first place).
• The control totals are now available to Chrome users. (There is no status bar in Chrome)
• This affects the Manual Recon screen, Cashbook/Bank transaction screens and Transfer Cashbook transactions screen.

 

• The Manual Recon suggest undo command no longer times out

• Export to excel bug with control characters
• Excel files underlying structure is xml and so invalid xml characters are converted to blanks prior to creating the export

• The Cashbook transaction allocation editor "Save & Reconcile" double-click bug has been fixed.
• The button will no longer submit multiple requests when double-clicked (It will disable after the first click and enable after)

• The Report viewer control Oracle parameter bug has been fixed

Structure changes

The ReconCancelBankDetailsTransaction table has been removed. This table was previously used for unreconciling a recon linking Bank transactions directly to Helper Bank transactions.

Direct Bank transaction to Helper bank transaction recons were removed in 2014.

Roadmap

In the immediate future, we will be looking at introducing the Helper data into the front-end. In particular, the Recon rule test functions and recon viewer will display any matched Helper data for verification purposes. The Manual recon screen will be extended to utilise the Helper data for matching in a non-obtrusive fashion.

Bank Recon 4.5.1039.3252 is available

Bank Recon 4.5.1039.3252 is available for download from the Twenty57 website. A big thank you to Ashish, Jai and Shivkant for the development work.


Features

• Import definitions
• BankRecon can now import transactions from Oracle stored procedures
• Entering the name of a configured stored procedure will return a parameter list
• One of the parameters must be selected as the Key value as per regular imports
• Tagging across accounts
• Tagging across accounts is now supported to complement searching across accounts
• In the case that transactions have been tagged from multiple accounts, the account name is added to transaction grid to disambiguate the source
• Tagged cashbook and bank transactions will open in the account in which they were tagged
• Public Recon profiles
• Manual recon profiles can be published to all users on the same account as a public profile
• Public profiles will appear in other users' Current Profile dropdowns who have access to the same account
• Any profile can be shown in / hidden from the 'Current Profile' list using the 'Manage Profiles' screen
• Only the creator of a public profile may edit/delete that profile
• Export to excel
• Cashbook and Bank transaction exports now produce Excel (xlsx) files in addition to csv files
• Reconciliations can now be exported in a single Excel workbook with 3 sheets:
• Details: Shows the Reconciliation details (ID, Account, Accounting Period, User, Description)
• Cashbook: Contains the reconciled cashbook transactions
• Bank: Contains the reconciled bank transactions
• When exporting reconciliations as a csv file, the 3 datasets are zipped into a single archive
• Accounting Periods
• There is a new setting on the Account maintenance screen to allow/deny closing an accounting period prior to the period's end date
• Adjust Opening Balance
• Accounting Period opening and closing balances for the Cashbook and Bank are shown
• A help dialog has been added to explain the functionality
• The earliest available Bank opening balance is displayed to illustrate the procedure and updating the bank opening balance now correctly propagates to all subsequent periods

Fixes

• Manual Recon screen
• When grouping bank transactions, the filter is now applied to the underlying transactions and not the resulting group
• Reports
• Parameterless reports now display correctly in the browser
• Generic CSV importer
• The Generic CSV importer no longer attempts to validate the statement number when selected as part of a multiple file import since it does not use the statement number
• Errors returned by the parser include the transaction number relative to the uploaded file and useful information on how to resolve any errors

Performance

• Classic Recon rules
• The classic Recon rule editor now supports a batch processing mode, which improves performance by executing the GetKeysCommands for a batch of matches instead of one-by-one processing
• An example is available in the application to illustrate the procedure and changes required to the Recon rule
• Auto Recon rule performance logging
• In Site Configuration, there is a new setting, "AutoRule Log Enabled"
• When checked, the consolidated duration of the left and right side queries of Recon rules are logged in the 'ReconRuleAuditLog' table, so that any poorly performing queries can be identified

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!