MDX Essentials: MDX Members: Introducing Members and Member

Monday Feb 24th 2003 by William Pearson
Share:

Author Bill Pearson continues his MDX Essentials Series with an introduction to Members and Member Functions. This lesson sets the stage for the upcoming MDX Member Functions segment of the MDX Essentials series, and explores the .Members operator.

About the Series ...

This is the fourth article of the series, MDX Essentials. The primary focus of this series is an introduction to the MDX language. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements needed for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

What We Accomplished in our Last Article

In the third article of the series, MDX Operators: The Basics, we explored some of the most basic components involved in the construction and manipulation of tuples and sets. The operators we explored in Lesson Three included curled braces "{}", commas "," and colons ":". We discussed the role each operator plays in MDX expressions and queries, explored the rules of syntax involved in putting each into action, and then provided hands-on exposure to the use of each in simple expressions. We executed the practice expressions to view their respective outputs, commenting on the significance of the results, as a means of a providing a basis for more complex query building as we progress through the series.

In this lesson, MDX Members: Introducing Members and Member Functions, we will introduce the concept of members, and discuss their pervasive significance within MDX. We will then launch the first discreet group of articles in the series, Member Functions. As part of this first foray into the Member Functions collection, we will begin our exploration of the powerful .Members operator. Within our exposure of this robust and highly useful operator, we will discuss the syntax within which we can best employ the function, illustrating some of its uses within MDX expressions through the use of practice exercises.

Introduction to Members and Member Functions

As we have noted, this lesson will be the first of the Member Functions segment of articles of our series. We will focus on the composition of these important components of MDX, and provide hands-on exposure to their use in simple expressions that we will run to view their output. Rules of syntax will be emphasized, the aggregate body of which will provide a basis for progressively more sophisticated query building as we progress through the series.

This lesson will include:

  • A brief discussion of Members and their role in MDX;

  • An introduction to the .Members operator, and an examination of the syntax that surrounds its use;

  • A examination of the MDX query results we obtain in examples that use the .Members operator under consideration;

  • A brief look at the NON EMPTY keyword, and an illustration of its usefulness in "clearing the decks" of empty tuples;

  • A look ahead to the Member Functions that we will explore in forthcoming articles.

As many of us are aware, members represent an important and pervasive concept in an understanding of MDX. A member is, simply, an item in a dimension; members compose the values of the attributes that belong to a dimension. Keep in mind that measures are themselves dimensions, and so they, too, are composed of members. To illustrate, for a dimension based upon geography, which might contain Country, State and City as levels, USA, Idaho, and New Orleans might represent valid members.

MDX contains a set of functions, known as member functions that enable us to perform operations upon any member of a dimension. Member functions return a member or a zero. A simple illustration that follows our geography example above would be as follows: The Parent function, applied to the member New Orleans - as in Parent (New Orleans) - would equal USA.

As we will discover in a later lesson, member functions allow us to perform operations, based upon a member's relative position, either up ("above" the member upon which the function is being performed) or down ("below" the member upon which the function is being performed) a dimensional hierarchy. A zero is returned if we attempt to go beyond the boundaries of a defined hierarchy - an example of this might be trying to apply the .Parent function to a member at the top of the hierarchy (which has no parent, in this context). We will see numerous examples of this in the coming Member Functions articles that focus on "family" member functions.

Working with Members and the .Members Operator

The .Members operator provides a ready means of obtaining the "membership" for a given level, hierarchy or dimension. The importance of the .Members operator becomes clear when we get enough exposure to MDX under our belts to realize that this is a very common point from which we conduct operations that are more involved.

A simple illustration of the .Members operator in action follows:

SELECT
 [Measures].Members ON COLUMNS,
[Department].Members ON ROWS
FROM [HR]

The result dataset returned would appear as shown in Illustration 1 below:


Illustration 1: Example Result Dataset from Using the .Members Operator

Discussion:

As we can see, use of the .Members operator in the illustration above produces a result dataset containing:

  • All members of the Measures dimension (as columns);

  • All members of the Department dimension (as rows).

It is important to remember that the .Members operator must be applied at a level within a dimension where hierarchy (and thus the "membership" we are requesting) is not ambiguous. If multiple hierarchies exist within a dimension, we must apply the .Members operator at or below the level of the "split," in a manner of speaking. If we attempt the operation we performed above, and multiple hierarchies exist within the dimension selected (this occurs often in Time dimensions, where fiscal and calendar hierarchies often share the same dimension), the "membership" we are requesting is not precise, and our attempt will end with an error.

Syntax

The .Members operator is appended to the right of the level, hierarchy, or dimension, as in the following example:

[Product].Members

It might also have been appended in the same manner to enumerate the members of the Product Family level within the same Product dimension:

[Product].[Product Family].Members

We will illustrate the use of the .Members operator at various levels in the Practice section that follows.

NOTE: While we have yet to introduce Calculated Members to any real extent within the series, it is important to be aware that Calculated Members will not appear in the result dataset returned by the .Members function. As we will discover later in the Member Functions segment of the series, the .AllMembers function, among other means, exist to include Calculated Members in our result datasets.

Let's practice some examples of the use of the .Members function and explore the results obtained.

Practice

We can reinforce our understanding of how the .Members function operates by constructing expressions that call it into action. We will use the MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) to construct and execute our expressions, and to see the results we obtain.

1.             Start the MDX Sample Application.

2.             Clear the top area (the Query pane) of any queries or remnants that might appear.

3.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.             Select the HR cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown below, complete with the information from the HR cube displaying in the Metadata tree (in the left section of the Metadata pane, between the Query pane at the top of the application window, and the Results pane, at the bottom.).


Illustration 2: The MDX Sample Application Window (Compressed)

We will begin with a simple illustration that involves the .Member operator: Let's say that an information consumer in the Human Resources department wants to know total organizational salaries for 1997 and 1998 (the two years captured in our corporate HR cube - and therefore the full "membership" of the Year level of the Time dimension).

5.             Type the following simple query into the Query pane:

-- MDX04-1:  Tutorial Query No. 1

SELECT

{ [Time].[Year].Members } ON COLUMNS,

{ [Measures].[Org Salary]} ON ROWS

FROM [HR]

6.             Click Query on the top menu, and then select Run.

We see the results below, which appear in the Results pane as soon as Analysis Services fills the cells that it determines to be specified by the query.


Illustration 3: The Query Result Dataset

Note: For an explanation of the comments line, which appears atop the query, together with a general discussion regarding attributes of the basic MDX query structure, see the first article of the series. For a discussion of the operators involved, here and going forward, see the first three articles of the series.

The query delivers the results that we requested.

7.             If it is desirable, save the query by selecting File ` Save As, and call the file something meaningful.

Now let's delve a bit deeper, and undertake an exercise to illustrate the use of the .Members function in varying levels of the same hierarchy.

8.             Type the following query into the Query pane:

-- MDX04-2:  Tutorial Query No. 2

SELECT

   { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

[Store].Members ON ROWS

FROM [Warehouse]   -- Remember to switch to the Warehouse cube!

9.             Click Query on the top menu, and then select Run.

The Results pane appears as partially shown below.


Illustration 4: The Query Results

The result dataset contains many empty members, intermingled with populated members, as we can see by scrolling down. Empty cells occur in MDX statements when data for the intersection of multiple dimensions (in our example, the intersection of the Units Shipped and Units Ordered measures, and the Store dimension) does not exist. To make our points with the .Members operator a bit clearer to see, let's issue instructions in our query to eliminate the empties, by adding the NON EMPTY keyword as follows:

10.         Type in the NON EMPTY keyword just before the following line of the query above:

[Store].Members ON ROWS

The Query pane of the Sample Application appears as shown in Illustration 5.


Illustration 5: The Query Pane with the Added NON EMPTY Keyword

Our use of the NON EMPTY keyword is simply a means of issuing instructions, for the stores in the second axis dimension (rows), which do not have values for the Units Shipped and Units Ordered of the product, to be excluded from the result dataset. The empty tuples are screened out of the result dataset of the MDX query.

11.         Click Query on the top menu, and then select Run.

The Results pane appears as partially shown below.


Illustration 6: The Query Results

Now, to continue with the focus of our lesson, the .Members operator; we can see in the example we have created that the use of .Members in the above query gives us every member of the dimension, including levels and the members for each of those levels. This result might tend to confuse more than help an information consumer, because total numbers would not necessarily agree with a total of the values appearing in the result dataset for either measure. In short, a "dump" of the entire list of hierarchy objects with their associated values might not serve to be useful from an analysis perspective to an uninformed consumer.

Let's explore going to a specific level in the hierarchy for the Store dimension.

12.         Type the following query into the Query pane:

-- MDX04-3:  Tutorial Query No. 3

SELECT

   { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

NON EMPTY [Store].[Store State].Members ON ROWS 

FROM [Warehouse]

We are now using the .Member function to ask for the "membership" of the Store State level in the row axis.

13.         Click Query on the top menu, and then select Run.

The Results pane appears as partially shown below.


Illustration 7: The Query Results

We see that we have now obtained a summary by store state of the two measures--and by store state only--because we made the more specific request for members of the Store State level.

We can easily replicate the effects we have obtained at the Store City and other levels of the Store dimension, as well as in the other dimensions and their levels. Practice the concept with various dimensions and their component levels to get a feel for how the .Members operator works.

As we progress into the Member Functions segment of our series, and the coverage of many of the member functions available in MDX, as well as into even more advanced stages of query building, we will revisit the .Members operator often. Practice with this powerful operator will make its use easy and natural; we will come to value it highly in our more advanced MDX exercises later.

Next in Our Series ...

In this lesson, MDX Members: Introducing Members and Member Functions, we introduced the concept of members, and discussed their significance within MDX. We then launched the first group of articles in the series, Member Functions with an exploration of the powerful .Members operator. As a part of exploring this highly useful operator, we discussed the syntax within which we can best employ it, and illustrated its common use within MDX expressions through the use of practice exercises.

In our next lesson, MDX Member Functions: The "Family" Functions, we will move into a multiple-article segment that focuses upon the member functions one at a time, contrasting the uses and effects of each. In the next article, we will expose the .Parent, .Children and Ancestor() functions, discussing the information they return, together with syntactical points of their use. We will illustrate further how to take advantage of these useful functions by performing practice exercises, and commenting on the result datasets we obtain.

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

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

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