3

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...

SSAS relations

Please see example data between the two tables enter image description here

I have to be able to duplicate this behaviour below...

enter image description here

4
  • 1
    I still don't get it, where do the actuals come from? all I see in the details table are named estimate
    – Tom V
    Sep 30, 2015 at 19:23
  • hi thanks for your reply. You can see in the screen shot the actual qty's are coming from the measures on the right.
    – jhowe
    Oct 1, 2015 at 14:09
  • @jhowe in excel yes, that is obvious, but how do they relate to the fact tables in your screenshots
    – Tom V
    Oct 1, 2015 at 19:22
  • prod orders is a measure group of factprodorders and production variance is a measure group off of production
    – jhowe
    Oct 1, 2015 at 20:44

1 Answer 1

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.