Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II - Page 2

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.

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

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

(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.

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).

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.

-- Step 1, Calculated Cells Tutorial
SELECT  {[Measures].[Warehouse Cost]} ON COLUMNS, 
    [Warehouse].[Warehouse Name].Members ON ROWS 
FROM Warehouse

The results dataset appears as shown in Illustration 3.

Now we will add a WITH clause, containing the definition of a calculated cell, which, for this exercise, we will call "Warehouse Cost Scope."

-- Step 2, Calculated Cells Tutorial
WITH 
   CELL CALCULATION [Warehouse Cost Scope] 
   FOR '([Warehouse].[Warehouse Name].MEMBERS)'
   AS 'CalculationPassValue (Warehouse.CurrentMember, 0)',
      CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, 
          [Warehouse Cost]), 0) > 11000'

The full query should now appear in the Query pane as shown in Illustration 4.