Complex Dimensional Model Help - With History Product to Part

I'm working on modeling a complex set of role playing dimension and not 100% sure how to model it.

We have Parts and Products. Think of it like Legos (parts) and Products (something built with Legos). Each part has attributes like Color, shape etc. Each Product has it's own set of attributes.

We make the Parts and the Products. We also put the Parts together to make Products. Each Part could go into several Products. However, the number of these parts varies depending on the Products. So a normalized Model may look like this:

Parts:
Part No Color Type
P1 Red C
P2 Blue C
P3 Green C

Products:
Product No Brand
Pr1 A
Pr2 A

Build
Product No Part NO Howmany
Pr1 P1 50
Pr1 P2 30
Pr2 P2 90
Pr2 P3 10

Part Inventory:
Part No InStock
P1 20
P2 40

Product Inventory:
Product NO InStock
Pr1 1
Pr2 5


My plan until history of the Build was required:
Part Dim
Product Dim
Product Inventory Snapshot Fact
Part Inventory Snapshot Fact
Build Fact-Less Fact - Bridge of Products, Parts, and Quantities

Now that our users need to know the Build at the time of the Inventory Facts it's not clear the best plan to store the history, a Part required to make a product may change or the quantity of parts may change and the number of parts is in the 100,000 range. Our users want to ask the question: We produced what Products on a specific date and how many of a specific Part did we need to create that Product along with other analysis that uses the Build link and needing to know which parts make up a product. We will also later have line item on sales that will be at the Product level and will need to link to the build as well.

Would a Type II dimension for Parts / Products help here? When the composition changes, a new Parts record is created with effective dates denoting when that composition was in effect. Fact table would point at the part key based on build date.

bruce.szalwinski wrote:Would a Type II dimension for Parts / Products help here? When the composition changes, a new Parts record is created with effective dates denoting when that composition was in effect. Fact table would point at the part key based on build date.

Thanks, I think somthing like this is what I need, but I'm not sure a Dimension is what's needed due to it being a Many to Many relationship. For example I have a Product Inventory Fact table with a grain of Date, Prodcut. Where would I store the Part / Product combo Dimension if the Product has 30 parts? The same issue exists when the Parts are made, there is no understanding of the time of making the Part which Product it will go in so while something like a Type II is what's needed I don't understand how to fit it in?

There was a discussion about Product Bill of Materials for Dimensional Model, https://kimballgroup.forumotion.net/t80-product-bom-for-dimensional-model, back in March 2009. That may provide some design ideas.

Thanks again;

The BOM example is pretty much what I was thinking with the Fact-Less Fact table that had the link between Product and Part with Quantity. My issue is still keeping history. I've considered the following:

Part Dim
Product Dim
Product Inventory Snapshot Fact
Part Inventory Snapshot Fact
Build Group - a group for each combination of unique Product, Part, Quanties allowing changes over time.
Build Fact-Less Fact - Bridge of Build Group, Products, Parts, and Quantities

I will then add the Build Group to the Product Iventroy the Build Group and use a Many to Many in SSAS. I'm hoping to test my result in a day to two.

I think the bridge concept comes up when implementing a multi-valued dimension. I think you may be talking about implementing something like this:

Fact -> Product Group (bom) -> Xref -> Parts and Products

Fact table contains a Product Group key
Product Group contains primary key, plus unique id that represent set of products / parts in the group at a particular time. When you get a new combination, you get a new key.
Xref contains product group key, product key, part key and quantities