Combine BottomCount() with Other MDX Functions to Add Sophistication

Discover, through a hands-on practice exercise, a more sophisticated business use for the
MDX BottomCount() function, in combination with the Generate(), Descendants(),
Ancestor() and other functions we have explored within our series. Join BI
Architect Bill Pearson in this, the second part, of an extended examination of
the MDX BottomCount() function.

BottomCount,
BottomCount, Generate, Descendant, Ancestor, order,
sort, processing, MDX, performance, set, query, tuning, optimize, logical, function, multidimensional
expressions, Analysis, Services

This
month, we will continue the examination of the BottomCount() function
that we began in the previous article of the MDX Essentials
series, Basic
Set Functions: The BottomCount() Function. We will explore a somewhat
more sophisticated use of BottomCount(), in combination with the Generate(), Descendants(), Ancestor() and other 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,
specifically within the context of the BottomCount() function.

Overview

Before getting “hands-on” with BottomCount() in our last session, we
introduced the function, stating that its highest value lies in the fact that we can use it as
a means for 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 that 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
this article, we will extend our examination of the BottomCount()
function to illustrate somewhat more sophisticated uses. We will dive right
into a practical scenario, where we will further evolve our understanding of
the operation of BottomCount() through:

• Defining an illustrative business need as posed to us by
  hypothetical groups of information consumers;
• Discussing the need from the perspective of MDX in general, and
  the BottomCount() function in particular;
• Constructing, 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;
• Discuss the syntax contained within the solutions we construct
  for the information consumers;
• Briefly discuss the results datasets we obtain in executing the
  MDX queries we construct.

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.

Combinine BottomCount() with Other MDX Functions to Add Sophistication

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 BottomCount() “sorts a set in ascending order, and returns
the specified number of tuples in the specified set with the lowest values”
(according to the Analysis Services Books Online).

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, the function returns the
set of 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

Because we discussed syntax and other considerations that
accompany the basic use of BottomCount() in Basic Set Functions: The BottomCount() Function, we will move directly into our practice example
in this article. The example 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 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.

Preparation

To reinforce our understanding of the various scenarios we
propose within our practice session, we will perform a practice 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
Dimensional Level

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 Reseller Sales within
the States of the USA for a given year. 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 Reseller Sales values from
the perspective of U.S. Geography for the Calendar Year 2003
operating cycle. The consumers wish to be able to present total CY
2003 Reseller Sales Amounts for the four Cities with the lowest
total Reseller Sales Amounts in each of the States (the
group is only concerned with U.S. operations from the perspective of this
business need). 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, illustrated in Table 1, to confirm our
understanding of the requirement.

The challenge in the requirement we have described lies
within the fact that we are working within multiple levels within Geography dimension
(Geography hierarchy) to produce the requested results. We are, in
effect, being asked to classify the four bottom Geography performers by
a lower level in the Geography dimension (City). In order to do
this, we will employ the BottomCount() function as we have done in
previous scenarios, but this time in conjunction with other MDX functions, to
present the State information associated with each of the bottom
performing Cities.

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. Let’s construct part of the query
to get started, initially starting with the core BottomCount() function.

— MDX072-1-1, BottomCount() and Descendants() Combination to retrieve “4 Least

—   Performing USA States (with regard to total Reseller Sales Amounts)

—      in CY 2003”

SELECT
    {[Measures].[Reseller Sales Amount]}  ON AXIS(0),
    {BOTTOMCOUNT(

       DESCENDANTS(

           [Geography].[Geography].CURRENTMEMBER,

             [Geography].[Geography].[City]

                  ),

             4,

          [Measures].[Reseller Sales Amount])

       }

    ON AXIS(1)
FROM

     [Adventure Works]
WHERE

        ([Date].[Calendar].[Calendar Year].[CY 2003])

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

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