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.

Note: For more information about my MDX Essentials column in general, see the section entitled “About the MDX
Essentials Series” that follows the conclusion of
this article.

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.

NOTE: For information
surrounding the BottomCount() function, see my article Basic
Set Functions: The BottomCount() Function
. For a
detailed exploration of the Tail() function, see
Basic
Set Functions: Subset Functions: The Tail() Function
. These articles are members of the MDX
Essentials
series at Database Journal.

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.

City

State

Reseller Sales
Amount

City 1

USA State 1

(Lowest Reseller Sales Amount for State 1)

City 2

USA State 1

(2nd Lowest Reseller Sales Amount for State 1)

City 3

USA State 1

(3rd Lowest Reseller Sales Amount for State 1)

City 4

USA State 1

(4th Lowest Reseller Sales Amount for State 1)

City 1

USA State 2

(Lowest Reseller Sales Amount for State 2)

City 2

USA State 2

(2nd Lowest Reseller Sales Amount for State 2)

City 3

USA State 2

(3rd Lowest Reseller Sales Amount for State 2)

City 4

USA State 2

(4th Lowest Reseller Sales Amount for State 2)

City 1

USA State 3

(Lowest Reseller Sales Amount for State 3)

City 2

USA State 3

(2nd Lowest Reseller Sales Amount for State 3)

City 3

USA State 3

(3rd Lowest Reseller Sales Amount for State 3)

City 4

USA State 3

(4th Lowest Reseller Sales Amount for State 3)

Table 1: Results Dataset, with BottomCount() Defining Columns

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.

1. 
Type the
following query into the Query pane:


— 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.



Illustration 1: Query Pane with Our Input …

2. 
Execute the
query by clicking the Execute (!) button in the toolbar.

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



Illustration 2: Results Dataset – Simple Use of the BottomCount()
Function

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles