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…
Please see example data between the two tables
I have to be able to duplicate this behaviour below…
Best Answer
I resolved this issue by creating a named query in the DSV which contains the product key and productorder keys, linked these back to the two fact tables, created a new dim for production orders (so i had one for each fact table), and created a new measure group off of the named query which acts as a bridge. In dim usage I then setup regular relationships to the 2 dims for each fact table. This seems to have fixed my issue!