Basic Set Functions: The BottomCount() Function, Part I

October 6, 2008

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.

NOTE: For information surrounding the TopCount() function, see my articles Basic Set Functions: The TopCount() Function, Part I and Part II. 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 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.