Hi all I have 2 fact tables with a measure group each, Production and Production Orders.
Production has production information at a lower granularity (at the component level)
productionordershas information at a higher level (order level with header quantities etc.).
I have created a surrogate key link between the two tables on
productionorderid. As soon as I add Prod ID (from
productiondetailsdim) to the pivot table it blats out the actual qty (from prod order measure group) and I cannot combine the qty's from the two measure groups... How can I design the correct relationship between the two? please see my dim usage diagram.
Production Details is the dim that links the two fact tables... at the moment
DimProductionDetails is in a fact relationship with Production.
I'm not sure what the relationship should be with Production Order (it is currently many to many) Thanks for your help! If I combine the line level qty's with the header level i don't want the header quantities summed for every line (duplicates) i just want it summed at the order(header level) alongside the line qty's...
I have to be able to duplicate this behaviour below...