Chapter 7 – Data Warehouse and Test Condition and Test Case Design- Reuse Or Automate Using EXCEL

Hi All,

In this blog I am going to explain how to write Test Conditions (What you are going to do?) Test Cases (How you are going to do?).

I am using a Excel Macro that will generate the TCN and TCA easily, the only thing is you need to configure.

I am not able to attach here – if you need it please drop me an EmailTo – [email protected] (please find the screen shots at the end of this blog)

One who doesn’t like when he asked to write test cases

Let me explain you how to set up Test Conditions (TCN) and Test Cases (TCA) .

As I said already in my earlier blogs we have below types of DWH testing

1. Loads from file to staging tables to IM tables
2. Loads from table to staging tables to IM tables
3. DIMN/FACT projects
4. Extract projects
5. Reports

You need to know how to reuse your TCN and TCA ! write it for one project and use FIND and REPLACE for other projects !! confusing?

For the Loads from file to staging tables to IM tables we will set up TCN and TCA to validate Source file, verification of data load into Staging tables and verification of data transformation of load into target tables. Suppose you have prepared for PROJECT_A where Source is file is File_1.DAT and Staging table is Staging_Table1 and target tables is Target_Table then use the same test cases by replacing File_2.DAT and Staging table is Staging_Table2 and target tables is Target_Table2 for Project_B.

Don’t Understand ??

For project A I designed the steps and for Project B I replaced with attributes related to Project B

Source File Validations (Steps) :

Staging Loads :

Verification of Staging is very easy, It will be one to one mapping, loading all the data from Source file or system into Intermediate tables.

Staging Data into Target Tables:

Staging (more than one STG) data will be loaded into one or more than one Target tables. But we need to write test cases for each tables.

Please cover below scenarios:

1. Reconciliation check – record count between the STG tables and target tables are same after applying filter rules

2. Inserting a record which is not loaded into target table for given key combination

3.Copy records , sending same records (same key ) which is already loaded into target tables – should not be loaded

4. Updating a record for a Key when Value columns changed on Day_02 loads

5. Logically Delete the records in the Target tables

6. values loaded by ETL Code

7. Values loaded by Process Tables

8. Values loaded by Reference Tables

(If you get any special scenarios please add them )

Write test cases for Target Table_1 and reuse them for other tables.

In the Excel sheet , just configure the rules as you require feed the Source and Target column, and Click Create button it will give you the Test Case or Test Conditions as you configured.

Configuring :