Case Study: Performant BIDownload As PDF
Performant Financial Corporation Line of Business: Financial waste avoidance & recovery Based in Livermore, California Over 1,500 employees Annual Revenue: $159M Operates offices in six U.S. locations
Government and healthcare organizations turn to Performant for solutions that prevent fraud and waste. Performant can identify and help recover payments that were made improperly or on the basis of fraud. Its solutions perform analysis, auditing, and recovery functions for use cases such as health insurance, Medicare, and student loans.
To increase its efficiency and competitiveness, Performant wanted to improve the timeliness and accuracy of key business intelligence systems. Those systems provide the foundation for Performant’s analysis of healthcare payments and student loans.
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. The company’s goal was to eliminate or reduce downtime and provide better decision support using information updated on a daily rather than weekly basis.
The experienced engineers on AI’s Data Team devised and ultimately delivered a new, accelerated ETL process. Rather than rebuild everything each processing cycle, the new process cleverly extracts only data relating to accounts with changes. The changes are merged with the existing data warehouse before business starts each day.
The benefits of this project were eventually multiplied six times across four data warehouses serving student loan recovery and two for healthcare analysis.
In 9 months AI delivered a solution that:
- Decreased ETL processing time (i.e. down time) from 48 hours to 2 hours.
- Enables decision-making based on fresh new information produced daily.
- Is resulting in a deeper understanding of the meaning of underlying data and reports.
- Allows Performant’s teams to all work from the same data using consistent, up-to-date, and accurate information.
The AI Data Team is now working on a self-serve business intelligence solution that will allow business users to actively participate in the analysis and drill down to find opportunities for reducing waste and fraud.
Performant needed to increase the performance of a key business intelligence reporting system used for analysis and audit for commercial healthcare and government Medicare payments.
The system was based on a data warehouse that required a “nuke-and-replace” ETL process that took 48 hours to complete. During this time, the data warehouse and reporting system was unavailable.
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."
Perhaps partially because the warehouse data lacked timeliness and partly because the meaning of resulting reports wasn’t always clear, many of 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 which were built using data from still other spreadsheets, introducing numerous opportunities for the introduction of errors. This oftentimes meant that team meetings would be populated by numerous individuals all with differing reports supposedly based on the same original source data.
Performant's business intelligence system produced reports on a lengthy one-week cycle, resulting in business analysts having to work with relatively stale information.
The system required 48 straight hours of downtime every week in order to perform a necessary ETL process.
Performant needed its business intelligence system to deliver more up-to-date, meaningful, and accurate reports based on data of known provenance.
In short, the goals were:
- 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
Performant wanted to improve the timeliness, accuracy, and consistency of business intelligence, improving intraorganization communication and enabling more effective elimination of waste and fraud.
Architecting Innovation (AI) has an expert Data Team with decades of in-the-trenches database, data warehouse, and business intelligence experience. AI’s team knows and understands a wide variety of technologies, including the Microsoft database and DTS software that was being used by Performant.
Plus, AI 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 team up to tune up the business intelligence ETL and reporting system.
AI was already working with Performant on a related project, and the Data Team at Architecting Innovation (AI) together has decades of database experience. So AI was the logical choice for implementing a solution.
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.
The Data Team at Architecting Innovation (AI) devised a clever new system for streamlining the ETL of data into the BI warehouse. Instead of extracting everything, only data related to changes was extracted. That was then merged with the data warehouse rather than replacing it.
The process used by the system was this:
- Extract all of the data to be used for analysis.
- Wipe the warehouse clean.
- Transform and load everything, creating the business intelligence warehouse all over again from scratch.
See the flowcharts, below.
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:
- Extract only data that relates to customers with changes.
- 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.
But it was worth it. The results were amazing.
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. 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.
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 business value of Performant’s business intelligence tools.