MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement

Monday Oct 3rd 2005 by William Pearson
Share:

MSAS Architect Bill Pearson introduces Drillthrough concepts, and then focuses on the use of the DRILLTHROUGH statement within MDX.

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.

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.


Illustration 1: Partial View: Returned Rowsets for a Value in a Query with DRILLTHROUGH

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.

Practice

Preparation: Set up the Reporting Services Environment

For purposes of our practice session, we will create a copy of the Foodmart Sales sample report (one of several report samples that are available for installation with Reporting Services). I often use a "clone" of an existing sample report in my Reporting Services articles because creating a fresh report in each article would mean spending a great deal of time in preparation before getting to the subject matter upon which the article focuses. In addition, creating a clone of the report means we can make changes to our report while retaining the original sample in a pristine state - perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Before we can work with a clone of the Foodmart Sales report, we need to create a Reporting Services project in the Visual Studio.Net 2003 Report Designer environment. Streamlining, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Reporting Services Report Designer environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments. If you already have a project within which you like to work with training or development objects, you can simply skip the Create a Reporting Services Project section.

If the sample FoodMart 2000 Analysis Services database or the Foodmart Sales report was not created / installed as part of the initial installation of the associated application, if either was removed prior to your beginning this article, or if either or both applications have yet to be installed, etc., see the respective Books Online or other documentation for the necessary procedures to prepare for the exercises that follow.

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to, and click, the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

The equivalent on my PC appears as depicted in Illustration 2.


Illustration 2: Beginning in Microsoft Visual Studio .NET 2003 ...

Visual Studio .NET 2003 opens at the Start page.

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 3.


Illustration 3: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

MDX_DRILLTHROUGH

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our input, as depicted in Illustration 4.


Illustration 4: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 5.


Illustration 5: The New Project Appears in the Solution Explorer

Having created a Report Project, we are ready to proceed with creating the new report clone.

Create a Copy of the Foodmart Sales Report

As we have noted, we will be working with a copy of the Foodmart Sales report, to keep the original sample intact for easy access and use at another time.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Add Existing Item from the cascading context menus that appear, as depicted in Illustration 6.

Click for larger image

Illustration 6: Adding the Report to the Project ...

The Add Existing Item - MDX_DRILLTHROUGH dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

3.  Using the Add Existing Item - MDX_DRILLTHROUGH dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - MDX_DRILLTHROUGH dialog, having been pointed to the sample Reports folder (which contains the Foodmart Sales report we seek), appears as partially shown in Illustration 7.


Illustration 7: Navigating to the Sample Reports Folder ...

4.  Right-click the Foodmart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 8.


Illustration 8: Performing a Quick Copy of the FoodMart Sales Report

6.  Within the Add Existing Item - MDX_DRILLTHROUGH dialog, navigate to the MDX_DRILLTHROUGH folder we created earlier, when we added the new MDX_DRILLTHROUGH project.

7.  Right-click somewhere in the white space inside the MDX_DRILLTHROUGH folder, within the dialog.

8.  Select Paste from the context menu that appears, as shown in Illustration 9.


Illustration 9: Select Paste within the New Folder ...

A copy of the Foodmart Sales report appears within the dialog.

9.  Right-click the new file.

10.  Select Rename from the context menu that appears.

11.  Type the following name in place of the highlighted existing name:

MDX_DRILLTHROUGH.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Foodmart Sales sample report appears as depicted in Illustration 10.


Illustration 10: The New Report File, MDX_DRILLTHROUGH.rdl

12.  Click the white space to the right of the file name, to accept the new name we have assigned.

13.  Re-select the new file by clicking it.

14.  Click Open on the dialog box to add the new report to report project MDX_DRILLTHROUGH.

MDX_DRILLTHROUGH.rdl appears in the Reports folder, within the MDX_DRILLTHROUGH project tree in the Solution Explorer, as shown in Illustration 11.


Illustration 11: The New Report Appears in Solution Explorer - Report Folder

15.  From the main menu in the design environment, select File ---> Save All, as depicted in Illustration 12.


Illustration 12: Select File --> Save All to Save Our Work So Far ...

Leaving the Report Designer open, we will now briefly visit Analysis Services to enable drillthrough for the Warehouse sample cube, which we will then target with MDX queries in our practice exercise.

Enable Drillthrough in the FoodMart Warehouse Cube within Analysis Services

Before we can leverage the Drillthrough capabilities of an Analysis Services cube, we must enable the feature from within the Cube Editor. When we enable Drillthrough, we also select the tables and columns that are included in the result set returned by a drillthrough operation. As many of us know, these columns can be from any table in the cube's underlying data source, (and can include columns that are not part of the cube's schema, if set up properly). We can also limit resources consumed by Drillthrough operations (a potentially significant concern with larger, more complex cubes), limiting the number of rows returned for the columns we have selected, when appropriate.

NOTE: For more information on Drillthrough in Analysis Services, see my articles Drilling Through to Details: From Two Perspectives and Mastering Enterprise BI: Create Aging "Buckets" in a Cube (both members of the Introduction to MSSQL Server Analysis Services series) here at Database Journal.

Let's enable Drillthrough for the Warehouse sample cube, taking the following steps.

1.  Open Analysis Manager, beginning at the Start menu.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server.

Our database(s) appear, in much the same manner as shown in Illustration 13.


Illustration 13: Example Databases Displayed within Analysis Manager

NOTE: Your databases / cube tree will differ, depending upon the activities you have performed since the installation of Analysis Services (and the simultaneous creation of the original set of sample cubes). Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The cubes appear as depicted in Illustration 14.


Illustration 14: Example Cubes within the FoodMart 2000 Analysis Services Database

6.  Right-click the Warehouse cube within the FoodMart 2000 Analysis Services database.

7.  Select Edit... from the context menu that appears, the relevant portion of which is shown in Illustration 15.


Illustration 15: Opening the Cube Editor

The Cube Editor opens.

8.  Select Tools --> Drillthrough Options... from the main menu inside the Cube Editor, as depicted in Illustration 16.


Illustration 16: Select Tools --> Drillthrough Options ...

The Cube Editor opens.

The Cube Drillthrough Options dialog appears.

9.  Click the check box labeled Enable drillthrough (atop the dialog) to check it.

10.  Within the selection checklist, on the Columns tab, ensure that only the checkboxes presented in Table 1 below are checked:

Column

Table

"warehouse_sales"

"inventory_fact_1997"

"day_of_month"

"time_by_day"

"warehouse_name"

"warehouse"

"warehouse_city"

"warehouse"

"warehouse_state_province"

"warehouse"

"warehouse_country"

"warehouse"

"product_id"

"product"

"brand_name"

"product"

"product_name"

"product"


Table 1: Select Settings for the Detail Drillthrough View...

The Cube Drillthrough Options dialog appears as partially shown in Illustration 17.


Illustration 17: The Cube Drillthrough Options Dialog (Partial View) with Settings

Our intent here is to establish a simple display of dated transactions - to give us a feel that transactions within reasonable date ranges are, indeed, underneath the totals we see in the value upon which we can drill through, Warehouse Sales. The columns selected here could obviously be varied to accomplish other specific needs, just as easily, and would apply to any measure in the cube.

11.  Click OK to accept settings.

The Drillthrough Settings message box appears, alerting us to the fact that our settings take effect only after the cube is saved, as depicted in Illustration 18.


Illustration 18: Drillthrough Settings Message Box

12.  Click OK to dismiss the message box, and to close the Cube Drillthrough Options dialog.

13.  In the upper left corner of the Cube Editor (top of the cube tree), right-click the Warehouse cube.

14.  Select Process Cube ... from the context menu that appears, as shown in Illustration 19.


Illustration 19: Select Process Cube ...

15.  Click Yes on the Save the cube dialog that appears, to save the cube, as depicted in Illustration 20.


Illustration 20: Save the Cube Before Processing ...

The Process a Cube dialog appears next. We will select Full processing, simply to ensure that we are all in sync going forward.

16.  Click the radio button to the left of the Full Process label, as shown in Illustration 21.


Illustration 21: Select Full Process ...

17.  Click OK to begin Full processing of the Warehouse cube.

Processing begins immediately, and the Process viewer appears, displaying the various logged events of the processing cycle. Processing completes, and the viewer presents the green Processing completed successfully message, as depicted in Illustration 22.


Illustration 22: Processing Completes Successfully as Indicated on the Process Viewer

18.  Click Close to dismiss the viewer.

Now that we have enabled drillthrough for the Warehouse cube, we are ready to examine running queries against it that leverage the capability. We will get some hands-on practice with this in the next section.

Procedure: The DRILLTHROUGH Statement in Action

Now that we have enabled and configured the drillthrough capabilities supported by Analysis Services, we can look at leveraging DRILLTHROUGH. Whether we seek to use DRILLTHROUGH within a Reporting Services Dataset, or within other client applications, the concept is basically the same. Recall that the flow of data in populating a cube is 1) from the underlying operational (typically relational) database to 2) an OLAP-supporting star schema (or hybrid), also within a relational database, and finally to 3) an OLAP cube in Analysis Services. While there is no physical evidence of an OLTP layer in the FoodMart samples (the FoodMart 2000 Database sample acts as the star-schema level), a conceptual diagram of the dataflow in the context of the Warehouse cube might resemble that shown in Illustration 23.


Illustration 23: Conceptual Data Flow Resulting in the Warehouse Cube

It is important to realize that when we perform a drillthrough, Analysis Services does not retrieve the source rows from the underlying OLTP, but reaches no further than the Data Mart, where it returns the rows making up the total upon which we drill from the star schema. This obviously means that the "transaction detail" that many expect drillthrough to provide is not "transactional" at all, and is only as granular as the star schema containing it. While there are ways to return actual relational transactional detail, the native drillthrough capability within Analysis Services - which is leveraged by the DRILLTHROUGH statement in MDX - drills through to the supporting rows in the star schema, and not beyond.

To reinforce our understanding of the basics we have covered so far, we will use the DRILLTHROUGH statement after first constructing a query, which we execute without it. This will make clear exactly what is happening: a single value is being drilled through upon, to display the underlying rowsets that exist within the underlying data mart. This will afford us a verifiable result that we can easily see is correct, in that the values in the rowsets displayed will add up to the total from which we have launched the drillthrough action.

We will establish a simple scenario that places DRILLTHROUGH within the context of meeting a business requirement similar to one we might encounter in our respective daily environments. The intent is, of course, to demonstrate the operation of the DRILLTHROUGH statement in a straightforward, memorable manner.

Let's turn to Reporting Services as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. Keep in mind that our interaction within this article with Reporting Services will be limited to using it as a Dataset generator, not as a reporting application - we carry these concepts into report construction in detail in the upcoming MSSQL Server Reporting Services article we have already referenced, Mastering OLAP Reporting: Drilling Through Using MDX.

1.  Return to the Report Designer in the Reporting Services development environment, ensuring that we are within the MDX_DRILLTHROUGH project, where we left it in our preparation phase earlier.

2.  Double-click the new MDX_DRILLTHROUGH report (MDX_DRILLTHROUGH.rdl) we created earlier in the Solution Explorer to open the report.

The MDX_DRILLTHROUGH report opens, defaulting to the Layout tab.

3.  Click the Data tab, to arrive at the primary Dataset for the existing report, ProductData, as depicted in Illustration 24.


Illustration 24: The Data Tab, Displaying the Existing ProductData Dataset

4.  Using the Dataset selector, select <New Dataset...>, as shown in Illustration 25.


Illustration 25: Creating a New DataSet ...

The New Dataset dialog appears. It is here that we will create a new Dataset in which to practice with our queries. Although we will not go further than practicing with the queries in this article, we could use the Dataset we create to support a report in Reporting Services.

5.  Type the following into the Name box of the dialog:

MDX036_DRILLTHROUGH 

6.  Ensure that FoodMart 2000 is selected in the Data source box.

One of the reasons I chose to create a project surrounding the FoodMart Sales report was the fact that the report contains an embedded Data Source for the FoodMart 2000 Analysis Services database. This means that, although we will be targeting a different cube than the existing report does, we can still reference the existing Data Source, rather than creating a new one, to save preparation time.

7.  Leave Command type at the default of Text.

The New Dataset dialog appears as depicted in Illustration 26.


Illustration 26: New Dataset Dialog, with Our Additions

8.  Click OK to accept our input and to close the New Dataset dialog.

The blank Data tab for the new MDX036_DRILLTHROUGH Dataset appears.

Let's assume, for our practice example, that we have received a call from the Reporting department of the FoodMart organization, requesting our assistance in meeting a specific report presentation need, based upon data the Warehouse sample cube. A group of report authors wants to build a report set that allows the ultimate consumer to drill through from a total value to the underlying values within the Data Mart. In this specific instance, the authors ask us to create an example, which they can later extrapolate to other products: they wish to see the Warehouse Sales value for a specific beverage product, Good Imported Beer, for the year 1997. This value will provide the basis for a subsequent drillthrough action, so that the authors can see how to support drillthrough capabilities, where the underlying data rowsets that make up the total value, can be displayed by the end consumer if he / she wishes to delve further into the composition of the value.

This represents a simple, yet practical, need that we can readily answer using the DRILLTHROUGH statement in conjunction with a base query, which we will first construct to retrieve the total value of 1997 Warehouse Sales for the indicated product.

Let's construct the simple base query, therefore, to return the requested Warehouse Sales information.

9.  Type (or cut and paste) the following query into the query pane of the Data tab:


-- MDX036-01  Base Query to Return Summary Value
SELECT
    { [Measures].[Warehouse Sales] } ON AXIS (0),
    {[Product].[All Products].[Drink].[Alcoholic Beverages]. 
        [Beer and Wine].[Beer].[Good].[Good Imported Beer] } ON AXIS (1),
    {[Time].[1997]} ON AXIS (2)
FROM 
    [WAREHOUSE]

10.  Execute the query by clicking the Run Query button in the toolbar, shown in Illustration 27.


Illustration 27: Click the Run Button to Execute the Query

The Results pane is populated by Analysis Services, and the Dataset depicted in Illustration 28 appears.


Illustration 28: Results Dataset (Compressed) - Total 1997 Warehouse Sales, Single Product

We see the data we have requested appear in the Results pane, in a manner that might seem confusing to those of us who are familiar only with the MDX Sample Application, the querying tool we use in most of the articles of the MDX Essentials series. The MDX Sample Application returns a cellset, which looks different from the recordset format returned by Reporting Services. Reporting Services flattens the data from Analysis Services into a two-dimensional grid as a part of converting the cellset to a recordset. As an example, in our immediate instance, the flattening process separates the Product dimension into each of its levels, creating the separate fields we see in our example for each of the levels. For that matter, each level of each dimension in a query is returned in its own separate field.

NOTE: Again, always keep in mind that dimensions that we do not specify assume the default values as set in the cube.

We see that the total 1997 Warehouse Sales for Good Imported Beer was approximately $ 195.61. Now, let's take a look at modifying the query to add the DRILLTHROUGH statement, and to see the results that we obtain.

11.  Modify the top line in the query (the commented line, beginning with "--MDX036-01") to the following:

-- MDX036-02  Query with DRILLTHROUGH statement added

12.  Just underneath the new comment line we have modified, and above the SELECT keyword, add the DRILLTHROUGH keyword.

The query now appears as shown in Illustration 29.


Illustration 29: The Query with the DRILLTHROUGH Statement Added

13.  Execute the query by clicking the Run Query button in the toolbar, once again.

The Results pane is populated by Analysis Services, and the Dataset depicted in Illustration 30 appears.


Illustration 30: Results Dataset (Compressed) - DRILLTHROUGH on Total Warehouse Sales

The supporting rowsets appear, the columns appearing as defined in our preparation steps in the Cube Editor earlier in this article. A quick examination of the details enable us to conclude that the Warehouse Sales values that appear indeed add up to the total Warehouse Sales value (approximately $ 195.61) of the simple select query upon which the DRILLTHROUGH statement is applied.

14.  Select File --> Save All on the main menu to save our work in the Reporting Services development environment, as desired.

15.  Close the development environment by Selecting File --> Exit, when desired.

Summary ...

In this article, we explored the MDX DRILLTHROUGH statement, commenting upon its operation and touching upon examples of effects that we can employ it to deliver. In preparation for our examination of DRILLTHROUGH, we created a project within Reporting Services, and by creating a "clone" report (based upon an existing sample OLAP report to save time), to automatically "bring forward" a data source connection that we could readily use. We then completed preparation by examining the steps involved in enabling and configuring Drillthrough within Analysis Services.

We next examined the syntax surrounding the DRILLTHROUGH statement. We then undertook illustrative examples of simple uses of the DRILLTHROUGH statement in practice exercises, beginning with a simple primary query that isolated a measure of interest to a group of hypothetical report authors, who also provided details surrounding a business need to drill through from the measure concerned to its underlying data mart components. Finally, we briefly discussed the results Datasets we obtained in the practice examples.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved