0

I have a fact measure group called production it is linked to a fact dim Production details. This contains production information at line and header level. If i am selecting line level items i expect the grand totals for headers to sum the distinct value (not sum dupes) for each order. There doesn't seem to be an option in excel to specify which columns you want to grand total when connected to a cube. How do i get around this?

enter image description here

enter image description here

jhowe
  • 10,198
  • 19
  • 48
  • 66

1 Answers1

1

This is the wrong design. I would change your cube and move the production header level metrics to a new fact table which has all the same dimension keys except for the Component Item dimension.

You can look for a property on the measure group called IgnoreUnrelatedDimensions. The default will repeat the header value for each item but not double count for the subtotals. Changing that setting to False will make the header values null at the item level.

But I suspect instead of using this setting you should use a many-to-many Component Item dimension similar to how we have discussed in the past.

Community
  • 1
  • 1
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • hi Greg thanks for your response, this is actually a continuation from another question that you answered http://stackoverflow.com/questions/32764281/ssas-relationship-granularity . I implemented what you said with bridge measure group etc. It works fine, the problem i'm having is they want to have both measures visible with component item. However componentitem is at line level (in prod details) not order level. To get the links to work correctly would mean extending the prod order measure group in include componentitem sk. But this fact table is used for other things. I can't change – jhowe Feb 18 '16 at 10:25
  • the granularity. so I think i have two options, work out a way in MDX to get the header levels to display properly, or create a new table for the prod order measure group as i can't extend the existing one. My preference is using MDX as it seems silly to duplicate data everywhere i could have everything in one fact table? Please advise, thanks! – jhowe Feb 18 '16 at 10:28
  • Can you provide another screenshot of the Dimension Usage tab? Can you just add Component Item to the bridge table and add Component Item as a many-to-many relationship on the pros order measure group? – GregGalloway Feb 18 '16 at 12:18
  • so bridge production is just a named query that links the two fact tables/measure groups. I can't extend the physical fact table for ProdOrders to include componentitems as it will create multiple lines per order and change the granularity. I am toying with the idea of creating a new fact table that contains header info, but is at the same granularity as the line fact table. the component line sk will then exist in both fact tables. please advise if this is what i should be doing. I will replace the existing prod order fact table with the new one. – jhowe Feb 18 '16 at 12:30
  • Add Component Line SK to the named query and then make it a many-to-many relationship on prod orders. Make sense? – GregGalloway Feb 18 '16 at 12:40
  • this is bridge_production named query SELECT PO.ProductionOrder_SK, P.Production_SK, P.Company_SK, PO.Item_SK AS ManufacturedItem_SK, P.ComponentItem_SK FROM vault.ProductionOrder AS PO INNER JOIN vault.Production AS P ON P.ProductionOrder_SK = PO.ProductionOrder_SK can you use my diagram to describe where/how to create the new relationship? So i don't need to add componentline_sk physically to the prodorders fact table? – jhowe Feb 18 '16 at 12:47
  • @jhowe is Production Order Qty the measure group with header detail? Then find the cell which is the intersection of Component Item and Production Order Qty and make it a many-to-many relationship using the bridge as the intermediate measure group. Then delete the header measures (which are currently in Production repeated on each detail row?) and add the header measures to Production Order Qty – GregGalloway Feb 18 '16 at 13:46
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103853/discussion-between-jhowe-and-greggalloway). – jhowe Feb 18 '16 at 14:16
  • Hi Greg, this works fantasically thank you... before i mark as answered can you reply to my comments in discussion? thanks – jhowe Feb 18 '16 at 17:07