Product and account dim FK in fact table
4 posters
Page 1 of 1
Product and account dim FK in fact table
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?
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?
Re: Product and account dim FK in fact table
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.
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.
Re: Product and account dim FK in fact table
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?
Re: Product and account dim FK in fact table
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Product and account dim FK in fact table
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- Posts : 19
Join date : 2013-07-11
Age : 44
Location : Herndon, VA
Re: Product and account dim FK in fact table
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.
So what is the rule to choose the best option?
Thanks for your help.
Similar topics
» Account Numbers in a Fact Table
» Connecting Actual and Budget Fact table to same Product and Customer Dimension
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Revenue Fact and Account Balance Fact
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Connecting Actual and Budget Fact table to same Product and Customer Dimension
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Revenue Fact and Account Balance Fact
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum