Calculated Members

The term calculated member refers to the creation of any MDX object through a calculation. The calculated member can be part of the Measures dimension where a simple MDX expression such as addition or subtraction of two or more base measures results in a new measure. Such calculated members on the Measures dimension are referred to as calculated measures. You can also create calculated members on other dimensions by specifying an MDX expression. These members are simply referred to as calculated members. To create a calculated member, click the Calculations tab of the cube editor. This takes you to the Calculations view, as shown in Figure 6-25. The Calculations view contains three window panes: Script Organizer, Calculation Tools, and Script.

Figure 6-25

The Script Organizer window pane shows the name of the calculation objects of the cube. Various types of calculations can be created in the Calculations view and one of them is the calculated member or calculated measure. You can apply a name to a subset of dimension members that is referred to as named sets. In addition to calculated members and named sets, you can define a script containing complex MDX expressions to perform complex business logic calculations. If you right-click within the Script Organizer you can see the selections to create a calculated member, named set, or a script command. These operations can also be performed by clicking the icons in the toolbar as indicated in Figure 6-25. You create calculated measures in this chapter, and the creation of named sets and script commands is detailed in Chapter 9.

The Calculation Tools window is identical to the Metadata browser window you have become familiar with in the Browser view. The Calculation Tools window contains three tabs: Metadata, Functions, and Templates. The Metadata view shows the measures and dimensions of the current cube. The Functions view shows all the MDX functions along with a template of the arguments needed for each function. In the Templates view you can see the templates for some of the common calculations used for certain applications such as budgeting and financial.

The Script window pane shows the details of the calculations. The default view of the Script window is called the Form view. The Script window can be toggled to a different view called the Script view. If you are in the Form view, the Script Organizer window will be visible and you can see the calculations of each object in the Script command window. If the Script window is switched to the Script view (using the appropriate icon) then all the calculations are shown as a single script and the Script Organizer pane becomes invisible. You can toggle between the two views by clicking the icons shown in Figure 6-25 or by selecting the option through the menu item Cube Note

All commands and selections available in Analysis Services 2005 are accessible via keyboard controls. You can switch between the three panes of the Script tab of the Cube Designer using the F6 function key and making the appropriate selection via menu items.

Calculated Measures

Calculated measures are the most common type of calculated members created in a cube. In your project you have the measures Sales and Product Cost in the two measure groups, Internet Sales and Reseller Sales. An important question to ask about any business concerns profits gained. Profit gained is the difference between total sales and cost of goods sold. In the Adventure Works DW cube you have created you have Sales through Internet as well as Reseller. Therefore you need to add these to sales amounts to calculate the total sales of products. Similarly, you need to calculate the total product cost by adding the costs of products sold through Internet and Reseller. Two calculated measures must be formed to perform these operations. Once you have created these two calculations, you can calculate the profit. Follow the steps below to create the calculated measure for profit.

1. Right-click in the Script command window and select New Calculated Member, as shown in Figure 6-26.

   
   Figure 6-26

   An object called Calculated Member is created in the Script command window. The Script window now shows several text boxes for you to specify the name of the calculation, the MDX expression for the calculated member, and certain properties for the calculated member.

2. Specify the name of the calculated member as [Total Sales Amount] in the Script window. In the Expression text box you need to type the MDX expression that will calculate the Total Sales Amount. As mentioned earlier, the Total Sales Amount is the sum of sales from the sales amounts in Fact Internet Sales and Fact Reseller Sales measures groups. Drag and drop these measures from the Metadata window and add the MDX operator "+" between these measures as shown in Figure 6-27.

   
   Figure 6-27

3. For cost of goods sold, create a new calculated measure called [Total Product Costs] using a method similar to the one described in step 2 but with appropriate Product Cost measures from the two measure groups.

4. Create a calculated measure called Profit. The MDX expression to evaluate Profit is the difference of the calculated measures you have created in steps 2 and 3. Enter the MDX expression [Measures].[Total Sales Amount] – [Measures].[Total Product Costs] in the Expression text box as shown in Figure 6-28.

   
   Figure 6-28

5. You have the option of specifying certain additional properties for the calculated measures you have created based on an MDX expression. By default all the calculated measures created are visible. You can specify color, font, and format strings for the calculated measures based on certain conditions. For example, if you want to highlight the profit in red if the amount is less than one million dollars and in green if it is greater than or equal to one million, you can do so by specifying the appropriate background color for the calculated member. Enter the following MDX expression for the background color:

        iif (     [Measures].Profit < 1000000,        255 /*Red*/,             65280 /*Green*/) 

   The MDX expression uses the IIF function. This function takes three arguments. The first argument is an expression that should evaluate to true or false. The return value of the iif function is either the second or the third argument passed to the function. If the result of the expression is true, the IIF function returns the second argument; if the expression is false, it returns the third argument. The first argument passed to the IIF function is to see if the profit is less than one million. If second and third arguments passed to the function are the values for the colors red and green. The values for the colors can be selected by clicking the color icon next to the background color text box.

6. To see the effect of the calculations you have created, go to the Cube Browser tab and deploy the project. As soon as the deployment is complete you will be asked to reconnect to the server in the Cube Browser. Reconnect to the server. If you expand the Measures folder you will see the calculated measures you have created. Drag and drop the measure Profit to the OWC detail area, hierarchy English Country Region name of the Dim Geography dimension on rows and hierarchy Style of the Dim Product dimension on columns. You will see the background color for the cells are either red or green based on the Profit value as shown in Figure 6-29.

Figure 6-29

Querying Calculated Measures

You can query the calculated measures similar to other measures in the cube by referencing them with the name. For example, if you want to query the calculated member Profit based on Model Name, you execute the following query.

     SELECT [Measures].[Profit] on COLUMNS,     [Dim Product].[Model Name].MEMBERS on ROWS     FROM [Adventure Works DW] 

If you want to retrieve all the measures in the cube instead of specifying each measure, you use [Measures] .MEMBERS. However, calculated members are not returned when you select [Measures].Members. You need to execute the following MDX query to retrieve the base measures along with the calculated members:

     SELECT [Measures].ALLMEMBERS on COLUMNS,     [Dim Product].[Model Name].MEMBERS on ROWS     FROM [Adventure Works DW] 

You have enhanced the Adventure Works DW cube by creating calculated measures and learned to set certain properties for the calculated measures via MDX expressions. The NonEmptyBehavior property for calculated measures is discussed in Chapters 7 and 13 along with creation of named sets and script commands.