MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement - Page 2

The DRILLTHROUGH Statement

Introduction

According to the Books Online, the DRILLTHROUGH statement "retrieves the source rowset(s) from the fact table (that is, data source) for a specified tuple." Used in combination with the MDX SELECT keyword, the statement allows a client application (including OLAP reporting applications, such as Reporting Services) to take advantage of DRILLTHROUGH. DRILLTHROUGH is used in combination with a SELECT statement that specifies a cell in a cube, and makes possible the retrieval of the rowset(s) that make up the value in the targeted cell.

As we will discuss in the practice exercise, the retrieved data is only as granular as the data in the underlying data source (data mart, etc.). DRILLTHROUGH provides modifiers, as we shall see in the Syntax section, with which we can limit the data returned.

We will examine the syntax surrounding the DRILLTHROUGH statement after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a simple scenario, constructed to support a hypothetical business need that illustrates uses for the function. This will afford us an opportunity to explore the manner in which the DRILLTHROUGH statement operates. Hands-on practice with DRILLTHROUGH, where we will create a simple SELECT query, and then apply the DRILLTHROUGH statement to see how it alters the output of the initial query, will help us to activate what we learn in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of its operation, the DRILLTHROUGH statement, when acting in conjunction with an MDX SELECT query, retrieves the rows of the underlying fact table that contribute to the total that appears in the cube cell upon which the drillthrough is performed. The effective use of DRILLTHROUGH in conjunction with our queries demands the satisfaction of a couple of requirements that, given an understanding of OLAP in general and the purpose and nature of drillthrough in particular, make ready sense. First, we can only use DRILLTHROUGH to perform drillthrough upon real measures - that is to say, we cannot drill through on calculated measures. Second, DRILLTHROUGH operates in a way such that it retrieves the underlying fact table rowsets for a tuple that we specify; this, as many of us are aware, means that one - and only one - member can be specified for each axis named within the query.

While the level position of the cell specified for drillthrough (whether it be at the highest hierarchical level, the lowest level, or at a level in between) is the basic determinant of how many rowsets are returned, the DRILLTHROUGH statement also provides us with a couple of additional options in managing the number of rowsets returned. The MAXROWS and FIRSTROWSET modifiers, the name of each of which indicates somewhat the effects it produces, are available to help us to exercise the degree of control that is appropriate to our business need. The modifiers might be more useful in some client applications than others might; within a reporting context, their uses would often be limited.

The DRILLTHROUGH statement can deliver a wide range of analysis and reporting utility, as we see in an upcoming article where I use it as an option for generating a drillthrough report in Reporting Services (LBC See Mastering OLAP Reporting: Drilling Through Using MDX in my Database Journal MSSQL Server Reporting Services series). As in so many cases with the Microsoft integrated business intelligence solution, consisting of MSSQL Server, Analysis Services and Reporting Services, this statement, just like many MDX functions we discuss in this series, residing within the Analysis Services layer, can be extended to support capabilities and attributes in the Reporting Services layer. Knowing "where to put the intelligence" among the various layers is critical to optimization, in many cases. For more of my observations on this subject, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

Let's look at some syntax illustrations to further clarify the operation of the DRILLTHROUGH function.

Syntax

Syntactically, anytime we employ the DRILLTHROUGH statement, we simply precede the query specifying the targeted cell with the DRILLTHOUGH keyword. The statement therefore takes the cell specified by the SELECT statement as the value for which it is to return the underlying rowsets. The general syntax is shown in the following string:

DRILLTHROUGH
[MAXROWS <Positive Number>] [FIRSTROWSET <Positive Number>]
<<MDX SELECT QUERY >>

As we noted earlier, the DRILLTHROUGH function can leverage one of two modifiers, positioned syntactically as shown above. The MAXROWS modifier limits the rows to a maximum number we specify, while FIRSTROWSET specifies which of the rows to return first. We will not work with these further, within the context of the article, but be aware that they exist as options for specifying limitations on the rowsets retrieved using DRILLTHROUGH.

A sample query using the DRILLTHROUGH function, based upon a query selecting a cell in the Sales sample cube, follows:

DRILLTHROUGH
SELECT
   { [Measures].[Store Sales]} on AXIS (0),
   { [Product].[Food].[Frozen Foods]} on AXIS (1),
   { [Time].[Year].[1997] } on AXIS (2)
FROM 
   [SALES]

Here, we are applying drillthrough to the value of Store Sales appearing at the intersect of Frozen Foods and 1997 (along with the default settings on the dimensions of the cube which we do not specify - an important consideration). The rowsets retrieved might resemble those partially shown in Illustration 1.

The columns that appear are a function of the selections we make when we enable drillthrough for the cube within the cube editor, a procedure that we will examine in our hands-on preparation and practice in the following section.