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

Product and account dim FK in fact table

4 posters

Go down

Product and account dim FK in fact table Empty Product and account dim FK in fact table

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

http://www.horus-df.com/

Back to top Go down

Product and account dim FK in fact table Empty Re: Product and account dim FK in fact table

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

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

http://aginity.com

Back to top Go down

Product and account dim FK in fact table Empty Re: Product and account dim FK in fact table

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

http://www.horus-df.com/

Back to top Go down

Product and account dim FK in fact table Empty Re: Product and account dim FK in fact table

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

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

Back to top Go down

Product and account dim FK in fact table Empty Re: Product and account dim FK in fact table

Post  sachij3u 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).
sachij3u
sachij3u

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

Back to top Go down

Product and account dim FK in fact table Empty Re: Product and account dim FK in fact table

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

http://www.horus-df.com/

Back to top Go down

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