Problem solve Get help with specific problems with your technologies, process and projects.

MDX functions: SCOPE, THIS and FREEZE

MDX in SQL Server 2005 Analysis Services brings exciting improvements, including query support and expression/calculation language.

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]) *

Let's take a snapshot of values before we apply the calculation with the following query:

DESCENDANTS([Date].[Calendar].[Calendar Year].&[2002],        [Date].[Calendar].[Calendar Quarter]),
{measures.[sales amount]}) ON 0,
{[product].[category].[bikes]} ON 1
FROM [adventure works]


  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]) *

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]) *

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

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

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.

Dig Deeper on Microsoft SQL Server Analysis Services (SSAS)