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 BI reports. RTTS was brought on to analyze the client’s current testing strategy and demonstrate how an automated testing solution could be implemented to improve their data testing process.

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. Their QA team was not able to fully verify all the data elements in their designated test cycles. As a result, several critical issues arose:

• Inadequate Coverage: Most of the data displayed in their MicroStrategy BI reports went untested.
• Production Data Defects: This lack of comprehensive testing led to numerous data defects in the production environment.
• Stakeholder Concerns: Discrepancies discovered by stakeholders undermined confidence in past, current, and future decisions based on the data in these BI reports.Discrepancies discovered by the stakeholders cast doubt on any past, current, and future decisions they have made using the data in these BI reports.

Solution

RTTS recommended an automated testing approach which involves testing each ETL leg over the entire ETL process. Using their own ETL data sets, the client was presented with multiple testing approaches and real-world examples of each. Key components of the strategy included:

• Implementing 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 for 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 going forward.
• Automated test development, once the data mappings were in place, to test each of the systems’ ETL legs.

This same strategy was applied to the MicroStrategy reports as the underlying data for these reports was populated by their ETL process.

Benefits

The client was provided valuable feedback and direction regarding their current ETL testing process. A test automation foundation was developed 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 was spending much of their time analyzing and comparing results manually. Test execution for their MicroStrategy regression had to be completed over several days. With an automated ETL testing solution 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.