image

BI / DWH Testing

Considerations for Testing BI Reports

Editor’s note: Wayne Yaddow is an independent consultant with over 20 years’ experience leading data migration/integration/ETL testing projects at organizations including J.P. Morgan Chase, Credit Suisse, Standard and Poor’s, AIG, Oppenheimer Funds, IBM, and Achieve3000.  Additionally, Wayne has taught  IIST (International Institute of Software Testing) courses on data warehouse, ETL, and data integration testing. He continues to lead numerous ETL testing and coaching projects on a consulting basis. You can contact him at wyaddow@gmail.com.

This is the fifth and final blog in a series on  Identifying Data Integrity Issues at Every DWH Phase.

[ part 1part 2 | part 3 | part 4 ]

Finally, another big share of DWH/BI testing is related to front-end report testing. The DWH/BI project team should ensure that everything tested in the back-end DWH and ETLs is now handled and displayed correctly in end-user reports. BI reports are often the most visible product of any DWH/BI system. Data quality defects that are not discovered during report verifications present a great risk of jeopardizing the entire DWH/BI solution’s credibility.

In the context of DWH/BI, the report testing phase is often not so much focused on the functions of the user interface and logic flows, but rather, the focus is on the quality of data. For testing purposes, it is usually challenging to separate the DWH/BI back and front-end testing efforts. It is of crucial importance to understand the entire flow of data from source to reports so that testers can better understand and critically think about the information the report is displaying. Since we have already focused on ETL/DWH challenges, in this section we briefly refer to the most common report testing challenges.

First, testing reports means that testers will need to be flexible in adapting to the different technologies in which reports might be delivered. Technologies might range from specific vendor tools, 100% custom-developed technologies, or something in between such as a hybrid between a vendor tool that was customized in-house to respond to business-specific needs. This means that sometimes the tester is not only testing the report, but also struggling with the environment and tools where the report is running.

As stated before, testing reports should not be isolated from the ETL/DWH; that is, report testers likely need profound knowledge of the processes that stream data to the end-user reports. Report testers should backtrack errors to their source – which may be source data, or the staging and transformations before load to the DWH.

In the end, it’s important to consider that report testing is about giving the end user the right information at the right time, and in the correct format. We recommend focusing on the following topics:

  • Base and derived data correctness (e.g., calculations)
  • Data aggregation accuracy (e.g., totals and sub-totals)
  • Entity attribute hierarchies (e.g., entry points)
  • Report layout (e.g., usability)
  • Prompts (e.g., invalid entries) and filters (e.g., cascade filtering)
  • Summarized and aggregated data
  • Table/chart formatting (e.g., rounding, decimal places) and naming conventions
  • Drilling, sorting and export functions (e.g., export to Excel)
  • Web browser compatibility
  • Linking and traceability among tables

And, due to the level of criticality and high visibility within companies, report testing should include a robust focus on delivery performance and security.

Conclusions

The suggested levels of DWH/BI testing rigor frequently require additional effort and skilled resources. However, by employing these methods, DWH/BI teams can be assured from day one of their data warehouse implementation and data quality. Doing so builds confidence in the end-user community and will ultimately lead to more effective DWH/BI implementations.

Many automated DWH/BI testing tools have become available to support the entire development lifecycle. Consider the best of these, particularly for end-to-end, data integration, data profiling, and regression testing.

Testing data warehouse and BI applications requires superior testing skills as well as active participation in requirements gathering and design phases. Moreover, in-depth knowledge of DWH/BI concepts and technology is crucial for understanding end-user requirements and therefore contributing to a reliable, efficient, and scalable design.

[Read Wayne’s paper on BI and Data Warehouse Testing: Identifying Data Integrity Issues at Every DWH Phase]