Author: Kartikey Brahmkshatriya, Mark Robinson

Data warehouse is the main repository of the organization's data. Data warehouse enables the data analyst to perform complex queries and analysis on the information within data warehouse without slowing down the operational systems. In this article I will discuss the various steps of the testing process and some concerns related to data warehouse testing. The Data warehouse testing consists of below phases

1. Requirements Testing
2. Unit Testing
3. Integration Testing
4. Acceptance Testing

Requirements Testing:

Requirements testing check for the completion of stated requirements. The requirements are mostly around reporting in the data warehouse. Therefore it is important to test if the reporting requirements can be met using the available data.

The business rules and requirements provide a solid foundation to the data architects and therefore the successful requirements should be structured closely to business rules and address functionality and performance. Using the defined requirements and business rules, high level design of the data model is created. Once requirements and business rules are available, rough scripts can be drafted to validate the data model constraints against the defined business rules.

Unit Testing:

Unit testing for data warehouses is white box. It should check the ETL procedures/mappings/jobs and the reports developed. This is usually done by the developers.

Integration Testing:

The next step after unit testing is the integration testing. Integration testing tests the initial and incremental loading of the data warehouse. Integration Testing would cover End-to-End Testing for DWH. Once the ETLs are tested for count and data verification, the data being showed onto the reports hold utmost importance. QA team should verify the data reported with the source data for consistency and accuracy.

Although the data present in a data warehouse will be stored at an aggregate level compare to source systems. Here the QA team should verify the granular data stored in data warehouse against the source data available. QA team must understand the linkages for the fields displayed in the report and should trace back and compare that with the source systems. Create SQL queries to fetch and verify the data from Source and Target. Sometimes it's not possible to do the complex transformations done in ETL. In such a case the data can be transferred to some file and calculations can be performed.

Acceptance Testing:

In acceptance testing phase the system is tested with full functionality and is expected to function as in production. At the end of UAT, the system should be acceptable to the client for use in terms of ETL process integrity and business functionality and reporting.

Data Warehouse Testing: Some Concerns

• Involve the users in testing the system.
• ETL testing should be a priority.
• Through the use of ETL tools, a minimal amount of code may be written for the testing phase. But this can be very misleading in terms of gauging time.
• The amount of code can't predict the number of data issues testing will uncover. Data issues are the bulk of the problems revealed by testing ETL processes, and the fixes can be time-consuming.
• Set aside time to develop reports that tie new data warehouse reports to legacy system reports. Every new business intelligence system brings an element of cultural change to an organization, and cultural change takes time.
• While data validation tests can be very thorough, they typically cannot address every possible data issue that will occur.
• Be sure testers have a grasp of the general level of data quality that will be in the data warehouse and be sure that expectation is communicated to business users well in advance of the testing phase. There are many business processes that have very low tolerances for error, e.g., financial services.
• Make sure there is a certain level of comfort with the quality of data being loaded into the warehouse before investing several days, in some cases weeks, executing ETL processes.
• Surprises erode the reliability of the system and starting over is expensive in terms of time and resources.
• Make sure that the ETL load processing time fits within the existing batch window.
• If the daily refresh process takes 25 hours to complete, testers may need to go back to the drawing board. With some careful thought and simulation of the test environment, estimating time is not as challenging as it may seem.
• Get sign-offs on all phases of testing or stop the project.
• Use subsets of production test data for all tests, especially system and user acceptance testing.
• Implementation of the data warehouse testing with real time data.

Conclusion

Evolving needs of the business and changes in the source systems will coerce continuous change in the data warehouse schema and the data being loaded. Hence, it is necessary that development and testing processes are clearly defined, followed by impact-analysis and strong alignment between development, operations and the business.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Useful Tips for Designing Oracle Data warehouse
   Oracle: Data Dictionary for Database Metadata
   DBA Tips for Verifying Oracle Data Replication!!
   Beware of WHEN OTHERS THEN NULL, Important Concerns!!