Further Combination of BottomCount() with Other MDX Functions

This
month, we will conclude the examination of the BottomCount() function
that we began in the two previous articles of the MDX Essentials series, Basic
Set Functions: The BottomCount() Function and Combine
BottomCount() with Other MDX Functions to Add Sophistication. We will explore another
somewhat more sophisticated use of BottomCount(), in combination with the Descendants(), .CurrentMember, Item(), and .Name functions, to meet an
example business need for a hypothetical client. The primary focus of this article,
like the other articles of this series, is to provide hands-on
application of the fundamentals of the Multidimensional Expressions (MDX) language,
doing so here specifically within the context of the BottomCount() function
in combination with additional MDX functions.

Overview

Before beginning our hands-on practice session in Basic Set Functions: The
BottomCount() Function, we introduced the function, stating that its utility lies in the fact that we
can use it as a means of isolating “worst” or “least” performers from among
potentially large populations of fellow members. We emphasized that this
ranking capability fills a critical need in many data analysis and decision
support scenarios, where we often seek to report upon “bottom” performers for
various reasons. We learned how BottomCount() facilitates this ranking
capability, allowing us to sort on the numeric value expression upon
which we wish to focus our analysis. We can direct the function to retrieve
the exact number of “bottom” members we wish to isolate (say, the “bottom ten,”
or the “bottom twenty”), for a “custom-fit” approach, that precisely matches
the dynamic analysis needs of our own environments.

In Combine BottomCount() with Other MDX Functions to Add
Sophistication, we continued our examination of BottomCount(),
reviewing briefly the basic operation of the function, and then focusing upon
more sophisticated effects that we can use it to deliver. We defined an
illustrative business need, as posed to us by hypothetical groups of
information consumers, and then discussed the need in general, with regard to
challenges inherent in meeting them with the BottomCount() function in
particular. We then constructed, in a
step-by-step manner, a final query to meet the expressed requirements using a
combination of BottomCount() and other MDX functions we have explored in
articles of the MDX
Essentials series. Throughout our practice session, we
discussed the syntax contained within the solution we constructed for the
information consumers, as well as the results datasets we obtained in employing
BottomCount() in combination with other functions, together with other
surrounding considerations.

In
this article, we will conclude our examination of the BottomCount()
function, illustrating another somewhat sophisticated use through a compounding
of the function with additional MDX functions. Following our approach in Combine BottomCount() with Other MDX Functions to Add
Sophistication, we will expose a practical
scenario, where we will further evolve our understanding of the operation of BottomCount()
through:

• Defining another illustrative business need as posed to us by
  hypothetical groups of information consumers;
• Discussing the need, once again, from the perspective of MDX in
  general, and the BottomCount() function in particular;
• Constructing, again, in a step-by-step manner, a query to ultimately
  meet the expressed requirement using combinations of BottomCount() and
  other MDX functions we have explored in articles of the series;
• Discussion of the syntax contained within the solutions we construct
  for the information consumers;
• Brief discussion of the results datasets we obtain in executing
  the MDX queries we construct throughout the steps of our practice session.

To review an introduction to the BottomCount()
function before beginning this article, which contains examples of its use in
basic scenarios, see my article Basic Set Functions: The
BottomCount() Function, a member of the MDX Essentials series at Database Journal.

Further Combination of BottomCount() and Other MDX Functions

In our introductory article, we
examined the syntax for the BottomCount() function, and then looked at
its behavior, based upon arguments we provided to achieve our ends. We
learned that, according to the Analysis Services Books Online, BottomCount()
“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 “worst”
/ “least”, in effect), based upon our input. In specifying the set
expression, count, and numeric expression, we use the syntax
shown in the following string:

BottomCount(<< Set >>, << Count >> [,<< Numeric Expression >>])

As we saw in Basic Set Functions:
The BottomCount() Function, 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 (or if, for that matter, a
returned value “ties” with other values in the same returned dataset, as we
shall see within the practice session that follows), the function returns the
set of affected members in natural order, without any sorting, behaving like
the Tail() function.

We will practice the combined use of the BottomCount() function
and other MDX functions in the section that follows.

Practice

Having discussed in detail the syntax and other
considerations that accompany the basic use of BottomCount() in Basic Set Functions:
The BottomCount() Function, and having summarized the
most important syntax information above, we will move directly into our
practice example in this article. As was the case in the illustration we
covered in Combine BottomCount() with Other
MDX Functions to Add Sophistication, the example we will
introduce in this article is a bit more elaborate, and will require somewhat
detailed explanation, as it involves the juxtaposition of BottomCount()
with other MDX functions. Consistent with
our routine approach within most articles of the MDX Essentials series,
we will use BottomCount() within the context of meeting an illustrative
business need for a group of hypothetical information consumers in the hands-on
practice example that follows. We will assemble our solution in multiple
steps, in an attempt to make its construction more easily understandable
overall.

Preparation

To reinforce our understanding of the scenario we propose
within our practice session, we will perform a hands-on exercise together. We
will do so in a backdrop that places BottomCount(), combined with other
MDX functions, 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 combined BottomCount() / other
functions 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

Perform
a BottomCount() within the Context of Another Dimension, and Return a Related
String for Presentation Purposes

Our current exercise comes in the form of
a request from the same group of information consumers with whom we worked in Basic Set Functions: The BottomCount() Function and Combine BottomCount() with Other MDX
Functions to Add Sophistication.
Expressing satisfaction with the solutions we offered within those scenarios, a group of information consumers within the Adventure
Works Planning and Budgets group wishes, once again, to see specialized
information about Reseller Sales values from the perspective of U.S.
Geography for the Calendar Year 2003 operating cycle. 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 the
realization of that need to accomplish like results in other, similar
requirements that arise.

This request, which again may sound simple
on the surface, also involves “bottom counts” of geographical units – this time
U. S. States (the group is only
concerned with U.S. operations from the perspective of this immediate business
need): The consumers want a query
that will produce a plain list containing the “bottom” individual State,
for each of the twelve Months of Calendar Year 2003, this time in terms of Reseller Order Quantity within
each respective month.

To restate, the consumers
wish to be able to present total Reseller
Order Quantity values, for the only States with the
lowest total Reseller Order
Quantity, for each of the twelve Months of Calendar Year 2003. We are told that the Adventure Works
cube, which houses the lion’s share of the Adventure Works Reseller Sales data,
contains the information we need to meet the business requirement.

Working with the consumers, we draft a sketch of the
proposed report layout, to confirm our understanding and to agree fully on the
requirement. The sketch appears as illustrated in Table 1.

We agree to pursue a solution, covering satisfaction of the
requirement in bite-size pieces. One challenge that stands out immediately is
the apparent need to return the U. S. State name, a text string, from
the third-lowest level of the Geography dimension (the level is actually
named State-Province within the Adventure Works cube). BottomCount()
is, of course, still at the heart of our contemplated approach, however, with
the Reseller Order Quantity the measure upon which the function is
based.

Let’s begin with our first step. We’ll construct a query
that lists all U.S. States, along with the total Reseller
Order Quantity, for each of the member Months of Calendar Year 2003.
This serves to build the core Axis(0) – or the “ON ROWS” –
specification, the “Months” part of which we will use within our final
solution. It also serves to present the Reseller Order Quantity for
each U. S State, for each respective Month, allowing us to
“proof” the accuracy of the subsequent steps, where we generate the name of the
least performing (“bottom”) State from the perspective of Reseller
Order Quantity, for each associated Month.

 — MDX073-1, Simple Reseller Order Qty Totals by Month, 2003,

 —   for Individual U.S. States

 SELECT

   {[Measures].[Reseller Order Quantity]} ON AXIS(0),
 NON EMPTY

   CROSSJOIN

      (

         {

            DESCENDANTS([Date].[Calendar].[Calendar Year].&[2003],

               [Date].[Calendar].[Month])

            },
         {

            DESCENDANTS( [Geography].[Geography].[United States],

               [Geography].[Geography].[State-Province] )

            }

         )

      ON AXIS(1)
 FROM

    [Adventure Works]

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

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

At this stage, we can identify, at a glance, the “least
performing” U.S. State (from the perspective of total Reseller
Order Quantity) for any given month of the year under examination.
Next, we will add the MDX required to generate the name of the “bottom”
performer for each respective month, initially placing the name next to the
column displaying the Reseller Order Quantity for each State. The
results produced will then provide easy verification of the accuracy of the single,
“least performing,” State name produced.

 — MDX073-2, Addition of Calculated Member to Produce Name of States

 —   with Least CY 2003 Total Reseller Order Qty

 WITH

 MEMBER

    [Measures].[Least Reseller Order Qty State]
 AS

    ‘BOTTOMCOUNT

       (

          DESCENDANTS

             (

                [Geography].[Geography].[United States],

                   [Geography].[Geography].[State-Province] ),

                      1, ([Date].[Calendar].CURRENTMEMBER,

                   [Measures].[Reseller Order Quantity]

                   )

                ).ITEM(0).ITEM(0).NAME’

This
will create the calculated member Least Reseller Order Qty State, which
will allow us to present a string containing the name of the single U.S.
State with the least Reseller Order Quantity for each Month,
alongside each State’s respective numeric volumes.