MDX in Analysis Services: Intermediate Concepts - Part 1

Thursday Apr 17th 2003 by William Pearson
Share:

Author Bill Pearson resumes his new series, MDX in Analysis Services. In this lesson, we will explore the use of MDX within Analysis Manager to return values from an Analysis Services cube.

About the Series ...

This is the second 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.

Introduction

In the first lesson of our series, we set out to develop a broad understanding of the rudiments of multidimensional expressions by exposing fundamental concepts and terms. We explored basic MDX syntax, introducing the use of the language to return information from multidimensional data sources. We created a calculated member, and then used it as a vehicle from which to navigate the structure of our cube, through the use of MDX expressions. Using illustrated examples, we explored ways to return information about members and practiced using basic conversion functions. Finally, we explored putting simple conditional tests and comparisons to work in our expressions.

In this tutorial, we will look beyond using MDX to retrieve member names and properties, and focus on leveraging the capabilities of the language to return values from a multidimensional cube. We will create calculated members / measures that derive their values from current members / within the context of current measures, to illustrate the versatility within these arrangements. We will then further explore returning values from an OLAP cube, taking opportunities along the way to discuss relevant parts of the MDX system of notation.

Cell Coordinates in MDX

MDX references specific cells in a way that embraces multidimensional concepts. The MDX notation system is fairly straightforward - with practice, one can easily read it in standalone form. Using a graphical illustration of a simple cube with three dimensions, we can express the coordinates (that is, the "address") for a cell in terms of the associated dimension intersect points. In Table 1, we represent a simple three-dimensional cube structure that displays the amount ("Actual" Amount) of expense incurred for the Los Angeles Store locations (in this case, a single store) of a grocery store chain, by expense account (Expense), over various time intervals (Time).

"Actual"

Expense

Time

Amount

Store
All

All

All

15333.08

Los Angeles

All

All

7472.88

Los Angeles

Net Income

All

(7472.88)

Los Angeles

Marketing

All

3960.00

Los Angeles

Marketing

Q2

960.00

Los Angeles

Marketing

June

320.00

Table 2: Illustration of a Cell "Address" in a 3-Dimensional Cube

To specify the location of the highlighted cell in the cube depicted above, we would provide an "address" that consists of one member for each dimension in the cube. The highlighted cell in Table 1 would be assigned the "address" (or coordinates in terms of the dimensions of the cube) shown below:

([Los Angeles], [Marketing], [Time].[All])

The "address" referring to a cell, such as the one above, or to groups of cells, is typically referred to as a tuple. Although a tuple must specify a member (used as a coordinate) for every dimension in the cube, we do not have to specify dimensions in cases where we mean to refer to the current member. The current member is assumed as a default in the majority of cases, unless we need to specify the dimension explicitly.

NOTE: As is the case in our Los Angeles example above, parentheses surround tuples that contain more than a single dimension in MDX notation.

We often work with MDX expressions that are made up of multiple independent values. We can use tuples from our cube, constants, or other values within our expressions, as we will see, to provide these values - values that we can then "fuse" to meet our requirements through the use of operators, such as "+", "-", "*" or "/" . We will gain experience manipulating values and operators in the next section, where we will illustrate these concepts by putting them into action.

Returning Values within Analysis Manager

The focus of our hands-on examples in this lesson will be upon the use of MDX to access values inside our multidimensional data source. We will explore a couple of approaches to the retrieval of values within Analysis Manager in this section. One of our primary objectives will be to leverage the use of the current member concept.

To prepare for the lesson, let's shift from the Warehouse cube we used in Lesson One to the Budget cube that, like the Warehouse cube, is supplied as a sample with the Typical installation of Analysis Services. We will create a calculated member within the Budgets cube by following the steps below:

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 below), by clicking the "+" sign to its left.


Illustration 1: Sample Cubes provided with the Analysis Services Installation

2.      Right click the Budget cube.

3.      Click Edit from the context menu that appears.

4.      When the Cube Editor screen appears, click the Data tab at its lower left (the Data Viewing pane).

The Data Viewing pane is activated, and appears as shown below in Illustration 2.


Illustration 2: The Cube Editor Data Viewing Pane, Budget Cube (Compressed View)

5.      Drag the Store dimension from the top pane down, and drop it on the existing top row axis label (Level 02 in the illustration, but this may differ depending upon previous actions performed in our individual sample databases, etc.), to replace the existing dimension (Account, in the case of the illustration) in the rows axis.

Dropping the icon that appears over the heading currently in place (a small, double-headed arrow appears at the "drop" point) "swaps" the existing dimension to the top and Store below, in the original dimension's old place.

The Data Viewing pane now appears as shown below:


Illustration 3: The Data Viewing Pane after our Dimension "Swap"

6.       Select Calculated Member under Insert (top menu).

(NOTE: For more on creating calculated members, see Lesson One).

The Calculated Member Builder appears.

7.      Type MyCalcMem2 into the Member Name box.

8.      In the Value Expression box, input the following:

[Measures].[Amount]/12

The Value Expression box appears as shown below.


Illustration 4: The Value Expression Box with Inserted Calculation

9.      Click OK.

MyCalcMem2's values equal one-twelfth of the respective Amount of each row (the "current" row). The calculation is performed for every cell in the MyCalcMem2 column, basing its value, in each case, upon the Store dimension's current member.

10.  Review the newly created MyCalcMem2 column, comparing it to the illustration below.


Illustration 5: The MyCalcMem2 Value is Based upon the Store Dimension's Current Member

11.  In the Cube tree, select MyCalcMem2 in the Calculated Members folder for the Budget cube.

The Basic tab of the Properties pane appears underneath MyCalcMem2.

12.  Select the Parent Dimension property of MyCalcMem2, on the Basic tab.

Because MyCalcMem2 has Measures as its Parent Dimension, we see that MyCalcMem2 is technically a Calculated Measure.





13.  Select the Store dimension within the Parent Dimension property, as shown below:






Illustration 6: Select Store as the Parent Dimension

We see from the dynamic caption at the bottom of the Properties pane that we are selecting "the dimension that contains the calculated member."

14.  Press Enter.

15.  Select the Value property on the Basic tab, once again.

16.  Click the ellipsis button ("..") to raise the Calculated Member Builder dialog.

17.  Type the following into the Value Expression box.

[Store].[Store Country].[USA]/12

18.  Click OK, and compare the result set to that pictured in Illustration 7.


Illustration 7: The Value for MyCalcMem2 Reflects the Entered Formula

19.  Select 1998, in the filter field for the Year dimension atop the Data Viewing pane. The Data Viewing pane presents the results set shown below.


Illustration 8: Results Set for Year 1998

The amounts are now negative, as the sample Budget cube only reflects actual expense items in its measures for 1998 (the "current year" of the cube). We will see this more clearly as we work with lower levels of the Account dimension hierarchy in later lessons. (Our focus here is not the composition of the Net Income number, but the interaction of the various cubes members between themselves.)

20.  Swap (by dragging and dropping, as we did before) the Account dimension from the top pane down to replace the Measures dimension in the columns axis.

The value of MyCalcMem2 is automatically filled with the contents of the corresponding Net Income cell (the Assets and Liabilities amounts are not populated in the Budget cube). The value for the Net Income member of the current account has been retrieved and displayed.

21.  Swap the Store dimension in the rows axis with the Measures dimension.

Now let's specify MyCalcMem2 as the dimension for which we are retrieving values. (This became possible when we made Store the Parent dimension of MyCalcMem2.)

22.  Select MyCalcMem2 within the Store dimension filter box.

The results set appears as below.


Illustration 9: The Results Set, Filtering for MyCalcMem2.

We can see, in each cell, the value retrieved by the expression that composes MyCalcMem2. For Net Income, whose value is reflected as ($18,153.64) (again, negative because only expenses are included in the cube), the current member of the Measures dimension ([Amount]) is derived in the context of the row, while the current member of the Account dimension ([Net Income]) is derived in the context of the column. The filter fields in the top pane define the context of the dimensions with which they are associated. MyCalcMem2 represents the current member of the Store dimension, pointing to the [USA] member through the expression ([USA]/12), which defines the calculated member.

Next, working within our current example, let's look further at retrieving values, by taking the following steps.

23.  Swap the Store dimension and the Measures dimension.

The Store dimension appears in the rows axis, presenting the results set shown below.


Illustration 10: The Store Dimension replaces the Measures Dimension.

24.  Highlight MyCalcMem2 in the Calculated Members folder of the Budget cube.

25.  Select the Value property for MyCalcMem2.

26.  Click the ellipsis button ("..") once again, to raise the Calculated Member Builder dialog.

27.  Type the following into the Value Expression box.

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

28.  Click OK to close the Calculated Member Builder.

The Data Viewing pane appears as shown in Illustration 11 below.


Illustration 11: The Tuple Appears in the Calculated Member.

The total for the Mexican locations' Net Income (or, as we explained earlier, the Total Expenses, as no Revenues amounts are stored in the Budget cube) appears in every cell of the MyCalcMem2 row. Besides the two dimensions that we specified (Net Income and Mexico), the current member is implicitly used to satisfy the requirement for the identification of the remaining dimensions' members. In other words, the two dimensions specified in the tuple, together with the current members for any unspecified dimensions, compose the coordinates for the cell, and, contextually, the value of the cell.

29.  Select the Parent Dimension property of Calculated Member MyCalcMem2.

30.  Select the Account dimension via the drop-down arrow.

31.  Click any place on the Data tab, Data Viewing pane, to refresh.

32.  Swap the Measures dimension with the Store dimension, placing the Measures dimension in the rows axis.

The results set appears as below.


Illustration 12: The MyCalcMem2 Value does not Change.

As we see in the illustration, the MyCalcMem2 column shows the Net Income figure for the Mexican stores; the presence of the Mexico dimension in MyCalcMem2 specifies the Mexico member of the Store dimension as part of the context within which the value is derived.



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

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