Compound key attributes
2 posters
Page 1 of 1
Compound key attributes
Hi all
I am struggling with following design issue: in our organization, a number of attributes (such as account executive, flagship store) are defined by the combination of customer and brand, where brand is a rollup of a product.
Our sales fact table is at customer and product level.
How do I model this? My thinking is to have a dimensional table d_customers_by_brand with primary key customer_surrogatekey and brand_surrogatekey. Joining to the fact table would mean joining the fact table customer surrogate key to this table's customer surrogate key, and joining the fact table product surrogate key through the item master table (which has the link between product and brand) to this table's brand surrogate key.
What do you think about this design? Should I have 2 surrogate keys, or just one which combines customers and brands?
Thanks in advance for your time and input.
Regards,
Bart
I am struggling with following design issue: in our organization, a number of attributes (such as account executive, flagship store) are defined by the combination of customer and brand, where brand is a rollup of a product.
Our sales fact table is at customer and product level.
How do I model this? My thinking is to have a dimensional table d_customers_by_brand with primary key customer_surrogatekey and brand_surrogatekey. Joining to the fact table would mean joining the fact table customer surrogate key to this table's customer surrogate key, and joining the fact table product surrogate key through the item master table (which has the link between product and brand) to this table's brand surrogate key.
What do you think about this design? Should I have 2 surrogate keys, or just one which combines customers and brands?
Thanks in advance for your time and input.
Regards,
Bart
Aikibart- Posts : 1
Join date : 2009-02-03
Location : Belgium
Re: Compound key attributes
The relationship between account executive, customer, brand for example, is a busines state. One would assume there is business process surrounding how this state is established. The purpose of a fact table is to record business events (such as transactions) as well as states. So, this relationship is best modeled as a factless fact table containing the appropriate surrogate keys as well as effective dates and a current flag.
Similar topics
» Are compound PKs ok for a dimension table?
» dimension table design question for around 100 attributes and higher level calculated attributes
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» SCD attributes
» Big dimensional attributes
» dimension table design question for around 100 attributes and higher level calculated attributes
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» SCD attributes
» Big dimensional attributes
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|