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 Drillthroughwithin Analysis Services;
- Examine configuration of Drillthroughto present the appropriate transactional details to its intended users;
- Examine the syntax surrounding the DRILLTHROUGHstatement;
- Undertake illustrative examples of simple uses of the DRILLTHROUGHstatement in practice exercises;
- Briefly discuss the results Datasets we obtain in the practice examples.
