MDX Essentials: Set Functions: The MeasureGroupMeasures() 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 expose another useful function in the MDX toolset, the MeasureGroupMeasures()
function. The general purpose of the MeasureGroupMeasures() function
is to return a list of all measures within a specified measure group.
The MeasureGroupMeasures() function is useful in limiting scope to the
member measures from a specified measure group within our
queries, MDX scripts, and elsewhere.

MeasureGroupMeasures()
can be leveraged
in activities that range from generating simple lists to supporting
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 MeasureGroupMeasures() Function

Introduction

According to the Analysis Services
Books Online
, the MeasureGroupMeasures()
function “returns a set of measures that belongs to the
specified measure group.” MeasureGroupMeasures()
has numerous applications. For
example, the function can be leveraged within queries of various types, used to
define scopes within MDX scripts, or employed to specify the Target
property within Analysis Services Actions. As is the case with most MDX
functions, combining it with other
functions allows us to further extend its power.

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

Discussion

To restate our initial explanation of its operation, the MeasureGroupMeasures()
function, returns the set of measures belonging to the measure
group
specified by a string expression we supply. MeasureGroupMeasures()
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 MeasureGroupMeasures() to support a
wide range of analysis and reporting utility.

Let’s look at some syntax illustrations to further clarify
the operation of MeasureGroupMeasures().

Syntax

Syntactically, in using the MeasureGroupMeasures()
function to return a set of measures, the measure group upon
which we seek to apply the function is specified within the parentheses to the right
of the MeasureGroupMeasures keyword. The function takes the string
expression
we supply as its argument, and returns a list of the measures
contained within the specified measures group. The general syntax is
shown in the following string:

MeasureGroupMeasures(String_Expression)

Putting MeasureGroupMeasures() to
work is straightforward. When using the function to return the list of measures
belonging to the measure group with which it works, we simply specify,
via a string expression within the parentheses of the function, the measure
group
for which we seek to retrieve the measures list. As an
example, within a query executed against the sample Adventure Works
cube, for a measure group named Sales Summary, the following
pseudo-expression:

MeasureGroupMeasures(‘Sales
Summary’)

returns the measures,
together with their values, contained within the Sales Summary measure group
of the cube. Based upon the very nature of the set of measures that
MeasureGroupMeasures() returns, the function lends itself to the role of
limiting data returned via the MDX Filter() function (as we shall see
within our practice section), among others. It is also easy, for the same
reason, to see why it is useful as a scoping mechanism within MDX scripts.

NOTE: For detailed information about the Filter()
function, see my article Basic
Set Functions: The Filter() Function
,
within the Database Journal MDX Essentials series.

We will practice some uses of the MeasureGroupMeasures() 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 MeasureGroupMeasures() function within queries that
illustrate its operation. The intent is to demonstrate the use of MeasureGroupMeasures()
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

For purposes of 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 filter multidimensional data in a manner that we think might best be
served with the MeasureGroupMeasures() function. Once our colleagues
provide an overview of the business requirements, and we conclude that MeasureGroupMeasures()
is likely to be a key component of the option we offer, we provide the
details about the function and its use just as we have done in the earlier
sections of this article. We convince the authors that they might best become
familiar with the MeasureGroupMeasures() function by examining an
introductory example, where we employ the function to generate a
straightforward list of measures that are contained within one of the
cube’s measure groups. Once the
basics are understood, we then propose, we will explore the use of MeasureGroupMeasures()
to accommodate a more challenging requirement that the client has proposed.

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