Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 3, 2005

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

By William Pearson

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:


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
    { [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)

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM