Source Fact data coming in at different levels of a conformed dimension
2 posters
Page 1 of 1
Source Fact data coming in at different levels of a conformed dimension
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.
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
Another note...
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.
Thanks
John w.
jweicher- Posts : 2
Join date : 2011-01-14
Re: Source Fact data coming in at different levels of a conformed dimension
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
![-](https://2img.net/i/empty.gif)
» clickstream fact data coming in with different levels of dimensional geography data
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Conformed Dimension Processing Multiple Data Marts
» Can a combination of a Fact and Dimensions constitute a inferred conformed dimension?
» Source Values and Conformed Values in the Dimension table
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Conformed Dimension Processing Multiple Data Marts
» Can a combination of a Fact and Dimensions constitute a inferred conformed dimension?
» Source Values and Conformed Values in the Dimension table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|