MDX Scripting with SCOPE Statement

Who is online?  0 guests and 0 members
Home  »  Articles  »  MDX Scripting with SCOPE Statement

MDX Scripting with SCOPE Statement

change text size: A A A
Published: 3/15/2010 by  DustinRyan  - Views:  [6521]  

While working at a client this past month, I encountered a set of special requirements that specified the need for some sets of custom calculations to be applied to some very specific subcubes. I may have been able to accomplish this goal with some very complex and painful-to-look-at MDX, but a better alternative for solving these requirements was to use a SCOPE statement. A SCOPE statement is ideal for limiting an MDX expression to a specific subcube, and in this article I am going to show you how, using a simple example in the Adventure Works cube, to make this happen.

So imagine that you need to apply some special calculations to a measure group that holds some forecasted sales numbers. As you might expect, sales in the fourth quarter will probably be higher than sales during the rest of the year. Also, sales within the Thanksgiving to Christmas time frame might also be higher than other weeks within the fourth quarter. To reflect the increase in the amount of sales during that time of the year, we'll need to use an MDX script that utilizes the SCOPE statement to apply a calculation to adjust those forecasts sales measures accordingly.

Browsing the Adventure Works cube, I've sliced the Sales Order Count measure (which will be our imaginary Forecasted Sales Order Count measure group) by Month of the Year and Calendar Quarter of the Year, just so we'll have some base line numbers to compare to our calculations after they've been applied.

 MDX Scope Statement

But because we want to reflect the increase in sales during the fourth quarter, let's create an MDX calculation script that uses the SCOPE statement that makes this adjustment for us. Go to your Calculations tab after opening your Analysis Services project, and click the New Script Command icon New Script Command icon.

Use this MDX script to apply our custom calculation to the subcube including the fourth quarter of our calendar year and Sales Orders Count measure:

SCOPE ([Date].[Calendar Quarter of Year].&[CY Q4],[Measures].[Order Count]); 

THIS = ([Measures].[Order Count]*2); 

END SCOPE  

The SCOPE statement specifies the subcube to which our custom calculation should be applied. The THIS statement specific the custom calculation to be applied to the subcube.

After adding this script and deploying and processing the cube, you can see the results of our calculation here.

MDX Scope Statement results

Now the Sales Order Count during the fourth quarter has been multiplied by two while the rest of the Sales Order Counts during the rest of the year remain untouched.

To apply a custom calculation to the month of December to reflect the forecasted increase in sales during the month, you would do something similar to what we just did: 

SCOPE ([Date].[Calendar Quarter of Year].&[CY Q4],[Measures].[Order Count]); 

THIS = ([Measures].[Order Count]*2);  

END SCOPE

SCOPE ([Date].[Month of Year].&[12],[Measures].[Order Count]); 

THIS = ([Measures].[Order Count]*2.5); 

END SCOPE 

Here are the results:

Scope statement results

Be aware of the fact that our custom calculations have stacked on top of one another. Because the initial calculation applied to the subcube of the fourth quarter has already been applied by the time the calculation is being applied to the month of December, we are actually multiplying the Sales Order Count by 2, and then by 2.5 for the month of December. If we wanted to only multiply the Sales Order Count by 2 for the months of October and November, and then multiply the Sales Order Count for the month of December by 2.5 without stacking our calculations, we'd need to do something like this:

SCOPE (({[Date].[Month of Year].&[10],[Date].[Month of Year].&[11]}),[Measures].[Order Count]);

THIS = ([Measures].[Order Count]*2);

END SCOPE; 

SCOPE ([Date].[Month of Year].&[12],[Measures].[Order Count]);

THIS = ([Measures].[Order Count]*2.5);

END SCOPE

And the results: 

MDX Scope Statement results 

I hope this has been a good example to help you understand how to use the SCOPE statement in your calculations and to give you a good starting point for applying calculations to certain subcubes within your cube. Another useful aspect of SCOPE statements to keep in mind is the fact that SCOPE statements can be embedded within one another, allowing you to apply one calculation to a specific subcube and another calculation to a certain subcube within the first specific subcube, so feel free to try SCOPE out and get as creative as you can to meet those obscure and confusing requirements handed to you by your end users! And feel free to post any comments, questions, or feedback, BIDN buddies!

 
4.66
/5
Avg: 4.66/5: (1 votes)

Comments (18)

DevinKnight
DevinKnight said:
Cool stuff! I just need an excuse to use it now
3/16/2010
 · 
 
by
Frankkearney
Frankkearney said:
Good stuff Dustin. I know that using SCOPE can be kind of confusing to some people including myself, and it is always nice to see "real world" examples of it other than time intelligence.
3/29/2010
 · 
 
by
CurrentMember
CurrentMember said:
Good article Dustin. Thanks for sharing! Luke
3/31/2010
 · 
 
by
dustinryan
dustinryan said:
Thanks, homies. I think eventually I'm going to do another article with another example of SCOPE, except something a little more complex.
3/31/2010
 · 
 
by
charan
charan said:
So I created a view TicketDescription, using the Rank I assing unique values to set of ticket and description.... I dump this RankId into Fact table...by joining the fact measure view to the view for Ticket Description. Now the fact has one measure called the response duration.....things work good ..I process and I go Browse the cube...If I drag ticketid 1...and then description...with Response Duration as measure...I get duplicate values... So.. something like this Ticket ID Response Duration(measure) 1 31 If I Use Description Ticket ID Description Response Duration(measure) 1 CreatedTicket 31 Ticket Escalated 31 Ticket terminated 31... Total is 93...which is wrong... So solve this...I pivoted the view Ticket Description on the basis of line item....Now when I pivot....each line itme holds a description.. SOmetin glike this TicketID lineitem1 lineitem2 lineitme3 1 Created Ticket Ticket Escalated Ticket terminated By doing this, the issue solves, but than....I will have to have 3 attributes for users to select to view the description...and which will be across the id...however they would like to see in single column.... Problem 1: how to avoid the aggregation if I do not pivot the description PRoblem 2: If I pivot then how to enable the users to browse on description displayed in single column instead of three separate columns...
3/28/2013
 · 
 
by
DustinRyan
DustinRyan said:
Please post your questions in the forums.
3/28/2013
 · 
 
by
hai98
hai98 said:
You are Providing brief information, this is very useful to us. Thank You Charan
4/21/2010
 · 
 
by
gdglee
gdglee said:
What I'm looking for is a way to modify for instance december data and have that data aggregate up whatever hierarchy it is in. In your examples the total is not modified, unless it is within scope. Do you think that is possible using the scope statement? Other suggestions if not?
5/4/2010
 · 
 
by
dustinryan
dustinryan said:
Yes, it's possible to do this with the scope statement. The key to using SCOPE is to be explicit with it. You'll need to specify the exact scope of the sub cube that you wish to modify. So if you have a measure that you want to aggregate differently depending on which hierarchy it is in, then you'll need to specify that in your SCOPE statement.
5/4/2010
 · 
 
by
ramdas
ramdas said:

Thank you, you have explained the use of SCOPE with examples which is very helpful. One of the things i have come across with MDX is the steep learning curve.

7/12/2010
 · 
 
by
dustinryan
dustinryan said:

No problem, ramdas. MDX does have a slightly steep learning curve, partly I think because it appears similar to tsql but is not. Once you can grasp that it becomes a bit easier. At least that was the case for myself.

7/12/2010
 · 
 
by
briankmcdonald

I like.. I like a lot... As Devin stated, I hope that I can make use of it soon!

8/5/2010
 · 
 
by
mikedavis
mikedavis said:

I just got an excuse to use this and it rocks.

8/5/2010
 · 
 
by
sxuan629
sxuan629 said:

Thank you!. This is really good for me to understand SCOPE.

3/23/2011
 · 
 
by
sxuan629
sxuan629 said:

Thank you!. This is really good for me to understand SCOPE.Smile

3/23/2011
 · 
 
by
akshaymdeo
akshaymdeo said:

I want to know one thing, as per my knowlegde the scope statement is view related as in the calculation is instatntiated when a perticular scope condition gets satisfied. So for huge ammount of data, according to me storing the data (calculations) in cube is a better option. Could you throw some light on this topic. 

3/28/2011
 · 
 
by
DustinRyan
DustinRyan said:

If you can store the calculations, thats always going to be the best performing option. I would always recommend storing the calculations in the cube when possible so only use SCOPE when necessary.

3/30/2011
 · 
 
by
ranjani_v
ranjani_v said:
Hi Dustin, I'm new to the MDX world, the above article help me a lot in understanding the scope .Can you pelase explain si,ilar for multiple this in MDX Here is the code I took from MSDN. Scope ( [Date].[Fiscal Year].&[2005], [Date].[Fiscal].[Fiscal Quarter].Members, [Measures].[Sales Amount Quota] ) ; This = ParallelPeriod ( [Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember ) * 1.35 ; /*-- Allocate equally to months in FY 2002 -----------------------------*/ Scope ( [Date].[Fiscal Year].&[2002], [Date].[Fiscal].[Month].Members ) ; This = [Date].[Fiscal].CurrentMember.Parent / 3 ; End Scope ; End Scope ; Thanks!
7/6/2012
 · 
 
by
Kshema
Kshema said:
Scope statmenent syntax confused me, but you example make make me understand it well. Thank You.
7/13/2012
 · 
 · DustinRyan likes this.
 
by
djha
djha said:
hey Dustin, thanks for the very simple and easy explnation. It has helped me understanding better than what it is explained at MSDN
8/22/2012
 · 
 · DustinRyan likes this.
 
by
DustinRyan
DustinRyan said:
Great!
8/22/2012
 · 
 
by

Most Recent Articles