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

Compound key attributes

2 posters

Go down

Compound key attributes Empty Compound key attributes

Post  Aikibart Mon Feb 15, 2010 4:45 am

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
Aikibart
Aikibart

Posts : 1
Join date : 2009-02-03
Location : Belgium

Back to top Go down

Compound key attributes Empty Re: Compound key attributes

Post  ngalemmo Wed Feb 17, 2010 1:47 pm

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

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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