Can normalised and denormalised tables can be joined - ETL

Can normalised and denormalised tables can be joined - ETL

Postby swati123 » Tue Jan 07, 2014 7:05 pm

can v join two tables - one with normalised data hvg primary key and second table with denormalised data not having primary key to get the result of particular employee who is working for 15 years?
swati123
 
Posts: 4
Joined: Thu Sep 19, 2013 1:20 am

Re: Can normalised and denormalised tables can be joined - E

Postby Durgak » Tue Jan 07, 2014 7:47 pm

Swathi,

There is a posiblity that we will be joining a normalized table and denormalized table in ETL. In case of Snow flake star schema, the dimension tables (which are denormalized) will have reference to the Normalized tables (Master dimension tables).

Thanks,
Durga
Durgak
 
Posts: 2
Joined: Tue Jan 07, 2014 7:35 pm

Re: Can normalised and denormalised tables can be joined - E

Postby swati123 » Tue Jan 07, 2014 7:55 pm

Can you give one eg. to understand this is much better way.

Thanks in advance,
swati
swati123
 
Posts: 4
Joined: Thu Sep 19, 2013 1:20 am

Re: Can normalised and denormalised tables can be joined - E

Postby Durgak » Tue Jan 07, 2014 8:39 pm

Sure Swathi.

We have two different schema's in dataware house.
1. Star Schema: A fact table surrounded by dimension tables will form as a star schema.
Disadvantage: As the dimension table size increases, there can be a chance of multiple columns carrying the redundant data (duplicate data) where by the performance degrades. To over come this, snow flake star schema has come into picture which is an extension of star schema.

2. Snow flake star schema : A fact table surrounded by dimension tables and these dimension tables will have reference to the master dimension tables.

Lets take the example of Walmart store as the table.

Storeid Storename location city state country
1 Walmart Haywood rd Greenville South Carolina US
2 Walmart woodsruff rd Greenville South Carolina US
3 Walmart pleasant burg Greenville South Carolina US
4 Walmart Daniel Island Charleston South Carolina US
5 Walmart Summerville Charleston South Carolina US
6 Walmart Isle of Palms Charleston South Carolina US
7 Walmart Market Street Charleston South Carolina US
8 Walmart George Street Richmond Virginia US
9 Walmart Park Lane Richmond Virginia US
10 Walmart Sumter Rd Richmond Virginia US

From the above table, you can see that there are three columns that are repetative in nature. When the table size grows, these repeatative columns will degrade the performance of the dataware house ( as these tables are used in Join conditions)

to over this disadvantage, the same table is split into two, store, a dimension table (de-normalized) and territory, a master dimension table (normalized) as below.

store table
------------------
Storeid Storename location territoryid
1 Walmart Haywood rd 1
2 Walmart woodsruff rd 1
3 Walmart pleasant burg 1
4 Walmart Daniel Island 2
5 Walmart Summerville 2
6 Walmart Isle of Palms 2
7 Walmart Market Street 2
8 Walmart George Street 3
9 Walmart Park Lane 3
10 Walmart Sumter Rd 3

territory table
----------------

territoryid city state country
1 Greenville South Carolina US
2 Charleston South Carolina US
3 Richmond Virginia US


here territoryid will be the primary key in the territory table( normalized table) which is seen as foreign key in store table ( denormalized table).


Thanks,
Durga
Durgak
 
Posts: 2
Joined: Tue Jan 07, 2014 7:35 pm

Re: Can normalised and denormalised tables can be joined - E

Postby swati123 » Tue Jan 07, 2014 10:24 pm

Thank you so much, u explained it very well.
Though i m from non-IT background and new to ETL topic....u explained me in a very descriptive manner.....:)

Thanks a ton,
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