What are the challenges of BI/data warehouse testing?
A manual QA testing approach in BI is time consuming and costly, and yet it is currently the norm.
Testing of data warehouses usually occurs late in the cycle. Business analysts get dragged into the process of checking reports produced by the business intelligence logic, which adds to their daily workload. When errors are detected, the cause of the error can be anything from a data quality issue stemming from when the data enters the data warehouse, to a data processing issue caused by a malfunction of the business logic along the layers of the data warehouse and its BI components. This is a painstakingly tedious task and often results in many hours of manual effort.
Moreover, a diverse set of technical skills are required to create and maintain data integrity tests that expose and prevent data integrity issues. These technical skills include ability to script, knowledge of databases and a fundamental SQL skills. Even for a person skilled in this “art”, tests are tedious and difficult to maintain when there is an underlying change to the system. This problem is further exacerbated when highly technical people create tests and hand them over to business or administrative teams for execution and maintenance.
Testing complexity is also increased by the fact that most organizations have heterogeneous environments made up of multiple types of source systems (e.g. Oracle, MS SQL, DB2) where data acquisition requires technical layer transformation or scrubbing to ensure it is complete, correct and ready for consumption.