Set Functions: The .AllMembers Function

Friday Dec 7th 2007 by William Pearson
Share:

Business Intelligence Architect Bill Pearson introduces the useful .AllMembers function, reinforcing the basic concepts, as always, with hands-on practice examples.

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:

Prepare MSSQL Server Management Studio to Query Analysis Services

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.



Procedure: Use the .AllMembers Function to Filter a Dataset to Meet a Business Need



Having demonstrated the basic operation of .AllMembers, we are ready to address another requirement to which the client representatives have referred in earlier conversations. To detail the requirement, our colleagues have asked us to address a specific, immediate need, although they hope to be able to extrapolate the concepts we introduce to other, similar needs that continually arise within the organization. The authors / developers have asked that we construct a query that presents all measures (including calculated members / measures) for each of the Product Categories offered by the organization for their current and prior year (2004 and 2003, respectively), presented by Product Category, and subanalyzed by Customer Country. Our colleagues explain that management is attempting to perform analysis upon the Categories, specifically within the context of the “contribution” of each Customer Country toward the totals for each Category value. While management will likely prune the measures presented to a smaller subset going forward, the initial exercise will allow them to see all measures, including calculations, for the purpose of considering each for relevance, before deciding which to eliminate from prospective presentations. The client representatives assure us that, once they understand the concepts, they will seek to extend, or otherwise modify, and parameterize various parts of this (or a similar) query, within reports they will later create using Reporting Services, but that this example will suffice for now.

Because the initial business requirement entails working with all measures (“all members of the Measure dimension,” as it were), we explain that .AllMembers promises to be useful in generating the desired presentation. We confirm our understanding of the foregoing needs, as well as our conclusion that we have happened upon a great opportunity to both assist the client in meeting its immediate needs and to provide examples that leverage the MDX .AllMembers function. We set out to craft a query that relies upon .AllMembers, in conjunction with a couple of other the MDX functions, to meet the business need.

1.  Select File --> New from the main menu, once again.

2.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 5.


Illustration 5: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

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


-- MDX061-2: Basic Use of .ALLMEMBERS Function;  Measure Dimension

SELECT

   CROSSJOIN(

      {[Measures].ALLMEMBERS},

         {[Date].[Calendar Year].[CY 2004]:[Date].[Calendar Year].[CY 2003]})

      ON AXIS (0),

   CROSSJOIN(

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

         [Customer].[Country].[Country].MEMBERS

      ON AXIS (1)

FROM

               [Adventure Works]

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


Illustration 6: Our Second Query in the Query Pane ...

4.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset partially depicted in Illustration 7 appears.


Illustration 7: Results Dataset – .AllMembers Applied to Deliver All Measures (Partial View)

In the returned dataset, we see the juxtaposed Years (2003 and 2004), which we generate via the MDX Range operator (“:”) and all measures within the cube – including calculated members / measures, which we deliver via the .AllMembers function. Moreover, we leverage the .Children and .Members functions to specify a row axis containing Product Categories, which we further subanalyze by Customer Country. We perform the desired juxtapositions within the query via the CrossJoin() function.

Of primary focus within our practice example is our use of the .AllMembers function, in conjunction with these other functions, to return all measures – in effect, “all members of the Measures dimension.” (We can easily verify operation by observing that all measures within the cube appear within the dataset – we can scroll over to see that all measures are present.) In this example, we can also see another characteristic of the behavior of .AllMembers in cases where a dimension contains only a single visible hierarchy: in such cases, the hierarchy can be referenced by the hierarchy name or the dimension name, because the dimension name in such a scenario is resolved to its only visible hierarchy. In our immediate example, Measures.AllMembers is a valid MDX expression because it resolves to the only hierarchy in the Measures dimension.

NOTE: For more detail surrounding the CrossJoin() function, see Basic Set Functions: The CrossJoin() Function. For information on the .Children function, see MDX Member Functions: The "Family" Functions. For an introduction to the .Members function, see my article MDX Members: Introducing Members and Member. Finally, for a discussion of the Range operator, see my article MDX Operators: The Basics. All articles are members of my MDX Essentials series at Database Journal.

5.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX061-002.mdx, and place it in the same location used to store the earlier query

The client developers and report authors express satisfaction with the results, and confirm their understanding of the operation of the .AllMembers function within the context we have presented, among other uses we have discussed in earlier sections. We suggest to our client colleagues that, among numerous possibilities, the Years (“beginning” and “ending,” for that matter) might be parameterized, that we might build in the capability to swap crossjoined members, and that we might add other capabilities within the ultimate reporting dataset query. Suffice it to say that, assuming a thorough knowledge of the various layers of the Microsoft integrated BI solution, one can obtain many powerful capabilities and features, and knowing “where to put the intelligence” within the sometimes multiple choices can mean highly tuned performance and effective solutions for consumers throughout our organizations. For more of my observations on this subject see Multi-Layered Business Solutions ... Require Multi-Layered Architects.

6.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we explored the MDX .AllMembers function, whose general purpose 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.

We examined the syntax involved with .AllMembers, and then undertook a couple of illustrative practice examples of uses for the function, generating queries that capitalized upon its capabilities. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed, as well as extending our discussion to other possible options and uses for the concepts we exposed.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved