Amit Chandra (6/10/2016)
I am stuck in a situation which I believe should have a ready solution because it looks like a common scenario to me. Any help is much appreciated.I have a fact 'F' and a dimension 'D'. The relationship between D and F is many to many. I have modeled this relationship in my dimension usage using a bridge table 'B' and and intermediate dimension 'D1'.
My fact F has both base measures(coming directly from the DSV) and some calculated members. When i browse in excel by dropping attributes from D and measures from F, I am getting expected results for my base measures. But calculated members is showing the grand total and wont breakup according to the many to many relationship as defined in cube.
Note - I am assigning default values to my calculated members in the DSV. Final assignment to the calculated members happen under scoped assignment.
Does SSAS not support relationships for calculated members? Too bad if it doesn't. Any workaround guys??
Amit Chandra
This is a difficult one to diagnose without more information. To begin with, yes, SCOPE is compatible with all types of relationship. The SCOPE statements would be the first place I would look - perhaps you need nested scopes? perhaps the scope is in the incorrect place in your script and is resolving at the wrong time? Perhaps the scope is incorrect? Who knows without seeing the whole thing and knowing a fair bit more 😀
Here are some good links to get your brain gestating on this problem (3 from Chris Webb and one from Alex Whittles):
https://blog.crossjoin.co.uk/2014/10/14/mdx-solve-order-scope_isolation-and-the-aggregate-function/
https://blog.crossjoin.co.uk/2010/08/03/order-of-nested-scope-statements/
If you're not comfortable with the MDX debugger then you can give the THIS= elements of your scopes simple results like "1" or "true" so that you know exactly which portion of your script is being hit at query time. It's a good way of debugging them without getting muddied with data itself.
Let us know how you get on.