SQL Server 2005 : Advanced OLAP - Calculations (part 1) - Calculated Members

There’s a lot more to explore than these measure and dimension features. For instance, Analysis Services allows you to store calculated MDX expressions on the server that you can use in your queries. You can reference custom-built sets by name, create calculated measures or dimension attribute members, and more. The Calculations tab of the cube designer provides a nice front end for specifying named sets and calculated members, without requiring you to hand-code the MDX yourself. You can view the generated MDX if you’re curious or if you want to learn more MDX through reverse engineering.

Imagine that the Northwind Trading Company has defined Albuquerque, New Mexico; Boise, Idaho; and Kirkland, Washington, as test markets and often needs to view the sales in those three markets. By creating a named set containing those three cities, we can provide users with an easy way to query data for just those test markets. We might also like to see net sales rather than having to look at the Total Sales and Discount measures separately. By creating a calculated member defined as the difference between Total Sales and Discount, we can have an easy way to look at net sales without needing to embed that data in the fact table or embed expressions in our queries.

We might also want to define an additional calculated member that specifies our net sales growth for any time period, by comparing the period’s net sales with that of the previous period and looking at the growth percentage (positive or negative) between the two. As you will see shortly, the Analysis Services 2005 calculations facility and MDX make this pretty easy.

Calculated Members

Let’s start by creating the calculated measures we just described. Click on the cube designer’s Calculations tab, shown in Figure 1.

Figure 1. The cube designer’s Calculations tab

A single calculation is already defined; leave that intact because the correct calculation of any new entries will depend on it. Next, click the New Calculated Member button (fourth from the left) on the designer’s toolbar (or select the Cube/New Calculated Member option on the main menu or the New Calculated Member option on the Script Organizer pane’s shortcut menu). This action adds a new calculated member to the cube, which you must now configure.

The main area of the designer window displays a form prompting you for various parameters of the calculated member (Figure 2).

Figure 2. The cube designer’s Calculations tab, in form view

Enter [Adjusted Sales] for the Name field (all calculated member names with embedded spaces must be contained within square brackets), confirm that MEASURES is selected from the Parent Hierarchy drop-down list, and enter the following MDX expression in the Expression text area:

[Measures].[Total Sales] - [Measures].[Discount]

Next, select “Currency” from the Format String drop-down list. The completed calculated member form for [Adjusted Sales] is shown in Figure 3.

Figure 3. The Calculation tab in Form view for the Adjusted Sales calculated member

You have just defined a calculated measure called Adjusted Sales, which is the difference between the values for Total Sales and Discount. To see the MDX code that the designer generated, simply click the Script View toolbar button (11th button from the left or right) or choose the Cube/Show Calculations In/Script option from the main menu. Starting on the second line of the exposed MDX script (excluding comments), you should see the following MDX code, which represents the calculated measure you just created.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Adjusted Sales]
 AS [Measures].[Total Sales] - [Measures].[Discount],
FORMAT_STRING = "Currency",
VISIBLE = 1;

As you can see, each field in the designer simply prompts you for a different clause of the MDX CREATE MEMBER command.

Complex Expressions

Calculation expressions can certainly be more complex than simple arithmetic formulas. The MDX query language is extremely rich and expressive, allowing you to embed powerful declarative analytic functionality within calculations.

First save and deploy your changes so that [Adjusted Sales] can be used within the expression for a new calculated measure (that is, we will create a new calculated measure with a formula that references [Adjusted Sales]).

Click the Calculations tab’s Reconnect toolbar button (third from the left) to refresh the Calculation Tools pane’s Metadata tab. Switch back to form view using the Form View toolbar button (10th from the left) or the Cube/Show Calculations In/Form option on the main menu. In the Script Organizer pane, select the [Adjusted Sales] calculated member you just created and add a new calculated member.

Tip

New calculations are always inserted after the one selected. Selecting the [Adjusted Sales] calculated member before adding the new one ensures that the latter will be added at the bottom of the list (and thus at the end of the script).

Name the new calculated member [Adjusted Sales Growth], and then select MEASURES from the Parent Hierarchy drop-down list. Skip past the Expression field, and select Percent from the Format String drop-down list. Now place your cursor back in the Expression text area. The MDX expression for this calculation will be more complex than the last, so let’s take it step by step.

First enter the following text:

CASE
    WHEN

Be sure to type a single space after the WHEN keyword. This allows you to insert code immediately afterward and ensures that a space will exist between the WHEN keyword and the inserted code.

Using Calculation Tools

The next section of code is prone to typographical errors if typed manually, so we will take advantage of the Calculations Tools pane to continue entering our MDX expression. You can think of the Calculations Tools pane and its Metadata, Functions, and Templates tabs as a sort of drag-and-drop IntelliSense feature: You can actually drag your cube’s objects, MDX function call prototypes, or entire code templates into the Expression area, and code corresponding to the item you have dragged will be inserted for you.

Important

The Calculation Tools pane is a coder’s tool. Therefore it is visible and usable not only when the cube designer’s Calculations tab is in form view but also when it is in script view.

If the Metadata tab is not already selected within the Calculation Tools pane, click it to make it active. Next, drill down on the Order Date dimension and locate the Year - Quarter - Month - Date hierarchy, which should appear as the dimension node’s last child node. Drag and drop this hierarchy object into the Expression area, positioning it at the end of the second line, one space to the right of the WHEN keyword. Your expression text should now appear as follows:

CASE
    WHEN [Order Date].[Year -  Quarter -  Month -  Date]

If the code does not appear exactly as above, you can manually edit it to make it so.

You will continue by using a mixture of drag-and-drop and manual typing techniques, developing the MDX expression to contain the code shown here. (Don’t type it just yet.)

CASE
    WHEN [Order Date].[Year -  Quarter -  Month -
  Date].CURRENTMEMBER.LEVEL.ORDINAL = 0 THEN
        NULL
    WHEN ISEMPTY(([Order Date].[Year -  Quarter -  Month -
  Date].PREVMEMBER, [Measures].[Adjusted Sales])) THEN
        "EMPTY"
    ELSE
        [Measures].[Adjusted Sales]/
        ([Order Date].[Year -  Quarter -  Month -
  Date].PREVMEMBER, [Measures].[Adjusted Sales]) - 1
END

In a moment, we’ll discuss what this expression actually calculates; we’ll first discuss whether and how to use the items on the various tabs of the Calculation Tools pane to build out parts of the code.

You will find that the [Measures].[Adjusted Sales] item can be dragged and dropped from the Metadata tab; simply expand the Measures node at the top of the Metadata tab’s tree view, and then drag and drop the Adjusted Sales node into the Expression text area. The CURRENTMEMBER and PREVMEMBER properties can be found in, and dragged from, the Navigation branch of the Functions tab’s tree view. Doing so might be more trouble than it’s worth, however. You might find it easier to use the List Members or Complete Word shortcut menu options from within the expression edit region. You can also use the like-named options available by selecting Edit/IntelliSense on the main menu. You might find the Ctrl+J (for List members), Ctrl+Space, or Alt+Right Arrow (for Complete Word) keyboard shortcuts even more convenient.

The LEVEL and ORDINAL property keywords can be found under the Metadata node of the Functions tab (not the Metadata tab). They can also be dragged and dropped, but, again, doing so offers little in the way of productivity increases and requires manual replacement of the generated <<Member>> and <<Level>> prefixes. Sometimes it’s best to use the Calculation Tools pane simply as a reference tool to remind yourself of MDX syntax; at other times, the drag-and-drop functionality can be useful. Typically, complex expressions involving long dimension, hierarchy, and/or level and member names are better dragged than typed.

More Info

The items on the Templates tab of the Calculation Tools pane offer skeletal versions of long code constructs and give you a great head start for digging deeper into MDX. Items from this tab must, by their very nature, be dragged and dropped.

Regardless of how you compose the expression code, you should perform a syntax check on it when you’re done. To do so, use the Check Syntax toolbar button (10th from the right, with a spell check icon) or choose the Cube/Check Syntax option from the main menu. A message box should appear with the message “The syntax check was successful.” .

Deciphering the Code

Now let’s discuss how the calculation expression we just entered actually works. The easiest way to understand the expression is to start at the bottom and work your way up. Start by looking at the formula within the ELSE clause of the expression:

[Measures].[Adjusted Sales]/
        ([Order Date].[Year -  Quarter -  Month -  Date].PREVMEMBER,
[Measures].[Adjusted Sales]) - 1

This parenthetical part of this formula calculates Adjusted Sales for the previous period within the Year - Quarter - Month - Date hierarchy of the Order Date dimension. Adjusted Sales for the current period is then divided by this number, and finally the number 1 is subtracted from the quotient. The resulting value is the period-over-period growth for the Adjusted Sales calculated measure.

The previous and current “periods” that the expression references are determined by the context set by the query calling this function. For example, if the expression is calculated for a member of the Year level, the expression will record annual growth in Adjusted Sales. If it is calculated for a member of the Quarter level, the expression will return quarter-over-quarter growth, and so on. We will see shortly how you can use this calculated member in the cube browser to see Adjusted Sales growth at several levels of the Order Date dimension’s Year - Quarter - Month - Date hierarchy.

Once you understand the formula in the ELSE clause, the rest of the expression is easy because it just deals with invalid comparison scenarios. Moving upward through the code, if the expression is evaluated for the very first period in its level (for example, January 1996 in the Month level), PREVMEMBER will be empty and the formula will return the hardcoded string EMPTY. If the period passed to the formula is at level 0 of the hierarchy (that is, the All member), the formula will return NULL because the All member by definition can have no predecessor.