Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I

About the Series ...

This is the seventeenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("MSAS"), with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Introduction

In this article, we will explore calculated cells, which enable us to apply functionality previously reserved for calculated members, custom members, and custom rollup formulas (all of which we have explored in previous articles) to a specific range of cells--or even to a single cell. We will describe the construction of a calculated cell, touching upon the basic properties that make it up.

In this lesson, we will do the following:

• Overview calculated cells and discuss some of the advantages and benefits that attend their use
• Discuss the properties that must be defined to create a calculated cell
• Discuss several optional property options that can be selected after definition of the calculated cell
• Overview briefly the practical use of Microsoft Windows operating system red-green-blue format (RGB) values
• Modify a calculated cell to enable exception highlighting in an example business need scenario

Calculated Cells in Analysis Services

The value within a calculated cell is computed at run time through a specified MDX expression. The expression is specified when the calculated cell is defined. The expression can be conditionally applied to a cell or range of cells, based upon an MDX logical expression. In these cases, the logical expression is also specified at the point of definition of the calculated cell.

Overview

The advent of MSSQL Server 2000 Analysis Services witnessed the arrival of calculated cells, which did not exist in earlier versions of the application. Calculated cells allow us to perform actions that could be accomplished only by calculated members, custom members and custom rollup formulas.

Calculated cells consist of three main elements:

• Calculation subcube
• Calculation condition
• Calculation formula

The calculation subcube is an MDX set expression that defines the slice of the cube over which the calculated cells will be in effect. A list of single dimension sets defines the calculation subcube, and each of the sets contains one of the following:

• All members of a dimension (including the Measures dimension): Calculated members can be included (using the .AllMembers MDX function).
• A single specified member of a dimension (Measures, again, is included).
• All members at a specified level within a dimension: Calculated members can be included (again, using the .AllMembers MDX function).
• The descendants of a specified member within a dimension.
• The descendants of a specified member at a specified level within a dimension.
• An MDX expression that generates a set containing one of the above sets.

This list of dimension sets, combined with the default member of all other unspecified dimensions in the cube, defines the calculation subcube.

Within the subcube, the calculation condition is compared with each member cell. As we noted earlier, the calculation condition is an MDX logical expression that acts to further limit the effects of the calculated cells. If the condition evaluates as True for a given cell, the formula in the calculated cell is applied to the member cell that indicates True, and the cell returns the calculated value. The member cell returns its original value if the calculation condition evaluates as False. The combination of the calculated cells condition and the calculation subcube is termed the calculation scope.

The third component of a calculation cell, the calculation formula, is an MDX value expression that calculates the value of the cells that lie within the calculation subcube.

To summarize the interaction between the parts of a calculated cell, we define a specific target range of cells, we supply a condition that must be met before applying a formula and we apply a formula within that specific range of cells for any cells meeting the condition. We will see the three main elements of the calculated cell in action in the steps of the practice exercise that follows.