The Power of Calculated Cells

Calculated cells can do more—and do it better—than calculated members

Recently, a DBA asked me why he should use a multidimensional database system such as SQL Server 2000 Analysis Services instead of just making his own set of Relational OLAP (ROLAP) tables to store the hierarchical structure of all a cube's dimensions and associated aggregations. I'm not sure whether he thought that Analysis Services is an immature product or whether he was just trying to understand how OLAP technology has evolved over time. If you haven't used Analysis Services, you might assume that it is rather basic because it has only been on the market since January 1999 with SQL Server 7.0 (as OLAP Services). But Analysis Services isn't a first-generation OLAP product at all. Microsoft acquired the product from an existing company (Panorama Software), built a development team from experienced engineers, and performed an in-depth competitive analysis before the company brought the first version of Analysis Services (OLAP Services) to market. Although Analysis Services isn't perfect, it stacks up well against its competitors.

One new feature that clearly differentiates Analysis Services from a basic OLAP engine is calculated cells. Calculated cells let you apply conditional formulas to a subset of a cube's cells. A conditional formula can change the value of the affected cells and the cell properties. Don't confuse cell properties with member properties. Cells are the values that appear in the grid of a query result; examples of cell properties include string formatting, foreground color, and background color. Dimension members are the business entities that appear on the row and column headings of a query result.

When the Analysis Services development team first told me about calculated cells, I didn't understand the feature's significance. I thought calculated cells were part of "feature creep"—a phenomenon in which useless or redundant features find their way into a new release of a product; it seemed that calculated members could do almost anything that calculated cells could. But the development team was obviously excited, so I knew there had to be more to the story. Well, guys, if you're reading this column, I get it now!

Five Good Reasons to Use Calculated Cells

Here are some reasons I can think of to use calculated cells. First, their ability to change cell properties with a condition formula is unique in MDX. So, for the first time, you can use MDX to customize query results, such as highlighting exceptions. For exception highlighting to work, your OLAP front-end application has to use cell properties when displaying query results, and most Analysis Services applications can't do this. However, I'm sure the popular Analysis Services front ends (such as Knosys ProClarity Analytical Platform or Business Objects' OLAP@Work) will do so soon. Exception highlighting lets you use font and color attributes to highlight certain cell values based on a numeric condition. For example, you might want to display in red any product sales numbers that are less than the sales value from the previous year.

Second, limiting a calculated cell formula to a well-defined subcube (a subset of the cube's dimension members and cells) is much easier than limiting a calculated member in the same way. To limit a calculated member, you must nest several conditional IIF statements; the result is difficult to read. For example, the following calculated member formula returns 500 for all cells in the (1998, Drink) subcube; in the rest of the cube's cells, the formula returns Mexico:

CREATE MEMBER [Sales].[Measures].[FiveHundred] as 'iif(Ancestor(Time.CurrentMember,[Year]).Name = "1998", iif(Ancestor(Product.CurrentMember, [ProductFamily]).Name = "Drink", 500, [UnitSales]), [Unit Sales])'

The following calculated cell formula affects the same subcube but is simpler and works whenever you select the existing Unit Sales measure:

CREATE CELL CALCULATION [Sales].[Five Hundred]FOR '(Descendants([1998]),Descendants([Product].[All Products].[Drink]),{[Unit Sales]})'AS '500'

Third, calculated cells work independently from other dimensions. A limitation of calculated members is that you must put a calculated member in an existing dimension. I created the earlier example of a calculated member in the Measures dimension, which means that you must use this calculated measure for the formula to be in effect. This limitation keeps you from being able to apply the formula to any cross-section of the cube that you select. Calculated cells don't have this limitation.

Fourth, calculated cells can change cubes without adding new elements. This ability is valuable because you can change the way a cube derives numbers without changing existing applications or OLAP reports. OLAP users can get the new values without having to change the way they navigate an existing cube. With a calculated member, the user or client application must select the calculated member in any MDX queries for the formula to affect the results.

Fifth, calculated cells can work in multiple passes. You can have a formula for a calculated cell that applies to more than one pass of an MDX query evaluation. Multiple-pass execution of MDX queries is possibly the most advanced topic in Analysis Services, so I won't go into details in this article. Suffice it to say that multiple passes let you do things such as multistaged budget calculation. If you have a cube with budgeted and actual expenses, you could create a multiple-pass formula like this one for budgeted numbers:

Multiple-pass evaluation is powerful, but it isn't the same as a compound (nested) IF statement. The example above applies step 1 to all the budgeted values in the budget category before applying step 2. So the average of the other budgeted values (which step 2 calculates) uses the results of step 1. In contrast, a compound IF statement would go through all three steps on one budgeted value before calculating the next budget value.

Creating Calculated Cells

As with calculated members and named sets, you can create calculated cells in three ways. You can use the Analysis Manager or the Decision Support Objects (DSO) API to create calculated cells on the server, you can use the CREATE CELL CALCULATION statement, or you can include the calculated cell definition in your MDX query by using a WITH clause.

The first and easiest way to create a calculated cell is to use the Calculated Cells Wizard in Analysis Manager. You can find the wizard in the Cube Editor by selecting the Insert drop-down menu and clicking Calculated Cells. Alternatively, you can click the Cube Editor's Insert Calculated Cells toolbar button, which looks like a small cube inside a bigger cube.

The Calculated Cells Wizard starts by asking you to specify the subcube, as Figure 1 shows. When you choose a members set type, more choices appear so you can complete the formula. In this example, I wanted all the cells in the categories of 1998 and Drink Products to be affected when someone selects the measure Unit Sales. To do this, I first selected the Time dimension from the list on the left, then chose the members set type Descendants of a member. After I selected this set type, the wizard let me select the member (1998) from a tree view. In the same way, I configured the Product dimension to be the descendants of the Drink product family. I selected Unit Sales from the Measures dimension and A single measure as the members set type. After configuring these three dimensions and leaving all other dimensions at their defaults, I clicked Next.

The next step in the wizard is to specify the condition under which Analysis Services should apply the formula to the subcube, as Figure 2 shows. In this example, I chose not to specify a condition, but any MDX expression that results in a true or false condition is legal here. For example, if I wanted to have the formula affect only the last dimension member in each Time sibling group, I could use

Time.CurrentMember.Name =Time.CurrentMember.LastSibling.Name

Figure 3 shows the next wizard dialog box, which lets you enter the formula for the calculated cell. Here, I entered the value 500, which causes all Unit Sales cell values beneath 1998 and Drink products to equal 500 (not particularly useful, but it demonstrates the point). Note that this formula will override the way dimension members are calculated, usually by aggregating the values of their children. For example, now the value of 1998 and the value of each individual quarter in 1998 will be 500.

The final wizard step lets you name the new cell calculation (I called this one Five Hundred). After you click Finish, you can save your cube (drop down the File menu and click Save), then click the data tab page at the bottom of the cube editor. This tab lets you browse the cube and verify that the cell calculation had the intended effects. In this example, if you drag Time onto the columns and Product onto the rows of the cube browser, then drill down into both dimensions, you'll see that all dimension members in 1998 and Drink products have the same value.

The second way to create a calculated cell uses the CREATE CELL CALCULATION statement. You can find the correct syntax for this method in my earlier example that compares a calculated member with a calculated cell formula.

The third method of creating a calculated cell formula uses a WITH clause in an MDX query. Here's an example of that syntax:

WITH CELL CALCULATION [Five Hundred]FOR '(Descendants([1998]),Descendants([Product].[All Products].[Drink]),{[Unit Sales]})'AS '500'SELECT Time.Year.Members ON columns,   [Product].[Product Department].Members ON rowsFROM Sales

Calculated cells are powerful. In fact, I'm convinced that calculated cells are more powerful and useful than calculated members. Calculated members have their place (e.g., performing custom groupings of other dimension members or "one off" formulas separate from the core cube), but calculated cells can do many things that people use calculated members for—and calculated cells can do them better. What's more, OLAP front-end applications don't have to change right away to support calculated cells because you can configure them on the server through Analysis Manager. And client applications don't require special support for calculated cells to work. I encourage you to start thinking of ways to change your OLAP applications, cubes, or methods of analysis to incorporate calculated cells. Send your ideas to me at [email protected].