Further Combination of BottomCount() with Other MDX Functions - Page 2

November 28, 2008

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

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