Data Warehouse Testing Categories

Good part of the testing of data warehouse testing can be linked to 'Data Warehouse Quality Assurance'. Data Warehouse Testing will include the following chapters:

Extraction Testing

This testing checks the following:

• Data is able to extract the required fields.
• The Extraction logic for each source system is working
• Extraction scripts are granted security access to the source systems.
• Updating of extract audit log and time stamping is happening.
• Source to Extraction destination is working in terms of completeness and accuracy.
• Extraction is getting completed with in the expected window.

Transformation Testing

• Transaction scripts are transforming the data as per the expected logic.
• The one time Transformation for historical snap-shots are working.
• Detailed and aggregated data sets are created and are matching.
• Transaction Audit Log and time stamping is happening.
• There is no pilferage of data during Transformation process.
• Transformation is getting completed with in the given window

Loading Testing

• There is no pilferage during the Loading process.
• Any Transformations during Loading process is working.
• Data sets in staging to Loading destination is working.
• One time historical snap-shots are working.
• Both incremental and total refresh are working.
• Loading is happening with in the expected window.

End User Browsing and OLAP Testing

• The Business views and dashboard are displaying the data as expected.
• The scheduled reports are accurate and complete.
• The scheduled reports and other batch operations like view refresh etc. is happening in the expected window.
• 'Analysis Functions' and 'Data Analysis' are working.
• There is no pilferage of data between the source systems and the views.

Ad-hoc Query Testing

• Ad-hoc queries creation is as per the expected functionalities.
• Ad-hoc queries output response time is as expected.

Down Stream Flow Testing

• Data is extracted from the data warehouse and updated in the down-stream systems/data marts.
• There is no pilferage.

One Time Population testing

• The one time ETL for the production data is working
• The production reports and the data warehouse reports are matching
• T he time taken for one time processing will be manageable within the conversion weekend.

End-to-End Integrated Testing

• End to end data flow from the source system to the down stream system is complete and accurate.

Stress and volume Testing

This part of testing will involve, placing maximum volume OR failure points to check the robustness and capacity of the system. The level of stress testing depends upon the configuration of the test environment and the level of capacity planning done. Here are some examples from the ideal world:

• Server shutdown during batch process.
• Extraction, Transformation and Loading with two to three times of maximum possible imagined data (for which the capacity is planned)
• Having 2 to 3 times more users placing large numbers of ad-hoc queries.
• Running large number of scheduled reports.

Parallel Testing

Parallel testing is done where the Data Warehouse is run on the production data as it would have done in real life and its outputs are compared with the existing set of reports to ensure that they are in synch OR have the explained mismatches.

Security Framework testing

Check all possible aspects of Security Framework.