MDX functions: SCOPE, THIS and FREEZE

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 amount]}) ON 0,
{[product].[category].[bikes]} ON 1
FROM [adventure works]

Results:

After the calculation is applied, the results for quarter 4, 2002, look different, as shown below:

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:

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.