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.
