ETL - quick review notes - kindly edit it if it is wrong

ETL - quick review notes - kindly edit it if it is wrong

Postby swati123 » Sun Jan 12, 2014 1:19 am

Hi Durga,

Your ETL sessions are excellent and easy to understand as u teach it in a very simple language.
I really appreciate your knowledge Transfer skills, efforts, patience level to attend each and everyones questions and answer them properly.

I hv prepared a quick review notes on ETL - based on your 4 days lecture.
Please check it & correct me wherever i m wrong. It will be a gr8 help for all of us.
ETL : Extraction, Transformation and Loading
- ETL process is genrally done by ETL developers & make ETL programmes.
- There are different tools in market to integrate the Data in Database eg :- Informatica powercenter (informatica corporation), Datastage(IBM/agnishow corporation), Chasper etc.
- OLTP (current data/ transactions) & OLAP(historic data/ transactions) are two types of Datas which are stored in DB and later in DWH by using ETL tool - eg. informatica.
DWH has 2 types of tables
- Fact tables (FT)

o Types of FT are :-
o Additive facts : when Fact Table joins with Dimensional Table within the schema & gives only summarized information is called as additive facts.
o Non-additive facts : when Fact Table joins with Dimensional Table within the schema & gives only detailed information is called as non additive facts
o Semi –additive facts: when Fact Table joins few of the Dimensional Table within the schema & gives summarized and detailed information is called as semi additive facts.
o

- Dimentionsal tables (DT):
o Types of DT are :-
1) Slowly changing Dimensional Table (SCD)
o SCD1 :
Here inserts are inserts and updates are updates (deals with current data)
o SCD2: Here inserts are inserts and updated are also inserts
o SCD3 : Here SCD3 maintains and update latest two records in DB
-so inserts are inserts, updates in one source column will update 2 columns of your target table.
2) Confirmed Dimensional Table (CDT) : When common DT are used in two fact tables, such kind of DTs are called as CDT.
3) Junk Dimensional Table (JDT) :- Here fact tabl and Dimensional tables are combined and displays combined data.
In DWH there are 2 types of schemas under which tables are organized
It is logic in which datas are placed in table.
- Star Schema : A Fact Table surrounded by different Dimensional Tables
- Snowflakes schema : It is extended version of Star Schema.
 A fact table is as usual surrounded by dimentional tables and these dimentional tables are further join to master dimentional table (i.e. normalized table).
- Enterprise warehouse schema
2 approaches in DWH
o Top to Bottom approach : If the DataMart is created first and then the Enterprise Warehouse Schema(EWHS) such type of arrangement is called as Bottom to top approach.
o Bottom to top approach: If Enterprise Warehouse Schema(EWHS) is created first and then the DataMart such type of arrangement is called as Bottom to top approach.
Operational Data store : This is general used for quick reports & is a part of reporting.

Thank you so much,

Swati
swati123
 
Posts: 4
Joined: Thu Sep 19, 2013 1:20 am

Return to ETL - Data warehousing Testing

Who is online

Users browsing this forum: No registered users and 1 guest

cron