Product and account dim FK in fact table

View previous topic View next topic Go down

Product and account dim FK in fact table

Post  remiby on Sun Jul 21, 2013 7:47 am

Should I have a product dim FK in my fact table since I can retrieve it from the account dim?
In Kimball's book The Datawarehouse toolkit Chapter 9, P203 (financial services), the model shows an account FK and a product FK in the fact table. I wonder whether account wouldn't be sufficient as it belongs to the same hierarchy?
Actually this question leads to another. Why is the status dim needed in my fact table (according to Ralf Kimball's book) when it can be reached through the account dim?
What do you think?

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: Product and account dim FK in fact table

Post  ngalemmo on Sun Jul 21, 2013 7:38 pm

Its a matter of form. A proper star schema contains a single fact table with direct FK relationships to all dimensions. A snowflake schema allows secondary references to dimensions.

By moving the key to the fact table, you are creating a proper star schema. Generally speaking, on most platforms, star schema performs better and is easier to use.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Product and account dim FK in fact table

Post  remiby on Mon Jul 22, 2013 3:58 am

Status could be an attribute of the account dimension not a table by itself. So I would not have to snowflake. Why should I make it a dimension table with direct link on my fact table?

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: Product and account dim FK in fact table

Post  BoxesAndLines on Mon Jul 22, 2013 8:31 am

Say you have a million accounts and you want to identify all accounts with a status of "Pending". If the status is modeled in the account dimension, I have to go through a large amount of records to identify pending accounts. If I have a Status dimension, I have a much smaller dimension that improves overall performance.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Product and account dim FK in fact table

Post  sachij3u on Mon Jul 22, 2013 12:21 pm

I too had a similar situation and it helped (as quoted by BoxesAndLines) from query performance to have the status dim in fact itself (and filter query on status. We also chose to have status sitting in the account dim too (just in case we might need it in other star schema).
avatar
sachij3u

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

Re: Product and account dim FK in fact table

Post  remiby on Fri Jul 26, 2013 9:27 am

I am a bit confused... According to Kimball book we have to avoid the "too many dimensions trap" and for that we sometimes need to create a junk dimension. But on the other hand from what I understand from your posts it is sometimes better to add dimensions (like status) for performance purpose...
So what is the rule to choose the best option?
Thanks for your help.

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: Product and account dim FK in fact 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