Set Functions: The AddCalculatedMembers() Function

Business Intelligence Architect Bill Pearson continues his examination of MDX functions, introducing AddCalculatedMembers(). In this article, we expose the 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 AddCalculatedMembers(), a basic, but highly useful, set function in the MDX toolset. The general purpose of AddCalculatedMembers() is to retrieve the base members of a specified set, together with all sibling calculated members.

AddCalculatedMembers() 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 AddCalculatedMembers() Function

Introduction

According to the Analysis Services Books Online, the AddCalculatedMembers() function “returns a set generated by adding calculated members to a specified set.” AddCalculatedMembers() 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 AddCalculatedMembers() function provides an intuitive option anytime we need to present, in a returned dataset, all members – including calculated members – that belong to a specified set. (When we employ a metadata-based MDX function to specify a set for retrieval, the default behavior we would expect would be that only base members, and specifically not calculated members, of the set are returned.

As we have noted to have been the case with many individual MDX functions we have examined within this series, combining AddCalculatedMembers() with other functions allows us to further extend its power. We will get a taste of this synergy in the practice exercises that follow.

We will examine the syntax for the AddCalculatedMembers() 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 AddCalculatedMembers() function.

Discussion

To restate our initial explanation of its operation, the AddCalculatedMembers() function examines a set expression that we specify and returns its base members, along with sibling calculated members, contained within the scope of that set expression. AddCalculatedMembers() 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 AddCalculatedMembers() to support a wide range of analysis and reporting utility.

Let’s discuss syntax to further clarify the operation of AddCalculatedMembers().

Syntax

Syntactically, in using the AddCalculatedMembers() function to return a set of members (including sibling calculated members), the set expression upon which we seek to apply the function is specified within the parentheses to the right of the AddCalculatedMembers keyword. The function takes the set expression (a valid MDX expression that returns a set) enclosed within the parentheses, and returns a set representing both the base members and the calculated members contained within the scope of the set expression.

The general syntax for the application of AddCalculatedMembers() appears in the following string:

 AddCalculatedMembers( <<Set_Expression>> )

Putting AddCalculatedMembers() to work is straightforward. When using the function to return the base, and sibling calculated, members contained within the set expression we have provided, we simply supply the required set expression within the parentheses to the right of the AddCalculatedMembers keyword. As an example, say we specify, within a query executed against the sample Adventure Works cube, a column axis containing the Clothing Product Category, with a row axis such as the following:

 ADDCALCULATEDMEMBERS({[Measures].[Internet Sales Amount]}) 
  ON AXIS (1) 

Moreover, say that we add a WHERE clause to filter the retrieved data set to Calendar Year 2004. Depending upon the calculated members we have defined within our cube (we might have added calculated members beyond those that appear in the pristine sample cube), we would expect to retrieve results similar to those depicted in Illustration 1.

We can see, within the dataset returned above, that, in addition to the single base measure, [Measures].[Internet Sales], contained within the set we have specified above, we also see the calculated members appear that lie within the scope of the set. We can verify that the appropriate calculated members are included by opening the Adventure Works cube within the Cube Designer, going to the Calculations tab, and examining the calculated members that appear, as partially shown in Illustration 2.

(Partial View)

Because all calculated members that appear within the retrieved data set share the same parent, [Measures], they are siblings of the base member, and are thus selected through our use of AddCalculatedMembers().

Because of the relative ease with which we can employ AddCalculatedMembers(), and because of the flexibility with which we can exploit it to meet various business needs (particularly those meeting metadata requirements), the function becomes a popular member of our analysis and reporting toolsets. We will practice some uses of the AddCalculatedMembers() 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 AddCalculatedMembers() function within queries that illustrate its operation. The intent is to demonstrate the use of AddCalculatedMembers() 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 can 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 AddCalculatedMembers() function. Once our colleagues provide an overview of the business requirements, and we conclude that AddCalculatedMembers() 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 AddCalculatedMembers() function by examining an introductory example, where we employ the function to generate a straightforward, all-inclusive list of members (both base and calculated) that are contained within the scope of a specified set expression, to be followed by an example whereby we perform the same operation to yield the calculated members only.

Procedure: Use the AddCalculatedMembers() Function to Generate a Simple Set of Base and Calculated Members in a Results Dataset

Let’s construct a simple query to provide a conceptual “starting point” for illustrating the use of the AddCalculatedMembers() function. The idea is to generate a basic data set that displays a single base member that we request, along with all calculated members that share the same parent, [Measures]. As our primary illustration, the client representatives tell us that they would like to see the base member Internet Sales Amount, alongside all calculated members whose parent is [Measures] (in effect, all calculated members within their cube).

Our client colleagues present the following specifics for this initial illustration: they wish to design and build a query that presents Internet Sales Amount, alongside all calculated measures, for each of the Customer Countries purchasing Adventure Works products in operating Calendar Years 2003 and 2004. They tell us that they want Internet Sales Amount and the calculated members to appear as columns and the Customer Countries to appear as rows. Moreover, they wish to break out the values for each of the two Calendar Years, affording consumers the capability to easily compare, one above the other, a given Customer’s values for each year. In effect, they wish to see Internet Sales Amount and all calculated members presented by Customer Country and subanalyzed by Calendar Year (for each of 2003 and 2004).

While management will likely prune the set presented to a smaller subset going forward, the initial exercise will allow our colleagues to see all calculated members alongside the single base member of interest, Internet Sales Amount, 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.

This initial display, we reason, will show the concepts behind using the AddCalculatedMembers() 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.

-- MDX064-1: Basic Use of ADDCALCULATEDMEMBERS() Function
SELECT
    ADDCALCULATEDMEMBERS({[Measures].[Internet Sales Amount]})
       ON AXIS (0),
    NON EMPTY
        CROSSJOIN(
           {[Customer].[Customer Geography].[Country].MEMBERS},
               {[Date].[Calendar Year].[CY 2003]:[Date].[Calendar Year].[CY 2004]}
           )
       ON AXIS (1)
FROM
    [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 3.

The above query sets the stage for our discussions of some of the uses of AddCalculatedMembers(), 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.

The Results pane is populated by Analysis Services, and a dataset similar to that partially depicted in Illustration 5, appears.

In the returned dataset, we see all members of the Country level of the Customer dimension (Customer Geography hierarchy). We have juxtaposed the crossjoin of each Country with each of Calendar Years 2003 and 2004 (generating them with the Range (“:”) operator) with the associated Internet Sales Amount base member, and all sibling calculated member, values. This simple dataset provides a great “beginner” illustration of the output of AddCalculatedMembers(), when used within a simple SELECT context.

Our developer / author colleagues express satisfaction with the contextual backdrop we have established for introducing the AddCalculatedMembers() 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 AddCalculatedMembers() Function within a Query that Retrieves Calculated Members Only

Having demonstrated the basic operation of AddCalculatedMembers(), we are ready to address another requirement to which the client representatives have referred in our ongoing 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 only calculated members (the current cube contains calculated members with [Measures] as parent only). The idea is simply to be able to generate a dataset from which a list can be maintained for “inventory purposes.

We propose that the first query we have constructed together will serve as a great basis for such a list – we need only pivot the column and row axes to make a neat, single column list of calculated members that will lend itself to cutting and pasting elsewhere (such as to Excel) and so forth. Other uses come to mind for such a list, we explain, and this will provide a convenient means of building upon a structure we have already put together.

Because the initial business requirement entailed working with a specified set (which we crafted to comprise a single base member among “all members of the Measure dimension”), AddCalculatedMembers() suited the need on its own. We explain that we now need to present only calculated members, so we must take a step to eliminate the base member or members that AddCalculatedMembers() generates, but that AddCalculatedMembers() still promises to be useful in generating the desired presentation. We confirm our understanding of the foregoing needs, concluding that we have happened upon a great opportunity to both assist the client in meeting its immediate need and to provide an example that leverages the MDX AddCalculatedMembers() function from a slightly different perspective than that of our simple, initial example. We set out to craft a query that relies upon AddCalculatedMembers(), in conjunction with another basic MDX “metadata” function, to meet the business need.

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.

-- MDX064-2: Use of ADDCALCULATEDMEMBERS() Function within a Query to Return 
--   List of Calculated Members Only
SELECT
   CROSSJOIN
      (
         {[Customer].[Customer Geography].[Country].MEMBERS},
            {[Date].[Calendar Year].[CY 2003]:[Date].[Calendar Year].[CY 2004]}
       )
    ON AXIS(0),

    ADDCALCULATEDMEMBERS
        (
            {[Measures].MEMBERS}
        )
            - [Measures].MEMBERS
   ON AXIS (1)
FROM
          [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 7.

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

In the returned dataset, we see the crossjoined Customer Countries and Calendar Years (2003 and 2004), as in our initial query, except that they now reside in the columns of the dataset. All calculated members now populate the rows axis. The calculated members are delivered via the AddCalculatedMembers() function, which this time specifies [Measures].Members as its set expression (which would result in the retrieval of all base members of the Measures dimension, as well as all sibling calculated members, being retrieved), in combination with subtraction of the [Measures].Members component of the output of AddCalculatedMembers(). The effect, of course, is to generate the full set of base and calculated members, and then to back out the base members, leaving only the calculated members.

Our client colleagues express satisfaction with the results, and confirm their understanding of the operation of the AddCalculatedMembers() function within the context we have presented, among other details we have discussed in earlier sections. We suggest to our client colleagues that, among numerous possibilities, the specified set might be parameterized (which might be useful assuming scenarios where calculated members exist in cube dimensions other than Measures); that we might build in the capability to swap crossjoined members; and that we might add other capabilities within more evolved reporting dataset queries.

Summary ...

In this article, we explored the MDX AddCalculatedMembers() function. The general purpose of AddCalculatedMembers() is to evaluate a specified set expression and return the base members, along with sibling calculated members, contained within the scope of that set expression.

We examined the syntax involved with AddCalculatedMembers(), 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.