DW Modeling - Should I use multiple value attribute with bridge?
3 posters
Page 1 of 1
DW Modeling - Should I use multiple value attribute with bridge?
Hello...this is my first post...
Here is an example of what I want to do.
I have a sales fact table to track the sales of some food products by quantity and dollar sales.
I also want to be able to tell them (the business analysts) how much of a particular product they sold last year. My problem is that I need to tell them the actual components of the product...lets imagine that a product has sugar 30% and salt 70%. I need to be able to tell them how much salt they used and much sugar they used last year so I would have to group by product and and quantity...and then?
What should I do?
Create a bridge with weights to group all the possible combinations I need to handle? Any idea?
Of course I don't want to create an attribute because it would be too many for a single dimension (I have 20 different components)
At the end the goal is to summarize all the sugar they sold last year.
I hope is clear enough for somebody to give me a hand!
Thanks so much!
Osky
Here is an example of what I want to do.
I have a sales fact table to track the sales of some food products by quantity and dollar sales.
I also want to be able to tell them (the business analysts) how much of a particular product they sold last year. My problem is that I need to tell them the actual components of the product...lets imagine that a product has sugar 30% and salt 70%. I need to be able to tell them how much salt they used and much sugar they used last year so I would have to group by product and and quantity...and then?
What should I do?
Create a bridge with weights to group all the possible combinations I need to handle? Any idea?
Of course I don't want to create an attribute because it would be too many for a single dimension (I have 20 different components)
At the end the goal is to summarize all the sugar they sold last year.
I hope is clear enough for somebody to give me a hand!
Thanks so much!
Osky
oskyimporto- Posts : 3
Join date : 2013-04-18
Re: DW Modeling - Should I use multiple value attribute with bridge?
I'd lean towards a secondary fact table.
Your primary fact table remains product sales.
The second fact table becomes something like "Ingredient Usage". Your ETL process can break out the products into the individual ingredients & portions.
Conformed dimensions between the 2 facts will associate the appropriate facts.
Hope this helps.
LAndrews- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: DW Modeling - Should I use multiple value attribute with bridge?
...lets imagine that a product has sugar 30% and salt 70%.
Mmmmm... sugary salt! 4 out of 5 Cardiologist recommend it! Kids love it too!
A bridge would work, but LA's suggestion of a separate fact has merit as it can reduce query complexity.
The issue with a bridge is dealing with units of measure. You may sell cases and need to translate that into volume or weight of components. It can be done, but is may be easier for the end user if a separate fact is created that already does the calculations. Both have advantages and disadvantages.
Similar topics
» Same attribute in multiple Dimensions ?
» Modeling an attribute that belongs in more than one dimension
» Customer Dimension Modeling - Level attribute
» What to do when a single attribute rolls up to multiple categories?
» Same attribute in multiple dimensions or Create new dimension?
» Modeling an attribute that belongs in more than one dimension
» Customer Dimension Modeling - Level attribute
» What to do when a single attribute rolls up to multiple categories?
» Same attribute in multiple dimensions or Create new dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum