Level 12

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 system was regularly expanded to handle additional calculations and logic related to the payment residuals.  Unfortunately, the way the system was originally built was not ideal for the volume and complexity of calculations that were eventually asked of it.

As the system aged and the size of data grew, the calculations 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 meant 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.

As time progressed, our customer desired to extend their residual reporting with more complex pricing options than the original system could support. In addition to this, their residuals team was constantly stretched to complete their monthly reporting cycle due to the amount of manual intervention needed. Reducing this workload was a priority, and would require a new reporting 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.

In 2014, after years of recommending an overhaul of this system, our customer was able to allocate the funds necessary to begin working on the project. The top priorities were:

• All residuals related calculations had to happen in the system.  No more edits to the Excel reports after they were generated.
• The final residuals calculations would need to be stored in the database for easy reporting.
• Performance would need to be increased significantly.
• Feature enhancements that would make the calculation process more accurate and prevent or detect errors.
• Add non-bankcard payment products to the residuals reports.

The Solution

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

1. The residuals calculator
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.

At the point that the calculator and reporting process were determined to be significantly stable, we went to work on all the additional functionality. This included:

• Modifications to make it easier to enter and report on one-off monetary adjustments
• Pricing profile UI and database cleanup
• Emailing of residuals reports
• Improved monthly finalization logic
• Exception reporting
• Audit trail for pricing: if any rate, fee, adjustment, etc. is changed by a user, the customer has a record of that change so that calculations could always be reconstructed if necessary.
• The inclusion of residuals calculations for a number of different payment products beyond credit and debit cards.

The Challenge

With many years invested in a legacy analytics tool that was no longer supported, our client needed to find a replacement that would allow their business users to continue to analyze and evaluate Key Performance Indicators (KPIs) and to pull reports for organizational activities and data.

The legacy application included approximately 20 business “views” that needed to be converted for use in the new tool.  As their business continued to grow and change, there would be the need to add new data and views, so flexibility and adaptability were important.

The tool itself would need to:

• Provide high level summaries, multiple types of visualizations, and also be able to drill down into the details.
• Be web-based so that access to the tool was not hindered by location or worksite.
• Have a very flexible permission system that would accommodate our customer’s multi-level multi-site management structure without making sensitive data available to the wrong people.
• Be very user friendly for non-technical business users. Pushing important data deeper into their organization would be a big win for our customer.
• It needed to remain flexible and powerful for more advanced users and business analysts.

The Solution

We worked with our client to research potential options and chose to implement Pentaho as the solution.  We were responsible for installing and customizing the Pentaho application to meet their business needs.  This included configuring the application to connect to Microsoft Active Directory for authentication as well as writing custom Java plugins for Pentaho in order to incorporate row level filtering for data access.

We designed and built a data warehouse and developed numerous (ETL) processes to organize and move data from their operations database into the data warehouse. We built an ETL framework that shared libraries with the operations database to cut down on development time and prevent errors caused by the ETL data models and operations data models getting out of sync.  Most of the ETL pipeline and data cube configurations are covered by automated tests.  Those tests help us catch errors early in the development process and give us confidence that the data these systems are providing to our users is correct.

As we converted each data view from the old tool to Pentaho, we worked with our customer to make sure the data that was being provided by the view was still relevant and accurate.  Due to this attention to the current & real needs of our customer, their BI landscape changed considerably, for the better.

Today our customer uses Pentaho for most of their business intelligence needs.  We have setup almost 50 different data cubes for their use. Those cubes cover a wide range of domains inside the organization including:

Operations

• Asset meter readings & utilization
• Asset Maintenance
• Customer Management
• Operational Efficiency
• Fuel Tracking
• Load Tracking
• Labor Efficiency
• Revenue Validation
• Safety
• Service Quality

Financial

• Asset Tracking
• Sales & Revenue
• P&L
• Vendor audits

Human Resources

• Staffing Goals
• Retention

In this project we provided CTO as a Service to help guilde the overall develpoment direction of the Red E App SaaS product, as well as some developer support. Our high level guidance and software development expertise helped Red E App scale up their platform as well as accelerate the internal development lifecycle. You can see CEO Jonathan Erwin's testimonial here: https://www.youtube.com/watch?time_continue=3&v=c9spJovCvk4&feature=emb_logo

In this project we inspect power generation windmills with drones. We are responsible for the software that launches the drones, pulls the data off the drones, and then sends that to Microsoft Azure where we spin up huge GPU instances to process the images through a machine learning model that can identify blade defects. That all then gets automatically added to a graphical interface for the client to interface with that shows them the severity of the damage, which blade, etc. so they can schedule repairs.