Duplicating Dimension Attributes

View previous topic View next topic Go down

Duplicating Dimension Attributes

Post  jimbo1580 on Tue Dec 01, 2009 4:54 pm

I have an accumulating fact table tracking the lifecycle of what we refer to as a "Case" in my organization. Each "Case" has a set of 9 specific events, for which we are tracking dates, and the measures in the fact table are the monetary values of the case at each step in the process, as it can change over time.

Two of the dimensions in the fact table are "Product" and "Company". The relationships are as follows: each "Case" is associated with a "Product" and each "Product" is sold by a particular "Company". There is a 1 -> M relationship between product and company.

The business users want to be able to analyze "Cases" by both products and companies. From what I have learned about dimensional modeling, it would seem that I should represent the relationship between products and companies only through the fact table, but I keep going back and adding "Company Name" and "Company Number" as attributes in my "Product" dimension because it makes sense to me, even though they are in the "Company" dimension. My reasoning is that if anyone wants to do any reporting or analysis on the product dimension itself, they would want the company info in there. Does anyone have any thoughts on this? Thanks

jimbo1580

Posts : 23
Join date : 2009-04-30

View user profile

Back to top Go down

Re: Duplicating Dimension Attributes

Post  BoxesAndLines on Tue Dec 01, 2009 8:54 pm

You can do this but the query maybe complex. An analogous example for me is using a date dimension FK in another dimension where I want to be able to leverage the date dimension functionality. This is also easily accomplished by just joining on date. If you want to examine the relationship between products and cases, I would look at creating a new fact table rather than joining dimensions. Resist the urge to draw a relationship between dimensions just because you can.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Duplicating Dimension Attributes

Post  ngalemmo on Wed Dec 02, 2009 1:18 pm

I agree with B&L that an additional factless fact table containing company key and product key is a more flexible way to support product dimension reporting. While your solution of storing company name in the product is simpler, it begs the question... would the business be interested in other attributes about the company when looking at products? You then fall into a trap where you need to go back and add more company attributes to the product table. The company/product relationship table also makes life simpler when companies merge.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Duplicating Dimension Attributes

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