Order of Nested SCOPE Statements

Funny how you can work with a product for years and years and still discover new things, isn’t it? I was writing some scoped assignments on a cube the other day and found that when you’re using nested SCOPE statements, the order that you put those SCOPE statements in the MDX Script is significant, contrary to what I had believed.

Consider a simple date dimension with the following attributes and attribute relationships:

Now, if we add the following MDX to the script, to scope on every member (including the All Member) on the date dimension:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;

SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Date].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;

You’ll see that it has changed the values of the DEMO measure for the whole of the date dimension from 1 to 2:

Now, if we add a second nested SCOPE on all the members of the Month level of the Month attribute (ie so not including the All Member on Month) as follows:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Date].MEMBERS);
SCOPE([Order Date].[Month].[Month].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
END SCOPE;

You’ll see that it now only changes month values, and nothing else:

This set of assignments gives the same result as the following, where there is no assignment on Date at all:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Month].[Month].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;

However, if you reverse the order of the two SCOPEs you get a different result. So:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Month].[Month].MEMBERS);
SCOPE([Order Date].[Date].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;
END SCOPE;

When you scope on all the members of Month except the All Member first, then all the members of Date including the All Member, the scope covers all dates and months:

Crossjoining these two sets in the same SCOPE has the same effect:

CREATE MEMBER CURRENTCUBE.MEASURES.DEMO AS 1;
SCOPE(MEASURES.DEMO);
SCOPE([Order Date].[Month].[Month].MEMBERS,[Order Date].[Date].MEMBERS);
THIS = 2;
END SCOPE;
END SCOPE;

What’s happening is that where you have two nested SCOPE statements using sets of members from different attribute hierarchies on the same dimension, the attribute relationships that exist between those attributes become significant. Without any SCOPE statements then the ‘current’ scope context in the MDX Script is the entire cube, and then each successive SCOPE overwrites the previous context to create a new context and that process of overwriting context is not commutative. This is called attribute overwrite; I’ve wrestled with it before and frankly it makes my head hurt, but you can find out more about it here.

I suppose, therefore, that it would be a good idea when you’re scoping on sets containing members from multiple attributes from the same dimension to crossjoin those sets together and use a single SCOPE, to avoid any potential confusion. While I can just about rationalise this behaviour I can’t guarantee I’d be able to predict how attribute overwrite worked on a real dimension with multiple nested SCOPEs… Remember, of course if you have multiple nested SCOPEs using sets of members from hierarchies on different dimensions then the order of nesting doesn’t matter because there’s no attribute overwrite going on.

Thanks to Tomislav and Akshai for their help in understanding this problem.