About the Series ...
This article is a member of the series, MDX Essentials.
The series is designed to provide hands-on application of the fundamentals of
the Multidimensional Expressions (MDX) language, with each tutorial
progressively adding features designed to meet specific real-world needs.
For more information about the series in general, as well as
the software and systems requirements for getting the most out of the member
lessons, please see my first article, MDX at
First Glance: Introduction to MDX Essentials.
Note: Current
updates are assumed for MSSQL Server, MSSQL Server Analysis Services,
and the related Books Online and Samples.
This article also makes use of MSSQL
Server Reporting Services ("Reporting Services"), as a more
robust and full-featured query tool than the MDX Sample Application.
Correct installation of Reporting Services, including current service packs, along with the
applications and conditions upon which Reporting Services relies, is therefore
assumed, as is the fact that you have access and the other rights / privileges
required to complete the steps we undertake in the article. For details on
installation and setup of Reporting Services, see the associated Books
Online, other Microsoft references, and my Database Journal series, MSSQL
Server Reporting Services.
Overview
As practically anyone involved in business
intelligence is aware, multidimensional databases contain aggregated
information to support rapid query processing. The beauty of OLAP (Online
Analytical Processing) is that it stores high-level summaries for virtually
instant delivery to our reports and other applications - summaries that can,
indeed, be assembled directly from an OLTP (Online Transactional Processing)
system, but only after it goes through the work of summing what might be
thousands (or more) transactions to get the same results.
The downside to the otherwise superior
reporting scenario of OLAP lies within its very advantage: aggregation.
An important consideration within the realm of business intelligence is the
provision of the capability of selective focus. More specifically,
within the context of this article, the enterprise needs to be able to see the
underlying transactions once he or she identifies a summary that raises
questions. An example might exist in the case of a real estate portfolio
manager who notes, while looking at monthly performance metrics for a group of
properties, that profit margins for a given property within the group seem
consistently lower than the rest, or perhaps that, over a three-year period, a
property's monthly profit has gradually trended lower, while others remain
stable. Because transactional data is not contained within the OLAP cube, the
manager needs a mechanism to present the underlying transactions (in this case,
the revenues and expense transactions) that make up the margins under
examination. This mechanism, to which the business intelligence community
refers as drillthrough, allows the property manager to see the
transactions that make up the margins, exposing the tenants, vendors, services,
and other entities involved within each, so that action can be taken to
ultimately control results.
MSSQL Server Analysis Services natively
supports drillthrough, assuming that the feature is enabled and configured for
the cube involved, and, as we shall see in this article, drillthrough
from a client application can be accomplished, in the most straightforward
approach, through the passage of the MDX DRILLTHROUGH statement to
Analysis Services. In this lesson, we will
examine the DRILLTHROUGH statement, whose general purpose is to enable a
client application to access specific "details" data housed within
tables that underlie a given balance stored within the cube.
This article will make use of Reporting Services, instead of
the MDX Sample Application upon which we often rely in this series,
because the function we examine requires a more robust application to
demonstrate its operation.
Design limitations in the MDX Sample Application, such as its inability
to handle more than two axes, or to fully leverage certain MDX functions in
general, make the choice of another application unavoidable. The integrated
Microsoft BI solution contains a powerful reporting package, however, which is
available to any organization with an MSSQL Server license, Reporting
Services.
As I
evangelize frequently in my articles, Reporting Services will commoditize
business intelligence. I convert enterprise BI systems such as Cognos, Business
Objects, Crystal, MicroStrategy and others to Reporting Services constantly,
and have begun to witness a high level of interest in doing so among my Fortune
500 clients, and, more recently, within the Education "industry," as
well as other sectors. Because I focus many other articles, within my MSSQL Server Reporting Services series, on OLAP reporting (a specialty of
mine), I constantly use MDX within the articles. Implementing Reporting
Services (even if only as a training tool in environments where other
enterprise BI solutions are in place) is a worthwhile exercise for anyone who
seeks an in-depth understanding of MDX, as I emphasize in articles and
presentations on a recurring basis.
In
this article, we will introduce the DRILLTHROUGH statement, commenting
upon its operation and touching upon examples of effects that we can employ it
to deliver. As a part of our discussion, we will:
-
Prepare for our practice session by creating a project within
Reporting Services, and by creating a "clone" report (based upon
an existing sample OLAP report to save time), within which we will perform our
exercises;
-
Examine how to enable Drillthrough within Analysis
Services;
-
Examine configuration of Drillthrough to present the
appropriate transactional details to its intended users;
-
Examine the syntax surrounding the DRILLTHROUGH statement;
-
Undertake illustrative examples of simple uses of the DRILLTHROUGH
statement in practice exercises;
-
Briefly discuss the results Datasets we obtain in the
practice examples.
» 
Comment and Contribute
