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.
The benefits of this project were eventually multiplied six times across four data warehouses serving student loan recovery and two for healthcare analysis.
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…
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.
The process used by the system:
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:
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.
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
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
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.