Month: July 2005

Time Ranges in AS2005

Chris Webb5 Comments
I had a mail at the beginning of last week from Mike Melnikov, who had been reading my blog entry on aggregating across a time range in AS2K. He told me he had a similar requirement which he’d implemented using a UDF in AS2K but was wondering if there was an easier way of doing what he wanted in AS2005. Basically he needs two Period dimensions in his cube, from which the user can select individual members or ranges, allowing the user to compare values for a measure across these two different Period selections. For example the user might want to compare Sales in January 1997 with Sales in February and March 1997 combined.
 
My original AS2K solution relied on having separate dimensions to specify the start and end dates of the time ranges, but for Mike’s problem this would mean four separate dimensions – rather a lot. However, OWC and Excel have always allowed multi-select on dimensions and I got the impression Mike was happy with using them, so this meant I could use just two dimensions; also, in AS2005 the ability to put sets in the WHERE clause makes selecting time ranges much easier and I wanted a solution that would work with this feature too.
 
The first step was to have two identical Period dimensions in the same cube, and this shows off one of the cool new features of AS2005 – role-playing dimensions. In AS2K in order to do this you needed two physical copies of the dimension in your cube, which is something of a waste of space and processing effort; in AS2005 you can have the same physical dimension appearing many different times in the same cube with different names. So in my cube, once my Period dimension had been added once I could simply add it again with the name [Compare Period].
 
Then was the question of what relationship to have between this second Period dimension and the cube. In the AS2K solution I went to a lot of effort to have a dimension which had no relationship with the cube, and of course AS2005 allows you to do this out of the box, but I decided not to do that and instead make it a ‘regular’ relationship and use MDX Scripting to do the work instead. If I’d have gone down the ‘no relationship’ approach I wouldn’t have been able to use sets in the WHERE clause (no way to use LINKMEMBER in this case, I don’t think) and I didn’t think I would have been able to get a two-dimension approach to work with OWC.
 
So my first thought on the MDX Script was that it should take the following approach:
  • Create a calculated measure called [Measures].[Sales Compare Period] to show the value of my original measure [Measures].[Sales], but for the second Period dimension and ignoring the first one
  • Populate the values of this new calculated measure by using an assignment like ([Measures].[Sales Compare Period]) = ([Measures].[Sales], ROOT([Period]))
  • Make sure the value of this calculated measure didn’t change as a result of the next assignment by using the FREEZE statement, so: FREEZE([Measures].[Sales Compare Period])
  • Then make sure the values of the original [Measures].[Sales] measure ignored the [Compare Period] dimension by making its value ([Measures].[Sales]) = ([Measures].[Sales], ROOT([Compare Period]))

So the MDX Script ended up looking like this:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]

AS NULL,

VISIBLE

= 1;

([MEASURES].[Sales Compare Period])=([Measures].[Sales],

ROOT([Period]));

FREEZE([MEASURES].[Sales Compare Period]);

([Measures].[Sales]) = ([Measures].[Sales],

ROOT([Compare Period]));

 

I found several problems though. First of all, the ROOT([MyDimension]) function only did what I wanted it to do (ie produce a tuple of all the All Members of every attribute hierarchy on MyDimension) when I didn’t have a set containing multiple members from MyDimension in the WHERE clause, and also failed for the Grand Total column for the original Sales measure in the OWC cube browser. I thought this was a bit rubbish and have raised this to the dev team, so hopefully it’ll get fixed. What I then did was to hard code the tuple instead of using ROOT(), so that my script looked like this:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]

AS NULL,

VISIBLE

= 1;

([MEASURES].[Sales Compare Period])=([Measures].[Sales],[Period].[Month].[All],[Period].[Quarter].[All],[Period].[Year].[All]);

FREEZE

([MEASURES].[Sales Compare Period]);

([Measures].[Sales]) = ([Measures].[Sales],[Compare Period].[Month].[All],[Compare Period].[Quarter].[All],[Compare Period].[Year].[All]);

The next issue was with overwriting the values of [Measures].[Sales] – it seemed to have different problems in the OWC cube browser in the Grand Total cells, gaving me an infinite recursion error, so I guess there was some kind of conflict with the VisualTotals functionality that OWC uses. Anyway at this point I thought I’d simply create another calculated measure to show these values instead, and not overwrite the values of the original measure but just hide it.

So, my MDX Script got changed again:

CALCULATE

;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Original Period]

AS ([Measures].[Sales],[Compare Period].[Month].[All],[Compare Period].[Quarter].[All],[Compare Period].[Year].[All] ),

VISIBLE

= 1 ;

CREATE

MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]

AS ([Measures].[Sales],[Period].[Month].[All],[Period].[Quarter].[All],[Period].[Year].[All] ),

VISIBLE

= 1 ;

 

Disappointingly old school really, although I suppose I could have done the same thing in a more verbose manner and still managed to use MDX Scripting. At least it works in OWC and I can use sets in the WHERE clause, for example:
 
SELECT {[Measures].[Sales Original Period],[Measures].[Sales Compare Period]}  ON 0,
{[Period].[Month].&[1], [Period].[Month].&[2]}
ON 1
FROM CUBE1
WHERE({[Compare Period].[Month].&[1], [Compare Period].[Month].&[3]})
 
… and it all works. Perhaps a certain MDX guru whose name begins with M can come up with a better way of solving this though?
 
Incidentally, I welcome emails like Mike’s asking me questions. I learn a lot from trying to work out the answers to other people’s questions!

Free AS2005 course from MS

Chris Webb7 Comments
Seen on Euan Garden’s blog:
Free SQL 2005 courses! Including one course on new features in AS2005:
Have to download it and check it out – it looks like there’s some info there which hasn’t made it into BOL and I don’t know about…
 
UPDATE: if you’re trying to view this course from a machine with SQL2005 installed on it (now why would you want to do that?) you’ll find that you might get an ‘unable to initialize viewer’ error. MS Support told me how to get round this – unregister msmxml6.dll – but I don’t know what impact that will have on anything else you might be running.