I have created a data model 2 months back as shown in the picture attached.It is a very complex one.Now the client wants bill of materials module in it.Can someone please suggest how do I integrate BOM into my model.Should I just use hierarchy function in my product dimension table?then will it integrate with all events present for all the components of a product.OR should I create a separate dimension for BOM and re create all events for BOM.
BOM in this case I preassume will be between ItemID and WarehouseItemID (which items make up to a warehouse item). I would join both these tables into one table with one key and use that to relate with link table.
Kiran thanks for the reply.could you please explain in detail.there are three stock tables..bin item ,product and warehouseitem.binitem is the important one .so if i relate to binitem and product then it will automatically relate to warehouse because of the links in link table....
So what to do now..should i use a hierarchybelongsto function?
Kevin you got it right. I think its best done mostly at SQL level since getting linkage for huge data would be a longer process (make judgement based on data sizes you are dealing with). In my last experience with BOM, I had one table with item, product and warehouse identity which is linked to the fact. You may want to consider some flags to distinguish order and sales transactions since they are not related so the same entity. Hence, there will be some redundency in the final table.
