THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Multiselect friendly MDX for calculations looking at current coordinate

Last year I wrote a blog about Writing multiselect friendly MDX calculations, which according to the statistics quickly became one of the most popular blog entries. In that blog we discussed how AS converts multiselect calculated members with Aggregate into sets in WHERE clause and how to code MDX expressions in order to avoid references to CurrentMember function, which fails when there is a set in the current coordinate. However, it is not always possible to avoid direct or indirect reference to CurrentMember. Let's take as an example calculations for the previous period described in the Time calculations in UDM: ParallelPeriod (another very popular post). ParallelPeriod function needs to have single member as a CurrentMember, because ParallelPeriod returns a single member. The same is true about many other MDX functions - PrevMember, Lag, Lead, Parent, Ancestors, Siblings, FirstChild, LastChild etc.

To demonstrate the issue, let's run couple of queries using [Prior Year Internet Sales Amount] calculated measure we developed in the the Time calculations post. To remind how it was defined, here is the relevant snippet of MDX Script:

CREATE [Prior Year Internet Sales Amount];

([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Date].MEMBERS) = ([Measures].[Internet Sales Amount], ParallelPeriod([Ship Date].[Fiscal].[Fiscal Year]));
([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Calendar Quarter].[Calendar Quarter].MEMBERS) = ([Measures].[Internet Sales Amount], [Ship Date].[Calendar Quarter].Lag(4));
([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Calendar Semester].[Calendar Semester].MEMBERS) = ([Measures].[Internet Sales Amount], [Ship Date].[Calendar Semester].Lag(2));
([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Calendar Year].[Calendar Year].MEMBERS) = ([Measures].[Internet Sales Amount], [Ship Date].[Calendar Year].PrevMember);
([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Calendar Week].[Calendar Week].MEMBERS) = ([Measures].[Internet Sales Amount], [Ship Date].[Calendar by Weeks].Lag(53));

FORMAT_STRING([Measures].[Prior Year Internet Sales Amount]) = 'Currency';

If we will run the following query, where we multiselect August 7th and 8th 2003

select
{[Measures].[Internet Sales Amount],[Measures].[Prior Year Internet Sales Amount]} on 0
from [Adventure Works]
where {[Ship Date].[Date].&[768],[Ship Date].[Date].&[769]}

Or more traditional one with calculated member doing Aggregate

with member [Ship Date].[Date].[Agg] as Aggregate({[Ship Date].[Date].&[768],[Ship Date].[Date].&[769]})
select
{[Measures].[Internet Sales Amount],[Measures].[Prior Year Internet Sales Amount]} on 0
from [Adventure Works]
where [Ship Date].[Date].[Agg]

we will get the same error in the cell for [Prior Year Internet Sales Amount]:

#Error MdxScript(Adventure Works) (656, 116) The MDX function PARALLELPERIOD failed because the coordinate for the 'Fiscal Year' attribute contains a set

Since the [Ship Date].[Date] attribute has a set in the current coordinate, ParallelPeriod cannot operate. Fortunately, there is a solution in this case as well. The solution relies on the fact, that from the business logic of view multiselect and ParallelPeriod can be commuted, i.e. ParallelPeriod over set needs to be an Aggregate of ParallelPeriods. So what we want to do is to iterate over members of the multiselect set, compute ParallelPeriod for each one and aggregate the values. We already know that we can use EXISTING operator to extract the set of multiselect. Therefore the following line in MDX Script

([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Date].MEMBERS) 
  = ([Measures].[Internet Sales Amount], ParallelPeriod([Ship Date].[Fiscal].[Fiscal Year]));
becomes
([Measures].[Prior Year Internet Sales Amount], [Ship Date].[Date].MEMBERS) 
  = Sum(Existing [Ship Date].[Date].[Date].MEMBERS,(ParallelPeriod([Ship Date].[Fiscal].[Fiscal Year]), [Measures].[Internet Sales Amount]));

Now if we issue the query

with member [Ship Date].[Date].[Agg] as Aggregate({[Ship Date].[Date].&[768],[Ship Date].[Date].&[769]})
select
{[Measures].[Internet Sales Amount],[Measures].[Prior Year Internet Sales Amount]} on 0
from [Adventure Works]
where [Ship Date].[Date].[Agg]

We no longer get an error, instead we get correct answer - sum of August 7th and 8th in previous 2002 year. Unfortunately, this solution imposes some performance hit on the scenarios where there is no subselect. It, of course, still works correctly, since EXISTING operator returns a set with single member in it, and Sum works fine with set with only one element, but there is performance lost in computing the EXISTING and going through Sum machinery. This performance loss by itself isn't significant, but the problem is that the MDX expression became more complex - instead of single call to one function - ParallelPeriod there three functions now involved, and the query optimizer may have problems choosing the optimal execution plan when there are other calculations invoking this one.

Therefore, it is certainly a choice of cube designer - keep the simpler and more performance friendly expression, but accept that multiselect on the Time dimension will cause errors, or allow more functionality and enable multiselects on Time, but be prepared for slower performance in all cases.

Published Saturday, January 13, 2007 12:07 PM by mosha
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement