Set Functions: The AddCalculatedMembers() Function - Page 2

March 3, 2008

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.