Chapter 7 – Data warehouse and Test Condition and Test Case design- Reuse or Automate using EXCEL

Posted on Updated on

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 – (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 :


Click on Create then 🙂


Hope you guys understood How to write test case and test conditions for BI Projects.

See you guys in my Next blog.




21 thoughts on “Chapter 7 – Data warehouse and Test Condition and Test Case design- Reuse or Automate using EXCEL

    Karthik said:
    March 21, 2014 at 5:35 pm

    Very informative , loved the blog

    Karthik said:
    March 25, 2014 at 10:24 am

    Very informative , Thanks Asik for a wonderful post , could you please send me some test case documents over the mail.


    Will said:
    June 14, 2014 at 8:03 am

    Good article and great tool. I’ve sent you an email requesting a copy. Thanks Will

      asikfromindia responded:
      June 14, 2014 at 11:55 am

      Thanks will, will check my mail and sent you the same !!!!

        Will said:
        June 15, 2014 at 1:22 pm

        Thanks, I look forward to receiving it.

    Will said:
    June 17, 2014 at 9:17 pm

    Hi Asik, I hate to be a pain, but when will you be able to send it? Thanks

    Will said:
    June 24, 2014 at 9:34 am

    Hi Asik, I haven’t received the .xls. Have you sent it?

    buy cheap followers on twitter said:
    June 24, 2014 at 6:57 pm

    Appreciating tthe hard wokrk you put into your site and
    detailed information you offer. It’s nice to come across a blog every once in a while
    that isn’t the same unwanted rehashed material.

    Fantaswtic read! I’ve bookmarked yojr site and I’m adding
    yoiur RSS feeds to mmy Google account.

    Hassan said:
    November 27, 2014 at 2:55 pm

    Please send me the Excel file for TCS.

    George said:
    June 3, 2015 at 8:48 am

    Dear Asik, Great work, would it be possible for you to send me the test case document please?

    Rajan said:
    September 12, 2015 at 4:27 pm

    I need all the document related to ETL Testing..Please please please mail me at

    Bhavana said:
    February 8, 2016 at 4:47 pm

    Hi, Can you please mail me the test case document

      asikfromindia responded:
      February 25, 2016 at 2:57 am

      whats ur email?

        Ratna said:
        March 16, 2016 at 4:01 pm

        very useful utility. Can you please send the excel to my mail id

    ratnasri vankayala said:
    March 16, 2016 at 5:42 pm

    Very good utility. Can you please send it to my email ?

    Dillip said:
    July 27, 2016 at 12:35 pm

    Great article and great tool to have. I’ve sent you an email requesting a copy. Can you please reply and forward the excel to my mail. Thanks Dillip

      Dillip said:
      August 1, 2016 at 9:52 am

      Hi Asik, I haven’t received the .xls. Have you sent it yet?

    Moynul Islam said:
    November 18, 2016 at 11:29 am

    How Can I Get the above test case documents over the mail?

    bob said:
    February 16, 2017 at 7:54 pm

    thats perfect – can you send me a mail too of the document to
    thanks in advance

    Ashish said:
    February 25, 2017 at 4:24 pm

    This looks fantastic..Please send it to me on

    Thanks in advance.

    Sudha said:
    November 13, 2017 at 10:59 pm

    this is very useful. can you please send me a copy of the test cases documents on

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s