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).
(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:
-- Step 1, Calculated Cells Tutorial
SELECT {[Measures].[Warehouse Cost]} ON COLUMNS,
[Warehouse].[Warehouse Name].Members ON ROWS
FROM Warehouse
7.
Execute the
query using the Run Query button.
The
results dataset appears as shown in Illustration 3.
Illustration
3: Results Set, Core Query
8.
Save the query as ANSYS18-1,
leaving it open for the next step.
Now we will add a WITH
clause, containing the definition of a calculated cell, which, for this
exercise, we will call "Warehouse Cost Scope."
9.
Remove the comment line (--
Step 1, Calculated Cells Tutorial) from the top line of the query.
10.
Add the following syntax into
the Query pane, before the SELECT clause:
-- 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.
Illustration 4: Creating
the Calculated Cell via the WITH Clause