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

February 24, 2003

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.