Updated May 29, 2023
Introduction to Data Warehouse Testing
Data Warehouse Testing is a series of Verification and Validation activities performed to check for the quality and accuracy of the Data Warehouse and its contents, where the activities need to be focused mainly on the Data, which should commence as a sequence of evaluations like comparing the huge quantities of data, validating the data from multiple different varieties of sources, ETL testing process for the data using SQL queries, Business Intelligence Testing is applied for validating the Functionality, Security, and Performance of the Data Warehouse, determining the capability of the Data Warehouse in supporting Analysis and Report generation, etc.
Data Warehouse Testing Methods
Testing is a word widely used to check the quality of products and correct if any defects are identified.
There are three basic levels of testing –
1. Unit Testing
- The developers can perform unit testing.
- It will be tested by each component separately.
- Eg., Procedure, Program, Scripts, Shell, etc.
2. Integration Testing
- The various modules are brought together and tested against each input.
- It will test whether it is good for integration.
3. System Testing
- It can be tested for the whole application.
- This testing aims to test the entire system to check if it is working correctly.
- The testing team will do this system testing.
- If the data warehouse is huge, minimal testing can be completed before creating the test plan.
Data Warehouse Testing Schedule
The test schedule is the process of creating in developing the test plan. This schedule will create an entire test plan estimation required for the data warehouse. Various methodologies are available to create the test plan, but making a plan for the data warehouse is very complex. There are some issues faced with creating the schedule.
- Some queries take a long time, like a day or two, to complete and retrieve the result.
- Sometimes the hardware may fail, E.g., Disk error, connection issue, or Disk loss.
- Causing human errors like deleting the tables, wrong data updation, etc.
Due to these above challenges, it recommended increasing double the time to allow for testing.
Testing Backup Recovery
Testing the disaster recovery system is the most important strategy.
Below are a few testing scenarios for backup recovery.
- Loss or damage of tables.
- Failure of Media files.
- Loss or damage to control files.
- Loss or damage of redo files.
- Loss or Damage of Archive files.
- Loss or damage of data files.
- Loss or damage of table space.
Data Warehouse Testing Operational Environment
When it comes to the environment, there are many sources to test such as which are explained below:
1. Security
It requires a separate document to test the disallowed operations and how they can be tested in each module.
2. Scheduler
Scheduling software manages the daily operation, which runs automatically within a given period. It has an interface between the software and the data warehouse. The testing process will check the processing of managing the operations.
3. Disk Configuration
Disk configuration needs to check the Input / Output operations of data. It has to validate multiple times to check the different conditions of control.
4. Management Tools
It will test all the management items in the system, such as.
- System Manager
- Event Manager
- Configuration Manager
- Database Manager
- Backup and recovery manager
Data Warehouse Testing Database
Testing the database-related item is for storage and retrieving validation which are as follows:
1. Testing the Database Manager
Testing the database manager is to validate the creation, running, and managing the query operations
2. Testing the Monitoring Tools
It will monitor the entire operations that take place by the system [E.g., If the system triggers an event, it will monitor and store it somewhere in the log file]
3. Testing the Database Features
Here are some of the features which need to be tested.
- How the query works parallel.
- How the index can be created parallel.
- How the data can be loaded in parallel.
4. Testing the Database Performance
The most important thing is to measure the performance while executing the complex query in the data warehouse, which must be normalized. During testing, check the query from multiple users and endpoints to meet the business requirements.
Testing the Application
The Application testing is the final level of the method to test in the GUI mode, and it needs to perform below:
- Have to check each function is working correctly.
- Check the application over some time.
- Testing the beginning and end of each weekly/monthly task.
- Ensure all the managers are integrated correctly and load the queries to meet the expectations.
Logistic of Testing
Finally, we also need to test some logistic functions in the system, such as –
- Backup and recovery functions
- Performance of the query
- Schedule manager
- Monitoring data’s
- Day-to-Day operational methods
- Scheduling software’s
Recommended Articles
This is a guide to Data Warehouse Testing. Here we discuss the introduction, testing methodology, and data warehouse testing schedule. You may also look at the following articles to learn more –
