Functional dependency between two dimensions

[wawanco]

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

[ngalemmo]

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.

[wawanco]

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:

Key	Customer	Type	Due Date
1	Cust1	INVOICE	yyyy/mm/dd
2	Cust2	CREDIT NOTES	yyyy/mm/dd

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

[nick_white]

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

[wawanco]

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 !

[Sponsored content]