Combine BottomCount() with Other MDX Functions to Add Sophistication

November 21, 2008

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:

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

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers