DW Modeling - Should I use multiple value attribute with bridge?

View previous topic View next topic Go down

DW Modeling - Should I use multiple value attribute with bridge?

Post  oskyimporto on Thu Apr 18, 2013 2:33 am

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

oskyimporto

Posts : 3
Join date : 2013-04-18

View user profile

Back to top Go down

Re: DW Modeling - Should I use multiple value attribute with bridge?

Post  LAndrews on Thu Apr 18, 2013 1:00 pm


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

View user profile

Back to top Go down

Re: DW Modeling - Should I use multiple value attribute with bridge?

Post  ngalemmo on Thu Apr 18, 2013 1:30 pm

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

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

View user profile http://aginity.com

Back to top Go down

thanks

Post  oskyimporto on Thu Apr 18, 2013 9:18 pm

You guys are the best, thanks so much!

oskyimporto

Posts : 3
Join date : 2013-04-18

View user profile

Back to top Go down

Re: DW Modeling - Should I use multiple value attribute with bridge?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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