5

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) productionorders has 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).

enter image description here

Please see example data between the two tables:

enter image description here

I have to be able to duplicate this behaviour:

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
jhowe
  • 10,198
  • 19
  • 48
  • 66

1 Answers1

4

Do you want the full actual qty from prod order measure group to repeat next to each product? If so a many-to-many relationship is right. I suspect once I explain how that many-to-many works you will spot the problem.

When you slice full actual qty from prod order measure group by product from the Production Details dimension it does a runtime join between the two measure groups on the common dimensions. So for example, if for if order 245295 has a date of 1/1/2015 while the production details for order 245295 have dates of 1/8/2015 then the runtime join will lose rows for that order and actual qty will show as null. So compare all the dimensions used on both measure groups and ensure all rows for the same order have the same dimension keys for those common dimensions. If for example dates differ then create a named query in the DSV that selects just the dimension columns from the production fact table which match the order fact table. Then create a new measure group off that named query and use the new measure group as the intermediate measure group in your many to many dimension. (The current many to many cell in the dimension usage tab should say the name of the new measure group not the existing Production measure group.)

Edit: if you want the actual qty measure to only show when you are at the order level and be null at the product level then try the following. Change the many-to-many relationship to a regular relationship and in the dialog where you choose how the fact table joins to the dimension change the dimension attribute to ProductionOrder_SK (which is not the key of the dimension) and choose the corresponding column in the fact table. Then left click on the Production Order measure group and go to the Properties window and set IgnoreUnrelatedRelationships to false. That way slicing actual qty by work center or by an attribute that is below grain in the Production Details dimension will show as null.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Hi thanks for your reply... when analysing in a pivot table, when i select line level components/qty's if I then want to select actualqty (header level) i don't want it to be aggregated for every line (duplicates) i just want it to be aggregated at the order level. This is what i'm stuck on. – jhowe Sep 28 '15 at 09:14
  • I see. Edited my answer to reflect that – GregGalloway Sep 28 '15 at 11:24
  • i edited original post, with an example. I have to be able to show both line level and header level qty's against each other. – jhowe Sep 28 '15 at 11:35
  • I can't tell from your one row example how you want it to behave? Should actual qty repeat for each product? – GregGalloway Sep 28 '15 at 12:57
  • yes it should repeat for each product thanks for your help – jhowe Sep 28 '15 at 13:13
  • Then try the new intermediate measure group for the many-to-many I described above. – GregGalloway Sep 28 '15 at 13:14
  • thanks very much greg i'm starting to understand about having the links between the two dimensions for it to work. I do not understand how to use an intermediate measure group. Do you have an example of this process or can you give me a step by step? i'm not very strong on SSAS. so just to clarify for the data i'm selecting from both measure groups, when i select a dimension attribute there has to be a common link in BOTH fact tables. – jhowe Sep 28 '15 at 13:52
  • See the section in my answer above that starts, "create a named query in the DSV that selects just the dimension columns from the production fact table which match the order fact table. Then create a new measure group..." And let me know if that doesn't help – GregGalloway Sep 28 '15 at 17:41
  • @jhowe yes you need a new measure group which has just the common dimensions where the value is the same at the order and product level. – GregGalloway Sep 28 '15 at 17:41
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/90885/discussion-between-jhowe-and-greggalloway). – jhowe Sep 29 '15 at 12:15