Further Combination of BottomCount() with Other MDX Functions

Friday Nov 28th 2008 by William Pearson
Share:

Join BI Architect Bill Pearson in concluding his extended examination of the MDX BottomCount() function. Discover, through a hands-on practice exercise, another business use for the MDX BottomCount() function, in combination with the MDX Descendants(), .CurrentMember, .Item() and .Name functions.

This month, we will conclude the examination of the BottomCount() function that we began in the two previous articles of the MDX Essentials series, Basic Set Functions: The BottomCount() Function and Combine BottomCount() with Other MDX Functions to Add Sophistication. We will explore another somewhat more sophisticated use of BottomCount(), in combination with the Descendants(), .CurrentMember, Item(), and .Name 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, doing so here specifically within the context of the BottomCount() function in combination with additional MDX functions.

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 beginning our hands-on practice session in Basic Set Functions: The BottomCount() Function, we introduced the function, stating that its utility lies in the fact that we can use it as a means of 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 how 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 Combine BottomCount() with Other MDX Functions to Add Sophistication, we continued our examination of BottomCount(), reviewing briefly the basic operation of the function, and then focusing upon more sophisticated effects that we can use it to deliver. We defined an illustrative business need, as posed to us by hypothetical groups of information consumers, and then discussed the need in general, with regard to challenges inherent in meeting them with the BottomCount() function in particular. We then constructed, in a step-by-step manner, a final query to meet the expressed requirements using a combination of BottomCount() and other MDX functions we have explored in articles of the MDX Essentials series. Throughout our practice session, we discussed the syntax contained within the solution we constructed for the information consumers, as well as the results datasets we obtained in employing BottomCount() in combination with other functions, together with other surrounding considerations.

In this article, we will conclude our examination of the BottomCount() function, illustrating another somewhat sophisticated use through a compounding of the function with additional MDX functions. Following our approach in Combine BottomCount() with Other MDX Functions to Add Sophistication, we will expose a practical scenario, where we will further evolve our understanding of the operation of BottomCount() through:

  • Defining another illustrative business need as posed to us by hypothetical groups of information consumers;
  • Discussing the need, once again, from the perspective of MDX in general, and the BottomCount() function in particular;
  • Constructing, again, 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;
  • Discussion of the syntax contained within the solutions we construct for the information consumers;
  • Brief discussion of the results datasets we obtain in executing the MDX queries we construct throughout the steps of our practice session.

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.

Further Combination of BottomCount() and Other MDX Functions

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, according to the Analysis Services Books Online, BottomCount() “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 “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 (or if, for that matter, a returned value “ties” with other values in the same returned dataset, as we shall see within the practice session that follows), the function returns the set of affected 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

Having discussed in detail the syntax and other considerations that accompany the basic use of BottomCount() in Basic Set Functions: The BottomCount() Function, and having summarized the most important syntax information above, we will move directly into our practice example in this article. As was the case in the illustration we covered in Combine BottomCount() with Other MDX Functions to Add Sophistication, the example we will introduce in this article 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 most articles of 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. We will assemble our solution in multiple steps, in an attempt to make its construction more easily understandable overall.

Preparation

To reinforce our understanding of the scenario we propose within our practice session, we will perform a hands-on 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 Dimension, and Return a Related String for Presentation Purposes

Our current exercise comes in the form of a request from the same group of information consumers with whom we worked in Basic Set Functions: The BottomCount() Function and Combine BottomCount() with Other MDX Functions to Add Sophistication. Expressing satisfaction with the solutions we offered within those scenarios, a group of information consumers within the Adventure Works Planning and Budgets group wishes, once again, to see specialized information about Reseller Sales values from the perspective of U.S. Geography for the Calendar Year 2003 operating cycle. 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 the realization of that need to accomplish like results in other, similar requirements that arise.

This request, which again may sound simple on the surface, also involves “bottom counts” of geographical units – this time U. S. States (the group is only concerned with U.S. operations from the perspective of this immediate business need): The consumers want a query that will produce a plain list containing the “bottom” individual State, for each of the twelve Months of Calendar Year 2003, this time in terms of Reseller Order Quantity within each respective month.

To restate, the consumers wish to be able to present total Reseller Order Quantity values, for the only States with the lowest total Reseller Order Quantity, for each of the twelve Months of Calendar Year 2003. 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, to confirm our understanding and to agree fully on the requirement. The sketch appears as illustrated in Table 1.

CY 2003 Month

Least Performing U. S. State

(with regard to Total Reseller Order Quantity)

January 2003

State with least Total Reseller Order Quantity

February 2003

State with least Total Reseller Order Quantity

March 2003

State with least Total Reseller Order Quantity

April 2003

State with least Total Reseller Order Quantity

May 2003

State with least Total Reseller Order Quantity

June 2003

State with least Total Reseller Order Quantity

July 2003

State with least Total Reseller Order Quantity

August 2003

State with least Total Reseller Order Quantity

September 2003

State with least Total Reseller Order Quantity

October 2003

State with least Total Reseller Order Quantity

November 2003

State with least Total Reseller Order Quantity

December 2003

State with least Total Reseller Order Quantity


Table 1: Desired Results Dataset (Final Version), with BottomCount() / Other MDX Functions Combination Defining Columns

We agree to pursue a solution, covering satisfaction of the requirement in bite-size pieces. One challenge that stands out immediately is the apparent need to return the U. S. State name, a text string, from the third-lowest level of the Geography dimension (the level is actually named State-Province within the Adventure Works cube). BottomCount() is, of course, still at the heart of our contemplated approach, however, with the Reseller Order Quantity the measure upon which the function is based.

Let’s begin with our first step. We’ll construct a query that lists all U.S. States, along with the total Reseller Order Quantity, for each of the member Months of Calendar Year 2003. This serves to build the core Axis(0) - or the “ON ROWS” - specification, the “Months” part of which we will use within our final solution. It also serves to present the Reseller Order Quantity for each U. S State, for each respective Month, allowing us to “proof” the accuracy of the subsequent steps, where we generate the name of the least performing (“bottom”) State from the perspective of Reseller Order Quantity, for each associated Month.

1.  Select File -> New to open a blank Query pane.

2.  Type the following query into the Query pane:

 -- MDX073-1, Simple Reseller Order Qty Totals by Month, 2003, 
 --   for Individual U.S. States
 
 SELECT 
   {[Measures].[Reseller Order Quantity]} ON AXIS(0),
   
 NON EMPTY
   CROSSJOIN
      (
         {
            DESCENDANTS([Date].[Calendar].[Calendar Year].&[2003], 
               [Date].[Calendar].[Month])
            },
             
         {
            DESCENDANTS( [Geography].[Geography].[United States],
               [Geography].[Geography].[State-Province] )
            }
         )
      ON AXIS(1)
 
 FROM 
    [Adventure Works]

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


Illustration 1: Query Pane with Our Input ...

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

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


Illustration 2: Results Dataset (Partial View) – Reseller Order Quantities for U.S. States, by Month in CY 2003

4.  Select File > Save As, name the file MDX073-1, and place it in a meaningful location.

5.  Leave the query open for the steps to follow.

At this stage, we can identify, at a glance, the “least performing” U.S. State (from the perspective of total Reseller Order Quantity) for any given month of the year under examination. Next, we will add the MDX required to generate the name of the “bottom” performer for each respective month, initially placing the name next to the column displaying the Reseller Order Quantity for each State. The results produced will then provide easy verification of the accuracy of the single, “least performing,” State name produced.

6.  Within the query we have saved as MDX073-1, replace the top comment line of the query with the following:

 -- MDX073-2, Addition of Calculated Member to Produce Name of States
 --   with Least CY 2003 Total Reseller Order Qty

7.  Save the query as MDX073-2, to keep MDX073-1 intact as a working sample.

8.  Add the following lines to the query, between the top comment line we just replaced, and the SELECT keyword that begins the query:

 WITH 
 MEMBER
      
    [Measures].[Least Reseller Order Qty State]
 
 AS
    'BOTTOMCOUNT
       ( 
          DESCENDANTS
             ( 
                [Geography].[Geography].[United States],
                   [Geography].[Geography].[State-Province] ), 
                      1, ([Date].[Calendar].CURRENTMEMBER,   
                   [Measures].[Reseller Order Quantity]
                   ) 
                ).ITEM(0).ITEM(0).NAME'

This will create the calculated member Least Reseller Order Qty State, which will allow us to present a string containing the name of the single U.S. State with the least Reseller Order Quantity for each Month, alongside each State’s respective numeric volumes.

The calculated member we create with this MDX is a “busy place.” It accomplishes the BottomCount(), providing as the << Set >> argument a Descendants() function. We saw the BottomCount() / Descendants combination in very similar action in the practice session we undertook within Combine BottomCount() with Other MDX Functions to Add Sophistication, and so I won’t go into that any further here, other than to point out that the function is structured to return the set of the members of the Geography dimension (Geography hierarchy) that belong to the level it specifies, the State-Province level. It is from this level that we are able to obtain the string representing the U.S. State Name that we need.

The BottomCount() function is provided a << Count >> argument of “1” , obviously, as we seek to return the “worst” (or “least”) performing State with regard to the Reseller Order Quantity measure, the final argument of the function, << Numeric Expression >>. The interim result of the BottomCount() function, therefore, is to return a single “worst” performing State member for each Month, which enables us to leverage the .Item function. .Item, in turn allows us to exploit its capability to return the member in a tuple, based upon its position. By using .Item(0), we select the first single-member (the product of the BottomCount() of 1) tuple in each Month set. (.Item contains a zero-based specification of position, hence the zero as the position argument.) We then use .Item(0) again, immediately after the first .Item function, to select the first (and only) member in the single-member tuple.

Finally, we use the .Name function to return the name of the State member that we have so precisely isolated. This function, combined with the functions just described, result in a calculated member that returns the name of the State with the bottom / lowest shipping Reseller Order Quantity in a given Month in CY 2003, which is handily identified by the BottomCount() / Descendants() combination we discussed earlier.

NOTE: For an introduction to the .Item function, see my article Basic Member Functions: The .Item Function, another member of the MDX Essentials series at Database Journal.

9.  Modify the ON AXIS(1) - or the “ON ROWS” - line of the query, which already contains the Reseller Order Quantity measure, to contain the new “Least Reseller Order Qty State calculated member / measure we defined above, as follows:

 {[Measures].[Reseller Order Quantity], 
    [Measures].[Least Reseller Order Qty State]} ON AXIS(0),

10.  Leave the remainder of the query in its original state.

The Query pane appears as depicted in Illustration 3, with our modifications marked.


Illustration 3: The Query, with Our Newly Added Calculated Member and Other Modifications

11.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the resulting dataset, partially shown in Illustration 4, appears.


Illustration 4: Results Dataset - New Calculated Member Appears (Partial View)

We note that the Name of a single U.S. State appears for each Month, alongside the respective Reseller Order Quantity totals. The presence of the months of CY 2003, which populate the rows already, result in the evaluation of the “Least Reseller Order Qty State calculated member for each month respectively, giving us an outcome that meets the expressed requirements of the information consumers.

12.  Re-save the file as MDX073-2.

13.  Leave the query open for the next step.

Using the results from our latest query execution, we can easily verify that the calculated member is producing the results that its name purports. An example of such verification appears in Illustration 5, where we see that, for November 2003, the “least performing” U. S. State returned by our calculated member reflects the name of New Mexico that displays the “bottom” total value in the monthly Reseller Order Quantity measure at its left.


Illustration 5: Verifying the “Least Reseller Order Qty State” Returned as Correct

(November 2003 as Example Month)

In the example, we can easily see that New Mexico, which displays a “(null)” total Reseller Order Quantity for the Calendar Year period chosen as an example, November 2003, is, indeed, the “bottom” performer. First, “no” (that is, “null”) Reseller Order Quantity is clearly at the bottom of the list, which contains members with “non-zero” values. And among other “nulls” in the list, we see that New Mexico, as a member, lies at the “bottom” of the “natural order” of the group of other States returning nulls – the default behavior of the BottomCount() function when “ties” occur in the “least” performer.

All that remains, to align the results dataset with the precise presentation requirements specified by the information consumers, is to remove the Reseller Order Quantity measure. (Recall that we added this measure earlier simply to allow us to verify the correct operation of the “Least Reseller Order Qty State calculated member.)

14.  Within the query we have saved as MDX073-2, replace the top comment line of the query with the following:

-- MDX073-3, Alignment of Results Dataset to the Business Requirements

15.  Save the query as MDX073-3, to keep MDX073-2 intact as a working sample.

16.  Remove the Reseller Order Quantity measure ([Measures].[Reseller Order Quantity]), along with the comma (“,”) that immediately follows it, from the existing ON AXIS(0) line within the SELECT statement of the query, so that the line appears simply as:

 {[Measures].[Least Reseller Order Qty State]} ON AXIS(0),

17.  Remove NON EMPTY, on the line below the row we removed in the last step (part of the ON AXIS(1) specification). The item to be removed appears as:

 NON EMPTY

18.  Remove CROSSJOIN, and the left parenthesis ( “(“ ) on the line just below it, both of which appear below the line we modified in the last step (also part of the ON AXIS(1) specification). The items to be removed appear as:

 CROSSJOIN
 (

19.  Remove the following: the second Descendants() function, along with the curly braces ( “{}” ) that enclose it, from the ON AXIS(1) definition, as shown below:

 {
         DESCENDANTS( [Geography].[Geography].[United States],
            [Geography].[Geography].[State-Province] )
         }

20.  Remove the comma ( “,” ) that appears at the end of the first Descendants() function, to the immediate right of the right curly brace ( “}“ ) that helps to enclose it.

21.  Finally, remove the right parenthesis ( “)“ ) on the line just below the lines removed in the last step.

To summarize the final set of modifications, the query appears, after the addition of the most recent comment (Step 14 above), and before our other modifications, as follows, with syntax to be deleted enclosed in rectangles:

 -- MDX073-3, Alignment of Results Dataset to the Business Requirements
 
 WITH 
 MEMBER
      
    [Measures].[Least Reseller Order Qty State]
 
 AS
    'BOTTOMCOUNT
       ( 
          DESCENDANTS
             ( 
                [Geography].[Geography].[United States],
                   [Geography].[Geography].[State-Province] ), 
                      1, ([Date].[Calendar].CURRENTMEMBER,   
                   [Measures].[Reseller Order Quantity]
                   ) 
                ).ITEM(0).ITEM(0).NAME'
 
 SELECT 
   {
[Measures].[Reseller Order Quantity],
[Measures].[Least Reseller Order Qty State]} ON AXIS(0),
NON EMPTY CROSSJOIN (
{ DESCENDANTS([Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Month])
},
{ DESCENDANTS( [Geography].[Geography].[United States], [Geography].[Geography].[State-Province] ) }
) ON AXIS(1) FROM [Adventure Works]

22.  Leave the remainder of the query in its original state.

The Query pane appears, with modifications having been made, as depicted in Illustration 6.


Illustration 6: The Query, after Final Modifications

23.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset shown in Illustration 7 appears.


Illustration 7: Results Dataset – Reflecting the Ultimate Business Requirement ...

24.  Re-save the file as MDX073-3.

25.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

This article served as a conclusion of our multi-part examination of the BottomCount() function, which we began, and continued, with the two previous articles of the MDX Essentials series, Basic Set Functions: The BottomCount() Function and Combine BottomCount() with Other MDX Functions to Add Sophistication, respectively. We briefly reviewed the operation of the BottomCount() function, and then extended our examination of BottomCount(), yet again, to include another somewhat more sophisticated use.

As we routinely do within the articles of the MDX Essentials series, we defined an illustrative business need, as posed to us by hypothetical groups of information consumers. We next discussed the need in general, with regard to challenges inherent to meeting them with the BottomCount() function in particular. We then constructed, in a step-by-step manner, a final query to meet the expressed requirements using a combination of BottomCount() and other MDX functions we have explored within articles of the MDX Essentials series. Throughout our practice example, we discussed the syntax contained within the solutions we constructed for the information consumers, as well as the results datasets we obtained in employing a combination of BottomCount() and other functions, together with other surrounding considerations.

About the MDX Essentials Series ...

This article is a member of the MDX Essentials series, a monthly column designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each article progressively adding features designed to meet specific real-world needs.

For more information about the column 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, among others.

» 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