Hi all
Continuing from My last post on SSAS
- BI # 19 – SSAS # 5 – How to Defining a Data Source and Data Source View
- MSBI # 20 – SSAS # 6 – How Defining a Cube in SSAS
- MSBI # 21 – SSAS # 7 – Understanding the Cube Designer Tabs in SSAS
- MSBI # 28 – SSAS # 9 – Modifying Measures, Attributes and Hierarchies in SSAS
- MSBI # 34 – SSAS # 10 – How to Defining Actions in SSAS
- Points cover in this post are as follows
- Introduction to calculation
- Defining Calculated Members
- How To define gross profit margin calculations
- How To define the percent of total calculations
- How To browse the new calculated members
- Defining Named Sets
- How To define a Core Products named set
- How To define a Large Resellers named set
- Defining Scoped Assignments Using Script Commands
In this post we are going to cover most of Calculation par of SSAS
Introduction to calculation
In this post , we will learn to define calculations, which are Multidimensional Expressions (MDX) expressions or scripts. Calculations let we will define calculated members, named sets, and execute other script commands to extend the capabilities of a Microsoft SQL Server 2005 Analysis Services (SSAS) cube. For example, we can run a script command to define a subcube and then assign a calculation to the cells in the subcube.
When we will define a new calculation in Cube Designer, the calculation is added to the Script Organizer pane of the Calculations tab of Cube Designer, and the fields for the particular calculation type are displayed in a calculations form in the Calculation Expressions pane. Calculations are executed in the order in which they are listed in the Script Organizer pane. we can reorder the calculations by right-clicking on a particular calculation and then selecting Move Up or Move Down, or by clicking a particular calculation and then using the Move Up or Move Down icons on the toolbar of the Calculations tab.
On the Calculations tab, we can add new calculations and view or edit existing calculations in one of two views in the Calculation Expressions pane:
· Form view. This view shows the expressions and properties for a single command in a graphical format. When we edit an MDX script, an expression box fills the Form view.
· Script view. This view displays all calculation scripts in a code editor, which lets you easily change the calculation scripts. When the Calculation Expressions pane is in Script view, the Script Organizer is hidden. The Script view provides color coding, parenthesis matching, auto-complete, and MDX code regions. we can expand or collapse the MDX code regions to make editing easier.
Defining Calculated Members
Calculated members are members of a dimension or a measure group that are defined based on a combination of cube data, arithmetic operators, numbers, and functions. For example, we can create a calculated member that calculates the sum of two physical measures in the cube. Calculated member definitions are stored in cubes, but their values are calculated at query time.
To create a calculated member, use the New Calculated Member command on the Calculations tab of Cube Designer. we can create a calculated member within any dimension, including the measures dimension. we can also place a calculated member within a display folder in the Calculation Properties dialog box.
In the tasks in this topic, we will define calculated measures to let users view the gross profit margin percentage and sales ratios for Internet sales, reseller sales, and for all sales.
To define calculations to aggregate physical measures
1.Open Cube Designer for the Analysis Services Tutorial cube, and then click the Calculations tab.
Notice the default CALCULATE command in the Calculation Expressions pane and in the Script Organizer pane. This command specifies that the measures in the cube should be aggregated according to the value that is specified by their AggregateFunction properties. Measure values are generally summed, but may also be counted or aggregated in some other manner.
The following image shows the Calculations tab of Cube Designer.
2.On the toolbar of the Calculations tab, click New Calculated Member.
A new form appears in the Calculation Expressions pane within which we will define the properties of this new calculated member. The new member also appears in the Script Organizer pane.
The following image shows the form that appears in the Calculation Expressions pane when we will click New Calculated Member.
3.In the Name box, change the name of the calculated measure to [Total Sales Amount].
If the name of a calculated member contains a space, the calculated member name must be enclosed in square brackets.
Notice in the Parent hierarchy list that, by default, a new calculated member is created in the Measures dimension. A calculated member in the Measures dimension is also frequently called a calculated measure.
4.On the Metadata tab in the Calculation Tools pane of the Calculations tab, expand Measures and then expand Internet Sales to view the metadata for the Internet Sales measure group.
we can drag metadata elements from the Calculation Tools pane into the Expression box and then add operators and other elements to create Multidimensional Expressions (MDX) expressions. Alternatively, we can type the MDX expression directly into the Expression box. Drag Internet Sales-Sales Amount from the Metadata tab in the Calculation Tools pane into the Expression box in the Calculation Expressions pane.
1.In the Expression box, type a plus sign (+) after [Measures].[Internet Sales-Sales Amount].
2.On the Metadata tab in the Calculation Tools pane, expand Reseller Sales, and then drag Reseller Sales-Sales Amount into the Expression box in the Calculation Expressions pane after the plus sign (+).
3.In the Format string list, select "Currency".
4.In the Non-empty behavior list, select the check boxes for Internet Sales-Sales Amount and Reseller Sales-Sales Amount, and then click OK.
The measures we will specify in the Non-empty behavior list are used to resolve NON EMPTY queries in MDX. When we will specify one or more measures in the Non-empty behavior list, Analysis Services treats the calculated member as empty if all the specified measures are empty. If the Non-empty behavior property is blank, Analysis Services must evaluate the calculated member itself to determine whether the member is empty.
The following image shows the Calculation Expressions pane populated with the settings that we will specified in the previous steps.
5.On the toolbar of the Calculations tab, click Script View, and then review the calculation script in the Calculation Expressions pane.
Notice that the new calculation is added to the initial CALCULATE expression; each individual calculation is separated by a semicolon. Notice also that a comment appears at the beginning of the calculation script. Adding comments within the calculation script for groups of calculations is a good practice, to help you and other developers understand complex calculation scripts.
6.Add a new line in the calculation script after the Calculate; command and before the newly added calculation script, and then add the following text to the script on its own line:
/* Calculations to aggregate Internet Sales and Reseller Sales measures */
The following image shows the calculation scripts as they should appear in the Calculation Expressions pane at this point in the tutorial.
7.On the toolbar of the Calculations tab, click Form View, verify that [Total Sales Amount] is selected in the Script Organizer pane, and then click New Calculated Member.
8.Change the name of this new calculated member to [Total Product Cost], and then create the following expression in the Expression box:
[Measures].[Internet Sales-Total Product Cost] + [Measures].[Reseller Sales-Total Product Cost]
9.In the Format string list, select "Currency".
10. In the Non-empty behavior list, select the check boxes for Internet Sales-Total Product Cost and Reseller Sales-Total Product Cost, and then click OK.
we have now defined two calculated members, both of which are visible in the Script Organizer pane. These calculated members can be used by other calculations that we will define later in the calculation script. we can view the definition of any calculated member by selecting the calculated member in the Script Organizer pane; the definition of the calculated member will appear in the Calculation Expressions pane in the Form view. Newly defined calculated members will not appear in the Calculation Tools pane until these objects have been deployed. Calculations do not require processing.
To define gross profit margin calculations
1.Verify that [Total Product Cost] is selected in the Script Organizer pane, and then click New Calculated Member on the toolbar of the Calculations tab.
2.In the Name box, change the name of this new calculated measure to [Internet GPM].
3.In the Expression box, create the following MDX expression:
([Measures].[Internet Sales-Sales Amount] – [Measures].[Internet Sales-Total Product Cost]) / [Measures].[Internet Sales-Sales Amount]
4.In the Format string list, select "Percent".
5.In the Non-empty behavior list, select the check box for Internet Sales-Sales Amount, and then click OK.
6.On the toolbar of the Calculations tab, click New Calculated Member.
7.In the Name box, change the name of this new calculated measure to [Reseller GPM].
8.In the Expression box, create the following MDX expression:
([Measures].[Reseller Sales-Sales Amount] – [Measures].[Reseller Sales-Total Product Cost]) / [Measures].[Reseller Sales-Sales Amount]
9.In the Format string list, select "Percent".
10. In the Non-empty behavior list, select the check box for Reseller Sales-Sales Amount, and then click OK.
11. On the toolbar of the Calculations tab, click New Calculated Member.
12. In the Name box, change the name of this calculated measure to [Total GPM].
13. In the Expression box, create the following MDX expression:
([Measures].[Total Sales Amount] – [Measures].[Total Product Cost]) / [Measures].[Total Sales Amount]
Notice that this calculated member is referencing other calculated members. Because this calculated member will be calculated after the calculated members that it references, this is a valid calculated member.
14. In the Format string list, select "Percent".
15. In the Non-empty behavior list, select the check boxes for Internet Sales-Sales Amount and Reseller Sales-Sales Amount, and then click OK.
16. On the toolbar of the Calculations tab, click Script View and review the three calculations we just added to the calculation script.
17. Add a new line in the calculation script immediately before the [Internet GPM] calculation, and then add the following text to the script on its own line:
/* Calculations to calculate gross profit margin */
The following image shows the Expressions pane with the three new calculations.
To define the percent of total calculations
1.On the toolbar of the Calculations tab, click Form View.
2.In the Script Organizer pane, select [Total GPM], and then click New Calculated Member on the toolbar of the Calculations tab.
Clicking the final calculated member in the Script Organizer pane before we click New Calculated Member guarantees that the new calculated member will be entered at the end of the script. Scripts execute in the order that they appear in the Script Organizer pane.
3.Change the name of this new calculated member to [Internet Sales Ratio to All Products].
4.Type the following expression in the Expression box:
Case When IsEmpty( [Measures].[Internet Sales-Sales Amount] ) Then 0 Else ( [Product].[Product Categories].CurrentMember, [Measures].[Internet Sales-Sales Amount]) / ( [Product].[Product Categories].[(All)].[All], [Measures].[Internet Sales-Sales Amount] ) End
This MDX expression calculates the contribution to total Internet sales of each product. The Case statement together with the IS EMPTY function ensures that a divide by zero error does not occur when a product has no sales.
5.In the Format string list, select "Percent".
6.In the Non-empty behavior list, select the check box for Internet Sales-Sales Amount, and then click OK.
7.On the toolbar of the Calculations tab, click New Calculated Member.
8.Change the name of this calculated member to [Reseller Sales Ratio to All Products].
9.Type the following expression in the Expression box:
Case When IsEmpty( [Measures].[Reseller Sales-Sales Amount] ) Then 0 Else ( [Product].[Product Categories].CurrentMember, [Measures].[Reseller Sales-Sales Amount]) / ( [Product].[Product Categories].[(All)].[All], [Measures].[Reseller Sales-Sales Amount] ) End
10. In the Format string list, select "Percent".
11. In the Non-empty behavior list, select the check box for Reseller Sales-Sales Amount, and then click OK.
12. On the toolbar of the Calculations tab, click New Calculated Member.
13. Change the name of this calculated member to [Total Sales Ratio to All Products].
14. Type the following expression in the Expression box:
Case When IsEmpty( [Measures].[Total Sales Amount] ) Then 0 Else ( [Product].[Product Categories].CurrentMember, [Measures].[Total Sales Amount]) / ( [Product].[Product Categories].[(All)].[All], [Measures].[Total Sales Amount] ) End
15. In the Format string list, select "Percent".
16. In the Non-empty behavior list, select the check boxes for Internet Sales-Sales Amount and Reseller Sales-Sales Amount, and then click OK.
17. On the toolbar of the Calculations tab, click Script View, and then review the three calculations that we just added to the calculation script.
18. Add a new line in the calculation script immediately before the [Internet Sales Ratio to All Products] calculation, and then add the following text to the script on its own line:
/* Calculations to calculate percentage of product to total product sales */
we have now defined a total of eight calculated members, which are visible in the Script Organizer pane when we are in Form view.
To browse the new calculated members
1.On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.
2.When deployment has successfully completed, switch to the Browser tab, click Reconnect, and then remove all hierarchies and measures from the Data pane.
3.In the Metadata pane, expand Measures to view the new calculated members in the Measures dimension.
4.Add the Total Sales Amount, Internet Sales-Sales Amount, and Reseller Sales-Sales Amount measures to the data area, and then review the results.
Notice that the Total Sales Amount measure is the sum of the Internet Sales-Sales Amount measure and the Reseller Sales-Sales Amount measure.
5.Add the Product Categories user-defined hierarchy to the filter area of the Data pane, and then filter the data by Mountain Bikes.
Notice that the Total Sales Amount measure is calculated for the Mountain Bikes category of product sales based on the Internet Sales-Sales Amount and the Reseller Sales-Sales Amount measures for Mountain Bikes.
6.Add the Date.Calendar Time user-defined hierarchy to the row area, and then review the results.
Notice that the Total Sales Amount measure for each calendar year is calculated for the Mountain Bikes category of product sales based on the Internet Sales-Sales Amount and the Reseller Sales-Sales Amount measures for Mountain Bikes.
7.Add the Total GPM, Internet GPM, and Reseller GPM measures to the data area, and then review the results.
Notice that the gross profit margin for reseller sales is significantly lower than for sales over the Internet. Notice also that the gross profit margin on the sales of mountain bikes is increasing over time, as shown in the following image.
8.Add the Total Sales Ratio to All Products, Internet Sales Ratio to All Products, and Reseller Sales Ratio to All Products measures to the data area.
Notice that the ratio of the sales of mountain bikes to all products has increased over time for Internet sales, but is decreasing over time for reseller sales. Notice also that the ratio of the sale of mountain bikes to all products is lower from sales through resellers than it is for sales over the Internet.
9.Change the filter from Mountain Bikes to Bikes, and review the results.
Notice that the gross profit margin for all bikes sold through resellers is negative, because touring bikes and road bikes are being sold at a loss.
10. Change the filter to Accessories, and then review the results.
Notice that the sale of accessories is increasing over time, but that these sales make up only a small fraction of total sales. Notice also that the gross profit margin for sales of accessories is higher than for bikes.
11. Expand CY 2004, expand H2 CY 2004, and then expand Q3 CY 2004.
Notice that there are no Internet sales in this cube for after July, 2004, and no reseller sales for after June, 2004. These sales values have not yet been added from the source systems to the Adventure Works DW database.
Defining Named Sets
A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. we can define named sets and save them as part of the cube definition; we can also create named sets in client applications. we will create named sets by combining cube data, arithmetic operators, numbers, and functions. Named sets can be used by users in MDX queries in client applications and can also be used to define sets in subcubes. A subcube is a collection of crossjoined sets that restricts the cube space to the defined subspace for subsequent statements. Defining a restricted cube space is a fundamental concept to MDX scripting. MDX scripting is covered in the next topic in this lesson.
Named sets simplify MDX queries and provide useful aliases for complex, typically used, set expressions. For example, we can define a named set called Large Resellers that contains the set of members in the Reseller dimension that have the most employees. End users could then use the Large Resellers named set in queries, or we could use the named set to define a set in a subcube. Named set definitions are stored in cubes, but their values exist only in memory. To create a named set, use the New Named Set command on the Calculations tab of Cube Designer.
In the tasks in this topic, we will define two named sets: a Core Products named set and a Large Resellers named set.
To define a Core Products named set
1.Switch to the Calculations tab of Cube Designer for the Analysis Services Tutorial cube, and then click Form View on the toolbar.
2.Click [Total Sales Ratio to All Products] in the Script Organizer pane, and then click New Named Set on the toolbar of the Calculations tab.
When you define a new calculation on the Calculations tab, remember that calculations are resolved in the order in which they appear in the Script Organizer pane. Your focus within that pane when you create a new calculation determines the order of the execution of the calculation; a new calculation is defined immediately after the calculation on which you are focused.
3.In the Name box, change the name of the new named set to [Core Products].
In the Script Organizer pane, notice the unique icon that differentiates a named set from a script command or a calculated member.
4.On the Metadata tab in the Calculation Tools pane, expand Product, expand Category, expand Members, and then expand All Products.
5. Drag Bikes into the Expression box.
You now have created a set expression that will return the set of members that are in the Bike category in the Product dimension.
The following image shows the Calculation Expressions pane for the named set that you created in this procedure.
To define a Large Resellers named set
1.Right-click [Core Products] in the Script Organizer pane, and then click New Named Set.
2.In the Name box, change the name of this named set to [Large Resellers].
3.In the Expression box, type Exists().
You will use the Exists function to return the set of members from the Reseller Name attribute hierarchy that intersects with the set of members in the Number of Employees attribute hierarchy that has the largest number of employees.
4.On the Metadata tab in the Calculation Tools pane, expand the Reseller dimension, and then expand the Reseller Name attribute hierarchy.
5.Drag the Reseller Name level into the parenthesis for the Exists set expression.
You will use the Members function to return all members of this set.
After the partial set expression, type a period, and then add the Members function. Your expression should look like the following:
Exists([Reseller].[Reseller Name].[Reseller Name].Members)
Now that you have defined the first set for the Exists set expression, you are ready to add the second set—the set of members of the Reseller dimension that contains the largest number of employees.
6.On the Metadata tab in the Calculation Tools pane, expand Number of Employees in the Reseller dimension, expand Members, and then expand All Resellers.
Notice that the members of this attribute hierarchy are not grouped.
7.Open Dimension Designer for the Reseller dimension, and then click Number of Employees in the Attributes pane.
8.In the Properties window, change the DiscretizationMethod property to Automatic, and then change the DiscretizationBucketCount property to 5
9.On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.
10. When deployment has successfully completed, switch to Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect on the toolbar of the Calculations tab.
11. On the Metadata tab in the Calculation Tools pane, expand Number of Employees in the Reseller dimension, expand Members, and then expand All Resellers.
Notice that the members of this attribute hierarchy are now contained in five groups, numbered 0 through 4. To view the number of a group, pause the pointer over that group to view an InfoTip. The members of this attribute hierarchy are grouped because the DiscretizationBucketCount property is set to 5 and the DiscretizationMethod property is set to Automatic.
12. In the Expression box, add a comma in the Exists set expression after the Members function and before the closing parenthesis, and then drag 83 – 100 from the Metadata pane and position it after the comma.
You have now completed the Exists set expression that will return the set of members that intersects with these two specified sets, the set of all resellers and the set of resellers who have 83 to 100 employees, when the Large Resellers named set is put on an axis.
The following image shows the Calculation Expressions pane for the [Large Resellers] named set.
13. On the toolbar of the Calculations tab, click Script View, and then review the two named sets that you have just added to the calculation script.
14. Add a new line in the calculation script immediately before the first CREATE SET command, and then add the following text to the script on its own line:
/* named sets */
You have now defined two named sets, which are visible in the Script Organizer pane. You are now ready to deploy these named sets, and then to browse these measures in the Analysis Services Tutorial cube.
To browse the cube by using the new named sets
1.On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial.
2.When deployment has successfully completed, click the Browser tab, and then click Reconnect.
3.Remove all hierarchies and measures from the Data pane.
4.Add the Reseller Sales-Sales Amount measure to the data area, and then add the Product Categories user-defined hierarchy to the row area.
Notice that only one member of the category attribute appears. This is because you had previously used this attribute as a filter in this session. All members will appear if you are working in a new session.
5.Select the drop-down arrow next to Category in the Row area, select the check box next to the (All) level to select all members of this level (if it is not already selected), and then click OK.
6.Expand Bikes to view the members of the Subcategory level, as shown in the following image.
7.In the Metadata pane, in the Product dimension, right-click Core Products, and select Add to Subcube Area.
Notice that only the Bike member of the Category attribute and members of the Bike subcategories remain in the cube. This is because the Core Products named set is used to define a subcube, the properties of which appear in the Filter pane, which is the pane above the Data pane. This subcube limits the members of the Category attribute in the Product dimension within the subcube to those members of the Core Product named set, as shown in the following image.
8.In the Metadata pane, expand Reseller, right-click Large Resellers, and then select Add to Subcube Area.
Notice that the Reseller Sales Amount measure in the Data pane only displays sales amounts for large resellers of bikes. Notice also that the Filter pane now displays the two named sets that are used to define this particular subcube, as shown in the following image.
Defining Scoped Assignments Using Script Commands
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, we 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, we 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. we 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.
To review the sales amount quota allocations by time and employee
1.Open Cube Designer for the Analysis Services Tutorial cube, and then click the Browser tab.
2.Remove all hierarchies and measures from the Data pane and remove all dimension members from the Filter pane.
3.Add the Sales Amount Quota measure from the Sales Quotas measure group to the data area of the Data pane.
4.Add the Fiscal Time user-defined hierarchy to the column.
5.Add the Employee Name attribute hierarchy to the row area.
Notice that no sales quota values have been defined for the 2005 fiscal year.
6.In the column area, remove FY 2002 and FY 2003.
7.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
1.Select the Calculations tab, and then select Form View on the toolbar.
2.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.
3.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.
4.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.
5.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.
6.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.
7.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.
8.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.
9.Add the necessary comma before the [Employee] part of the new member.
Notice that the initial red squiggly line disappears.
10. 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.
11. 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.
12. 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
1.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.
2.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. we 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.
3.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.
4.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.
5.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.
6.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.
7.In the Pivot Table pane, remove FY 2002, FY 2003, and FY 2004 from the column area.
8.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.
9.In the Pivot Table pane, try to expand FY 2005 in the column area.
Notice that we 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).
10. 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.
11. On the Debug menu, click Stop Debugging (or press Shift-F5 on your keyboard).
12. 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
1.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). 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).
2.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
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).
3.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.
4.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.
5.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
6.In the column area, expand H1 FY 2005.
Notice that the value for the Q1 FY 2005 member has not yet been calculated.
7.Click anywhere in the data area of the Pivot Table pane and then click Show Empty Cells on the Calculation tab toolbar.
8.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.
9.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.
10. On the Debugging menu, click Stop Debugging (or press Shift-F5 on your keyboard).
11. 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
1.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.
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.
2.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.
3.Review the values for July 2004 and August 2004.
Notice that no value is currently calculated for either July 2004 or August 2004.
4.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.
5.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.
6.In the column area, remove FY 2005 and then add Q4 FY 2004.
7.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.
8.On the Debug menu, click Stop Debugging.
9.Remove the breakpoint in the calculation script.
10. On the toolbar, click Save All.
Hope you have understood basic aspect of Calculations in SSAS and ready to use every aspects for same
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
(B)usiness (I)ntelligence Mentalist 