Functional dependency between two dimensions
3 posters
Page 1 of 1
Functional dependency between two dimensions
Hi,
I have a fact table with (among others columns) ITEM_ID - COLOR_ID - AMOUNT.
Color is linked (i.e. functionally dependent) to the item (= an item has one and only one color), It would be interesting in that case to merge ITEM dimension and COLOR dimension.
The point is that I want to be able, in the final report, to have the turnover distribution against color, and if there is no blue item sold, I want to see the line (BLUE --- $0). But if COLOR is part of ITEM dimension, if no blue items have been sold, BLUE will just not appear in the DWH.
What would you be the proper design in that case ?
Thank you for your help !
Thibault
I have a fact table with (among others columns) ITEM_ID - COLOR_ID - AMOUNT.
Color is linked (i.e. functionally dependent) to the item (= an item has one and only one color), It would be interesting in that case to merge ITEM dimension and COLOR dimension.
The point is that I want to be able, in the final report, to have the turnover distribution against color, and if there is no blue item sold, I want to see the line (BLUE --- $0). But if COLOR is part of ITEM dimension, if no blue items have been sold, BLUE will just not appear in the DWH.
What would you be the proper design in that case ?
Thank you for your help !
Thibault
wawanco- Posts : 6
Join date : 2014-05-28
Location : Paris
Re: Functional dependency between two dimensions
It should not matter wither product and color are separate dimensions, unless you are building to order. If this is build-to-order keep them separate as the color of the item presumably would not be a characteristic of the item until after it is built.
If it is not build-to-order, then why would you state "But if COLOR is part of ITEM dimension, if no blue items have been sold, BLUE will just not appear in the DWH"? Would not the dimensions contain everything available to be sold? To see what didn't sell involves using an outer join.
Now, if the issue is there are 100 possible colors and a particular item is only available in 5, then you have issues. If every variant (i.e. color) of a product has a unique SKU, then by all means include color in the item dimension. If not you could still do it my making color part of the natural key, presuming your transaction data has that information. If you want to keep the dimensions separate, a support table that tracks colors associated with products is necessary to bound color related 'did not sell' queries.
If it is not build-to-order, then why would you state "But if COLOR is part of ITEM dimension, if no blue items have been sold, BLUE will just not appear in the DWH"? Would not the dimensions contain everything available to be sold? To see what didn't sell involves using an outer join.
Now, if the issue is there are 100 possible colors and a particular item is only available in 5, then you have issues. If every variant (i.e. color) of a product has a unique SKU, then by all means include color in the item dimension. If not you could still do it my making color part of the natural key, presuming your transaction data has that information. If you want to keep the dimensions separate, a support table that tracks colors associated with products is necessary to bound color related 'did not sell' queries.
Re: Functional dependency between two dimensions
Actually, I wanted to take a very simple example to illustrate my point, but it turns out that I have just made it more confusing ....
Let's try with the real case
My ITEMS dimension describes accounting items, which can be of type INVOICE, CREDIT NOTE, DRAFT etc...
A row is added to the ITEMS dimension when an accounting item is issue. (The fact table describe the clearance of these items).
For instance I can have the following records in ITEMS dimension:
In this instance, no draft has been issued (never), nevertheless, I want to have the following report:
Number of invoices : 1
Number of credit notes : 1
Number of drafts : 0
It's why I was thinking about supporting a separate TYPE dimension and perform an outer join to report the lines above.
I hope my problem is clearer now !
Thibault
Let's try with the real case
My ITEMS dimension describes accounting items, which can be of type INVOICE, CREDIT NOTE, DRAFT etc...
A row is added to the ITEMS dimension when an accounting item is issue. (The fact table describe the clearance of these items).
For instance I can have the following records in ITEMS dimension:
Key | Customer | Type | Due Date |
1 | Cust1 | INVOICE | yyyy/mm/dd |
2 | Cust2 | CREDIT NOTES | yyyy/mm/dd |
Number of invoices : 1
Number of credit notes : 1
Number of drafts : 0
It's why I was thinking about supporting a separate TYPE dimension and perform an outer join to report the lines above.
I hope my problem is clearer now !
Thibault
wawanco- Posts : 6
Join date : 2014-05-28
Location : Paris
Re: Functional dependency between two dimensions
Sounds like the Promotion Coverage issue Kimball discusses in his book: you have 1000 products covered by a promotion, your sales fact table records sales but how do you know which products didn't sell? You create a Promotion coverage fact table that has a record for every product in the promotions (Dims of Product, Promotion, etc.) and then combine queries from both the coverage table and sales fact table to give you all products regardless of whether they sold or not.
Swap Product in his example for Item Type in your example and you probably have a solution
Swap Product in his example for Item Type in your example and you probably have a solution
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Functional dependency between two dimensions
Shame on me, I've read this book so many times and I've never seen the link between this part and my problem !
The only difference I can see is that Promotion and Product have a many-to-many relationship while my TYPES and ITEMS is one-to-many (one item can only have one type). Consequently, I had the opportunity to merge TYPES dim and ITEMS dim into only one dimension, but to be able to implement the coverage table I need to support two separates dimensions...
Anyway, thanks for the solution !
The only difference I can see is that Promotion and Product have a many-to-many relationship while my TYPES and ITEMS is one-to-many (one item can only have one type). Consequently, I had the opportunity to merge TYPES dim and ITEMS dim into only one dimension, but to be able to implement the coverage table I need to support two separates dimensions...
Anyway, thanks for the solution !
wawanco- Posts : 6
Join date : 2014-05-28
Location : Paris
Similar topics
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
» Correlated - Separate Dimensions OR Single Dimensions ?
» Deleting Dimensions and Bridge Dimensions
» Design all dimensions as conformed dimensions
» Question about Dimensions with SKeys to join with other dimensions.
» Correlated - Separate Dimensions OR Single Dimensions ?
» Deleting Dimensions and Bridge Dimensions
» Design all dimensions as conformed dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|