Same fact for different dimension types

Question

I work for a manufacturing company and am working with fact data that can be for both a part (an input to a manufactured product) or a manufactured product (the final output which the company then sells). I have separate dimension tables for part and product as the attributes are different and they really are 2 different things.

For the fact data, all of the attributes are the same it's just that one record will point to a part and another will point to a product. A single record cannot point to both.

Does it make sense to have 2 separate fact tables, or have 1 fact table with a PartKey and a ProductKey where one of those will always point to the -1 or unknown record in the non applicable dimension table?

Thanks in advance for the help.

Answer 1

You should have 2 fact tables.

Since Part and Product are different objects, the sets of keys in your fact tables are not the same. Formally speaking, they have different grain.

For example, let's say your dimensions are Date, Plant, Supplier, Part, Product, Customer. Then Fact "Inputs" grain might be:

• Date
• Plant
• Supplier
• Part

while Fact Output grain might be:

• Date
• Plant
• Product
• Customer

These fact tables have 2 shared dimensions (Date and Plant), and 2 unique dimensions (Supplier vs Customer, Part vs Product). Which confirms that their grain is different, and they shouldn't be forced into one table - consistency of table granularity is the key requirement in dimensional modeling.

You will be able to analyze parts and products together across the shared dimensions, and yet do more specialized analysis for each of them individually. Over time, you will have flexibility to add more shared and unique dimensions, without resorting to trickery.