Set Functions: The .AllMembers Function

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 introduce a basic, but highly useful, set function in the
MDX toolset, the .AllMembers function. The general purpose of .AllMembers
is to return a set composed of all members within a specified
dimensional level or hierarchy. The set returned includes all calculated
members
contained within the specified level or hierarchy.

.AllMembers
can be leveraged
in a wide range of activities, from the generation of simple lists to the
support of sophisticated conditional and other calculations and presentations.
We will introduce the function, commenting upon its operation and touching upon
creative 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 .AllMembers Function

Introduction

According to the Analysis Services
Books Online
, the .AllMembers function “evaluates
either a hierarchy or a level expression and returns a set that contains all
members of the specified hierarchy or level, which includes all calculated
members in the hierarchy or level.” .AllMembers
has numerous applications. For
example, the function can be leveraged within queries to create datasets, in
reporting applications such as MSSQL Server Reporting Services, for the
support of picklists within the reports, for the support of axes within various
end presentations, and so forth. The .AllMembers
function provides an intuitive
option anytime we need to present, in a returned dataset, all members
including calculated members – that belong to a given hierarchy or
level within a dimensional structure. As is the case with most MDX
functions, combining .AllMembers
with other functions allows us to further extend its power, as we shall see in
the practice exercises that follow.

We will examine the syntax for the .AllMembers
function after a brief discussion in the next section. We will then explore,
from the straightforward context of MDX queries, and within practice examples
constructed to support hypothetical business needs, some of the uses it offers
the knowledgeable user. This will allow us to activate what we explore in the Discussion
and Syntax sections, where we will get some hands-on exposure in
creating expressions that employ the .AllMembers function.

Discussion

To restate our initial explanation of its operation, the .AllMembers
function returns the set of members, including calculated members,
belonging to the hierarchy or level specified by a valid MDX
expression we supply. .AllMembers can be used for a great deal more than
simple list retrieval, as we have intimated. When coupled with other functions
or used within MDX scripts, among other applications, we can leverage .AllMembers
to support a wide range of analysis and reporting utility.

Let’s look at some syntax illustrations to further clarify
the operation of .AllMembers.

Syntax

Syntactically, in using the .AllMembers
function to return a set of members, the hierarchy or level
upon which we seek to apply the function is specified to the left of the AllMembers
keyword (and delimited by a period). The function takes the hierarchy
or level expression (a valid MDX expression that returns a hierarchy
or level, respectively) to which it is appended as its argument, and
returns a set representing the members (including calculated
members
) belonging to the hierarchy or level
specified. The general syntax for the application of .AllMembers to a hierarchy
or level appears, respectively, in the following two strings:

<<Hierarchy_Expression>>.AllMembers
<<Level_Expression>>.AllMembers

Putting .AllMembers to work is
straightforward. When using the function to return the set of members
/ calculated members, belonging to the hierarchy or level
upon which we intend it to work, we simply append the function to the right of
the targeted hierarchy or level. As an example, within a query
executed against the sample Adventure Works cube, specifying the Sales
Amount
measure in the column axis, and the Sales Channel hierarchy
(within the dimension of the same name) in the row axis, and applying the .AllMembers
function to the Sales Channel hierarchy, as shown in the following pseudo
code:

 [Sales Channel].[Sales Channel].ALLMEMBERS

returns the level members of
the Sales Channel hierarchy (including the All level), juxtaposed
against the associated Sales Amount values, as depicted in Illustration
1
.



Illustration 1: Members of the Sales Channel Hierarchy,
Returned by the .AllMembers Function

Because of the ease with which
we can employ .AllMembers in returning complete member sets for hierarchies
or levels, the function becomes a popular member of our analysis and reporting
toolsets. (Reporting Services generates .AllMembers expressions
throughout the code it generates, in many cases where it builds crossjoins and
the like, based upon selections made by the author in the graphical interface
of the MDX query editor, as many of us have already noticed).

We will practice some uses of the .AllMembers function
in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have
covered, we will use the .AllMembers function within queries that
illustrate its operation. The intent is to demonstrate the use of .AllMembers
in a straightforward, memorable manner that efficiently illustrates its
operation.

We will turn to the SQL Server Management Studio as a
platform from which to construct and execute the MDX we examine, and to view
the results datasets we obtain. If you do not know how to access the SQL
Server Management Studio
in preparation for using it to query an Analysis
Services
cube (we will be using the sample Adventure Works cube in
the Adventure Works DW Analysis Services database), please
perform the steps of the following procedure, located in the References
section of my articles index:

This procedure will take us
through opening a new Query pane, upon which we will create our first
query within the section that follows.

Procedure: Satisfy Business Requirements with MDX

As a basis for our practice example, we will assume that we
have received a request for assistance from representatives of our client, the Adventure
Works
organization. As we have noted in other articles of the series, the
Reporting department, a group of client-facing authors and developers, often
requests assistance with designing queries to support organizational analysis
and reporting efforts. As a part of our relationship with Adventure Works,
as well as with other clients, we provide on-site staff augmentation for
business requirements gathering and training, as well as combined development
workshops / “train the trainer” events.

In a brief discussion with members of the Reporting
department, we learn that a need has arisen to craft MDX queries for some new
analysis and reporting requirements. First, several requirements have been
identified to generate datasets, from the Adventure Works cube, to
support OLAP reports that management has requested. The client has implemented
the integrated Microsoft BI solution, and, in addition to using Analysis
Services
as an OLAP data source, they use Reporting Services as an
enterprise reporting solution. The MDX we explore together, we are told, will
thus be adapted for ultimate use within Reporting Services, in multiple
parameterized reports.

The requests relayed by the client representatives evidence
a need to present multidimensional data in a manner that we think might best be
served with the .AllMembers function. Once our colleagues provide an
overview of the business requirements, and we conclude that .AllMembers is
likely to be a key component of the option we offer, we provide the details
about the function and its use, much as we have done in the earlier sections of
this article. We convince the authors that they might best become familiar
with the .AllMembers function by examining an introductory example,
where we employ the function to generate a straightforward, all-inclusive list of
members
that are contained within one of the cube’s dimensional levels,
to be followed by an example whereby we perform the same operation to yield the
cube’s measures.

Procedure: Use the .AllMembers Function to
Generate a Simple Set of Members in a Results Dataset

Let’s construct a simple query to provide a conceptual
“starting point” for illustrating the use of the .AllMembers function.
The idea is to generate a basic dataset that displays each of the members contained
within the Calendar Year level of the Date dimension (Calendar
hierarchy), which exists within the Adventure Works cube. This initial
display, we reason, will show the concepts behind using the .AllMembers function
and, we hope, introduce some of the ways we can employ it effectively. Once we
have accomplished our immediate goal in this section, we will further evolve
these concepts in meeting another business requirement in the procedure that
follows it.

1. 
Type (or cut
and paste) the following query into the Query pane:


— MDX061-1: Basic Use of .ALLMEMBERS Function

SELECT

CROSSJOIN({[Date].[Calendar Year].ALLMEMBERS},

{[Measures].[Internet Sales Amount]})

ON AXIS (0),

NON EMPTY

{[Product].[Product Categories].CHILDREN}

ON AXIS (1)

FROM

[Adventure Works]

The Query pane appears,
with our input, as shown in Illustration 2.



Illustration 2: Our Initial Query in the Query Pane …

The above query sets the stage for our discussions of some
of the uses of .AllMembers, and certainly accomplishes the basic
objective of illustrating, in the simplest manner, how it works. The idea is
to generate a dataset to activate the concepts in the minds of our client
colleagues.

2. 
Execute the
query by clicking the Execute button in the toolbar, as depicted in Illustration
3
.



Illustration 3: Click Execute to Run the Query…

The Results pane is populated by Analysis
Services
, and the dataset, shown in Illustration 4,
appears.



Illustration 4: Results Dataset – Simple “Members List”
Scenario

In the returned dataset, we see all members of the Calendar
Year
level of the Date dimension (Calendar hierarchy).
We have juxtaposed all of the Years (generating them with the .AllMembers
function) contained within the cube with the associated Internet Sales
Amount
values for each of the non-null Product Categories that Adventure
Works
offers. This simple dataset provides a great “beginner” illustration
of the output of .AllMembers, when used within a simple SELECT
context.

3. 
Select File
> Save MDXQuery1.mdx As …, name the file MDX061-001,
and place it in a meaningful location.

Our
developer / author colleagues express satisfaction with the contextual backdrop
we have established for introducing the .AllMembers function. We will employ
the function again in our next steps, to a large degree to expand upon its use
in the first example.

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