Adding Calculations to Your Cube

In Chapter 6, you learned how to use the Calculations tab in the cube designer for creating calculated members and named sets. In Chapter 7, you learned about the calculation model in Analysis Services 2005 and how cell calculations are created within MDX Scripts. In this section, you learn how to review and test cell calculations.

You use the Calculations tab in the cube designer to define all calculations which then become part of the MDX script of your cube. In this section, you continue using the Adventure Works DW sample project included with Microsoft SQL Server 2005 product to explore the functionality available in the Calculations tab.

The following steps show how to review the definitions of some of the calculations defined in the Adventure Works sample cube and how to verify the results as the calculations are applied to the specific cells:

1. Open the Adventure Works DW sample project located at C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise and, if you haven't done so already, deploy the project to the Analysis Services instance on your machine. If you have a default Analysis Services instance, you can deploy the project without changing the project properties. However, if you have installed named instances of Analysis Services and SQL Server you must change the project's deploy properties to target the right instance of Analysis Services and you must also change the relational data source in the project to point to your SQL Server instance.

2. Open the Adventure Works cube and click the Calculations tab. You will see all the calculated members, named sets, and calculations specified within the MDX script as shown in Figure 9-15. The first command selected in the Script Organizer, which is also the first command in the MDX script, is the Calculate statement. The Calculate statement is automatically added to each cube created by the cube wizard. The Calculate statement can be anywhere within the MDX script, but it must be included so be careful not to delete it. You can also add comments to the MDX script to make it easier to understand the purpose of the calculations by inserting your comments between the /* and */ characters as shown in Figure 9-15. All comments within the MDX script are detected by the cube designer and converted to a green font color for easier reading.

   
   Figure 9-15

3. The Calculations tab has two views: the Form View and the Script View. Figure 9-15 shows the Form View in which you can select a command listed in the Script Organizer pane to see its definition independently. Figure 9-16 shows the Script View, which displays when you click the Script View icon in the Calculations toolbar. In Script View, you can see all the commands together in the script pane.

   
   Figure 9-16

4. Click the Form View icon on the Calculations toolbar, scroll through the Script Organizer pane, and then click the first Scope statement. As you can see in Figure 9-17, when you select an item in the Script Organizer, the corresponding script displays on the right side. As you learned in Chapter 7, this Scope command restricts the cube space to Sales Amount Quota for Fiscal Quarters in Fiscal Year 2005.

   
   Figure 9-17

5. Click the statement that appears below the Scope statement in the Script Organizer to see the following assignment statement, which allocates the Sales Amount Quota for the Fiscal Year 2005 based 135% of the Sales Amount Quota in the Fiscal Year 2004:

        This = ParallelPeriod             (                 [Date].[Fiscal].[Fiscal Year], 1,                 [Date].[Fiscal].CurrentMember             ) * 1.35 

6. As you add statements and commands to the MDX script, you should test the script to ensure the affected cells or members get the correct values. One way to test an MDX expression is to use it in a query and evaluate the results. This approach can become time-consuming, especially when you have complex expressions like those shown in Chapter 7. A better alternative is to use the debugging capabilities of the Analysis Services 2005 Cube Designer in which you can quickly validate results. Because a cube's MDX Script is just a sequence of MDX statements, you can evaluate each statement separately by using the Cube Designer's debugging feature. Debugging your MDX script is similar to debugging application code. You can set breakpoints to evaluate a sequence of MDX statements that precede the statement specified as a breakpoint. To try the debugging capabilities, click the Script View icon in the Calculations toolbar, scroll through the list of MDX statements to locate the first Scope statement in the MDX script, and then set a breakpoint as shown in Figure 9-18. Set the break point by clicking in the margin to the left of the Scope statement. The breakpoint appears as a solid red circle as shown in Figure 9-18. Once you have set the break point, you see the statement being highlighted in red.

   
   Figure 9-18

7. To start debugging mode, press the function key F10. After deploying the database, the Cube Designer switches to debugging mode which divides the script pane into two sections, as shown in Figure 9-19. Standard Visual Studio environment debugging windows, such as Autos, Locals, and Breakpoints, among others, might also automatically open when debugging starts. Close these windows to allocate more screen space to the script pane. The top half of the script pane now contains the MDX script with the breakpoint statement highlighted in yellow. The bottom half now includes an Office Web Components Pivot Table control loaded as well as several sub panes labeled MDX1, MDX2, MDX3, and MDX4. The Pivot Table is useful for browsing the dimensions and measures as you execute the statements. The MDX panes 1 through 4 can be used to execute regular MDX queries during the debugging session.

   
   Figure 9-19

8. The debugger stopped execution of the MDX script at the statement with the breakpoint. You can monitor the effect of the subsequent statements by placing the Fiscal hierarchy of the Date dimension and the Sales Amount Quota measure in the Pivot Table, as shown in Figure 9-20. Expand the Fiscal Years 2004 and 2005 to see the Fiscal Semesters. All the measure values are currently highlighted in yellow for each quarter because the option to highlight affected cells is enabled by default. You can use the fifth icon from the right in the Calculations toolbar "Highlight changed cells" to toggle this behavior.

   
   Figure 9-20

9. The next statement, which is the assignment MDX expression to specify the Sales Amount Quota for the Fiscal year 2005, is now ready for execution. Press F10 to step through the Scope statement. The assignment statement is now highlighted with a yellow background, shown in Figure 9-21, but has not yet been executed.

   
   Figure 9-21

10. Execute the assignment statement by pressing the F10 key, which executes one statement at a time. As soon as the assignment statement is executed, the Sales Amount Quota value for the year 2005 changed from empty to 18,539,550.00. The cells corresponding to Fiscal Year 2005 and the Grand Total are both affected by the assignment statement and are the only cells highlighted, as shown in Figure 9-22.

Figure 9-22

You have now successfully tested the MDX script calculations you learned about in Chapter 7. You can also use the MDX1 through MDX4 panes for additional debugging. For example, before stepping into the statement that assigns values to the quarters of year 2005 you can send the following MDX query to query the current values for 2005 quarters:

     SELECT [Measures].[Sales Amount Quota] ON 0,     [Date].[Fiscal].[Fiscal Quarter].members ON 1     FROM [Adventure Works] 

Just click one of the MDX tabs as shown in Figure 9-23, type the MDX query, and click the Execute MDX button. The Execute MDX button is the button containing the green arrow. The MDX query executes in the current context of debugging and retrieves the cells specified by the query, and displaying the results in the results pane.

Figure 9-23

If you currently have one of the MDX panes open during execution of the MDX script, the results of the executed statements are immediately reflected in the results. Figure 9-24 shows the results of the MDX query before and after the execution of the MDX script to assign a sales quota for the year 2005.

Figure 9-24

The debugger also allows you to simulate a different user during execution. Click the Change User icon (sixth icon from the right on the Calculations toolbar) to change to a different user or a role when you need to verify the results of an MDX script with appropriate security for your end users. Obviously, you don't want users to view data that they aren't supposed to see.

Now that you have learned how to use and debug calculations in Analysis Services 2005, you're ready to learn about two other important types of cube enhancements, KPIs and Actions.