Further Combination of BottomCount() with Other MDX 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:

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles