Procedure: Use the Max() Function to Select
Peak Sales and the Month in Which They Occur
Having
demonstrated the basic operation of Max(), we are ready to address another
requirement that the client representatives describe. 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 commonly arise within the organization. Our
colleagues have asked that we construct a query that presents a slightly more
sophisticated dataset, and uses the Max() function in a way that differs
from our previous example, where we simply retrieved a maximum measure value
for a specified set.
Max(), of course, can be used in
numerous ways that lie beyond the obvious selection of the”largest number” or
“biggest amount” for a set we use as a basis in the function. We often, for
instance, use the function to select the “last” (or “greatest”) month (or other
Date period), as an example where we have conducted activity or maintain a
balance. The business requirement that the client representatives communicate
next contains an element of such a need, as we shall see.
Our
colleagues tell us that they wish to support the capability of Logistics users
to report upon and analyze peak sales of the Resellers, with which Adventure
Works maintains relationships, to market and distribute its products. To
begin, they wish to be able to generate a dataset that presents the highest
monthly sales values for Resellers who have completed sales within the
last Calendar Year, 2003. Moreover, they wish to present,
alongside this “peak sales” value, the period within which the sales
were achieved (which they wish to label “Peak Period” within the
returned dataset), for ease of use for immediate needs, as well as to support
some charts they wish to create in Reporting Services at a later time.
The client representatives make us aware that they wish to screen out Resellers
who have no activity at all over the given Calendar Year, simply to
streamline reports and make them more compact.
We
explain that the Max() function promises to be useful in generating the
desired presentation. 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 as well as to provide examples
that leverage the MDX Max() function. We set out to craft a query that
relies upon Max(), in conjunction with other the MDX functions,
to meet the business need.
The basic Max() function involved will be housed
within a couple of calculated members, which we will call Peak Sales and
Peak Period, respectively. We will then employ the calculated members
within a core query that arranges the information it retrieves in the manner
requested. Our query will be constructed as described in the following
procedure.
1.
Select File
--> New from the main menu.
2.
Select Query
with Current Connection from the cascading menu that appears next, as depicted
in Illustration 5.
Illustration 5: 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:
-- MDX062-2 More Sophisticated Example of MAX() Function in Use
WITH
MEMBER
[Measures].[Peak Sales]
AS
'MAX(DESCENDANTS( [Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month]),
[Measures].[Reseller Sales Amount]
)'
MEMBER
[Measures].[Peak Period]
AS
'IIF(
MAX([Date].[Calendar].[Month].MEMBERS, [Measures].[Reseller Sales Amount]) > 0,
TOPCOUNT(
DESCENDANTS( [Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month]),
1,
[Measures].[Reseller Sales Amount]).ITEM(0).NAME, null
)'
SELECT
{[Measures].[Peak Sales], [Measures].[Peak Period]} ON AXIS(0),
NON EMPTY
{[Reseller].[Reseller Type].[Reseller].MEMBERS}
ON AXIS(1)
FROM
[Adventure Works]
The Query pane appears,
with our input, as shown in Illustration 6.
Illustration 6: Our Second Query in the Query Pane ...
We note that we have taken steps, within the conditional
element of the Peak Period calculated measure, to present a null in
place of the default that would display (the first period, January, of 2003)
were we to simply to allow default behavior. This allows us to screen null
rows out via Non Empty. For those Resellers experiencing at
least one sale within Calendar Year 2003, we derive a Peak Sales
value using the Max() function, specifying the members of the Month
level of the Date dimension (Calendar hierarchy) as the Set
Expression across which the Numeric Expression representing the
Reseller Sales Amount measure is evaluated.
We use the TopCount() function within the creation of
the Peak Period calculated measure, effectively stating that we wish to
retrieve the first, or “topmost” values that occur within the Months (we
use the Descendants() function to specify that the query focus upon the
relevant Month level members of Calendar Year 2003)
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 partially depicted in Illustration
7 appears.
Illustration 7: Results Dataset – Max() Leveraged to
Deliver More Sophistication (Partial View)
In the returned dataset, we see the list of Resellers who
have been evaluated as having sales activity in at least one period of Calendar
Year 2003. Juxtaposed with these names, we observe the two calculated
members we have constructed, Peak Sales (containing the primary use
of the Max() function) and Peak Period (within which we make
referential use to the Max() function as it exists in the first calculated
member – primarily to support conditional logic that nulls the derived Peak
Period date, and which allows it to be filtered out via Non Empty).
The second calculated member also leverages the combination of the .Item()
and .Name functions to generate the month and year format that
represents the Peak Period output in the dataset.
NOTE:
For more detail surrounding the TopCount() function, see Basic
Set Functions: The TopCount() Function, Part I and Basic
Set Functions: The TopCount() Function, Part II . For
information on IIF(), see String
/ Numeric Functions: Introducing the IIF() Function and String /
Numeric Functions: More on the IIF() Function. For exposure to the Descendants() function,
see various articles throughout the MDX
Essentials series.
For information on the .Item()
function, see Basic
Member Functions: The .Item() Function. For an introduction to
the .Name function, see String
Functions: The .Name Function. For examples of the use of Non
Empty, see various articles throughout this series. Finally, for an introduction to the .Members function,
see my article MDX
Members: Introducing Members and Member.
5.
Select File
-> Save MDXQuery2.mdx As ..., name the file MDX062-002.mdx,
and place it in the same location used to store the earlier query.
To
corroborate the operation of the solution we have proposed above, let’s examine
the sales figures concerned for a given reseller over all periods in the year
under examination. From this quick test dataset, we can see that our
calculated members (each leveraging the Max() function) have served us
as expected, both in selection of the appropriate value for Peak Sales
and the appropriate month for Peak Period.
6.
Select File
--> New from the main menu, as we did
earlier.
7.
Select Query
with Current Connection from the cascading menu that appears next, once
again.
A new
tab, with a connection to the Adventure Works cube appears in the Query
pane, as before.
8.
Type (or cut
and paste) the following query into the Query pane:
-- MDX062-3 "Check Query" to Ascertain Solution Effectiveness
SELECT
CROSSJOIN(
{DESCENDANTS( [Date].[Calendar].[Calendar Year].[CY 2003],
[Date].[Calendar].[Month])},
{[Measures].[Reseller Sales Amount]}
)
ON AXIS(0),
{[Reseller].[Reseller Type].[Reseller].[Best o' Bikes]:
[Reseller].[Reseller Type].[Reseller].[Bicycle Exporters]}
ON AXIS(1)
FROM
[Adventure Works]
The Query pane appears,
with our input, as shown in Illustration 8.
Illustration 8: Verifying Our Results with a “Test Data”
Query
We have simply constructed a query to generate the Calendar
Year 2003 monthly values for Reseller Sales Amount for a couple of
the organization’s resellers, Best o’ Bikes and Bicycle Exporters.
We will be able, thereby, to easily identify the “highest value” of each that
represents Peak Sales value among the months. We can then also see, at
a moment’s glance, the month in which these sales took place (and thus confirm
the Peak Period involved). We can then compare the results of our
sample to the values delivered by our two calculated members, Peak Sales
and Peak Period, from the last dataset retrieved by the last query, to
determine accuracy – and thereby verify the effectiveness of our approach.
The Results pane is, as before, populated by Analysis
Services. The dataset depicted in Illustration 9 appears.
Illustration 9: Results Dataset – Confirmation Query
In the returned dataset, we see the values we have discussed
appearing for each month in Calendar Year 2003 for each sample Reseller.
We also note that the values presented by the Peak Sales calculated
member in our solution for each of the two Resellers agrees to what we
can easily see is the largest value within the confirmation dataset, and that
the month within which the “highest month’s sales” took place in the
confirmation dataset agrees to the Peak Period month returned in our
solution. The comparisons appear as shown in composite Illustration 10.
Illustration 10: Results Datasets Composite – Indicating
That Our Solution is Effective
The
Logistics department representatives express satisfaction with the results, and
confirm their understanding of the operation of the Max() function
within the context we have presented, among other uses we have discussed in
earlier sections. We suggest to our client colleagues that, among numerous
possibilities, the Resellers (single, multiple, or ranged, etc.), as
well as various components of the Date dimension (Calendar or Fiscal
Hierarchy, Year, Semesters, Quarters, and so
forth), and even the “number of highest values” (say, for example, “top two” or
“top five”, etc.), and other components of our query might be parameterized, within
their Reporting Services environment. Moreover, we emphasize, we might
add other capabilities within the ultimate reporting dataset query.
Suffice
it to say that, assuming an “above ordinary” 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.
10.
Experiment
with the “test query,” as desired, to examine and compare the values for other
Resellers to those delivered by the previous query via the Max()
function.
11.
Select File
-> Save MDXQuery3.mdx As ..., name the file MDX062-003.mdx,
and place it in the same location used to store the earlier query, if and when
desired.
12.
Select File
-> Exit to leave the SQL Server Management Studio, when ready.
Summary ...
In
this article, we introduced and explored the MDX Max() function, one of
several aggregate functions available within the MDX toolset, whose general
purpose is to deliver a maximum value
of a numeric expression that is evaluated over a set. We emphasized that Max() can
be leveraged in a wide range of activities, from the “generation of maximums”
from simple sets of dimensional members to multidimensional juxtapositions we
can compose to deliver even more sophisticated results. Moreover, we learned
that Max() can serve as an excellent tool to support sophisticated
conditional logic, as well as other calculations, to deliver exactly the
analysis and reporting presentations required by our clients and employers.
We
examined the syntax involved with Max(), 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
