Case Study: AI and Performant Reduces ETL Processing Time from 48 to 2 Hours

Download As PDF
performant-logo-1-1[1]
placeholder_200x200
placeholder_200x200
placeholder_200x200

Background

Performant BI is a financial waste avoidance and recovery company based in Livermore, CA with over 1,500 employees. Government and healthcare organizations turn to Performant for solutions that prevent fraud and waste through analysis, auditing, and recovery functions for use cases such as health insurance, Medicare, and student loans.

RESULTS AT A GLANCE:

      • Decreased ETL processing time (i.e. down time) from 48 hours to 2 hours.
      • Enabled decision-making based on fresh new information produced daily.
      • Created a deeper understanding of the meaning of underlying data and reports.
      • Enabled teams to all work from the same data using consistent, up-to-date, and accurate information.

The benefits of this project were eventually multiplied six times across four data warehouses serving student loan recovery and two for healthcare analysis.

The Challenge

Performant wanted to improve the timeliness and accuracy of key business intelligence systems, which provide the foundation for Performant’s analysis of healthcare payments and student loans. Improving this would make Performant more efficient and competitive.

Performant’s existing system used ETL to load a data warehouse for computing reports on a weekly basis. The warehouse required 48 hours of downtime each week while it was loaded with fresh data.

Due the tortoise-like pace of the ETL process, the data warehouse was only loaded once a week. This meant that analyses were frequently conducted using relatively stale data.

In addition to lacking timeliness, the meaning of the reports was subject to interpretation. The experts who originally created the reports were no longer available, and the origin and precise meaning of some of the information presented was not always clearly understood. As one data team member put it, "The ugly side of BI is that people love their numbers, but you have to do the due diligence and look at the raw data and determine whether and how it supports the conclusions being reported."

Performant’s analysts continued to use their own ad hoc spreadsheet reports as the basis of their analyses. Those ad hoc spreadsheets were often created using information from other spreadsheets introducing numerous opportunities for errors. This often meant that team meetings would be populated by numerous individuals all with differing reports supposedly based on the same original source data.

Performant, recognizing the inefficiencies of its processes decided to embark on a massive project to fix this. The company’s goals when planning this project were to…

  • Provide more timely reports
  • Reduce downtime
  • Improve accuracy of reports
  • Deliver the right reports based on solid agreed-upon data sources and calculations
  • Provide “single source of truth” reports that reduce or eliminate the need for ad hoc spreadsheets

THE SOLUTION

Architecting Innovation was already working to re-architect Performant’s healthcare solution by integrating Particular Software’s NServiceBus Enterprise Service Bus to provide communication between software services comprising the system. Since Performant was already collaborating with AI consultants, it was only natural that they would also work together to tune up the business intelligence ETL and reporting system.


AI has an expert data team with decades of in-the-trenches database, data warehouse, and business intelligence experience. They understand a wide variety of technologies, including the Microsoft database and DTS software that was being used by Performant.


The ETL setup being used by Performant’s business intelligence system was simple but slow. The underlying data warehouse was based on Microsoft SQL 2000 and DTS. As part of the solution, The AI Data Team upgraded to Microsoft SQL 2012 and SSIS.

Existing Process

The process used by the system:

  1. Extract all of the data to be used for analysis.
  2. Wipe the warehouse clean.
  3. Transform and load everything, creating the business intelligence warehouse all over again from scratch.

See the flowcharts, below.

Existing Process

New Process

The key to accelerating the ETL process was to reduce the amount of data being transformed and moved. To accomplish that, the AI data team decided to extract only data that related to customers who had updates since the last ETL. Only data relating to changes would be transformed and merged with the BI data warehouse, greatly reducing the amount of data needing to be pulled and processed.

Here’s what the new process is like:

  1. Extract only data that relates to customers with changes.
  2. Transform and merge the changes into the reporting warehouse.

This solution sounds simple, and the implementation effort really was, but nearly 90% of the time for the project was spent in the early stages, analyzing data and determining how to optimally extract and merge data into the warehouse. It was a complex process made more difficult by the absence of subject matter experts knowledgeable regarding the system being replaced.

RESULTS

The primary goals of this project were to improve performance and the timeliness of reports. Those goals were accomplished with more than an order of magnitude improvement in performance. ETL processing time, during which the business intelligence data warehouse was unavailable, went from 48 hours to just two hours. Rather than rebuild everything each processing cycle, the new process cleverly extracts only data relating to accounts with changes. The changes merged with the existing data warehouse before business starts each day. This allowed scheduling of the ETL process to run on a nightly rather than weekly basis, eliminating downtime during business hours and making it possible for analysts to work with much more up-to-date and relevant information.

The entire project took six to nine months for the team to complete. Ideally, documentation for business processes, data flows, and reports would be available and provided as an input to a business intelligence project, but all except the last 30 days of the total project time was spent analyzing and producing that documentation. Actual implementation was the easy part.

During the extensive up-front work documenting dataflows and business processes, it was discovered that Performant’s team frequently had a different impression for how things should work than what was reflected in the system they were using. As a result, the new reports differ not only from the ad hoc spreadsheet reports that some business analysts at Performant had been using, but also from the reports that had been created using the previous business intelligence reporting system.

Overall the project was a success. With the new system, Performant’s teams now have consistent, up-to-date, and accurate information from which to make their decisions. The benefits of the project have since been applied a total of six times across business intelligence data warehouses serving student loan recovery and healthcare analysis systems.

Reporting Timeline

THE FUTURE

The business intelligence systems for which the ETL process described in the case study was performed are all currently "batch" systems. Reports are computed by IT and delivered to analysts who need the information for rooting out opportunities. To magnify the benefits of the new healthcare application, AI and Performant are embarking on creation of a self-service business intelligence system. With self-service BI, users will be able to drill down to determine the root causes for what they see in reports. This will make analysts much more efficient as they look for ways to cut fraud and waste, greatly increasing the value of Performant’s business intelligence tools.