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.
Lets 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.
Illustration 1: Example Returned Data:
AddCalculatedMember() Function Employed in Query
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.
Illustration 2: Calculated Members within the Scope of Our
Specified Set Expression
(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
Lets 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 Customers 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.
1.
Type (or cut
and paste) the following query into the Query pane:
-- 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.
Illustration 3: Our Initial Query in the Query Pane ...
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.
2.
Execute the
query by clicking the Execute button in the toolbar, as shown in Illustration
4.
Illustration 4: Click Execute to Run the Query...
The Results pane is populated by Analysis
Services, and a dataset similar to that partially depicted in
Illustration 5, appears.
Illustration 5: Results Dataset Single Member with
Sibling Calculated Members Scenario (Partial View)
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.
1.
Select File
--> New from the main menu, once again.
2.
Select Query
with Current Connection from the cascading menu that appears next, as shown
in Illustration 6.
Illustration 6: 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:
-- 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.
Illustration 7: Our Second Query in the Query Pane ...
4.
Execute the
query by clicking the Execute button on the toolbar.
The Results pane is, once again, populated by
Analysis Services. This time, the dataset partially shown in Illustration
8 appears.
Illustration 8: Results Dataset AddCalculatedMembers()
Applied to Deliver All Calculated Measures (Partial View)
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.
5.
Select File
-> Save MDXQuery2.mdx As ..., name the file MDX064-002.mdx,
and place it in the same location used to store the earlier query
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.
6.
Select File
-> Exit to leave the SQL Server Management Studio, when ready.
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.