Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Grouping members together

with 2 comments

One of the weaknesses of Analysis Services, in my opinion, is support for creating custom groupings of members. I reckon that 90% of all calculated members on non-measures dimensions must be doing just this, ie just doing an AGGREGATE or SUM over a set of members, and yes calculated members will return the right values but my complaint is something else. It’s that you then have no idea what members were aggregated together inside this calculated member, and that functions like VISUALTOTALS, NONEMPTYCROSSJOIN etc that you would like to be ‘group aware’ of course aren’t. Some examples needed, I think…

Consider the following query on Foodmart 2000:

WITH MEMBER [Customers].[All Customers].[USA].DEMO AS ‘AGGREGATE({[Customers].[All Customers].[USA].[CA], [Customers].[All Customers].[USA].[OR]})’
SELECT MEASURES.MEMBERS ON 0,
{[Customers].[All Customers].[USA].CHILDREN, [Customers].[All Customers].[USA].DEMO} ON 1
FROM SALES

Wouldn’t it be nice, then if VISUALTOTALS ‘knew’ what was in the set and this query

WITH MEMBER [Customers].[All Customers].[USA].DEMO AS ‘AGGREGATE({[Customers].[All Customers].[USA].[CA], [Customers].[All Customers].[USA].[OR]})’
SELECT MEASURES.MEMBERS ON 0,
VISUALTOTALS({[Customers].[All Customers].[USA],{[Customers].[All Customers].[USA].[CA], [Customers].[All Customers].[USA].[OR]} },  "* DEMO")
ON 1
FROM SALES

returned the same results as this query?

WITH MEMBER [Customers].[All Customers].[USA].DEMO AS ‘AGGREGATE({[Customers].[All Customers].[USA].[CA], [Customers].[All Customers].[USA].[OR]})’
SELECT MEASURES.MEMBERS ON 0,
VISUALTOTALS({[Customers].[All Customers].[USA],[Customers].[All Customers].[USA].DEMO },  "* DEMO")
ON 1
FROM SALES  

And that when you did a NONEMPTYCROSSJOIN against your calculated member, it would return the same results as when you did a NONEMPTYCROSSJOIN against the set that was aggregated in the calculated member? And perhaps also that you could drill down from the calculated member to see the members inside it?

Of course this isn’t possible at the moment, because a calculated member could contain any sort of calculation, so AS simply can’t make any assumptions. But if there was a special kind of group calculated member, which simply took a set of members as its definition and which always returned an AGGREGATE of that set, surely AS could make these assumptions?

Just a thought…

Written by Chris Webb

January 12, 2005 at 4:00 pm

Posted in Random Thoughts

2 Responses

Subscribe to comments with RSS.

  1. Well, there is a support for custom grouping in the form of CREATE SESSION CUBE – that Excel uses in order to create custom grouping. I also don\’t understand why would you use both Aggregate and VisualTotals in the same query. Anyway, in Yukon the right way to do this kind of things are by using sets in WHERE clause, and since they change the current coordinate to set – all of the MDX functions suddenly become aware of it.

    Mosha

    January 23, 2005 at 12:54 am

  2. I outlined another method of doing this (in AS2000) in a comment at:
     
    http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!654.entry
     
     

    David

    June 12, 2006 at 10:42 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,081 other followers

%d bloggers like this: