Duplicating Dimension Attributes

[jimbo1580]

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

[BoxesAndLines]

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.

[ngalemmo]

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.

[Sponsored content]