Case Study

Background

A leading global private equity firm was seeking guidance on implementing an automated data testing solution to help identify problems in the ETL process and eliminate data discrepancies from their MicroStrategy business intelligence (BI) reports. 

Client’s Challenges

The client’s existing manual testing efforts faced significant challenges when trying to evaluate the millions of data points produced by their ETL process. As a result, the QA team was unable to fully verify all the data elements within their designated test cycles. 

• This lack of coverage meant that most of the data displayed in their MicroStrategy BI reports went untested. 
• This inevitably led to data defects in production 

Once the stakeholders discovered this, doubt was cast on any past, current, and future decisions they have made using the data in these BI reports. 

Solution

RTTS was brought on to analyze the client’s current testing strategy and demonstrate how an automated testing solution, such as QuerySurge, could be implemented to improve their data testing process. 

QuerySurge is an enterprise-grade data quality platform that automates the validation of data across your entire ecosystem ‐ from data warehouses and big data lakes to BI reports and enterprise applications. 

Using their own ETL data sets, the client was presented with multiple testing approaches and real-world examples of each. 

• Implement integrated test automation throughout their Data Warehouse architecture, including at the MicroStrategy Report level. 
• An automated testing approach that involves testing each ETL leg over the entire ETL process, which requires clearly defined data mappings for each leg of the ETL process. 

These data mappings are the key to successful data validation and define the transformation logic that should be included in each test. 

At the start of the engagement, there was no structured documentation process in place. Working together with the QA, BA, and Development teams, RTTS assisted in the creation of: 

• Initial mapping documentation and defined a process in which this information can be maintained in the future. 
• Automated tests were developed, once the data mappings were in place, to test each of the system’s ETL legs. 
• This same strategy was applied to the MicroStrategy reports, using QuerySurge BI Tester, as the underlying data for these reports was populated by their ETL process. 

Benefits

The client received valuable feedback and direction on their current ETL testing process. A test automation foundation was developed with QuerySurge, which provided complete end-to-end data validation and streamlined their data testing process. Integrating these automated tests with Azure DevOps allowed the client to initiate tests quickly and view results with ease. 

Previously, the QA team spent much of its time manually analyzing and comparing results. Test execution for their MicroStrategy regression had to be completed over several days. With QuerySurge being used for this comparison and analysis, the execution time is now measured in hours. 

The time savings have allowed the QA team to develop additional automated tests and expand their coverage over the ETL process.