Few words about: SCOPE and THIS keywords

Today I would like to talk a little bit about two MDX keywords: SCOPE and THIS.  Below I would like to show how to use it and build something called subcube.

Subcube is a simple term when we talk about slice and dice our cube. For Example if our multidimensional cube has only one dimension attribute: Year and OrderQuantity as a measure, if we filter this dimension to have only year 2010 – it will be subcube of entire cube.

As you can see here, SCOPE syntax is very simple – in brackets we have to put a subcube expression. After this we can operate on specified subcube, if we want to change it we need to use another keywords – END SCOPE.  If we close our SCOPE with END SCOPE our script comes back to default scope(entire cube).

SCOPE

(

[Measures].[Internet Order Count],

[Date].[Calendar Year].&[2010]

);

END SCOPE;

Example above shows that we want to operate on specific measure together with specific member of Date dimension. But you can ask why we need something like this? There is a lot of reasons, one of them is ability to set specific value to specific member – How to achieve it? With THIS!:)

To explain how it works we analyze simple example below.

SCOPE

(

[Measures].[Internet Order Count],

[Date].[Calendar Year].&[2010]

);

This=20000;

End Scope;

We set Scope to year 2010 and correlate it to Internet Order Count. After that we use Keyword this with equal operator and set static value 20000. OF course we can use dynamic MDX statements not only static values. As you can see in the picture below value will be visible in the right place.

mdx resultFeel free to use my example – you only need SSAS OLAP project deployed that can be downloaded here.

Advertisements

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