Applying the concept of heterogeneous product schema in manufacturing & supply chain

Dear moderators and users,

This is my first post here.

I am involved in dimensional modeling of a manufacturing DW/BI project. I have the following scenario. Let me describe it here.

The image above depicts a sample process flow in a factory; with dummy names. In real scenario, there are many more process steps and they are interlinked in a much more complicated way. Every process has ONE output (product) and ONE input (bill of materials - BOM). The BOM may contain 20-40 different materials with specific quantities. In the intermediate steps, the product of one step may be considered as input for the next step downstream. The finished is considered as product of the final step.

Now this scenario is similar to the heterogeneous product scenario, although products of all the process steps may not be marketable product as such. Important points to consider are:
1. To the business users, every process produces a product.
2. There is very limited commonality (only code and description) among products produced by different processes.
3. All the KPIs are process specific (e.g. Uptime / downtime of machines in process area A, Reject rate of process area B, etc.) and the names of the measures are also process specific (i.e. no generic names of measures).
4. There is apparently no need for comparing measures across process areas (because there is no basis of comparison as such); at least till now.

My questions are:
1. Is my approach of considering this scenario in the same way as heterogeneous product scenario correct?
2. Since there is very limited commonality between products at different steps, and there is no common measure, is it still better to have a core product dimension and a core fact?

Thanks & Regards. Enlightening me in this regard will be much appreciated.

Kajal Kumar Das

Why would you not have a product dimension that includes raw materials or sub-assemblies? Commonality between products is not an issue. Any product dimension would naturally contain non-common items.

What fact tables you have depends on what data you are getting and what you are modeling. If you are just modeling the process steps, then you would have a fact at the step grain. If you are receiving materials issues you would probably have a second fact at the step/material grain tracking that information.

I would not be concerned with incoming materials in the process step fact. You relate the fact to the product being produced (output), not used. You can get usage from the bill of materials related to the product being produced. (supported by a BOM bridge table to the product dimension).

Regarding your comment on BOM usage, that's exactly what I have done. There are KPIs on materials usage and wastage; in the data model, fact table is related to product and product dim is related to BOM via a BOM usage bridge table.

However, my query was different. Should I apply the concept of heterogeneous product schema here? The motivation is: products at different process steps do not have much commonality in terms of attributes and facts are also process specific. Now, as per the Kimball design pattern on heterpgeneous product schema modeling, there should ideally be (taken from banking industry example):
1. one core product dim (it's more like master product dim)
2. one core fact (for facts that are common across products and for comparison purpose)
3. as many specific product dimensions as the number of different type of products with the same key shared with the core product dim.
4. as many product specific fact tables as the number of different type of products

In my case, although the scenario fits this model, there is apparently no need for the core fact table and core product dim table. #3 and #4 are of course required. Is it still better to have the core fact and dimension tables? Please recommend.

How different are these attributes?

You may want to consider sub-dimensions (aka subtype clusters). Have a single common product dimension with common (and mainstream) attributes. For products with additional attributes, have another table with the same PK as the product dimension. Its not a snowflake since the fact will hold the fk to either table. Use the appropriate table(s) and attributes as required for a query.