Intrinsic Member Properties: The MEMBER_KEY Property

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.

Overview

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.

The purpose
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.

The MEMBER_KEY
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
    practice exercises;
  • Briefly discuss the results datasets we obtain in the practice
    examples.

The MEMBER_KEY Property

Introduction

According
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.

Discussion

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.

Syntax

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:

<<Member_Expression>>.MEMBER_KEY

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)
under consideration.

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.

NOTE: For
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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles