MDX Essentials: Set Functions: The MeasureGroupMeasures() Function

Wednesday Nov 7th 2007 by William Pearson
Share:

Join Business Intelligence Architect Bill Pearson as he leads a hands-on introduction to the MeasureGroupMeasures() function. Put MeasureGroupMeasures() to work in your own scripting or reporting environment to exploit a list of all measures in the specified measure group.

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.

Procedure: Use the MeasureGroupMeasures() Function to Generate a Simple Set of Measures in a Results Dataset

Let’s construct a simple query to provide a conceptual “starting point” for illustrating the use of the MeasureGroupMeasures() function. The idea is to generate a very basic dataset that displays each of the measures contained within the one of the larger measure groups, named Sales Summary, which exists within the Adventure Works cube. This initial display, we reason, will show the concepts behind using the MeasureGroupMeasures() 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 a more elaborate business requirement in the procedure that follows it.

1.  Type (or cut and paste) the following “single axis” query into the Query pane:


--MDX060-001 "Starter Query" to Demonstrate 
-- Simple Use of MEASUREGROUPMEASURES()

SELECT

   MEASUREGROUPMEASURES('Sales Summary')ON AXIS(0)

FROM

       [Adventure Works]

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


Illustration 1: Our Initial Query in the Query Pane ...

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


Illustration 2: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset, shown in Illustration 3, appears.


Illustration 3: Results Dataset – Simple “Measures List” Scenario

In the returned dataset, we see that the member measures of the Sales Summary measure group, together with their respective total values, appear as expected. This simple dataset provides a great “beginner” illustration of the output of the MeasureGroupMeasures() function when used within a simple SELECT context.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX060-001, and place it in a meaningful location.

Our developer / author colleagues express satisfaction with the contextual backdrop we have established for introducing the MeasureGroupMeasures() 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 MeasureGroupMeasures() Function to Filter a Dataset to Meet a Business Need

Having demonstrated the basic operation of MeasureGroupMeasures(), we are ready to address a somewhat more sophisticated 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 delivers total Reseller Sales-related measures for which Fiscal Year 2002 measure values exceeded $ 100,000 for more than four months. Our colleagues explain that management is attempting to perform analysis upon a handful of Resellers with whom the organization did business in an earlier year, when far fewer Reseller relationships existed. Management is interested only in a scope of sales-related values above the stated threshold for their immediate information needs, but, as always, the client representatives assure us that, once they understand the concepts, they will seek to parameterize various parts of the query, such as the threshold value, the number of months at that value, and so forth, within reports they will later create using Reporting Services.

Because the business requirement entails working with only measures whose totals exceed the $ 100,000 threshold for more than four months, we explain that the MeasureGroupMeasures() function promises to be useful in support of the necessary filter to isolate the targeted values. 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 MeasureGroupMeasures() function. We set out to craft a query that relies upon MeasureGroupMeasures(), in conjunction with the MDX Filter() function, that meets 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 4.


Illustration 4: 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:


--MDX060-002 Use of MEASUREGROUPMEASURES() to
-- Filter a Dataset for Multiple Criteria

WITH

SET 

	[Fiscal Year 2002 Analysis Months] 

AS 

    DESCENDANTS(

        [Date].[Fiscal].[Fiscal Year].[FY 2002],

            [Date].[Fiscal].[Month])

SELECT

    [Fiscal Year 2002 Analysis Months]   ON AXIS(0),

    NON EMPTY

	    {FILTER(

	        MEASUREGROUPMEASURES('Reseller Sales'),

	            COUNT(

	        FILTER([Fiscal Year 2002 Analysis Months], 

	    MEASURES.CURRENTMEMBER > 100000) 

	)> 4)} ON AXIS(1)

FROM

    [Adventure Works]

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


Illustration 5: 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 depicted in Illustration 6 appears.


Illustration 6: Results Dataset – Filtering Based Upon Multiple Criteria

In the returned dataset, we see the columns we have defined via our named set Fiscal Year 2002 Analysis Months. (We leverage the Descendants() function within our named set definition to specify the desired months - those of Fiscal Year 2002 - as columns within the dataset.) Of primary focus within our practice example is our use of the MeasureGroupMeasures() function in conjunction with the Filter() function to return only measures whose total values exceed the thresholds, both for the values themselves and for the number of months (a “count” of the months) specified by the client. (We can easily verify operation by observing that all measures within the Reseller Sales measure group do not appear within the filtered dataset – we can also lower the dollar threshold to bring in more of the measures to confirm our understanding). As we can see, the Count() and .CurrentMember functions are also employed in helping us to meet the desired business requirement.

NOTE: For more detail surrounding the Filter() function, see Basic Set Functions: The Filter() Function. For information on several of the “relative” functions, of which .CurrentMember is an example, see my article MDX Member Functions: "Relative" Member Functions. For an introduction to the Count() function, see my article Basic Numeric Functions: The Count() Function. Finally, examples of usage of the Descendants() function are presented throughout my MDX Essentials series, of which all of the foregoing articles are members.

5.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX060-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 MeasureGroupMeasures() function within the context we have presented, as well as within other uses we have discussed in earlier sections. We suggest to our client colleagues that, among numerous possibilities, the year might be parameterized, that we might build in the capability to switch from Calendar to Fiscal Year, that the tandem thresholds we specify (measure value, and number of months at that value) might be parameterized, and that we might add myriad 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.

The client representatives inform us that their immediate goals have been met, and that the examples we have shared illustrate the principles of operation behind MeasureGroupMeasures(), as a part of helping them to support the expressed business requirements.

Summary ...

In this article, we explored the MDX MeasureGroupMeasures() function, whose general purpose is to return a list of all measures within a specified measure group. We noted that the MeasureGroupMeasures() function is also useful in limiting scope to the member measures from a specified measure group within an MDX script, and that the function can be leveraged in activities that range from generating simple datasets to supporting sophisticated filtering and scoping operations, among other capabilities.

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