Leveraging OLAP
Hierarchies to Return Values
In the practice exercises in our last
session, we created a calculated member and performed retrieval of
values from the sample Budget cube. We made use of the current
member concept and discovered how we could perform a calculation for every
cell in the calculated member column, based upon the current member
of the corresponding row axis. We then practiced specifying dimensions in our
expressions to achieve desired results.
In this lesson, we will extend our
evolving use of calculated members to determine members' contribution to greater
wholes. This determination is quite desirable and common in financial and
other reporting. A good example might be the percentage of total
organizational expense that belongs to each individual store, to support
analysis of store performance relative to peer stores, or for other possible
purposes. We will also perform the calculation of each store's share of the
subtotals at the various levels of the Store dimension hierarchy
(City, State, and Country).
Our intent will be to demonstrate
further the power of OLAP cubes to embrace and exploit the hierarchical
relationships between dimension members. As a part of our efforts, we will
practice using tests to identify empty members, illustrating both why
this might be necessary, and how we can build in logic to deal with
complications that these members might present.
To
prepare for the lesson, let's open Analysis Manager (see previous
tutorials if you need a refresher on Analysis Manager, or if you are joining
the current series with this article), and create a new calculated member
within the Budget cube. This will provide a "fresh start," and
allow us a review of a few steps we accomplished before, within a standalone
tutorial.
From
the Analysis Manager console, and at the Cubes folder within the FoodMart
2000 database sample:
1.
Expand the Cubes
folder (seen in the Illustration 1), by clicking the "+"
sign to its left.
Illustration 1: Sample
Cubes provided with the Analysis Services Installation
2.
Right click the Budget
cube, and then click Edit from the flyout shortcut menu.
3.
When the Cube Editor screen
appears, click the Data tab at its lower left (Data Viewing
pane).
The Data Viewing pane
is activated, retrieving the view that was last saved, or perhaps the default,
if none was saved from previous lessons.
4.
Drag the Store
dimension from the top pane down to the current dimension in the Rows
axis (you can drop the icon that appears over the heading currently in place -
a small, double-headed arrow appears at the "drop" point), to "swap"
the current dimension to the top and Store below. (It will appear as Store
Country, the label that appears physically above All Stores
(technically the top level), in the current dimension's old
place).
5.
Drag the Measures dimension from the top pane down
to replace the dimension in the Columns axis, unless it is already
there. The heading will appear as MeasuresLevel, with the measure Amount
appearing just underneath.
6.
Select 1998, in the
filter field for the Time dimension atop the Data Viewing pane.
The contents
of the Data Viewing pane now appear as shown below:
Illustration 2: The Data
Viewing Pane Contents after our Modifications (Compressed View)
7.
Select Insert
(top menu).
8.
Select Calculated
Member on the drop-down menu, as shown in Illustration 3 below.
Illustration 3:
Selecting Insert -> Calculated Member
The Calculated
Member Builder appears.
9.
Type Expense
% into the Member Name box. In the Value Expression
box, input the following expression:
[Amount]/([Amount], Ancestor([Store].CurrentMember, #91;Store].[(All)]))
The Calculated
Member Builder dialog should now appear as partially shown below.
Illustration 4: The Calculated
Member Builder Dialog (Partial View)
» 
Comment and Contribute
