Chapter 8 – Business Intelligence – Do you know how to Make Cofee? Then you know (E)xtract T(ransform) & L(oad)

Posted on Updated on

Hi All,

[I am using Civil Engineers and Coffee making scenarios for Better Understanding for Beginners]

Most widely spoken concept in Business Intelligence world is ETL – Extract Transform and Load. The persons who are in IT field already they call it as ETL Architecture πŸ™‚ .

ETL Architecture :

Who use the word Architecture most commonly ? – Civil Engineers. They say Building Architecture so when you are constructing something you are using the word Architecture. You cant construct anything without prior planning. So a Civil engineer will draw a diagram that will tell how the building going to look like.

Likewise in Business Intelligence world, we have Solution Designers (Civil Engineers) who designs the ETL System and they are also called System Architectures!!!

So now you understood why we are calling it as ETL System Architect πŸ™‚

Now let me explain how Extract Transform and Load process works.

How to make Coffee?

Asik in the below image is preparing coffee for his wife 😦 .

Image

In the first step, he is preparing decoction (extracting the essence of the coffee from Beans).

In the second step, he is transforming it to Coffee by adding correct proportion Milk,Sugar

In the third Step, he loaded the Coffee into Container for Drinking

After drinking the coffee (testing) he found (defects) he needs to add more sugar 😦 he added and ready for (reporting) studying.

Now how ETL works ?

Image

Developers involved in designing the Extract Transform Load Jobs. What is a Job? to make it understand ! we can say it is a Function.

These jobs will be written using Unix Scripting.

Image

Extract Job (decoction ready)Β will bring all the data into Staging tables – testers needs to verify all the data is moved from file to Staging table

Transform Job (added milk and sugar) will apply rules and filter the records , alters the records as per documents – testers needs to verify all the data transformed as expected

Load Jobs (coffee ready )Β once the transform jobs ran successful then Load jobs will load the data into Intermediate tables (will be used for delta check in Day_02) – testers needs to verify the the transformed data is loaded into target tables

1.In Load job we have Insert as well as Delta

Hope you guys understood the ETL Logic!!!

In my next blog, I will explain about INSERTS , UPDATES , Logically DELETES

Cheers

Asik

Advertisements

12 thoughts on “Chapter 8 – Business Intelligence – Do you know how to Make Cofee? Then you know (E)xtract T(ransform) & L(oad)

    Dwiti said:
    December 10, 2013 at 4:54 pm

    Hahahaa…
    For a non IT person & someone who does it daily, you make it sound like a tough series of trial and error…. you can almost make a flowchart πŸ™‚

    Raveendra Reddy said:
    December 14, 2013 at 5:00 pm

    Thank You Asik……….

    […] Please Check my ETL Process post how the data is loaded and what we need to verify from this Step : […]

      Venkat said:
      December 16, 2013 at 4:29 pm

      Thanks Asik

    Srinivas said:
    December 22, 2013 at 6:20 am

    Hi Asik
    I am a Manual tester learning ETL testing now. Thanks for posting valuable information it is very helpfull for those who are n ew to ETL testing

    1.If I use informatica as ETL tool, as a tester how I need to verify at the staging that data has transformed as per the business logic
    2.How to validate the data is loaded successfully into target table(do I need to write sql queriis in target database and check)

      asikfromindia responded:
      December 22, 2013 at 6:35 am

      Hello Srinivas,

      It doesnt matter what tool is used to load the data into warehouse, we need to verify the data as per the transformation logic.

      You need to write SQL query against Source Tables with all the rules and get the result set from source then simply select the Target tables and verify the data that you got from Source Query and the data loaded into Target tables are same

    Srinivas said:
    December 26, 2013 at 2:54 pm

    Thanks Asik,

    Srinivas said:
    December 26, 2013 at 3:02 pm

    Need some clarification here ..1.Do we need to test source table also as per the transformation logic or only the target table
    2.If my source is a flat file then how to verify it the source table (we cannot writ the queries riight?)

      asikfromindia responded:
      March 6, 2014 at 10:05 am

      What you said is correct !!!! Please load the flat file into a temp table and write queries to validate or manually eye ball the fields as per the specifications

        tarak said:
        October 13, 2014 at 11:14 am

        source is flatfile and relational db how to validate the data?

    Shruts said:
    July 3, 2014 at 11:22 am

    Yes , That would work . Thanks!!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s