ExSync – Data Synchronisation

ExSync 4

It has been busy for us at Exchequer Dynamics. All sorts of varied projects have arisen where we have had to think on our feet and adapt quickly to customer requests for much more than raw data extraction from Advanced Exchequer.

To name a few we have had eCommerce integrations with Ocean eCommerce & Adobe Commerce leading to a developing framework that can fit any database-driven eCommerce engine. Web developers can now get a site live in days instead of weeks or months.

We had an ongoing project with Edinburgh Telford College to integrate Advanced Exchequer with IBM Cognos BI. Working closely with Cognos experts at Barrachd we developed the ultimate business intelligence platform for Advanced Exchequer. This led to the development of a windows service application for our ExSync engine.

Once development started on the Windows Service Application, a decision was made to completely re-write the whole framework of the ExSync product to take into account the rapid release program. With the new version, we can now bolt in new releases of the Pervasive database quickly and at the same time, allowing users to select the Advanced Exchequer version going back to version 6.00 from the at the time current version 6.10. Previously we had a version for each Advanced Exchequer release.

This simplifies the data migration for customers wanting to move away from Advanced Exchequer and we can act quickly and cost-effectively to migrate data. Another by-product of the development of the new Windows Service version was a blistering performance gain.

We now find ourselves advising Advanced Exchequer customers looking to migrate from Advanced Exchequer and with our ExFusion product we are able to help them make the decision to stay and extend Advanced Exchequer with our cohesive integration layer.

It was not on the road map, but customer requirements had caused us to take a slight detour and develop the completely new ExSync version 4 with the option to be run as a service. Offering a new interface and major new features:-

A Corrections table for Transactions/Lines has been added. This allows adjustment values to be added to the transactions to correct posting run failure values where Advanced Exchequer is out of balance. This is a major step forward and makes reporting easy.

An optional service application to run selected synchronisation in conjunction with the Windows scheduler.
The service can also be triggered across the network from the command line or batch file from other applications.
The ability to add SQL code execution to Transactions & Jobs synchronisation to modify the data or execute corrections.
The ability to select Customers/Suppliers/Stock from the last used date. This will speed up updates with options to select all data, from a particular date or from today.
The ability to select Jobs from a range of job numbers, all jobs, or from a job number forward. This speeds up the update process.

Advanced Exchequer leaves null values in the company/daily rate currency values which causes “divide by zero” errors on multi-currency reporting. We set all null values to 1 so that division works.
At the back end, we have made provision for a ‘Developer’ edition which will be a further upgrade option. This gives customers and/or their developers the ability to have post-synchronisation custom stored procedures, the triggering of Shell API’s or external events/applications/batch files. Also, on the development road map, there will be SOAP integration, turning ExSync into an automation framework. The Service and Developer edition allows for complete customisation.

Developers can add code to do pretty much anything on the extracted data. The possibilities are endless. It transforms the engine into a management system capable of executing multiple procedures on any system. It is limited only by the constraints of MSSQL, MySQL or your imagination.

Since August 2012, the service edition ver. 1.0.0.1 has had the ability to trigger global events, which is something not possible in the standard application. When the service finishes a sync cycle it will be able to trigger a global procedure that can trigger many other procedures or VB script…. Shell…. You name it! The scope is absolutely huge! All running from Windows schedule or on-demand.

ExSync: Service

Having listened to our current and potential customers, Exchequer Dynamics has taken the current data conversion application ExSync to the next level as a Windows service.

A service is designed to be a 24/7, background process to perform specific functions and generally does not require user intervention. As far as ExSync is concerned it has been designed to be ‘set and forget’.

As part of the companies ‘Quality without Compromise’ ethos, the development team have been looking further at memory management, data throughput and enhancing overall performance.

The result of the hard work is impressive and rewarding:

The ExSync Windows Service Application has a staggering 78% increase in performance and 30% drop in memory usage over the existing ExSync product (v.3.0.1.1) which is already renowned as the fastest tool on the market for data extraction from the Advanced Exchequer accounting system. This represents a worthwhile upgrade for customers with larger data sets.

We have bench-tested both products using an average sized companies data for a three-year period, extracting 1,538,019 records from Advanced Exchequer.

The synchronisation time dropped from nearly 2½ hours to just 30 minutes!

We also tested the memory usage for the service. We tested using 4GB of RAM (Memory allocation for the service), which is the recommended amount of memory to achieve the best results. The memory usage has decreased by 30% when using the Windows Service Application!

The records processed per second is equally as staggering with the new ‘Service’ crunching through nearly 850 records per second.

The ExSync Windows Service Application had been beta-tested at Edinburgh Telford College where Exchequer Dynamics was contracted to deliver an integration with IBM’s Cognos Business Intelligence platform.

EDL was working in partnership with award-winning IBM Cognos partner Barrachd to deliver the multi-dimensional OLAP cube technology to the college’s finance department. The power of this technology and the speed with which organizations can analyze large amounts of data has to be seen to be believed. OLAP cubes are a critical business intelligence tool for the competitive and forward-thinking enterprise.