MDX Essentials: MDX Members: Introducing Members and Member - Page 2

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:

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.