MDX in Analysis Services: Intermediate Concepts - Part 1 - Page 7

April 17, 2003

33.  Select Canada (under "All Stores") in the filter field for the Store dimension atop the Data Viewing pane. The pane appears as shown below.

Illustration 13: The Tuple Value Remains even with the Canada Filter

We see that MyCalcMem2 retains the tuple value of ([Account].[Net Income],[Store].[Store Country].[Mexico]), even when the Canada filter is in place, for the results set. This demonstrates clearly that the expression, and not the current member for the Store dimension, determines the context of the value.

Say we are presented with a requirement to quantify the difference in Net Income between Mexico and Canada stores. We can create an expression within our calculated member to calculate the difference and return the amount. This will provide an added illustration of the use of tuples from our cube, along with an appropriate operator, to build an expression to fit a simple business need.

34.  Select Value property for MyCalcMem2 once again.

35.  Click the ellipses button.

The Calculated Member Builder dialog appears.

36.  Type in the following into the Value Expression box:

([Account].[Net Income],[Store].[Store Country].[Mexico])-([Account].[Net Income],[Store].[Store Country].[Canada])

37.  Rename the Calculated Member "NAFTA Partners Difference" using the Member Name box in the dialog.

The Calculated Member Builder dialog appears as shown below.

Illustration 14: The Calculated Member Builder Dialog with Modifications

38.  Click OK to enact changes.

The Calculated Member Builder dialog closes, leaving the result set shown in Illustration 15.

Illustration 15: The New Expression Renders the Difference, as Expected

We see, when we determine the difference in this way, that the figure delivered by MDX (-761,869.84) indeed agrees with the independent calculation (the difference between -790,921.84 and -29,052.00). We obtain the difference regardless of the presence of conflicting filters in the top of the Data Viewing pane, because we have explicitly specified dimensions in our expression.

39.  Click File ` Exit from the top menu to leave the Cube Editor.

40.  Click No to abandon, or Yes to save, changes to the cube.

41.  Close Analysis Manager as desired by clicking Console ` Exit in the upper top menu.

Next in Our Series ...

In this 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 from cells 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.

Our next tutorial expands further the intermediate topics we introduced in this 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 important in building expressions.

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