June 10, 2016 at 8:15 pm
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
June 11, 2016 at 9:28 am
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.
June 25, 2016 at 10:42 pm
Sorry for arriving late. I actually got around the problem using resources shared by you. Chris Webb's blog helped. I had to play around with the scope definition and get it right. Thanks!
I am now faced with another problem. In my scoped assignment I have 3 dimensions. One of the dimensions is pretty huge and has many members so it takes a while to load when dropped in excel while browsing. For the grand total behavior to work correctly, I need to define the scope as - [Outlet].[Customer].[Customer].MEMBERS
But this impacts the performance and is almost unusable. When I scope as - [Outlet].[Customer].AllMembers I get usable performance. But this way my grand total gives incorrect result because this has [All] member included in it.
Anyone knows how I can get around this. Why does [Outlet].[Customer].[Customer].MEMBERS behave so badly? Anyone can suggest a better MDX?
June 26, 2016 at 4:40 am
Amit Chandra (6/25/2016)
Sorry for arriving late. I actually got around the problem using resources shared by you. Chris Webb's blog helped. I had to play around with the scope definition and get it right. Thanks!I am now faced with another problem. In my scoped assignment I have 3 dimensions. One of the dimensions is pretty huge and has many members so it takes a while to load when dropped in excel while browsing. For the grand total behavior to work correctly, I need to define the scope as - [Outlet].[Customer].[Customer].MEMBERS
But this impacts the performance and is almost unusable. When I scope as - [Outlet].[Customer].AllMembers I get usable performance. But this way my grand total gives incorrect result because this has [All] member included in it.
Anyone knows how I can get around this. Why does [Outlet].[Customer].[Customer].MEMBERS behave so badly? Anyone can suggest a better MDX?
Can you actually post the MDX please?
One possible avenue is to use [Outlet].[Customer].AllMembers and exclude the all member with EXCEPT or the DESCENDANTS function with a flag. Another thing that may be happening is that if this dimension is big and involved in a crossjoin, if you are not filtering (by using NONEMPTY, EXISTS, FILTER etc..) then it'll crossjoin everything.
Another possible solution is to design an aggregation. But if the dimension is as big as you say then this may have an even more detrimental effect on your processing time.
June 26, 2016 at 8:16 pm
Here is the MDX -
SCOPE ([Measures].[YTD Rolling POD Outlet],
[Outlet].[Customer].AllMembers,
[Date].[Fiscal Month].[Fiscal Month].MEMBERS,
[Item].[Brand Category].[Brand Category].MEMBERS,
[Item].[IPL].[IPL].MEMBERS,
[Item].[IPL Familyand Bottle].[IPL Familyand Bottle].MEMBERS
);
THIS = IIF ([Item].[Price Segment].CurrentMember IS [Item].[Price Segment].&[L],
IIF ([Measures].[YTD RAD] > 0.2395,1, 0),IIF ([Measures].[YTD RAD] > 0.4835, 1, 0)
);
END SCOPE;
Problem is with Outlet dimension. I need to find a way to exclude [All] member from my Outlet scope definition. Help?
June 27, 2016 at 12:42 am
Amit Chandra (6/26/2016)
Here is the MDX -SCOPE ([Measures].[YTD Rolling POD Outlet],
[Outlet].[Customer].AllMembers,
[Date].[Fiscal Month].[Fiscal Month].MEMBERS,
[Item].[Brand Category].[Brand Category].MEMBERS,
[Item].[IPL].[IPL].MEMBERS,
[Item].[IPL Familyand Bottle].[IPL Familyand Bottle].MEMBERS
);
THIS = IIF ([Item].[Price Segment].CurrentMember IS [Item].[Price Segment].&[L],
IIF ([Measures].[YTD RAD] > 0.2395,1, 0),IIF ([Measures].[YTD RAD] > 0.4835, 1, 0)
);
END SCOPE;
Problem is with Outlet dimension. I need to find a way to exclude [All] member from my Outlet scope definition. Help?
Okay, well as I said the all member can be excluded by using EXCEPT or DESCENDANTS (or FILTER or "-" if you don't feel like typing). As I mentioned if you have large dimensions then that sub cube you are defining could get pretty big. Think about when and why this calculation would be used and consider refining it.
June 30, 2016 at 6:50 am
Thanks a ton! My scope was considering a lot more than i required. It was hitting half a million rows every time user dropped 'Outlet' dimension onto excel. With below tweak, the results are lightning fast. Fraction of seconds -
SCOPE ([Measures].[YTD Rolling POD Outlet],
FILTER([Outlet].[Customer].[Customer].MEMBERS,[RAD Depletions 9LE Actual All] > 0),
[Date].[Fiscal Month].[Fiscal Month].MEMBERS,
[Item].[Brand Category].[Brand Category].MEMBERS,
[Item].[IPL].[IPL].MEMBERS,
[Item].[IPL Familyand Bottle].[IPL Familyand Bottle].MEMBERS
);
THIS = IIF ([Item].[Price Segment].CurrentMember IS [Item].[Price Segment].&[L],
IIF ([Measures].[YTD RAD] > 0.2395,1, NULL),IIF ([Measures].[YTD RAD] > 0.4835, 1, NULL)
);
END SCOPE;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply