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 article, we
will introduce a “staple” MDX numeric function, the Max() function. Max()
is one of several aggregate functions with which we can choose to perform
aggregations upon a set of values. (We introduce other aggregate functions
within individual articles of the MDX Essentials series.) Max(),
like the rest of these functions, aggregates a set of(one or more) measure
values associated with a set of dimension members. Simple examples
might include the selection of the maximum revenue from a set of products or
the maximum monthly personnel headcount over a range of months.
As
most of us are already aware, Max() can be leveraged throughout a wide
range of activities, from the generation of maximums from simple sets of
dimensional members to the composition of multidimensional juxtapositions, for more
sophisticated results. As is the case with many MDX functions, Max()
can serve as an excellent tool to support sophisticated conditional logic, as
well as other calculations, and to deliver exactly the analysis and reporting presentations
required by our clients and employers. 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 Max() Function
Introduction
According to the Analysis Services
Books Online, the Max() function “returns the maximum value of a numeric expression
that is evaluated over a set.” Max() has
myriad applications, some obvious and intuitive to even the most inexperienced
MDX users, and others that offer sophisticated solutions to more elaborate
business requirements, such as the selection of transaction or balance values
as of the “most recent date” within our cubes. The function can be leveraged within
queries to create datasets, in reporting applications such as MSSQL Server
Reporting Services, for the support of presentations of simple maximums,
for the support of intelligent default dates within our report parameters, and
a host of other creative and useful effects. The Max() function
provides an intuitive option anytime we need to present, in a returned dataset,
the largest value(s) for a set, based upon a numeric expression and a set we
supply. As is the case with most MDX functions, combining Max() with other functions allows us to further
extend its power, as we shall see in the practice exercises that follow.
We will examine the syntax for the Max()
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 learn in the Discussion
and Syntax sections, and allow us to get some hands-on exposure
in creating expressions that employ the Max() function.
Discussion
To restate our initial explanation of its operation, the Max()
function returns the maximum value of a numeric expression we supply, for
the members of a set which we also specify. Max() can be used for a
great deal more than simple “maximum value” retrieval, as we have intimated.
When we couple it with other functions or employ it within MDX scripts, among
other applications, we can leverage Max() to support a wide range of
analysis and reporting utility.
Let’s look at some syntax illustrations to further clarify
the operation of Max().
Syntax
Syntactically, we employ the Max() function by
specifying a Set Expression (a valid MDX expression that returns the set
over which we are attempting to return an associated “maximum” value), followed
by a Numeric Expression (a valid numeric expression that is typically an
MDX expression of cell coordinates that returns a number) within parentheses to
the immediate right of the function. The function takes the Set Expression and
Numeric Expression thus appended as its arguments (the two expressions
are separated by a comma), and evaluates the Numeric Expression across
the set. The maximum value from that evaluation is returned. (If a Numeric
Expression is not specified, the set specified by the Set
Expression is evaluated in the current context of the members of the set,
and the maximum value for the evaluation is returned.) Analysis Services
ignores nulls when calculating the maximum value within a set of
numbers. Because Max() returns a numeric value, we use it most
commonly within the construction of calculated members.
The general syntax is shown in the following string:
Max(Set_Expression, Numeric_Expression)
Putting
Max() to work is straightforward. When using the function to return
the maximum value(s) of the Numeric Expression we have provided as
evaluated over the set we have specified, we simply supply the required Set
and Numeric Expressions within the parentheses to the right of the Max
keyword. As an example, say we create, within a query executed against the
sample Adventure Works cube, a calculated member which we might
name “Top Orders,” (that is, say, for the “highest value for the Internet
Order Count measure”) containing the following pseudo code:
MAX([Date].[Calendar Year].[CY 2001]:[Date].[Calendar Year].[CY 2004],
[Measures].[Internet Order Count] )
Moreover,
say that we select the set of the same Date range dimensional members
and the calculated member within our column axis, and the Product Category
members within our row axis, we might expect to retrieve results similar to
those depicted in Illustration 1.
Illustration 1: Example Returned Data: Max() Function
Employed in Calculated Member
It is easy to see, within the
dataset returned above, that the maximum value, based upon the members of the
dimensional date range we have provided, is returned, as the calculated member
value presents the highest value within each of the rows retrieved. Because of
the ease with which we can employ Max(), and because of the flexibility
with which we can exploit it to meet various business needs, the function
becomes a popular member of our analysis and reporting toolsets. We will
practice some uses of the Max() function in the section that follows.
» 