What is BI/Data Warehouse Testing?

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.

Why is BI/data warehouse testing important?

Businesses trust their BI tools to drive strategy and process optimization decisions, but the power of those tools depends upon a complex infrastructure accurately collecting and transforming critical data sources. The slightest data integrity error can have catastrophic impacts that ripple throughout the organization. Inaccurate data quality not only leads to sub-optimal decisions; it also opens the door to regulatory risks.

Typically, data integrity issues go undetected unless domain experts discover a discrepancy in a report. At this late stage, it’s difficult and time-consuming to unravel and remediate the problem. The fact of the matter is that without a data testing strategy, the effectiveness of your BI tool will be compromised.

What are the benefits of BI/data warehouse testing?

• Identify data acquisition (ETL) errors
• Reduce test creation and maintenance time
• Expose defects earlier—when they’re faster and easier to resolve
• Improve data quality
• Eliminate delays from manual testing
• Comply with regulatory requirements (e.g., BCBS 239, Basel III, SOX, etc.)
• Reduce the “testing burden” on business users

How do I get started with BI/data warehouse testing?

To make BI/data warehouse testing a reality in your organization, you need to figure out:

• When and where to start automating
• How to select the right systems for test automation
• How to ensure test coverage across different systems
• What test types to cover
• How to minimize test maintenance

For quick answers to these questions watch BI/Data Warehouse Test Automation: Why, When, and How. You’ll learn:

• What questions and challenges arise over the course of a typical BI/DWH testing initiative
• How to focus limited test automation resources on your greatest business risks
• How to understand complex business rules and determine how much is tested