Featured Database Articles

Calculated Cells in an MDX Query

As we
learned in Part I, the value within a calculated cell is computed
at run time through an MDX expression, which 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, which would also be specified
at the point of definition of the calculated cell.

We
explored the means of creating a calculated cell from the Analysis Manager, and
practiced various aspects of the creation of a calculated cell in an
illustrative example, in our last lesson. Our interaction with calculated cells
at that juncture was through the graphic interface of the Analysis Manager,
from which we input some of the supporting MDX syntax, via the appropriate Member
Properties fields. In this lesson, we will perform all the steps for calculated
cell creation directly in MDX.

Overview

As in the process of creating a calculated member within an
MDX query (see my various DatabaseJournal articles, index page,
for a listing), the WITH clause comes into play: We can also use the WITH
clause to define calculated cells and their properties. Alternatively,
calculated cells can be created in an MDX query using the CREATE CELL
CALCULATION statement, but we will focus on the WITH clause
approach. This would be a typical approach for creating a calculated cell with
session scope, in contrast to the global scope one would expect
to provide with a calculated cell created by using the Calculated Cells Wizard
or by using the CREATE CELL CALCULATION statement. We discussed session
and global scope in Part I.

Creating
a Calculated Cell in MDX

We
will begin our creation of a calculated cell by firing up the MDX Sample
Application once again, as it provides a platform that is available to most
MSAS users from which to build MDX queries.

1.
Go to the Start
button on the PC, and then navigate to Microsoft SQL Server --> Analysis Services, then to the MDX Sample
Application.

We are
initially greeted by the Connect dialog, shown in Illustration 1
below.

Illustration 1: The
Connect Dialog for the MDX Sample Application

The
illustration above depicts the name of my server, MOTHER1, and properly
indicates that we will be connecting via the MSOLAP provider (the default).

2.
Click OK.

(We
might also choose to cancel the dialog box and connect later by clicking Connect
on the File menu.)

The MDX
Sample Application window appears.

3.
Clear the top
area (the Query pane) of any remnants of queries that might appear.

4.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.

5.
Select the Warehouse
cube in the Cube drop-down list box.

The MDX
Sample Application window should resemble that shown in Illustration 2,
complete with the information from the Warehouse cube displaying in the Metadata
tree (left section of the Metadata pane).

Illustration 2: The MDX
Sample Application Window

We
will create an MDX query that defines and creates a calculated cell, and that
also returns cell properties as a part of the query result. Let's start with a
simple core query, upon which we will base our work in the steps of the
practice example.

6.
Type the
following core query into the Query pane of the sample application: