Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

AdventureWorksDW2008 - dimension table references other dimension table

4 posters

Go down

AdventureWorksDW2008 - dimension table references other dimension table Empty AdventureWorksDW2008 - dimension table references other dimension table

Post  JimmyRichards 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

Back to top Go down

AdventureWorksDW2008 - dimension table references other dimension table Empty Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

AdventureWorksDW2008 - dimension table references other dimension table Empty Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  John Simon 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

http://jsimonbi.wordpress.com

Back to top Go down

AdventureWorksDW2008 - dimension table references other dimension table Empty Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

AdventureWorksDW2008 - dimension table references other dimension table Empty Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  Vishy 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

Back to top Go down

AdventureWorksDW2008 - dimension table references other dimension table Empty Re: AdventureWorksDW2008 - dimension table references other dimension table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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