Material and Product hierarchies

Question

I have a master data with both the material and product details in a single table. I am creating a star schema and my question is do i need to make two dimension table with separate material attributes and product attributes or can i have both in a single dimension table? The current master data looks has the following fields -
Material id, name, type, product hier 1,2,3,4...product hierarchy, product category, sub category. In my case both material and product are same, so a single id.

I am thinking of making it in a single table, but is that the best practice? Any future potential issues?

Many thanks in advance, Arun

Answer 1

The important (and obvious) thing is, that the fact table has two separate foreign keys: PRODUCT_ID and MATERIAL_ID, both referencing your single dimension table.

This setup is not always best practice for OLTP systems, because in this case the database can't enforce the referential integrity. (You may store a product ID in the MATERIAL_ID column).

But for data-warehouse the database constraints are typically not enabled and are enforced in the loading job, so this setup is fine.

The decision to split is more dependent on the origin of the two dimensions. If both of them are maintained together, I see no reason to split them. If the two dimension are independent, with different lifecycles and separate sources, there is no reason to combine them.

And BTW Kimball IMO mentions the split of hierarch levels (not separate dimensions). So he sees as an mistake to split the product attributes and the hiearchy and category attributes (which is not your problem).

Answer 2

It depends on your business requirement.

If you ever need to produce a report that shows (say) units produced of product category by material, then you need to keep them in separate dimensions.