Ad HocTopCount and BottomCount Parameters

Introduction and Business Scenario

Because they allow
information consumers to assume a role in guiding the delivery of information -
and add a "self-serve" component to the reporting experience - parameterization
in general is a popular topic in the forums and newsgroups of most enterprise
reporting applications. My first exposure to the concepts of parameterization
was in working with very early versions of Cognos Impromptu. My continued application
of those concepts over the succeeding years within Cognos, Crystal, Business Objects, MicroStrategy,
and a host of other, more specialized applications, has given me a great appreciation
for the opportunities that exist in the business environment for effective
parameterization. Whether the reports are to be printed, displayed on screen,
or any of the other options for production / deployment, it is easy to see the
value that parameterization can add in making the selection and delivery of
enterprise data more focused and consumer-friendly.

While
I have extended parameterization concepts into many arenas, none have captured
my attention as much as their deployment within the integrated Analysis
Services / Reporting Services pairing. These applications work together to
provide business intelligence in a way that is powerful and highly flexible.
Indeed, I often advise clients who are seeking a consultant to implement the
integrated Microsoft BI solution (composed of MSSQL Server, MSSQL Server
Analysis Services, and Reporting Services) to seek a "multidimensional
architect" - a consultant who has a good working knowledge of each of the
components, and who can determine where, among three or more possible "logical
layers," to place which components so as to optimize the system as
a whole.

An
excellent example of parameterization within this context can be had in the
exploitation of the popular MDX TopCount() and BottomCount()
functions.

A Quick Overview of the TopCount and BottomCount Functions

As many of us who routinely use MDX in our business
environments are aware, the TopCount() and BottomCount()
functions are highly valuable in enabling us to isolate best performers from among hundreds,
perhaps thousands, of fellow members. This ranking capability is critical in
data analysis and decision support scenarios: In many business situations, we
seek to report upon "best performers" for various reasons. TopCount()
facilitates our doing so, allowing us to sort on a numeric value expression
that we can provide. We can tell the function how many "top" members
we wish to retrieve (say, the "top ten," or the "top twenty"),
for a "custom-fit" approach, that matches our needs precisely. BottomCount()
allows us to perform the opposite, but in a procedurally similar manner: we can
thus force a sort on a numeric value expression for the number of "bottom"
members we wish to retrieve. Particularly useful in the analysis of "underperformers"
within many contexts, BottomCount() helps us to isolate candidates for
elimination (as with underperforming locations, products - even people), or
other appropriate action.

According to the Analysis
Services Books Online, the TopCount() function returns a specified
number of items from the topmost members of a specified set, ordering the set
first." We specify three parameters, a set, a count, and a measure,
in most cases, and TopCount() returns the number of top performers (or "top
anything," in effect), based upon our input. BottomCount(), by
contrast, provides the specified number bottommost members, and works, in all
considerations except "direction" and starting point, in a manner similar
to TopCount().

Syntactically, the set upon which we seek to perform the TopCount()
operation is specified within the parentheses to the right of TopCount,
a common arrangement within MDX functions, as we have seen in our previous
articles. The syntax is shown in the following string, and is identical, except
for the keyword that begins it, for BottomCount().

TopCount(<< Set >>, << Count >>
[,<< Numeric Expression >>])

The following example expression illustrates the use of
the TopCount() function, within a simple business context. Let's say
that a group of information consumers with whom we are working within the
FoodMart organization wishes to see the top tenProduct Names for
operating year 1997, based upon total Store Sales for each
product.

The basic TopCount() function specifies the "top
ten Product Names" (with the number "10" as the Count
specification, and [Product].[Product Name].Members as the Set
specification, of the function). TopCount() assembles the top ten
members from the perspective of Store Sales (the Numeric Expression
upon which the complete set of Product Names will first be sorted by the
function). The function with arguments is represented in the ON ROWS
specification of the following query (enclosed in the dashed-line box):

I
often parameterize the TopCount() and BottomCount() functions
within a Reporting Services application I am developing as part of a larger
implementation of the integrated Microsoft Business Intelligence solution for a
given client. While this is only a tiny part of the overall structure we
typically assemble for a combined OLAP and relational reporting system, it will
provide an interesting glimpse of the much larger population of opportunities
that I find daily in working with these powerful analysis and reporting tools.

In the
following sections, we will perform the steps required to add parameterized top
and bottom counts to an OLAP report. To provide a report upon which we can
practice our exercises, we will begin with the Foodmart Sales sample
report that, among other samples, accompanies the installation of Reporting
Services, and which is based upon the ubiquitous Sales sample cube that
comes along with an installation of Analysis Services.

For
purposes of our practice procedure, we will assume that information consumers within
the Marketing office of the FoodMart organization have expressed the need for modifications
of the existing Foodmart Sales report. The drilldown capabilities in the
report meet most of their analysis needs, but recent requirements to perform "outlier
analysis" with regard to the products that the organization sells, have
resulted in our being called to assist with creating a custom report that meets
an immediate, specialized need in a user-friendly manner.

In discussing their
requirements in detail, representatives of the Marketing department state that
a particular analysis operation would be dramatically enhanced if they could
simply specify, at report run time, that they wished to see a variable number
of "top products" with regard to sales. Ideally, they would
like to be prompted for the number of products each time they wished to
run the report, as the number might vary with each information request. "Top
ten" (of whatever number) analysis of this sort is quite common within
OLAP, and we express confidence that we can meet the described need.

The consumers request
some additional changes to the report body itself, primarily that we remove the
existing parameter, which allows us to filter products by Product Family.
The new report will also be simpler with regard to dimensional levels: The
consumers state that the row axis (currently occupied by several levels of the Product
dimension, which, in turn, drill down to Product Brand Name as the
lowest level) needs only to display the Product Name, the lowest level
of the Product dimension in the Sales cube. Because this will be
a fixed report, designed for a limited use by analysts, we have no need of
drilldown features in the row axis. Finally, the consumers ask that the report
be modified to allow date selection via a cascading picklist - a feature that
will replace the current date drilldown in the column headings.

As an added
embellishment, we propose the extension of the requirement to include the
capability to dictate, also at run time, our selection of "top" or "bottom"
performers, it being our experience that analysis of bottom performers can be
useful in many cases. The capability to focus on bottom performers is a natural
extension of the need to analyze top performers, if not, perhaps, to support different
actions by the organization once "bottom" status is ascertained. The
information consumers receive our suggestion with enthusiasm, and agree that
they can envision several uses for such a capability. We thus plan from the
outset to provide a multi-purpose tool in a compact, efficient "package."
These capabilities will also convert the existing Foodmart Sales report
to a multi-purpose report whose presentation is dictated on the fly by the easy,
ad hoc input of varying criteria, allowing the analysts to meet
differing conditions and analysis needs rapidly. As is often the case with the
addition of parameterization, the organization will ultimately be able to
accomplish more with fewer reports.

As part of our typical
business requirements gathering process, we listen attentively to the details, formulating,
in the background, an idea of the steps we need to take in modifying a copy of
the report to produce the desired results. Then, having grasped the stated
need, and having confirmed our understanding with the intended audience, we
begin the process of modifying the FoodMart Sales report to satisfy the
information consumers.