Creating
a Calculated Cell
We can create
calculated cells with the Calculated Cell Wizard or the Cube Editor,
both of which are accessed from within Analysis Services. Calculated cells
originated in this way are created with a global scope, meaning that
they will be "permanently" available to all information consumers who
browse the cube, much like a calculated member created within the cube
superstructure.
We can also create
calculated cells with a session scope, meaning that they are available
only to MDX queries executed within the session in which they are born. These
calculated cells are essentially "creatures of the MDX query," and
they can be used to provide a higher level of flexibility for client
applications (among other uses).
We will examine the
steps involved in creating calculated cells under both global and session
scopes, in this and the subsequent article, respectively. We will focus on the
calculated cell in an MDX query in our next lesson, Using Calculated Cells in Analysis
Services, Part I.
Creating a calculated
cell within Analysis Services, the primary focus of this lesson, will serve to
introduce us to general concepts from the perspective of the Calculated Cell
Wizard, in which the process of creation is largely guided and
user-friendly. This brief overview with the wizard will afford us an
opportunity to focus on the attributes and characteristics of the calculated
cell itself, before getting involved in the MDX syntax in our next lesson.
Creating the
Basic Calculated Cell
We will start Analysis
Services, navigating to the Warehouse cube, which is supplied as a
sample when we install Analysis Services.
1.
Start Analysis Manager.
2.
Expand the Analysis Servers
folder by clicking the + sign to its left.
3.
Expand your server (typically
named the same as your PC, but determined by the installation/setup), exposing
the FoodMart2000 database, as shown in Illustration 1.
Illustration 1: Navigate
to the FoodMart2000 Database in Analysis Manager
4.
Expand the FoodMart2000
database by clicking on the + sign to its left.
5.
Expand the Cubes folder
by clicking the + sign to its left.
The cubes appear,
similar to those shown in Illustration 2.
Illustration 2: Sample Cubes Provided with the Analysis Services Installation
6.
Right-click the Warehouse cube,
then click Edit from the context menu.
The Cube Editor appears.
First, let's set the
stage for the steps to come. We will be performing an analysis that focuses on
individual warehouses, so let's set up the Data viewing pane to conform
to our needs.
7.
Click the Data tab on
the right half of the screen.
8.
Drag the Warehouse dimension
to the rows area (the left column of the lower pane section), dropping the icon
onto the current column heading.
9.
Ensure that 1997 is displayed
in the filter box in the Time dimension in the top half of the
pane, indicating that we are filtering on 1997.
10.
Double-click USA,
in the Country column, to expand the hierarchy and show the underlying
states.
11.
Double-click the State
Province column heading to drill down to display members of
the City level.
12.
Double-click the City
column heading to drill down to display individual Warehouse Names.
13.
Scroll over so that the Warehouse
Cost measure appears alongside the individual warehouse members.
The Cube Editor Preview
pane should appear, as partially shown in Illustration 3.
Illustration 3: Cube
Editor Preview Pane (Partial View), After Adjustments
14.
Select Insert from the
top menu.
15.
Click Calculated Cells...
on the Insert menu, as shown in Illustration
4.
Illustration 4: Select
Calculated Cells... to Initialize the Calculated Cells Wizard
The Calculated Cells
Wizard Welcome dialog appears, as shown in Illustration 5.
Illustration 5: The Calculated Cells Wizard Welcome Dialog Box
The Define the
Calculation Subcube dialog box appears.
» 