Functional dependency between two dimensions

View previous topic View next topic Go down

Functional dependency between two dimensions

Post  wawanco on Wed Jul 02, 2014 7:38 am

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

wawanco

Posts : 6
Join date : 2014-05-28
Location : Paris

View user profile

Back to top Go down

Re: Functional dependency between two dimensions

Post  ngalemmo on Wed Jul 02, 2014 11:36 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Functional dependency between two dimensions

Post  wawanco on Thu Jul 03, 2014 3:17 am

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:

KeyCustomerTypeDue Date
1Cust1INVOICEyyyy/mm/dd
2Cust2CREDIT NOTESyyyy/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

wawanco

Posts : 6
Join date : 2014-05-28
Location : Paris

View user profile

Back to top Go down

Re: Functional dependency between two dimensions

Post  nick_white on Thu Jul 03, 2014 7:00 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Functional dependency between two dimensions

Post  wawanco on Thu Jul 03, 2014 8:44 am

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 !

wawanco

Posts : 6
Join date : 2014-05-28
Location : Paris

View user profile

Back to top Go down

Re: Functional dependency between two dimensions

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