Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Order of Nested SCOPE Statements

with 2 comments

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:

image

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:

image

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:

image

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:

image

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.

Written by Chris Webb

August 3, 2010 at 10:20 pm

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  1. Thanks Chris !

    Elizabeth

    August 7, 2010 at 5:48 am

  2. […] For this calculation, we need to make two scoped assignments on a single calculated measure called [YTD Sales]: one to show the Fiscal YTD calculation for the Fiscal Date, Fiscal Month Name, Fiscal Quarter, Fiscal Semester and Fiscal Year attributes; and one to show the Calendar YTD calculation for the Calendar Date, Calendar Month Name, Calendar Quarter, Calendar Semester and Calendar Year attributes. When scoping on ranges of attributes like this, there’s another easy rule to follow: scope on the set of every member, including the All Member, from the attribute hierarchy at the lowest granularity attribute, and the set of every member, not including the All Member, from the highest granularity attribute. These two sets also need to be in the same, rather than separate, SCOPE statements for reasons I explain here. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers

%d bloggers like this: