Time Ranges in AS2005

MDX 4 Minutes
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!

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

Published

5 thoughts on “Time Ranges in AS2005”

  1. Yes, this solution will work, but I don\’t like it too much, because it forces cube to become unnessesary bigger by including the same dimension twice. It isn\’t even a good case for role playing dimensions, because here really the Time dimension plays same role two times ! (i.e. it is bound to the same column in the fact table). Another drawback of this solution, is that even when looking at the very same data – the cache/aggregations won\’t be reused, i.e. (Sales, Period.August) are the same as (Sales,ComparePeriod.August), but AS won\’t have any means of knowing it :(I didn\’t think too much about the best solution here, but it seems to me, that the proper way of doing it won\’t involve MDX at all. I would explore path of creating multiple linked measure groups here – but of course this approach needs a reality check, I am not sure it will work directly.

    Reply

  2. Chris, sorry for my late response. i tried your solution and it really works,1. but i have some more issues to add after Mosha. a. as i understand we need to rewrite all "normal" measures because Compare Period will affect them.b. we have about 5 such dimensions for compare. (for example date, department, factory, …) so we will need to duplicate all of them and write in all formulas, and have such long filters twice (with "current" sales and "compared" sales)2. we saw problems with Grand Total in original Sales using no-relation dimension in April CTR, but in June release it start working… so really i am turning my head to no-relation dimensions.3. i will be surprised if i would not be able to get (and use) multi items from such no-relation dimension, if i can see it in browser… so my question – do you know how to get selected items from [Compare Period] dimension when i put it as filter in the browser?thanks for you attention, Mike

    Reply

  3. Hi Mike,To answer your questions:1 a) Yes, but you could avoid doing this by using the utility dimension approach (create a new hierarchy on one of your dimensions, create a calculated member on that dimension and always ensure that that calc is selected in your query) somehow which would be more elegant.b) Yes, which makes me think it might be better to stick with your current approach of a custom-built client.2&3) The problem with the no-relation approach is that you have to find what the selection made on the unrelated dimension is and translate that into a selection on the related dimension. At the moment OWC (I think) uses calculated members to do multi-select, which means it\’s not possible to find which members the user has actually selected.

    Reply

  4. Chris, I have a similar problem except that i need a way to provide an unlimited number of sets for slicing.  The logic would be that users would create several sets and perform a logical AND between the sets (OR within set).  To complicate matters, i can not make any assumptions on the number of sets a user may want to create from the same hierarchy, therefor using role-playing dimensions isn\’t practical.  Do you see any way to solve this problem using MDX?  My fear is that it\’s a cube-design issue.  We previously solved the problem by limiting the number of sets the user can create to 7, then creating 7 dimensions, one for each set. 

    Reply

  5. Hmm, if you needed unlimited sets then you\’d need to write a custom front-end of some kind – it sounds like you have some specialised needs that don\’t fit with any off-the-shelf tools that I know of and as you seem to have found you can\’t really put unlimited role playing dimensions into the cube.

    Reply

Leave a Reply