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

October 3, 2005

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers