In the domain of agile and data warehouse (DW) project testing, a DW Master Test Plan (MTP) is often considered an out-dated practice, even unnecessary. With this viewpoint, project teams may overlook long-standing motives and rationale for a project-wide DW MTP.
A DW “Master Test Plan” represents the plan of action and processes designed to accomplish quality assurance from beginning to end of a DW development lifecycle. The document describes all planned testing for each SDLC phase and how QA will be managed across all levels of testings (ex., unit, component, integration, system testing, etc.). The MTP provides a high-level view of the quality assurance policies and strategies to be implemented (based on ISO/IEC 29119).
Such a plan is often developed using the DW project’s “DW Project Vision” document, business and technical requirements, data dictionaries, data models for source and target schemas, data mappings, and extract, transform, load (ETL) and business intelligence (BI)/ analytics application specifications.
Since the main objective of a quality assurance (QA) and testing process is to purge the data warehouse of the most serious and disruptive bugs, ad-hoc/ unplanned testing without an over-riding master plan would be ineffective in reaching a project’s quality objectives.
Well defined DW data quality/ testing objectives in the MTP at the onset of a DW project are vital. Such objectives will be a guiding influence to keep the project teams’ focus on building and maintaining DW quality while under development rather than delaying until the end of the project to inspect the results.
Objectives and contents of a Master Test Plan
A DW master test plan should describe the testing strategy/ approach for the entire DW and software development cycle. The MTP is intended for the project team so that they may plan and carry out all test activities, evaluate the quality of test activities, and manage those test activities through to successful completion.
The MTP should be published and distributed for approval by business and technical stakeholders’ to inform everyone about key areas of the planned testing process: Testing and quality objectives, scope and constraints of planned testing, test environments, test data sources, methods of testing, QA tools, processes, schedule, and testing resources required for the project.
The MTP also summarizes the test team’s objectives for: work products, testing procedures, testing assumptions, project risks, QA entry and exit criteria, testing roles and responsibilities (including those of business analysts, developers, users, etc.), defect tracking and reporting process, and change control process.
Categories of planned DW testing
The types of quality assurance verifications conducted during unit, integration, system, and acceptance tests (described in the DW MTP) should be the following:
- Business requirement verifications
- ETL testing
- DW testing (record counts, data quality, performance, etc.)
- System integration testing
- Functional and nonfunctional business requirements and
- Technical requirements testing
Another focus of the DW MTP is the end-to-end DW test process to include:
- Validating the loading of all required DW tables
- Correct execution of all data transformations according to business rules and reporting requirements, and
- Successful completion of data cleansing operations
And, remember to identify for stakeholders, the categories of testing that are targets for automation.
Skills and experiences needed for DW testing
The MTP should demonstrate that DW testing is a unique endeavor that requires specific knowledge and skills:
- A firm knowledge of DW structures, analytics and database concepts
- Advanced expertise with database queries
- Expert data profiling methods & tools skills
- Experience with data analysis functions in MS Excel or other specialized tools
- Skills to develop DW/ BI test plans
Testing challenges faced by the DW project
Known testing challenges, and an approach to each, should be described in the MTP; doing so will serve as an important aid in test planning. A few of those challenges often include:
- A lack of descriptive precision in requirements documents
- Frequently changing business and technical requirements
- Heterogeneous, complex, and massive data movement
- Missing and duplicate data to be identified, corrected, and tested
- Data that must be transformed and cleansed (often resulting in complex testing)
- A dearth of commercial or open-source DW testing tools
Quickly learn how you can identify and reduce DW/ BI data quality risks
An MTP should document major priorities to be placed on reducing business and technical risks associated with the development, deployment, and operation of the data warehouse. Through the use of appropriate test cases and allocation of appropriate testing resources, risks will be significantly reduced and the effort to perform testing, in units of time, can be made more effective and efficient. The MTP will likely enable the project teams to develop a superior product and lay a strong foundation for each iteration.
Conclusion
An effective DW Master Test Plan is the cornerstone of the entire DW verification effort. The MTP will act as a guide for test engineers to develop the entire variety of detailed test plans: unit, ETL, integration, performance, security, end-to-end, and BI test plans.
Each project has different needs and thus each DW project may require a different MTP template. An MTP should be the document stakeholders reference to understand what will be tested and the approach for each testing task.
The DW MTP should provide visible confirmation to project stakeholders that adequate consideration will be given to governing the test effort and, where appropriate, to require stakeholders review and approve the MTP.
