MDX in Analysis Services: Intermediate Concepts - Part 2

Tuesday May 27th 2003 by William Pearson

Join Bill Pearson as he further develops intermediate concepts using MDX in Analysis Services. In this tutorial, Pearson works with hierarchical relationships in leveraging MDX to meet analysis needs.

About the Series ...

This is the third tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services (to which I will refer in most cases as simply "Analysis Services," to save time and space). Our primary focus is the manipulation of multidimensional data sources, using MDX expressions in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.


In our last tutorial, we took MDX beyond the retrieval of member names and properties, and began to focus on leveraging the capabilities of the language to return values from a multidimensional cube. We created calculated measures whose values were based upon a constant, then upon current members, and explored additional uses of calculated members and measures. We practiced returning values, based upon the specification of dimensions within MDX expressions, to extend the expressions' utility within the context of reporting from an OLAP data source. Moreover, we examined various aspects of the MDX notation system along the way.

In this tutorial, we will expand further the intermediate topics we introduced in our last lesson. We will take on practice examples where we will delve into handling hierarchical relationships in our expressions. We will also discuss a way to identify empty members, and illustrate why this is often important in building expressions.

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)

10.  Click the Check button to ascertain proper MDX syntax.

The Syntax is OK message box appears, indicating that the expression entered at least does not violate any general syntax rules. While the "check" process does not validate the propriety or accuracy of the expression beyond the context of syntax, it still serves as a great way to detect missing or unpaired parentheses, and other typical keystroke errors.

11.  Click OK.

The message box closes, placing us back at the Calculated Member Builder dialog.

12.  Click OK again.

The Calculated Member Builder dialog closes.

13.  In the Cube tree, select Expense % in the Calculated Members folder for the Budget cube, if it is not already selected.

The Basic tab of the Properties pane appears underneath Expense %.

14.  Select the Parent Dimension property of Expense %, on the Basic tab.

15.  Ensure that the Measures dimension appears in the selector box for the Parent Dimension property.

16.  Click the Advanced tab in the Properties pane.

17.  Select the Format String property of Expense %.

18.  Select Percent as the format for the string, by insuring the word appears in the selector box for the Format String property.

19.  Press Enter.

The result set appears as shown in Illustration 5.

Illustration 5: The Modified Result Set from the Data Viewing Pane

20.  Double-click the Mexico member of the Store Country level (Rows axis) to explode to the Mexico Store State view of Store dimension.

We have now exploded to the second level for Mexico in the Store hierarchy, that of the individual Mexican Store State members. The results should appear as shown below.

Illustration 6: Data View Drilled Down to the Mexican Store State Members

We note that, while the Expense % for the individual Mexican Store States does, indeed, equal 100% at the All Stores Total rollup (the top dimension level), our calculated member does not extend the same treatment to the hierarchical levels. That is, the Expense % for the Mexican Store States does not indicate the relative contribution of the Store States to their respective immediate rollup levels. (In other words, the Mexican Store States' percentages do not total to 100% at their hierarchical rollup level Mexico Total - they show their respective contributions to the top level, All Stores, only, in that they simply add to a total of 76.21%, the Mexico Total contribution).

We will modify our expression to use an extended version of the Ancestor function to 1) identify the hierarchical Parent for the Store States and 2) calculate context sensitive rollup percentages, based upon our position in the drill-down of the hierarchy. The distance version of the Ancestor function has the same meaning as the Parent function, when the "distance," or the number of levels removed from the original member (in our case, the current member) to the target member (in our case, the Parent member) is 1.

21.  Right-click the Expense % calculated member.

22.  Select Edit from the appearing Context Menu.

The Calculated Member Builder reappears.

23.  In the Value Expression box, input the following expression:

	[Amount]/([Amount], Ancestor([Store].CurrentMember,1))

24.  Click OK to apply the changes.

	[Amount]/([Amount], Ancestor([Store].CurrentMember,1))

The results of the modified expression should appear as shown below.

Illustration 7: The New Result Set from the Data Viewing Pane

Now the percentages for the Mexican Store States add to 100%. We have achieved our objective, in that the group of the Store Country level Expense % values, as well as the group of the Store State level Expense % values, total 100% of their respective rollup levels (the level Totals). This scenario demonstrates how MDX allows us to leverage the hierarchical structure of the OLAP data source in our expressions.

Dealing with Empty Members

We notice a "fly in the ointment," however, in Illustration 7: The top level Expense % value is nonsensical. Our expression does not yet provide for cases where the Parent (distance = 1 from the current member) is non-existent; the top level cannot refer to a higher level, thus our expression generates a zero in its denominator. And, as we all know, the result of dividing by zero is "undefined."

We will handle this annoying situation with another enhancement to our expression. We will add a test that will help us to identify top-level members, and to ensure that they are treated differently, to accommodate the fact that, when it comes to Parents, they are "empty." While there are numerous ways to handle this, we will keep it simple, and take the following steps:

25.  Right-click the Expense % calculated member, once again.

26.  Select Edit from the appearing context menu.

The Calculated Member Builder reappears.

27.  In the Value Expression box, input the following expression:

IIF([Store].Currentmember.Level.Ordinal =0,1,[Amount]/([Amount], 

28.  Click OK to apply the changes.

The result set that is retrieved should appear as shown below.

Illustration 8: The Result Set after Addition of the Conditional Test

(Remember to take advantage of the Check button to make sure that the correct number of parentheses, etc., is placed.)

Our modified expression is saying that, "if the ordinal for the current member under consideration is zero, substitute a "1," rather than performing the calculation we have built up to this point (the calculation is the "object" of the "else" clause that appears after the "then" value "1" above). The purpose of the conditional test is to determine if the ordinal (a measure of depth from the root level) of the level of the current member equals zero -whether the current member is at the top (or "All") level, and, thus, parentless.

We see from the above that we have achieved success in tailoring our expression to accommodate empty members, in our case those members from which a zero result is generated when our expression is applied to "parentless" members. The zero result would have caused mischief in our expression, as it would result in a zero denominator - something that most of us have learned to be "bad juju" in many scenarios, particularly in the present example, where information consumers are not likely to be amused with unintelligible metrics.

30.  Select File -> Save or -> Save As... from the top menu, as desired.

31.  Select File -> Exit to close the Cube Editor, handling prospective dialogs as appropriate.

Next in Our Series ...

In this tutorial, we expanded further the intermediate topics we introduced in Tutorial Two of the series. We took on practice examples where we delved into handling hierarchical relationships in our expressions. We also discussed one of multiple ways to identify empty members, illustrating why this is important in building expressions.

In our next lesson, Retrieving Values from Multiple Cubes, we will examine how we can use MDX within Analysis Services to retrieve values from multiple cubes simultaneously, offering us the often useful option of accessing multiple OLAP data sources together for analysis and reporting. We will discuss a real-world scenario in which a need for this capability commonly occurs: We will demonstrate how we can compute a per unit average, within the context of providing a Revenue per Unit Sold value based upon values retrieved from two separate OLAP data sources.

» See All Articles by Columnist William E. Pearson, III

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved