About the Series …
This article is a member of the series, MDX Essentials.
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 for getting the most out of the lessons
included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.
Note: Current updates are assumed for MSSQL
Server, MSSQL Server Analysis Services, and the related Books
Online and Samples.
In this lesson, we will examine MEMBER_KEY, an intrinsic
member property. MEMBER_KEY. As we noted in Intrinsic Member
Properties: The MEMBER_CAPTION Property, the intrinsic member properties supported by SQL
Server 2005 Analysis Services are of two types: context sensitive
member properties and non-context sensitive member properties. MEMBER_KEY
belongs to the second group of properties. As
a general group, intrinsic member properties
provide additional information that can be used by applications to enhance the
user’s experience. Support for the non-context sensitive member properties is
the same for all members, regardless of individual context.
of the MEMBER_KEY property is to support the return of the member key,
in the original data type, for the member with which it is associated. MEMBER_KEY can be useful in a host of
different applications, particularly in scenarios where backward compatibility
is a consideration; and as I have noted to be the case for other functions and
properties within the MDX Essentials series, MEMBER_KEY allows us to exercise a great deal
of presentation “sleight of hand,” in working with MDX in Analysis Services,
as well as within Reporting Services and various other reporting
applications that can access an Analysis Services cube.
property can be leveraged in activities that range from generating simple
lists to supporting sophisticated presentations. It is a particularly
effective tool when we need to provide parameter picklist support (where
it can serve multiple purposes, such as an index) and the like, as we shall
see. We will introduce the function, commenting upon its operation and
touching upon examples of effects that we can employ it to deliver. As a part
of our discussion, we will:
- Examine the syntax surrounding the function;
Undertake illustrative examples of the uses of the function in
Briefly discuss the results datasets we obtain in the practice
The MEMBER_KEY Property
to the Analysis Services Books Online, the MEMBER_KEY property specifies
“…The value of the member key in the original data type. MEMBER_KEY is for
backward-compatibility.” The Books Online further state that the “…
MEMBER_KEY has the same value as KEY0 for non-composite keys, and MEMBER_KEY
property is null for composite keys.”
MEMBER_KEY has many applications, including the rather obvious
uses with Analysis Services members that are included in the definition, as
well as its pairing with other MDX functions to leverage its power even
further. As we have seen is the case with the .Name function (among
others), as well as the MEMBER_CAPTION property, in other articles of
this series, MEMBER_KEY can also be synergistically combined with the .CurrentMember function; we will
see an example of this combination within the practice exercises that follow.
We will examine the syntax involved in
leveraging the MEMBER_KEY property after our customary overview in the Discussion
section that follows. After that, we will conduct a practice example, within a
scenario constructed to support a hypothetical business need that illustrates
uses for the property. This will afford us an opportunity to explore some of
the presentation options that MEMBER_KEY can offer the knowledgeable
user. Hands-on practice with MEMBER_KEY, where we will create
expressions that leverage the function, will help us to activate what we learn
in the Discussion and Syntax sections that follow.
To restate our initial explanation of its operation, the MEMBER_KEY
property, when acting upon a member, returns the member key
of the object to which it is appended with the period (“.”) delimiter. The
value returned is null for composite keys, and equates to a “.KEY0” use
of the KEY(x) property (which, as we learn within the respective article
within this series, returns the zero-based ordinal of the key) for non-composite
keys. MEMBER_KEY can be used for a great deal more than the support of
simple lists of unique object names, as we have intimated. When we couple it with
other functions, we can leverage MEMBER_KEY to deliver a wide range of
analysis and reporting utility.
As in so many cases with the Microsoft integrated business
intelligence solution, consisting of MSSQL Server, Analysis Services
and Reporting Services, this property, properly exploited within the Analysis
Services layer, can be extended to support capabilities and attributes in
the Reporting Services layer. Knowing “where to put the intelligence”
among the various layers is critical to optimization, in many cases. For more
of my observations on this subject, see Multi-Layered
Business Intelligence Solutions … Require Multi-Layered Architects.
The MEMBER_KEY property returns, as we have noted, a
typed key associated with the member. If we are using MEMBER_KEY in
conjunction with a member with which a composite key is associated, a null
value is returned.
Let’s look at a syntax illustration to further clarify the
operation of MEMBER_KEY.
Syntactically, anytime we
employ the MEMBER_KEY property to return the associated key, the member
for which we seek to return the key is specified to the left of MEMBER_KEY.
The property takes the object to which it is appended as its argument,
and returns, in the original data type, the key of the object specified.
The general syntax is shown in the following string:
In short, putting MEMBER_KEY to
work couldn’t be easier. When specifying the property to return the Key
of a member or members, we simply append it to the right of the member(s)
As is typically the case with
MDX functions, operators and properties, the MEMBER_KEY property can
often be best leveraged by combining it with other functions, operators or
properties, particularly “relative” functions, to generate lists of names, and
so forth, as we shall see in short order.
information on several of the “relative” functions, see my article MDX
Member Functions: "Relative" Member Functions,
within the Database Journal MDX Essentials series.
We will practice some uses of the MEMBER_KEY property
in the section that follows.