MDX in Analysis Services: Intermediate Concepts - Part 2 - Page 2
May 27, 2003
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.
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:
7. Select Insert (top menu).
8. Select Calculated Member on the drop-down menu, as shown in Illustration 3 below.
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.