MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement

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.
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