Data Warehouse Testing

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 evaluation 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 which 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

• Unit testing can be performed by the developers.
• It will be tested by each component separately.
• g., Procedure, Program, Scripts, Shell, etc.

2. Integration Testing

• The various modules are brought together and testes against each input.
• It will test whether it is good for integration.

3. System Testing

• It can be tested for the whole application.
• The purpose of this testing is used to test the entire system to check if it is working correctly or not.
• The testing team will do this system testing.
• If the data warehouse is very large and 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. In this schedule, it will create an entire test plan estimation required for the data warehouse. There are various methodologies available to create the test plan, but it is very complex to make a plan for the data warehouse. There are some issues faced with creating the schedule.

• Some of the queries taking a long time like a day or two to complete and retrieve the result.
• Sometimes the hardware may failures E.g., Disk error, connection issue, Disk loss.
• Causing human errors like deleting the tables, wrong data updation, etc.

Due to these above challenges, 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 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 it can be tested in each module.

2. Scheduler

Scheduling software is managing the daily operation which runs automatically with the given period of time. It has an interface between the software and the data warehouse. In the testing process, it will check the processing of being managing the operations.

3. Disk Configuration

Disk configuration needs to check the Input / Output operations of data. Ii 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 takes place by the system [E.g., If an event triggered to the system, it will monitor and store somewhere in the log file]

3. Testing the Database Features

Here some of the features which need to be tested.

• How the query works parallel.
• How the index can be creating parallel.
• How the data can be load 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 and it has to be normalized. During testing to check the query execute from multiple users and endpoints to meets 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 a period of time.
• Testing the beginning and end of each weekly / monthly tasks.
• To ensure all the managers are integrated correctly and in order to 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 and testing methodology of data warehouse testing along with its detailed explanation. You may also look at the following articles to learn more –

1. Data Warehouse Design
2. What is Data Mart?
3. What is a Data Scientist?
4. Types of Data Warehouse