Jon Axon’s blog

OK, this is nothing whatsoever to do with BI, but my colleague Jon Axon has started his own blog here. If you’re interested in concurrent programming then check it out – and make sure you hit the site several times to make him feel like someone is actually reading his blog! It will make him feel so much better…

Grouping members together

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…

Aggregating values across a date range

Very often you’ll come across a requirement for users to be able to aggregate values across abitrary date ranges. For example, they might want to see the sum of Unit Sales between any two months, say Feburary and April 1997. While this is fairly easy to accomplish with a calculated member when you have control of the MDX, when your users want to use an off-the-shelf client it becomes more difficult – how do you allow people to pick a start date and an end date on your single time dimension?

The way I usually solve this is by creating two time dimensions, one for the start date and one for the end date. However if you hang both off the time key column in your fact table you’ll see that initially no data is returned unless you select the same members on both dimensions – a transaction can’t be in two months simultaneously. You can get round this in a calculated member and sum up the values between the two months, but its not very elegant. What you really want is for your start date dimension to act like a normal dimension and for the end date dimension to be present in the cube but have no effect on it, so then in your calculated member you can simply aggregate all the values between the currentmember on start date and the member on start date which equates to the currentmember on end date.

This is actually very easy to do in AS2005 as you can add a dimension to a measure group without it having any relationship to the fact table. In AS2K though you need to go through the following steps:

  • Create your cube with one time dimension as usual, and call it Start Date
  • Copy this dimension and paste it to the same database, and when asked to rename it call it End Date
  • Create a virtual cube containing everything from your original cube.
  • To add the End Date dimension to this virtual cube you can’t, unfortunately, use Analysis Manager, you have to use some DSO code. Here’s a bit of VBScript as an example:

Dim myserver
Dim db
Dim vcube
Dim fromdim
Set myserver=createobject("dso.server")
myserver.connect("MyServer")
Set db = myserver.mdstores("MyDatabase")
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
Vcube.update

  • Now you have a virtual cube with two time dimensions, but the End Date dimension does nothing. To automatically sum up the values of a measure between the selected start and end dates, you would create a calculated measure something like:

SUM({
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
:
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END DATE].CURRENTMEMBER, [START DATE]))
}, VALIDMEASURE(MEASURES.[MY MEASURE]))

One last thing to notice – since we’re now summing up a range of months rather than returning a single month, query performance might suffer. In which case it might be worth taking a look at my recent entries on tuning YTD-style queries… 

More on tuning YTD-style calculations

I’m indebted to Deepak Puri for coming up with a much less complex MDX expression for finding the common ancestors in a set of months. Here it is:

WITH
/*The initial time range expressed in months*/
SET MYRANGE AS ‘{[Time].[1997].[Q1].[3] :[Time].[1998].[Q3].[7]}’
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] asExcept(Descendants([Time].CurrentMember,, LEAVES), MYRANGE).Count = 0′
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE ASFilter([Time].Members, [Measures].[InRange] AND Not ( [Measures].[InRange], [Time].Parent))’
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 ASSETTOSTR(MYRANGE)’
MEMBER MEASURES.STEP5 ASSETTOSTR(MYNEWRANGE)’
/*Show working*/
SELECT {MEASURES.STEP1, MEASURES.STEP5} ON 0 FROM SALES

%d bloggers like this: