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:
Q1 CY 2002Q2 Cy 2002Q3 Cy 2002Q4 CY 2002
Sales AmountSales AmountSales AmountSales 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 2002Q2 Cy 2002Q3 Cy 2002Q4 CY 2002
Sales AmountSales AmountSales AmountSales 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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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:
[IMAGE]
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:
[IMAGE]
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-02Nov-02Dec-02Q4 CY 2002
Sales AmountSales AmountSales AmountSales 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.
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.
Copyright 2007 TechTarget
More on this topic:
Use cube partitions to improve Analysis Services performance
Analysis Services 2005 in SQL Server has improved security
SQL Server Business Intelligence toolbox
