Data Warehouse Testing

Mayank Pundir
August 20th, 2015

Content

  • Introduction
  • Types of DWs systems
  • What is ETL?
  • Types of DWs Testing

Introduction

What is Data warehouse?
A data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis. DWs are central repositories of integrated data from one or more disparate sources. They store current and historical data and are used for creating analytical reports for knowledge workers throughout the enterprise. Examples of reports could range from annual and quarterly comparisons and trends to detailed daily sales analyses.

Types of DWs systems

  • Data mart
    The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts are small slices of the data warehouse.
  • Online Transaction Processing (OLTP)
    The OLAP contains Transactional data containing INSERT, UPDATE and DELETE.
  • Online analytical processing (OLAP)
    The OLAP conatins low volume of transactions. Queries are often very complex and involve aggregations.
  • Predictive analysis
    The Predictive analysis system is also called as CRM(Customer relationship management).Is is used to finding parttern in data by using complex mathematical models that can be used to predict future outcomes

How it works?

Image

The dataware house works on the concept of Extract, Transform and Load (ETL):
- Extracts data from homogeneous or heterogeneous data sources
- Transforms the data for storing it in proper format or structure for querying and analysis purpose
- Loads it into the final target (database, more specifically, operational data store, data mart, or data warehouse)

Types of DWs Testing

  • Reconciliation testing
    Sometimes, it is also referred as Source to Target count testing. In this check, matching of count of records is checked. Although this is not the best way, but in case of time crunch, it helps.
  • Constraint testing
    The test analyst, maps data from source to target and identify whether the data is mapped or not. Following are the key checks: UNIQUE, NULL, NOT NULL, Primary Key, Foreign key, DEFAULT, CHECK.
  • Validation testing (source to target data)
    Here, test analyst, validates each data point and match source to target data.
  • Testing for duplicate check
    It is done to ensure that there are no duplicate values for unique columns. Duplicate data can arise due to any reason like missing primary key etc.

Types of DWs Testing Cont..

  • Testing for attribute check
    To check if all attributes of source system are present in target table.
  • Logical or transformation testing
    Depending upon the scenario, following methods can be used: boundary value analysis, equivalence partitioning, comparison testing, error guessing or sometimes, graph based testing methods. It also covers testing for look-up conditions.
  • Incremental and historical data testing
    Test to check the data integrity of old & new data with the addition of new data. It also covers the regression test scenarios.
  • GUI/navigation testing
    To check the navigation or GUI aspects of the front end reports with respect to backend data.






Thank You!