Source Fact data coming in at different levels of a conformed dimension

View previous topic View next topic Go down

Source Fact data coming in at different levels of a conformed dimension

Post  jweicher on Fri Jan 14, 2011 6:10 pm

All-

I apologize for a question that has probably been asked several times, but after a search nothing seem to match my situation closely enough to be helpful. Probably because my problem is actually quite straight-forward almost simple

I am designing a model to handle some otherwise pretty simply Cost vs Revenue data, for various products. The situation is that I while we have a pretty clean, conformed Product dimension and hierarchy (values from both systems match cleanly across the various levels), the Cost data is simply available at a lower granularity than that of Revenue.

To illustrate, our Product dimension has the attributes to represent the following levels:

- Product Type
---> Product Class
------> Product Portfolio (source revenue data comes to us with these values)
---------> Product Name (source cost data comes to us with these values)

I'm already planning on having different fact tables for Cost amounts and Revenue amounts. And of course to do an actual cost vs. revenue comparison, data from the two tables would have to be rolled to lowest common level (in this case Portfolio) before doing a meaningful comparison using the conformed hierarchy.

The problem is how to load the actual data. I have to believe I'm missing some fundamental concept, because the only thing I can think of is to either:

a) make my actual Product dimension only go so far as to Portfolio, load both facts at that level during ETL, and put Product Name on the Cost fact directly as a degenerate dimension so I don't lose that extra detail level of information, or
b) Do something similar but model an actual full dimension table for Product Name, and have the conformed dimension again only go down to Portfolio so it can again be used to load both Fact tables.

But both options seem ridiculous when the Product dimension is actually genuinely conformed. Portfolio values are the same in both the Cost and Revenue worlds. Same for the higher levels. There is just an additional lower level of detail available in the Cost world.

Any thoughts on how to handle a situation such as this? It's actually a pretty clean situation. I simply have data from two domains that come to me at different levels of the same hierarchy.

Any suggests are greatly appreciated. I know this is probably a rookie question.

Thanks-
John W.


jweicher

Posts : 2
Join date : 2011-01-14

View user profile

Back to top Go down

Another note...

Post  jweicher on Fri Jan 14, 2011 6:38 pm

Also, I realize I could solve this problem in the data for the Product dimension itself, by duplicating the entries at the Portfolio level as additional "products" at the Product Name level as well, rolling to their same-named parents. But that seems like a bit of a hack and I'm just assuming there's an appropriate modeling technique to handle this situation.

Thanks
John w.

jweicher

Posts : 2
Join date : 2011-01-14

View user profile

Back to top Go down

Re: Source Fact data coming in at different levels of a conformed dimension

Post  LAndrews on Fri Jan 14, 2011 7:11 pm


John,

You are going down the right track.

Kimball's talks about "roll-up dimensions" in The Data Warehouse Toolkit 2nd edition, page 83.

Basically the solution you are looking for is 2 dimensions, conformed from Portfolio up..

i.e.

DIM_Product: (for use on the Cost Fact)

DIM_Product_Key
- Product Type
---> Product Class
------> Product Portfolio
---------> Product Name

DIM_Portfolio: (for use on the revenue fact)

DIM_Portfolio_Key
- Product Type
---> Product Class
------> Product Portfolio


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Source Fact data coming in at different levels of a conformed dimension

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