MDX also offers several new constructs specific to calculations embedded within MDX scripts – they cannot be used for queries. The SCOPE statement can be particularly handy for budgeting applications or for "what if" analysis. SCOPE allows you to specify the conditions in which the assignment of values should take place. You can use another new statement, "THIS", in conjunction with the SCOPE statement to perform the actual assignment of a value (or calculation) to one or multiple measures.
For example, let's suppose we were in the budgeting process and we predicted that bike sales in the fourth quarter of 2002 would be 50% higher than bike sales in the first quarter of the same year. We expect such an increase due to all the Christmas shopping people will do in the fourth quarter. So we could set the sales amount for the bikes' category as follows:
SCOPE (measures.[sales amount],
[product].[category].[bikes],
[Date].[Calendar].[Q4 CY 2002]);
THIS = ([product].[category].[bikes], [Date].[Calendar].[Q1 CY 2002])
*
1.50
END SCOPE;
Let's take a snapshot of values before we apply the calculation with the following query:
SELECT CROSSJOIN(
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2002],
[Date].[Calendar].[Calendar Quarter]),
{measures.[sales
Requires Free Membership to View
amount]}) ON 0,
{[product].[category].[bikes]} ON 1
FROM [adventure works]
Results:
| Q1 CY 2002 | Q2 Cy 2002 | Q3 Cy 2002 | Q4 CY 2002 | |
| Sales Amount | Sales Amount | Sales Amount | Sales Amount | |
| Bikes | $5,669,191.70 | $5,759,704.33 | $8,035,127.17 | $7,022,335.00 |
After the calculation is applied, the results for quarter 4, 2002, look different, as shown below:
| Q1 CY 2002 | Q2 Cy 2002 | Q3 Cy 2002 | Q4 CY 2002 | |
| Sales Amount | Sales Amount | Sales Amount | Sales Amount | |
| Bikes | $5,669,191.70 | $5,759,704.33 | $8,035,127.17 | $8,503,787.56 |
Note that we could also use a similar construct to modify a measure based on its existing value. For example, the following would be a valid statement, increasing the sales amount for quarter, 2002, by 30%:
SCOPE (measures.[sales amount],
[product].[category].[bikes],
[Date].[Calendar].[Q4 CY 2002]);
THIS = ([product].[category].[bikes], [Date].[Calendar].[Q4 CY 2002])
*
1.3
END SCOPE;
The SCOPE statement can affect one, a multiple or all members of a given hierarchy or level. For example, we could modify the sales amount across all members in order to see what the profits would look like, given that the cost remains the same, as follows:
SCOPE (measures.[sales amount],
[product].[category].[bikes],
[Date].[Calendar].[Calendar Quarter].members);
THIS = ([product].[category].[bikes], [Date].[Calendar].[Q1 CY 2002]) *
1.5
END SCOPE;
The FREEZE statement prevents modifications to the measured values, from any calculation that appears after the current statement in the MDX script. FREEZE can be useful if you have a calculation that needs to apply to certain levels within a given hierarchy without affecting other levels. For example, we might want to increase the current value of the sales amount for bikes across each month within the fourth quarter of 2002. However, the current value for that quarter should remain intact. We could accomplish this with the following statements:
This type of calculation logic would be useful if one group of users would like to see budgeted values at the month level and the other group would like to view the current values at the quarter and year levels.
We can confirm new values after the calculations are applied by using the following query:
The results below confirm that the value for the fourth quarter of 2002 hasn't changed because the sum of October, November and December would be considerably higher:
| Oct-02 | Nov-02 | Dec-02 | Q4 CY 2002 | |
| Sales Amount | Sales Amount | Sales Amount | Sales Amount | |
| Bikes | $2,821,775.57 | $4,244,891.85 | $3,466,835.09 | $7,022,335.00 |
Summary
This tip introduced you to some of the most exciting improvements to MDX -- query, expression and
calculation language for Analysis Services 2005. Although at first glance the language isn't
drastically different, it has grown considerably compared to previous versions of Analysis
Services.
More powerful Analysis Services MDX in SQL
Server 2005
Home: Introduction
Part 1: CASE statements
now supported by MDX script
Part 2: Sub-queries
supported in Analysis Services MDX
Part 3: MDX functions: SCOPE, THIS and
FREEZE
ABOUT THE AUTHOR
Baya Pavliashvili is a DBA manager overseeing database operations that support more than
one million users. Pavliashvili's primary areas of expertise are performance tuning, replication
and data warehousing.
This was first published in March 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation