Scoped Assignments and Multiselect

Reposted from Chris Webb's blog with the author's permission.

Something interesting to note regarding how scoped assignments behave with multiselect…

On the Adventure Works cube, add the following code to the MDX Script:

CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;      SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[Calendar Year].MEMBERS);         THIS = 2;     END SCOPE;       SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[All Periods]);         THIS = 3;     END SCOPE;

Then run the following query:

SELECT [Measures].[TESTCALC] ON 0

FROM [Adventure Works]

WHERE([Date].[Calendar Year].&[2001])

It returns the value 2 as you would expect. Now run the following query where there is a set in the Where clause, giving a multiselect on 2001 and 2002:

SELECT [Measures].[TESTCALC] ON 0

FROM [Adventure Works]

WHERE({[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002]})

It returns the value 1 – which, strangely, is the value of the original TESTCALC calculated measure before any of the scoped assignments were applied, even though it would seem that the two scoped assignments between them should cover the cells returned by this query.

Now delete the code you added to the MDX Script and add the following:

CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;
  SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].MEMBERS);

    THIS = 4;

END SCOPE;

When you try to run the two queries above you get the value 4 returned in both cases. When I first saw this I thought the results returned by two scenarios were inconsistent and that there was something buggy going on in the first one, but Jeffrey told me there is a specific rule in the engine that dictates this behaviour, so it’s how it’s intended to work – as a result, it’s something that needs to be understood and allowed for in any scoped assignments you write.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .