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

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.

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

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

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.

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

-- MDX071-2, "Bottom Four 2004 Sales Producers" with Internet Order Qty 
--   Measure, Gross Profit and GP Margin Calculated Members

{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity],
   [Measures].[Internet Gross Profit], [Measures].[Internet Gross Profit Margin]} 
      ON AXIS(0),

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

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

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.

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:

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

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.

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

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.

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.