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

Monday Oct 6th 2008 by William Pearson
Share:

Isolate worst / lowest performers from the member population at large, and perform sophisticated analysis with BottomCount(). Join MSAS Architect Bill Pearson in the first of a pair of articles focusing upon the powerful BottomCount() function, where we experience “hands-on” some of the possible ways it can offer multi-perspective decision support.

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.

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:

Prepare MSSQL Server Management Studio to Query Analysis Services

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()
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
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
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
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
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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved