AdventureWorksDW2008 - dimension table references other dimension table

View previous topic View next topic Go down

AdventureWorksDW2008 - dimension table references other dimension table

Post  JimmyRichards on Sat Mar 24, 2012 1:31 pm

In AdventureWorksDW2008, one of the dimension tables, DimCustomer references DimGeography via the column GeographyKey. Does this break the Kimball and star schema rules for data warehouse design? If it was designed according to Kimball’s rules, wouldn’t the DimGeography details be listed in DimCustomer in a denormalized structure, so you wouldn’t have to join with DimGeography at all? Am I right about this or am I missing something? If I am correct about this, does this make it bad design or just an alternative design?

Thank you!!

JimmyRichards

Posts : 1
Join date : 2012-03-24

View user profile

Back to top Go down

Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  ngalemmo on Sat Mar 24, 2012 10:14 pm

Yes, its a snowflake. Its not ideal. If you want to avoid a snowflake you would put the geography attributes in the customer dimension.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  John Simon on Sun Mar 25, 2012 9:59 pm

It's actually an Outrigger. The SalesTerritory dimension also has a GeographyKey.

So it's actually in accord with the Kimball approach.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  ngalemmo on Mon Mar 26, 2012 12:54 am

Outrigger, snowflake, it's still a dimension with a FK to another dimension. Also, Kimball isn't against it (I don't think he is 'against' anything), it's just that it should be used if appropriate for the situation. In fact, he has published situations where snowflaking is recommended.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  Vishy on Mon Mar 26, 2012 2:08 am

If you have aggregate totals on territory then go by this design and have territory independant dim having S.key in the fact.

If you have very big customer dim then again you should go by this design as it won't bloat the customer dim.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum