Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part II

About the Series …

This
article is a member of the series MSSQL
Server Reporting Services
. The series is designed to introduce MSSQL Server
Reporting Services
(“Reporting Services”), presenting an overview of
its features, with tips and techniques for real-world use. For more
information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools
needed to complete the hands-on portion of this article, see BlackBelt
Administration: Linked Reports in Report Manager
, another article within this
series.

About the Mastering OLAP Reporting
Articles …

One of
the first things that become clear to “early adopters” of Reporting Services
is that the “knowledgebase” for Analysis Services reporting with this
tool is, to say the least, sparse. As I stated in my article, Mastering
OLAP Reporting: Cascading Prompts
(where I treated the subject of cascading
parameters
for Reporting Services 2000), the purpose of the Mastering
OLAP Reporting
subset of my Reporting Services series is to focus on techniques for using Reporting
Services
for OLAP reporting. In many cases, which I try to outline in my articles at
appropriate junctures, the functionality of well-established, but expensive,
solutions, such as Cognos PowerPlay, can be met in most respects by Reporting
Services
– at a tiny fraction of the cost.

The
vacuum of documentation in this arena, even taking into consideration the
release of several books surrounding Reporting Services 2005 in
recent months, continues to represent a serious “undersell” of Reporting
Services
, from an OLAP reporting perspective. I hope to contribute
to making this arena more accessible for everyone, and to share my
implementation and conversion experiences as the series evolves. In the
meantime, we can rest assured that the OLAP potential in Reporting
Services
will contribute significantly to the inevitable commoditization of
business intelligence, via the integrated Microsoft BI solution.

For
more information about the
Mastering
OLAP Reporting
articles,
see the section entitled “About the Mastering OLAP Reporting
Articles”
in my
article Ad Hoc
TopCount and BottomCount Parameters
.

Overview

As I
noted in Mastering
OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount()
Function, Part I
, we have focused, in several
recent articles of this series, upon various aspects of parameterization
within the Reporting Services environment. In some cases we have
supported parameterization from structures completely contained within Reporting
Services
, and in others we have created parameter (predominantly picklist)
support from within other layers of the integrated Microsoft business
intelligence solution. As many of us are aware, enterprise reporting applications
typically allow for parameterization (via what are sometimes known as
“prompts” or “parameter prompts”) to enable information consumers to quickly
find the information they need from a report. These parameters, whose
values are physically passed to an axis specification or a slicer in the dataset
query
, often act to put filters into place “on the fly;” the filters
are thus enacted when the consumer types or selects a value, or a series of
values, at run time.

We emphasized, in Part I, that, 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 continued application of the underlying concepts
over many 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 challenging to overstate 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. As I have noted in
Part I, I often advise clients who are
attempting to locate 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.

NOTE: For details surrounding hands-on
approaches (as you will see, they are Legion) to the mechanics behind
supporting parameterization, (including the generation of picklists)
in Reporting Services, see these articles in
my MSSQL Server Reporting Services series here at Database Journal:

Throughout these articles, as well as elsewhere, we have
generated simple lists to provide virtually all we need to support parameterization
within Reporting Services and other enterprise reporting applications.
In this article, we will continue to pursue the objective we stated in Mastering
OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount()
Function, Part I
: We will perform
a more detailed examination of the mechanics behind parameterizing an MDX
function
– or more precisely, for parameterizing the variable
argument within such a function. We will illustrate the process using the
popular TopCount() function, which we introduced in Basic
Set Functions: The TopCount() Function, Parts I
and
II
(a member of my MDX
Essentials
series at Database Journal), but the same
logic can be extrapolated to many other similar MDX functions, as I have noted,
and will continue to note, in my articles.

In this article, we will continue to get hands-on exposure to
parameterizing TopCount() within the sample OLAP report that we
cloned in Part
I
. After
introducing the general concepts, in Part
I
, we began
our practice session by setting up a scenario within which to work with a basic
OLAP report in exposing the steps involved in parameterizing the TopCount()
function specifically. We began by opening the sample Report Server project, AdventureWorks
Sample Reports
, and ascertaining connectivity of its shared Analysis
Services
data source. Next, we created
a clone of an existing sample report, containing a matrix data region, with
which to perform our practice exercise.

Once we had made structural modifications to the clone report, to
further prepare for our practice exercise session, we performed a brief
overview of the MDX TopCount() function, discussing details of the use
we intended for the function to perform in support of the stated reporting needs
of a hypothetical client, as well as touching upon general concepts surrounding
the parameterization of MDX functions in general, and the TopCount()
function specifically.

In this, the second part of our article, we will:

  • Ensure the
    adequacy of the datasets, created automatically when we added query
    parameters as a last step in Part I, to support report parameters and
    meet business requirements;
  • Add syntax to
    the Month dataset query to enforce cascading, based upon
    the selection made for the Year parameter by an information
    consumer at runtime;
  • Leverage the
    MDX TopCount() function, containing count and Month parameter
    placeholders;
  • Discuss the
    interaction of the various components in supporting the runtime parameter
    that the end consumer sees;
  • Discuss the
    results obtained with the development techniques that we exploit.

Parameterizing the TopCount() Function (continued …)

Throughout many past
articles of the MSSQL Server Reporting Services series, we have
leveraged parameters within the context of MDX queries. To reiterate my
introduction in Mastering
OLAP Reports:
Parameterizing Number of “Top” Items with the MDX
TopCount() Function,
Part I,
Reporting Services 2000, initially intended as a component of the MSSQL
Server 2005
“BI Release”, was released to market early, as many of us are
aware, with resulting limitations in some of its capabilities. One of the
challenges that faced many of us was the absence of an MDX Editor. Those
of us who were comfortable with MDX syntax were not impaired
significantly (although we had to deal with circumstances that accompanied parameterization
in Reporting Services 2000, such as the need to string our queries
for passage from Reporting Services to the Analysis Server, and
the inability to “test generate” our Datasets, once we had parameters
in place within the MDX queries). Those however, who were already challenged
with MDX as a language almost certainly found no amusement in dealing
with the added mechanics.

As we discussed in Part I, Reporting Services 2005
introduced the MDX Query Builder, a tool that satisfies, due to
its “on-off” flexibility, most practitioners who are comfortable writing direct
MDX queries, as well as the MDX – challenged among those authors and
developers who need to work with OLAP data sources. This enhancement,
unsurprisingly, changed the way that many of us had already become accustomed
to writing the underlying MDX queries for our Analysis Services
reports.

Objective and Business Scenario

In Part I, we stated our objective to perform
an extended
examination of parameterization within a copy of an existing sample Reporting
Services 2005
report that we created for this purpose. Our focus, we
stated, was and is to parameterize a specific MDX function, TopCount(),
to demonstrate the process behind supporting parameterization of an
index
value we can supply in this and similar functions at runtime. We stated
that we would concentrate, in this two-part article, upon the passage of our
selection of a value to the function, more than the details of setting
up parameters and so forth in Reporting Services (we cover the
latter in detail in various articles I have written for that specific purpose),
although we do touch upon most of the typical aspects of setting up parameters.

In Part I, we reviewed the basics surrounding
the TopCount() function, and then we began to prepare to perform an
exercise within which we would put the function to work to meet the stated
business requirements of a hypothetical client.

NOTE: To gain the most from completing
the steps of our continuing practice session below, you will have needed to
complete Part I of this article. We will continue where we left off
in the first half, and continue to work with objects that we created in Part I, as we complete the steps of our practice session.
You must complete the preparation and initial practice session in Part I before continuing with the
practice session below.

As we
noted in Basic Set Functions: The TopCount() Function, Parts I and II, both members of my Database
Journal
MDX Essentials series, the TopCount() function allows
us to meet numerous common business needs, including (and especially) those
that require that we return the “top n” members (such as cities or stores),
usually for a given time frame (which might actually be “all time”), based upon
a valid measure (such as total profits, total margin, etc.).

An
example might be the top ten Warehouse Cities for operating year 1997,
based upon total Warehouse Profit for each city.

I emphasized
in Part I of this article that I have found the TopCount() function
to be highly useful in both statistical and financial reporting. The
capability to specify “the top [number of values we request],” for
example, in patient readings, can become highly popular among, say, doctors who
are tracking the effectiveness of new treatments and the like, allowing them to
further explore (via drilldown, drillthrough, etc.) why certain clinics, for
example, are experiencing better patient results than others, once these “top”
clinics are identified. For more information, together with an illustration
that further clarifies the operation of TopCount(), please see Part I.

As we
noted in Part I of this article, and as we shall see in our continuing practice session,
parameterization of the count variable within the TopCount()
function lies at the heart of the procedure we shall undertake within our procedural
example below. We will detail the steps within a sample report, which we have
cloned to save time, focusing largely upon setup of the parameter
mechanism within the primary report dataset, together with its linkage to
the corresponding Report Parameter and elsewhere.

Parameterizing
TopCount()

I
often parameterize the TopCount() function 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 typically assembled 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 “number
of top items
” within a given scenario to an OLAP report. To provide a
report upon which to practice our exercises, we cloned and modified, in Part I, the Sales Reason Comparisons sample report
that, among other samples, accompanies the installation of Reporting
Services 2005
, and which is based upon the ubiquitous Adventure Works cube,
contained within the Analysis Services database named Adventure Works
DW, that comes along with an installation of Analysis Services 2005.

To
restate the scenario we posed in Part I, we will assume that information consumers within
the Marketing office of the Adventure Works organization have expressed
the need for modifications of the existing Sales Reason Comparisons report.
The existing capabilities in the report meet most of their analysis needs, but
recent requirements for a more flexible presentation of the data has 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 have stated that ongoing analysis operations would be dramatically
enhanced if they could simply limit, at report run time, the data returned and
presented in the report to the “top x” number of Sales Reasons,
removing the others to support a specific analysis focus.

Our colleagues have informed
us, again as we summarized in Part I, that the original report needed some other basic
changes (primarily the removal of the existing report parameter, as
well as its underlying support mechanisms, which originally allowed us to
filter products by Product Category, among other minor changes), which
we accomplished within the preparation section of Part I. We also streamlined and regrouped the report with
regard to other considerations: Instead of three measures in the column
axis, we modified the report to display only two, Internet Orders and Internet
Sales Amount
. Moreover, as the client representatives told us that the
report would apply to the Adventure Works organization as a whole, and
would not need to break out measures by Territory Group, we eliminated
this existing grouping within the matrix data region. Finally, we grouped the
measures by Month, instead, so as to present both measures for a
given month / year combination. We then added Year and Month
report parameters
, which would allow information consumers to run the
report “as of” any month / year they choose at runtime.

The modifications we
made in Part I, and that we will continue to
make in this half of the article, will result in a multi-purpose report, the month
/ year
presented by which can be dictated on the fly by the easy, ad hoc
selection
from associated picklists. Moreover, and more along the lines of
our specific focus , the analysts will be able to limit the presentation to a
“top” number (determined at runtime) of Sales Reasons, removing the others to
allow these information consumers to rapidly meet differing conditions and
analysis needs. As is often the case with the addition of parameterization,
the organization will ultimately be able to accomplish more with fewer reports.

For more information
on the TopCount() function from the perspective of our reporting
requirements, as well as for the original specification of the needs as
expressed by our client colleagues, see Mastering
OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount()
Function, Part I
.

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