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.
» 
Comment and Contribute
