Testing & Business Intelligence Projects

Like most systems, BI / DW projects require “front-end” functional testing, which is usually focused on validating the information being produced by “reports”, e.g., a dashboard expressed on a web portal. However, BI / DW testing best practices suggest rigorous testing on the “back-end”, i.e., testing data integrity within source and target databases, OLTP systems, and during ETL/OLAP processes. The reason for this is the very nature of a Data Warehouse, which pulls information from myriad sources and whose content is constantly changing. Common challenges:

  • Large volume of duplicate data or incomplete data extracted from source systems
  • Poor data cleansing processes
  • Malformed aggregation processes resulting in dropped data during transformation
  • Incorrect mapping of dimensions in "cube data"
  • Data Volatility (frequency of change)

The following are typical BI / DW “Backend” testing best practices:

  • Test Plans: composed of test strategies (which support/enhance the development methodology), test cases, and templates to track defects and test results
  • Data Validation: Execute SQL queries against source and target databases
  • Data Comparison: SQL queries to compare data at each stage of transformation, including data retrieved via OLTP systems
  • Test Frameworks: Custom-built, reusable test utilities to populate data from source systems