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 examine the BottomCount() function,
whose greatest utility lies in its capability to enable us to isolate worst /
lowest performers from among numbers (even hundreds, thousands or more) of
fellow members. This ranking capability is critical in data analysis and
decision support scenarios: In many business situations, we seek to report upon
“worst” (or simply “lowest / least”) performers for various reasons. BottomCount()
facilitates our doing so, allowing us to sort on a numeric value expression
that we can provide. We can tell the function how many “bottom” members
we wish to retrieve (say, the “bottom ten,” or the “bottom twenty”), for a
“custom-fit” approach, that matches our needs precisely.
In
this article, we will introduce and overview the BottomCount() function.
We will first comment upon the general operation of BottomCount(), and
then we will:
-
Examine the syntax surrounding the function;
-
Undertake illustrative examples of the rudimentary uses of the
function in practice exercises;
-
Briefly discuss the results datasets we obtain in the practice
examples.
The BottomCount() Function
Introduction
According
to the Analysis Services Books Online, the BottomCount() function
“sorts a set in ascending order, and returns
the specified number of tuples in the specified set with the lowest values.” The
BottomCount() function stands out as an excellent general example of the
potential power of MDX. We specify three parameters, a set expression,
a count, and a numeric expression (typically an MDX expression of
cell coordinates that return a number), and BottomCount() returns the
number of bottom performers (or “bottom” / “least”, in effect), based upon our
input.
As
we shall see, BottomCount() sorts the set we specify by the numeric
expression we provide (if we provide one) within the function, thus
breaking the natural hierarchy of the set. The basis of sorting by BottomCount()
closely resembles that used by the TopCount() function. If a
numeric expression is not specified, the function returns the set of members in
natural order, without any sorting, behaving like the Tail() function.
We will examine the syntax for the BottomCount()
function, and then look at its behavior based upon input we might provide. Moreover,
we will undertake practice examples constructed to support hypothetical
business needs that illustrate uses for the function. This will allow us to
activate what we explore in the Discussion and Syntax
sections, by getting some hands-on exposure in creating expressions that
leverage the function. Finally, our next article will build upon the basics we
expose here, and leverage BottomCount() within more sophisticated
examples of business usage.
Discussion
To restate our initial explanation of its operation, the BottomCount()
function sorts a set based upon a numerical expression we
provide, and then picks the bottom (whatever number we provide in the Count
parameter of the function) items in that set, based upon the rank order
established by the function. The frequent requirement to examine a subset
of tuples at the bottom of the general set is easily answered by the BottomCount()
“sort, and then pick this number of members, from the bottom up” approach.
The BottomCount() function can, of course, be used
for a great deal more than the support of simple listings with “least values.”
When we couple it with other functions, we can leverage BottomCount() to
deliver a wide range of analysis and reporting utility. As in so many cases
with the Microsoft integrated business intelligence solution, consisting of MSSQL
Server, Analysis Services and Reporting Services, this
function, leveraged from within the Analysis Services layer, can be
extended to support capabilities and attributes in the Reporting Services
layer. Knowing “where to put the intelligence” among the various layers is
critical to optimization, in many cases. For more of my observations on this
subject, see Multi-Layered
Business Intelligence Solutions ... Require Multi-Layered Architects.
Let’s look at some syntax illustrations to further clarify
the operation of BottomCount().
Syntax
Syntactically, the set
upon which we seek to perform the BottomCount() operation is specified
within the parentheses to the right of BottomCount, a common arrangement
within MDX functions, as we have seen in many of our articles. The syntax is
shown in the following string.
BottomCount(<< Set >>, << Count >> [,<< Numeric Expression >>])
We follow <<Set>>, the set
specification, with a comma, which is followed by <<Count>>, the numeric
expression we provide to dictate the number of “bottom” tuples we wish for
the function to return. <<Count>> is, in turn, followed by a numeric
expression whereby we specify the “sort by” criteria. As we have
mentioned, BottomCount() always breaks the natural hierarchy of the set
– due to the fact that the set is sorted upon the third argument we
provide as <<Numeric Expression>>, before returning the bottom
(specified number of) tuples from that sort.
The items returned by BottomCount()
are limited to the << Count >> input that we provide,
even in the case of “ties” in the <<Numeric Expression>> value.
This may be important to consider when evaluating the data retrieved by the
function, in addition to behavior, already mentioned above, of the function
when no sort criteria (in the form, again, of the <<Numeric
Expression>>) is provided.
We will practice some uses of the BottomCount() function
in the section that follows.
Practice
Preparation
To reinforce our understanding of the basics we have covered
so far, we will first use the BottomCount() function in a simple
scenario, to illustrate its operation as the primary focus. We will do so in a
backdrop that places BottomCount() within the context of meeting a
business need for a group of hypothetical information consumers – with
requirements similar to those we might encounter in our respective daily
environments. The intent, of course, is to demonstrate the operation of the BottomCount()
function in a straightforward, memorable manner.
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:
This procedure will take us through opening a new Query
pane, upon which we can create our first query within the section that follows.
Procedure: Satisfy Business Requirements with MDX
Let’s assume, for our practice example, that we have
received a call from our client, the Adventure Works organization,
requesting some information surrounding general sales of a specific group of
accessory products that the organization offers its customers. Our client
colleagues assure us (as they often do) that, although the requirement described
meets an immediate need, they will extrapolate what we teach them in realizing
that need to accomplish like results in other, similar requirements that arise.
To be specific, a group of
information consumers within the Adventure
Works Planning and Budgets group wishes to see specialized information
about a Product Accessory Subcategory: the consumers wish to
identify the bottom four performers (based upon annual Internet Sales
Amount) in the Tire and Tube product subcategory for Calendar
Year 2004.
We discuss the details of the
need with our colleagues, and set about demonstrating an approach to meeting
those needs, as we take the following steps.
1.
Type the
following query into the Query pane:
-- MDX071-1 Simple use of BottomCount():
-- "Bottom Four 2004 Sales Producers"
SELECT
{[Measures].[Internet Sales Amount]} ON AXIS(0),
{BOTTOMCOUNT(
[Product].[Product Categories].[Subcategory].
[Tires and Tubes].CHILDREN,
4, [Measures].[Internet Sales Amount])} ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Date].[Calendar].[Calendar Year].&[2004])
The BottomCount() function
we use in defining the row axis above specifies the “bottom four children of
the Tire and Tube accessory subcategory - with the number “4” as the Count specification, and with
[Product].[Product Categories].[Subcategory].[Tires and Tubes].CHILDREN
as the Set specification,
of the function. BottomCount() assembles the bottom four children from
the perspective of Internet Sales Amount (the Numeric Expression
upon which the complete set of Tire and Tubes children will first be
sorted by the function).
2.
Execute the
query by clicking the Run Query button in the toolbar.
The Results pane is populated by Analysis
Services, and the dataset shown in Illustration 1 appears.
Illustration 1: Result Dataset – Simple Use of BottomCount()
As we expected, the bottom four performers within the
Tire and Tubes subcategory are returned, having been sorted by Internet
Sales Amount, and having had the bottom four values in that sort isolated
as we requested in the function.
As is the case with many MDX functions, BottomCount() can
be used to deliver sophisticated analysis datasets, particularly when we employ
it along with other MDX functions. We will practice the use of BottomCount()
in the section that follows, building from a simple example, similar to
that which we saw above, to a scenario where we apply the function, in
conjunction with a calculated member, to reveal an additional analytical
perspective. In our next article, we will examine even more sophisticated
applications of the function, combining its use with that of other functions we
have explored in articles of the series.
3.
Select File
> Save As, name the file MDX071-1,
and place it in a meaningful location.
4.
Leave the
query open for the next section.
We provide the Planning and Budgets operatives with the
simple “bottom four performers” data we have generated, and they express
satisfaction with our having met the initial requirement. After giving the matter
some thought, they tell us that we might add a couple more data elements to the
query to make it support the full blown report that they had been considering
when making their initial request. They tell us that they need to add the Internet
Order Quantity (another measure in the cube), and Internet Gross Profit and
Internet Gross Profit Margin % (currently represented in the Adventure
Works cube as calculated measures) to the presentation, keeping the BottomCount()
based upon the Internet Sales Amount measure, as before. This, they
feel, will “round out” the presentation to provide more utility from an
analysis perspective, presenting “more information at a single glance.”
5.
Within the
query we have saved as MDX071-1, replace the top comment line of the query
with the following:
-- MDX071-2, "Bottom Four 2004 Sales Producers" with Internet Order Qty
-- Measure, Gross Profit and GP Margin Calculated Members
6.
Save the query
as MDX071-2, to keep MDX071-1 intact as a working sample.
7.
Modify the column
axis / “ON AXIS(0)” specification line (currently syntax line four) of the
query to contain the Internet
Order Quantity measure, together
with the Internet
Gross Profit and Internet
Gross Profit Margin calculated measures, to appear as follows:
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity],
[Measures].[Internet Gross Profit], [Measures].[Internet Gross Profit Margin]}
ON AXIS(0),
8.
Leave the
remainder of the query in its original state.
The
Query pane
appears as depicted in Illustration 2, with our modifications marked.
Illustration 2: The Query with Added Measure and Calculated Measures
9.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated, and the dataset shown in Illustration 3 appears.
Illustration 3: Result Dataset – With Our Modifications
We note that, although the order of the “bottom four” has
not changed (the BottomCount() function continues, after all, to be
based upon the Internet Sales Amount measure), the Internet Order
Quantity measure is not sorted. (We will focus on this measure as an
“additional perspective” next.) Moreover, as an aside, we notice that the Internet
Gross Profit Margin is the same for each accessory subcategory – a
relatively unsurprising circumstance within sample databases such as Adventure
Works DW, which are, in large part, mass-populated from core data by simple
calculations, extensions, etc.
10.
Re-save the
file as MDX071-2.
11.
Leave the
query open for the next step.
We again present the results to the information consumers,
who are quite happy with the outcome. At this point, we propose to extend the
requirement once again, and to modify the query to return the bottom four
performers within the Tire and Tubes subcategory based upon the newly
added Internet Order Quantity measure. The “sort” criteria, we tell our
client colleagues, can be parameterized within a robust reporting application
such as Reporting Services; ad hoc selection of analytical perspectives
such as we are examining, we say, may act to broaden the perspective currently
obtained with Internet Sales Amount (as it might with any other measure
/ calculated measure relevant to the business) alone. For that matter,
through parameterization, we might deliver virtually unlimited analytical “views”.
We have only to make one simple change to the function to
deliver this additional perspective. We will do so, and further confirm our
understanding of the operation of the modified BottomCount() function,
by taking the following steps:
12.
Within the
query we have saved as MDX071-2, replace the top comment line with the
following:
-- MDX071-3, "Bottom Four 2004 Sales Producers" with Internet Order Qty
-- Measure, Gross Profit and GP Margin Calculated Members;
-- Internet Order Qty Measure as Key
13.
Save the query
as MDX071-3, to keep MDX031-2 intact as a working sample.
14.
Replace [Measures].[Warehouse
Profit] within the BottomCount() function (in the Rows Axis /
ON AXIS(1) specification) with [Measures].[Internet
Order Quantity],
the member we added to the Column Axis / ON AXIS(0) specification
above.
The ON
AXIS(1) specification appears as follows after the change:
{BOTTOMCOUNT(
[Product].[Product Categories].[Subcategory].[Tires and Tubes].CHILDREN,
4,[Measures].[Internet Order Quantity])} ON AXIS(1)
The complete Query pane appears as depicted in Illustration 4,
with our modifications marked, once again.
Illustration 4: The Query with Our Modifications Marked
15.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated, and the dataset shown in Illustration 5 appears.
Illustration 5: Result Dataset – Presenting a New Perspective, Indeed
It quickly becomes obvious that we have derived a new view
of the business, indeed, with this small modification. The operation of the BottomCount()
function, based in this instance upon the Internet Order Quantity
recorded for each of the Tire and Tubes subcategory groups, results in a
sort of the subcategories accordingly. The subsequent selection of the bottom
four from this sort presents Tire and Tubes subcategory
groups that did not appear earlier, because their Internet Sales Amount levels
were not among the lowest in the organization. The substitution of the Internet
Order Quantity however, as the basis for the BottomCount() function
has revealed Tire and Tubes subcategory groups that simply don’t
“register “in the former query, because individual group item sales price
drives much higher extended Internet Sales Amount – and obscures the
fact that they are among lower performers when it comes to quantities sold. The
fact that they are lower performers from this perspective might be useful in
analyzing other considerations – for example, if shipping costs for these individual
product subcategories were higher than others, with higher Internet Order
Quantity numbers, management might want to delve into the reasons
behind this circumstance.
This revelation is welcomed by the information consumers,
who can now enhance their analysis capabilities by taking into consideration both
perspectives (Internet Sales Amount and Internet Order Quantity)
to isolate and analyze bottom performers from multiple perspectives. The study
of the bottom performers from the tandem perspectives, our colleagues now
realize, will likely add valuable lessons that can be extrapolated to other Products,
as well as other operational dimensions, regardless of size of only one
(perhaps the traditional “standard”) measure. This is an excellent
illustration of the power of multidimensional analysis, courtesy of the pairing
of the BottomCount() function with a different member to delve deeper in
the analysis of bottom organizational performers.
Our client colleagues express satisfaction with the results,
and confirm their understanding of the operation of the BottomCount() function
within the contexts we have presented in the practice exercises. We reiterate
to the Reporting team that knowing “where to put the intelligence” within the
various layers of the Microsoft integrated BI solution can mean highly tuned
performance and effective solutions for consumers throughout our
organizations.
16.
Re-save the
file as MDX071-3.
17.
Select File
-> Exit to leave the SQL Server Management Studio, when ready.
Summary ...
This
article served as the first of a pair of articles surrounding the potentially
powerful BottomCount() function. In this introductory session, we
examined the BottomCount() function, noting its obvious value in
equipping us with a means of isolating the lowest performers from among
hundreds, thousands or more fellow members. We noted that this ranking
capability is often critical in data analysis and decision support scenarios,
and then discussed how BottomCount() facilitates our performing such
ranking, as a part of covering the general operation of the function. We
then examined the syntax surrounding the BottomCount().
We next
undertook exercises where we practiced using the function in meeting the
business requirements of a hypothetical group of information consumers. We
focused on a simple use of the function, to allow for minimal distraction while
grounding ourselves in the basics. We then provided a straightforward, yet
meaningful, example of how we might leverage our core query (with the addition
of another measure, together with a couple of calculated measures),
to achieve a revealing additional perspective in our analysis of the
performance of product subcategory (and, by extrapolation, other operational)
groups. Throughout the practice examples, we briefly discussed the results
datasets we obtained with regard to the BottomCount() function, together
with other surrounding considerations.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
About