The Challenge

A merchant processing company had retained us to build and maintain their residual (commission) reporting system since early 2008.

As time went on, the volume of data they were ingesting and managing was outgrowing the calculator they were using to pay out commissions.

As the system aged and the size of data grew, the data management process began to take longer and longer and the server resources used grew larger and larger. In addition, the needs of the customer were changing. Their monthly residuals process involved manually editing the Excel reports after they were generated to handle edge cases that the residuals calculator didn't know about or didn't have the data to handle. This lead to messy data, meaning that the actual residuals amounts paid to the customer would vary between the file and that data in the database, leading to difficulties when trying to report on residuals.

Their residuals team was constantly stretched to complete their monthly reporting cycle due to the amount of manual intervention needed. Reducing the manual data handling was a priority, and would require a new data management process. The need to be able to ensure that the calculated residuals numbers were correct, as well as the reports themselves, also rose in importance.

Our clients were able to plan and eventually get to a place where they had the resources to handle overhauling their residuals system. Once the system overhaul was underway, we had to ensure that the data and data pipelines were properly managed so that:

The Solution

Our solution was broken up into three main components and stages so we could use an iterative development process:

  1. The residuals data model
  2. Report generation
  3. Everything else
We began working with the customer to fully spec the residuals calculation process, in order to determine what calculations and types of records would need to be supported. We then began coding with a full rewrite of the data model and calculator for residual records. Tests were written at the same time for automated confirmation of the calculator’s results, to prevent changes from having unintended results or bug regressions.

When the calculator was completed and “covered” with testing, our focus shifted to the reports. Again, tests were created to ensure that each cell on each worksheet was checked for accuracy. The reports generated from the new system were compared to the reports generated by the old system, plus any manual adjustments, to verify accuracy. Any discrepancies were debugged and fixed. This proved to be an incredibly valuable process as we sometimes found that the new system was correct and the older system and/or the manual changes being applied were creating errors.

Once the data processing workflow as stable we were able to serve our client with software customization to aide in their management and interaction with the data: