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.
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 not able to fully verify all the data elements in 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 this was discovered by the stakeholders, doubt was cast on any past, current, and future decisions they have made using the data in these BI reports.
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, part of the software division of RTTS, is the smart Data Testing solution that automates the data validation and testing of Big Data, Data Warehouses, Business Intelligence Reports and Enterprise Applications with full DevOps for Data functionality for continuous testing.
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 which 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, we assisted in the creation of:
- Initial mapping documentation and defined a process in which this information can be maintained going forward.
- Automated tests were developed, once the data mappings were in place, to test each of the systems ETL legs.
- This same strategy was applied to the MicroStrategy reports, using QuerySurge’s BI Add-on feature, as the underlying data for these reports was populated by their ETL process.
The client was provided valuable feedback and direction regarding 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 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 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.