Defining Scoped Assignments Using Script Commands

Article
12/03/2008

In the tasks in the previous two topics in this lesson, you learned to add calculated members and named set calculations to the calculation script of a cube. In addition to these two specific types of Multidimensional Expressions (MDX) calculations, you can also use the New Script command to perform most actions within a cube that MDX supports.

In Microsoft SQL Server 2005 Analysis Services (SSAS), MDX scripts can apply either to the whole cube or to specific sections of the cube, at specific points within the execution of the script. You have already learned about the default script command, the CALCULATE statement, which populates cells in the cube with aggregated data based on the default scope.

The default scope is the whole cube, but as mentioned in the previous topic, you can use the SCOPE statement to define a more limited scope, known as a subcube, and then apply an MDX script to only that particular cube space. The SCOPE statement defines the scope of all subsequent MDX expressions and statements within the calculation script until the current scope ends or is rescoped. The THIS statement is then used to apply an MDX expression to the current scope. You can use the BACK_COLOR statement to specify a background cell color for the cells in the current scope, to help you during debugging.

In the tasks in this topic, you will use the SCOPE and THIS statements to define sales quotas for each fiscal quarter within fiscal year 2005. You will then allocate sales quotas to the month level for all fiscal years in the cube. You will also learn about how to use breakpoints to help you in debugging the calculation script.

Reviewing Sales Amount Quota Allocations by Time and Employee

To review the sales amount quota allocations by time and employee

Open Cube Designer for the Analysis Services Tutorial cube, and then click the Browser tab.
Remove all hierarchies and measures from the Data pane and remove all dimension members from the Filter pane.
Add the Sales Amount Quota measure from the Sales Quotas measure group to the data area of the Data pane.
Add the Fiscal Time user-defined hierarchy to the column.
Add the Employee Name attribute hierarchy to the row area.

Notice that no sales quota values have been defined for the 2005 fiscal year.
In the column area, remove FY 2002 and FY 2003.
In the column area, expand FY2004, expand H1 FY 2004, and then expand Q3 FY 2004.

Notice that the sales amount quota for each fiscal month in the fiscal quarter is the same amount as the sales amount quota for the fiscal quarter. This is because the grain of the time dimension in the Sales Quota measure group is the quarter level, as discussed in Lesson 5.

The following image shows the sales quota for each employee who has a sales quota for each month within the fourth quarter of fiscal year 2004.

Defining the Scope for the Sales Amount Quota Calculation for Fiscal Year 2005

In this task, you will review the current scope, modify the scope, and then define a calculation that determines the sales amount quota values for the 2005 fiscal year based on the values for the 2004 fiscal year.

To define the scope for the Sales Amount Quota calculation for fiscal year 2005

Select the Calculations tab, and then select Form View on the toolbar.
In the Script Organizer pane, select Large Resellers, and then click New Script Command on the toolbar of the Calculations tab.

Notice that an empty script appears in the Calculation Expressions pane and that this script command is displayed with a blank title in the Script Organizer pane.
In the Calculation Expressions pane, type the following statement:
```
/* Changing Scope to All or Default Member */
SCOPE (ROOT())
```
This SCOPE statement changes the cube scope to the All, or default, member for all attributes in the cube.
Click New Script Command on the toolbar of the Calculations tab, and then type the following statement in the Calculation Expressions pane:
```
/* Defining the cube scope for the sales amount quotas for FY2005 */
SCOPE ()
```
Notice that a red squiggly line appears underneath the final parenthesis, to indicate that you have to define a set of members within the parentheses for the SCOPE statement. You will start by adding the Sales Amount Quota measure to the SCOPE statement to include this measure within the scope.
On the Metadata tab in the Calculation Tools pane, expand Measures, expand Sales Quotas, and then drag the Sales Amount Quota measure into the parentheses for the SCOPE statement in the Calculation Expressions pane.

Notice that the red squiggly line disappears. Next, you will add the FY 2005 dimension member to the SCOPE statement to add this time dimension member to the current scope.
On the Metadata tab in the Calculation Tools pane, expand Date, expand Fiscal, expand Fiscal Time, expand Fiscal Year, and then drag the FY 2005 member into the SCOPE statement in the Calculation Expressions pane immediately after the [Sales Amount Quota] member of the set that you are defining.

Notice the red squiggly line under the [Date] part of this new member of this set. This line informs you that a syntax error exists immediately before this dimension member, because a comma is required between each member of the set within the SCOPE statement.
Add the necessary comma before the [Date] part of the FY 2005 member of the Date dimension.

Notice that the initial red squiggly line disappears. Next, you will add the members of the Employees user-defined hierarchy in the Employee dimension to the SCOPE statement to add these members to the current scope.
On the Metadata tab in the Calculation Tools pane, expand Employee, and then drag the Employees user-defined hierarchy into the SCOPE statement in the Calculation Expressions pane immediately after the [Date].[Fiscal Time].[Fiscal Year].&[2005] member of the set that you are defining.

Notice the red squiggly line under the [Employee] part of this new member of this set, to indicate that a comma is required between each member of the set within the SCOPE statement.
Add the necessary comma before the [Employee] part of the new member.

Notice that the initial red squiggly line disappears.
At the end of the [Employee].[Employees] member of this set within the SCOPE statement, add the following clause to complete the definition of this third member of the set:
```
.Members
```
This clause specifies that all members of the Employees hierarchy within the Employee dimension should be included in the current cube scope.
Verify that your completed SCOPE statement matches the following script:
```
SCOPE ([Measures].[Sales Amount Quota],[Date].[Fiscal Time].[Fiscal Year].&[2005], [Employee].[Employees].Members)
```
You have now completed your scope definition that defines the subcube to which you will apply an MDX expression to calculate the sales quota amount for Fiscal Year 2005.
On the toolbar of the Calculations tab, click Script View, and then review the newly added script commands.

Notice that each script command appears as you typed it in the Calculation Expressions pane, but with a semicolon added to the end of each script command. Notice also that the comment before each script command helps you understand each separate command, as shown in the following image.

Defining and Testing the New Sales Quota Calculation for Fiscal Year 2005

In this procedure, you will add a new script command to the calculation script that calculates the sales quota amount for Fiscal Year 2005 for all members of the Employee dimension. However, instead of adding the script command in the Form view, you will add the script command directly in the Script view. In the Script view, you must make sure to add a semicolon between each script command.

To define and test the new Sales Quota calculation for fiscal year 2005

In the Script view, type the following statements on a new line at the end of the calculation script:
```
/* Applying a calculation to the subcube */
THIS = [Date].[Fiscal Time].[Fiscal Year].&[2004] * 1.25;
```
The THIS statement allocates a new value to the Sales Amount Quota measure at the intersection of the Employee member and the FY 2005 member of the subcube. The new value is based on the value that exists for the intersection of the Employee member and the Sales Amount Quota measure in Fiscal Year 2004 multiplied by 1.25.
In the Calculation Expressions pane, click in the margin to the left of the THIS statement to set a breakpoint.

Notice that a red dot appears in that margin and that the statement is highlighted in red, as shown in the following image. When you execute this project in debug mode, the changes to your project are deployed to your instance of Analysis Services, the cube is processed, and the calculation script executes until the breakpoint is encountered. You can then step through the remaining scripts one by one. If you do not set a breakpoint and run the project in debug mode, the calculation script will break at the first calculation script, the CALCULATE statement.
On the Debug menu, click Start Debugging (or press F5 on your keyboard).

The project is deployed and processed, and the calculation script executes until the breakpoint is encountered.
Hide all docked windows to provide more area to view the Pivot Table pane that appears at the bottom of the Calculations tab.

The Pivot Table pane that appears, as shown in the following image will help you with debugging.
In the Pivot Table pane, add the Sales Amount Quota measure to the data area, add the Date.Fiscal Time user-defined hierarchy to the column area, and then add the Employee Name attribute hierarchy from the Employee dimension to the row area.

Notice that the FY 2005 member of the Fiscal Year level for the Sales Amount Quota measure for each employee contains no value in the data area for the Sales Quota Amount measure, as shown in the following image. By default, empty cells are displayed in the Pivot Table pane when you are in debug mode.
Press F10 to execute the THIS statement and calculate the sales quota for fiscal year 2005.

Notice that the cells in the Pivot Table pane at the intersection of the Sales Amount Quota measure, the FY 2005 dimension member, and the Employee Name member are now calculated. Notice also that the cells affected by the THIS statement are highlighted in yellow. There is a toolbar icon to enable or disable the highlighting of changed cells. By default, changed cells are highlighted.
In the Pivot Table pane, remove FY 2002, FY 2003, and FY 2004 from the column area.
Right-click anywhere in the data area and then click Show Empty Cells to remove the check mark next to this option and hide all empty cells (this option is also available on the Calculations tab toolbar). This will make it easier to view all the employees who have sales amount quota values, as shown in the following image.
In the Pivot Table pane, try to expand FY 2005 in the column area.

Notice that you cannot expand FY 2005 because the values for the H1 FY 2005 member at the intersection of the Sales Amount Quota measure and the Employee Name attribute hierarchy was not calculated (because they were outside the current scope).
To view the empty cells for the values for H1 FY 2005 members, click anywhere in the Pivot Table pane and then click Show Empty Cells on the Calculations tab toolbar to show all empty cells, as shown in the following image.
On the Debug menu, click Stop Debugging (or press Shift-F5 on your keyboard).
Remove the breakpoint in the calculation script, by clicking the breakpoint in the left margin.

Allocating Sales Amount Quotas to Fiscal Year 2005 Semesters and Quarters

In this procedure, you modify the scope to include the fiscal semester members of Fiscal Year 2005 instead of the Fiscal Year 2005 member, and you will then allocate one-half of the sales amount quota value for Fiscal Year 2005 to each semester in Fiscal Year 2005. You will then modify the scope to include the fiscal quarter members of Fiscal Year 2005 instead of the fiscal year 2005 member, and then allocate one-fourth of the sales amount quota value for Fiscal Year 2005 to each quarter in Fiscal Year 2005. You will then test these allocations.

To allocate sales amount quotas to Fiscal Year 2005 Semesters and Quarters

In the Script view of the Calculation Expressions pane, type the following statement on a new line at the end of the calculation script:
```
/* Allocation of Sales Amount Quota to the 2005 Fiscal Semesters */
SCOPE ( [Date].[Fiscal Semester].[Fiscal Semester].Members ); 
```
This SCOPE statement is a nested SCOPE statement because no END SCOPE statement appears between this SCOPE statement and the previous SCOPE statement. When a SCOPE statement is nested, the nested SCOPE statement inherits the parent scope for those attributes that are not rescoped. The previous SCOPE statement does not directly modify the Sales Amount Quota measure, the Employees user-defined hierarchy, or the Fiscal Time user-defined hierarchy. Instead, it adds each member of the Fiscal Semester attribute hierarchy to the subcube definition (by using the Members function). For more information, see Members (Set) (MDX). As a result of the nested SCOPE statement, the cube space now includes all members that are at the intersection of the Employee member and the Sales Amount Quota measure in any fiscal semester in Fiscal Year 2005 (there is currently only one fiscal semester in Fiscal Year 2005 in the cube).
In the Calculation Expressions pane, type the following statement on a new line at the end of the calculation script:
```
THIS = [Date].[Fiscal Time].CurrentMember.Parent / 2;
```
This statement allocates to each fiscal semester in the defined cube space the calculated value for the fiscal quarter. The CurrentMember.Parent function is used to allocate to each member one-fourth of the value of its parent. For more information, see CurrentMember (MDX), and Parent (MDX).
In the Calculation Expressions pane, type the following statement on a new line at the end of the calculation script:
```
/* Allocation of Sales Amount Quota to the 2005 Fiscal Quarters */
SCOPE ( [Date].[Fiscal Quarter].[Fiscal Quarter].Members ); 
```
This SCOPE statement is also a nested SCOPE statement because no END SCOPE statement appears between this SCOPE statement and the previous SCOPE statement. The previous SCOPE statement does not directly modify the Sales Amount Quota measure, the Employees user-defined hierarchy, or the Fiscal Time user-defined hierarchy. Instead, it adds each member of the Fiscal Quarter attribute hierarchy to the subcube definition (by using the Members function). As a result, the cube space now includes all members that are at the intersection of the Employee member and the Sales Amount Quota measure in any fiscal quarter in fiscal year 2005 (there is currently only one fiscal quarter in Fiscal Year 2005 in the cube).
In the Calculation Expressions pane, type the following statement on a new line at the end of the calculation script:
```
THIS = [Date].[Fiscal Time].CurrentMember.Parent / 2;
```
This statement allocates to each fiscal quarter in the defined cube space the calculated value for the fiscal quarter. The CurrentMember.Parent function is used to allocate to each member one-half of the value of its parent.
In the Calculation Expressions pane, click in the margin next to the final SCOPE statement to set a breakpoint, and then press F5 on your keyboard.

The calculation script executes until the breakpoint is encountered.
Click anywhere in the data area of the Pivot Table pane and then click Show Empty Cells on the Calculation tab toolbar to hide empty cells.

Notice that the data pane is populated with the same measures and hierarchies that you used on the last pass through the debugger, and that the value for the H1 FY 2005 member has been calculated, one-half of its parent's value, as shown in the image later.

Finally, notice that the value for each FY 2005 member is recalculated based on the aggregation of its members, which in this case is the first fiscal semester of the fiscal year. The value of the Fiscal Year 2005 member is affected by the calculation for the Fiscal Semester 2005 member because each script executes as a separate pass. To pin an existing value so that it is not affected by subsequent statements in the calculation script, use the FREEZE statement. For more information, see FREEZE Statement (MDX).
In the column area, expand H1 FY 2005.

Notice that the value for the Q1 FY 2005 member has not yet been calculated.
Click anywhere in the data area of the Pivot Table pane and then click Show Empty Cells on the Calculation tab toolbar.
In the column area, expand Q1 FY 2005.

Notice that no value is allocated to the two months within the first quarter of Fiscal Year 2005 because these members are not yet within the scope of the current subcube (until the final two statements in the script are executed). The time dimension in the Analysis Services Tutorial cube only contains the first two months of Fiscal Year 2005. Therefore, there are no Q2 FY 2005 members.
Press F10 to execute the SCOPE statement and then press F10 again to execute final statement in the calculation script, which applies the calculation to the current subcube.

Notice that the value for the Q1 FY 2005 member is calculated, and the value of the H1 FY 2005 and the FY2005 member values are re-calculated (as the aggregate of their child members), as shown in the following image. Notice also that the value for each fiscal month in Fiscal Year 2005 (July and August of 2005) is not calculated. In the next procedure, you will allocate appropriate values to each quarter.
On the Debugging menu, click Stop Debugging (or press Shift-F5 on your keyboard).
Remove the breakpoint in the calculation script.

Allocating Sales Quotas to Months

In this procedure, you will modify the scope to include the fiscal month level in all fiscal years (the previous scope statements limited the calculations to fiscal year 2005 only). You will then allocate one-third of the fiscal quarter value for the sales amount quota for each employee to each fiscal month.

To allocate sales quotas to months

In the Calculation Expressions pane, add the following statements on a new line at the end of the calculation script:
```
/* Allocate Quotas to Months */
SCOPE ( [Date].[Fiscal Time].[Fiscal Month].Members );
```
This SCOPE statement is another nested scope statement that modifies the cube space to which you will apply an MDX expression to allocate sales amount quotas to each fiscal month based on the value for each fiscal quarter. This SCOPE statement is similar to the previous nested SCOPE statement, but notice that the Fiscal Time user-defined hierarchy itself is rescoped. Therefore, the members of the cube space will now include all fiscal month members of the Date dimension, instead of just the fiscal month members of Fiscal Year 2005.

Note

Compare this modification of the cube scope with the modification of the cube scope in the previous procedure to make sure that you understand the difference between the two scope change statements.
In the Calculation Expressions pane, add the following statements on a new line at the end of the calculation script:
```
THIS = [Date].[Fiscal Time].CurrentMember.Parent / 3; 
```
This statement allocates to each month member of the fiscal time hierarchy one-third of the value of its parent—the quarter level. This calculation will apply to all fiscal months within the cube.
In the Calculation Expressions pane, click in the margin to the left of the final THIS statement to set a breakpoint, and then press F5 on your keyboard.
Review the values for July 2004 and August 2004.

Notice that no value is currently calculated for either July 2004 or August 2004.
Right-click anywhere in the Data pane, and then click Show Empty Cells to display only cells that have values.

This will let you more easily see how the final statement is applied in the calculation script.
Press F10 to execute the final statement.

Notice that the Sales Amount Quota value for each employee for each fiscal month is calculated to a value that is equal to one-third of its parent value, as shown in the following image.
In the column area, remove FY 2005 and then add Q4 FY 2004.
Expand FY 2004, expand H2 FY 2004, and then expand Q4 FY 2004.

Notice that the value for each fiscal month is equal to the total value for the fiscal quarter, as shown in the following image.
On the Debug menu, click Stop Debugging.
Remove the breakpoint in the calculation script.
On the toolbar, click Save All.

Note

A completed project through Lesson 6 is available by downloading and installing the updated samples. For more information, see Obtaining Updated Samples in Installing Samples.

Next Lesson

Lesson 7: Defining Key Performance Indicators (KPIs)

Share via