Rahman

• Jan 31st, 2006

 

Because in Data warehousing historical data should be maintained, to maintain historical data means suppose one employee details like where previously he worked, and now where he is working, all details should be maintain in one table, if u maintain primary key it won't allow the duplicate records with same employee id. so to maintain historical data we are all going for concept data warehousing by using surrogate keys we can achieve the historical data(using oracle sequence for critical column).

so all the dimensions are marinating historical data, they are de normalized, because of duplicate entry means not exactly duplicate record with same employee number another record is maintaining in the table.

gazulas

 Profile Answers by gazulas Questions by gazulas

• Jan 31st, 2006

 

dear reham thanks for ur responce,,,,,  First of all i want to tell one thing to all users who r using this site.  please give answers only if u r confident about it. refer it once again in the manual its not wrong. If we give wrong answers lot of people who did't know the answer thought it as the correct answer  and may fail in the interview. the site must be helpfull to other , please keep that in the mind.

regarding why dimenstion tables r in denormalised in nature.

i had discussed with my project manager about this. what he told is :->

The attributes in a dimension tables are used over again and again in queries. for efficient query performance it is best if the query picks up an attribute from the dimension table and goes directly to the fact table and do not thru the intermediate tables. " if we normalized the dimension table we will create such intermediate tables and that will not be efficient

sm1506

• Apr 11th, 2006

 

Yes, what your manager told is correct. Apart from this, we maintain Hierarchy in these tables. Maintaining Hierarchy is pretty important in the dwh environment. For example, if there is a child table and then a parent table. if both child and parent are kept in different tables, one has to every time join or query both these tables to get the parent child relation. so if we have both child and parent in the same table, we can always refer immediately. this may be a case.

Similary, if we have a hierarchy something like this...county > city > state > territory > division > region > nation

If we have different tables for all, it would be a waste of database space and also, we need to query all these tables everytime. Thats why we maintain hierarchy in dimension tables and based on the business, we decide whether to maintain in the same table or different tables.

na123

 Profile Answers by na123 Questions by na123

• Aug 22nd, 2006

 

hello everyone,

 i don't know the answer of this question but i ve to tell u that how can we say that dimension table is de-normalized because in snowflake schema we normalized all the dimension tables.

what would be ur comment on this??

Stephen Ross

• Mar 29th, 2007

 

I am a beginner to DW,
but as I know fact tables - Denormalized
And Dimension Tables - Normalized.
If I am wrong, please correct.

Adil M

• Jul 4th, 2007

 

De-normalization is basically the concept of keeping all the dimension hierarchies in a single dimensions tables. This causes less number of joins while retriving data from dimensions and hence faster data retrival. This is why dimensions in OLAP systems are de-normalized.