I’m honoured by my mention in Mosha’s blog! But I think my entry on Grouping in MDX, and Mosha’s comments on it, need some further clarification and discussion.
First of all, CREATE SESSION CUBE. I agree it is probably the best way to implement grouping at the moment and that it works well in Excel, but it’s not ideal:
- Surely it’s a client-only solution? You’d want the option of creating these groupings on the server for all users, just as you would any other calculated member, and CREATE SESSION CUBE doesn’t offer you that, does it?
- I don’t know what the overhead of creating a session cube on the client is (or, in Yukon, on the server) – but is it as small as creating a calculated member? What is the impact on caching, especially given the fact that you have to drop and create the session cube when you change or add a new group?
- A lot of the time, for example in Reporting Services, you are only working with one-off queries rather than sessions and you really want to be able to create a group with query rather than session scope.
- The MDX needed to create the session cube and then manage it within the session seems overly complex for such a simple requirement.
Secondly, to address Mosha’s point on why you would need the VisualTotals and Aggregate functions in the same query: it’s because you’d want to use your new group member in any scenario where you could use a normal member, and that includes a query which used VisualTotals. Imagine you had a measure which showed the distinct count of customers across all your stores, and you wanted a report which had a) a single group member containing your top 5 stores, b) several other individual stores and c) showed the visualtotal of all the distinct customers in both the group and the individual stores displayed. I think that would be a reasonable requirement and one which wouldn’t be possible unless AS ‘knew’ what members went into the group.
Thirdly, sets in the WHERE clause (and also subcubes in the FROM clause, which do the same thing) inYukon. Unfortunately, this only works when you’re slicing by the group and not when the group is on a visible axis so it doesn’t fit the scenario I was describing.
Overall, then, CREATE SESSION CUBE is almost the functionality that I want but it doesn’t allow groups to be defined on the server. So we’re close…!