Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 28, 2008

Further Combination of BottomCount() with Other MDX Functions

By William Pearson

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:

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date