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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles