Actions in Analysis Services 2005: The Drillthrough Action

Monday Nov 13th 2006 by William Pearson
Share:

BI Architect Bill Pearson provides in-depth coverage of the Drillthrough Action in Analysis Services 2005. Our hands-on exposure to Drillthrough Action creation and operation includes a focus upon the MDX Condition expression as a means of restricting Drillthrough Action availability.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services ("Analysis Services"), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portion of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

About the Mastering Enterprise BI Articles ...

The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel – or outstrip - those found in the more "mature" enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost.

The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among other enterprise BI vendors, to date, represents a serious "undersell" of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the Microsoft integrated solution will commoditize business intelligence.

For more information about the Mastering Enterprise BI articles, see the section entitled "About the Mastering Enterprise BI Articles" in my article Relative Time Periods in an Analysis Services Cube, Part I.

Introduction

In Actions in Analysis Services 2005: An Introduction we overviewed the general types of Actions with which we, as Analysis Services developers, can support information consumers. We discussed examples of consumer needs that might be classified within each of the general Action types, as well as reviewing the points of interaction at which we can place "triggers" to allow analysts and information consumers a means of accessing valuable, but often external, information in a non-disruptive manner. We stated in Actions in Analysis Services 2005: An Introduction that we would extend our introduction to individual examinations of specific Action types in separate articles. Individual articles, we reasoned, would afford us the time and focus required to examine each type in detail, from a perspectives of creation and maintenance. This article will focus upon the new Drillthrough action that makes its debut, along with many other new features, in Analysis Services 2005.

In this article, we will gain some familiarity with the Drillthrough action – how it works and some of its capabilities - and then get some hands-on exposure to creating a Drillthrough action within the sample AdventureWorks development environment, which can be installed with MSSQL Server 2005. Our examination of the Drillthrough Action in this article will include:

  • An introduction to the Drillthrough Action, including an overview of its uses and the data it presents;
  • A hands-on practice exercise, wherein we set up a working Drillthrough Action, based upon the sample Adventure Works Analysis Services database;
  • A discussion regarding the various settings that we exploit in the design and creation of our Drillthrough Action;
  • Special focus upon the use of an MDX Condition expression to restrict the availability of a Drillthrough Action;
  • Verification of the effectiveness of our new Drillthrough Action, from the tandem perspectives of availability of the Action and operation of the Action, in meeting the business requirements of a hypothetical client.

The Drillthrough Action

Overview and Discussion

As we noted in Actions in Analysis Services 2005: An Introduction, Drillthrough Actions provide a means for an information consumer to view the fact table rows that underpin an aggregated value within a cube cell. This special Action type debuts in Analysis Services 2005, improving the more rudimentary MDX approach to drillthrough that we knew in Analysis Services 2000. (See my article Drilling Through to Details: From Two Perspectives for a discussion of drillthrough options in the previous version). Drillthrough Actions targets are always cells (single or multiple), in conjunction with specific Measure Groups. (For more on Measure Groups, and how they are associated, see my article Mastering Enterprise BI: Working with Measure Groups, also a member of the Introduction to MSSQL Server Analysis Services series.)

The Drillthrough Action is based upon the Rowset Action, in that it returns fact table details in a rowset. (It is important to keep in mind that the rows come from the fact table, and not from the originating relational database – when we say that Drillthrough makes it possible to see the transactions underlying a given aggregation, we don't mean the original transactions per the OLTP. There are other ways to accomplish a "drillthrough to the OLTP.")

We will create an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in other articles of this series, the Analysis Services Project that we create within the Studio will assist us in organizing and managing the numerous objects that we will need to support our work with an Analysis Services database.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 for use with Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Hands-On Procedure

We will get some hands-on experience with our subject matter in a practice session. We will first need to prepare for our exercises by creating an Analysis Services project, within which to work from the Business Intelligence Development Studio. We will rely heavily upon samples that ship with MSSQL Server 2005, to minimize the preparation time required to create a working practice environment. This "shortcut" will afford anyone with access to the installed application set and its samples an opportunity to complete the steps in the practice session.

Preparation

If you prefer to work within an existing copy of the Adventure Works Analysis Services project (perhaps you have already made a copy for work with previous articles), or you intend to create a new, pristine copy of the original from the CDs or another source for this (and possibly other) purposes, please feel free to skip the related preparatory sections.

Create a New Analysis Services Project within a New Solution

For purposes of our practice session, we will create a copy of the Adventure Works Analysis Services project, one of several samples that are available with (albeit installed separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a "clone" of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Analysis Services and other components of the Microsoft integrated business intelligence solution in general.

To create a copy of the sample Adventure Works Analysis Services project, please see the following procedure in the References section of my articles index:

Make a Copy of an Analysis Services Sample Project within a New Solution

Ascertain Connectivity of the Relational Data Source

Let's ensure we have a working data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone (the default for the Adventure Works DW project sample's connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default).

If you do not know how to ascertain connectivity of the relational data source, please perform the steps of the following procedure in the References section of my articles index:

Ascertain Connectivity of the Relational Data Source

Procedure

Work with a Drillthrough Action

Create a Drillthrough Action

Let's first create a Drillthrough Action within our new environment, to get some experience with the process. As is the case with other Action types, we create and maintain Drillthrough Actions via the Actions tab of the Cube Designer. For purposes of our practice session, we will say that we have been asked by the Adventure Works organization to add a Drillthrough Action to the Adventure Works cube. The client representatives with whom we are working have told us that they would like to create a Drillthrough Action to prototype a specification, which will be used in conjunction with a client application that is currently being designed. The application's developers have decided to use the Drillthrough Action within the client application, and wish to see a working model in place, so as to help determine the specifications for their interface. We agree that building an Action for this purpose will be a great way to get started, and then ask for the details from the client representatives with which we are working.

Our client colleagues tell us that the business requirement is for the drillthrough to present details of Reseller transactions. The Action, to be called Reseller Sales Information (to differentiate it from another, similar Drillthrough Action already present within the sample Unified Dimension Model (UDM), will be established for a specific intended audience, who will need to be able to see, for the aggregated Reseller Sales Amount and Reseller Order Quantity values within the cube, the following details (to mean fact table entries) making up those totals:

  • Employee involved with the Reseller;
  • The Product, Product Category and Product Subcategory;
  • Reseller and Reseller's Business Type;
  • Respective Calendar Quarter of Product Shipment.

Moreover, the client representatives have told us that they wish to enforce a reasonable limit for number of rows that can be returned by a consumer in a given drillthrough event (they wish to begin with a limit of 2,500 rows). They also ask that we "disable" the Drillthrough Action for the Accessories Category of their Products offerings, to eliminate the high volume of details that the category will contain for relatively low-priced items.

We listen carefully to the requirements, and, once we confirm our understanding of the need, we set out to create a Drillthrough Action, taking the following steps:

1.  Inside the Solution Explorer, right-click the AdventureWorks cube within the Cubes folder of our project.

2.  Select Open from the context menu that appears, as depicted in Illustration 1.


Illustration 1: Opening the Cube Designer ...

The Cube Designer opens for Adventure Works. Cube, defaulting to the Cube Structure tab.

3.  Click the Actions tab.

The Actions tab opens.

4.  Select Cube --> New Drillthrough Action from the main menu, as shown in Illustration 2.


Illustration 2: Select Cube --> New Drillthrough Action

The Actions tab changes to reflect the settings for a Drillthrough Action, as the Drillthrough Action Form Editor opens. A default Name of "Drillthrough Action" appears in the top and center portion of the tab.

5.  Replace the default Name with the following:

Reseller Sales Information

6.  Select Reseller Sales in the Measure group members selector.

Here we are simply selecting the Measure Group with which our Drillthrough Action is to be associated.

7.  Type the following into the box labeled Condition (Optional) immediately underneath the Measure group members selector:

[Product].[Product Categories].CURRENTMEMBER <> [Product].[Product Categories].[Category].[Accessories]

Here we are entering an MDX expression, which will act to further restrict availability of the Action (not to restrict the Drillthrough details returned, as we shall see). What we are basically saying with the condition is "make the Action available for selection, when the Product Category is not Accessories, for the measures defined. In the business requirement we outlined earlier, the client representatives have specified that they wish to exclude the Accessories Category, to filter out the high volume of (lower dollar value) details that the category contains.

8.  Select MEASURES in the Dimensions column of the Drillthrough Columns table.

9.  Check the boxes to the immediate left of each of Reseller Sales Amount and Reseller Order Quantity, within the selector of the Return Columns column, as depicted in Illustration 3.


Illustration 3: Selecting Return Columns by Checking the Boxes to the Left ...

10.  Click OK to accept the selections.

We see the two measures populate the top row of the Return Columns column of the table.

11.  In like manner, make the selections detailed in Table 1 in the respective remaining rows of the table:

Dimensions

Return Columns

Employee

Employee

Product

Product, Category, Subcategory

Reseller

Reseller, Business Type

Ship Date

Calendar Quarter of Year


Table 1: Selections to Add to the Drillthrough Columns Table

In the Drillthrough Columns table, we are defining what attribute or measure data will appear in the Data Sample Viewer in the Cube Browser, as we shall see in the next section.

12.  If required, click the down arrows to the immediate left of Additional Properties, just underneath the Drillthrough Columns table, to expand the properties into view.

13.  Type the number 2500 into the Maximum Rows input box.

We are limiting the data returned to 2500 rows in this example, although we might have left the setting blank – or set it to zero – to allow retrieval of all rows specified within the definition of the Action.

The remaining settings in this section relate more to client considerations, such as when and how the Action should be carried out, recommendations to the client regarding which of its applications might be most likely to use the Action, the association of appropriate icons alongside the Action name in context (and other) menus, descriptions and captions, and so forth. The Actions tab – Drillthrough Action Form Editor appears, with our input, as shown in Illustration 4.


Illustration 4: Actions Tab – Drillthrough Action Form Editor with Our Settings

We are ready to accept our settings, deploy the project, and then move to the Cube Browser, where we can inspect the results of our handiwork.

14.  Deploy the DBJ AdventureWorks DW project.

NOTE: If you do not know how to ascertain alignment of the project to the destination server, and / or to deploy the project, please perform the steps of the following procedure in the References section of my articles index:

Deploy the Analysis Services Project

Verification

Inspect the Operation of the Drillthrough Action within the Cube Browser

Once the deployment of the project has been accomplished, we can fully leverage the Browser that the Cube Designer makes available to us. We will do so by taking the following steps:

1.  Click the Browser tab to initialize the Browser for the Designer, as depicted in Illustration 5.


Illustration 5: Click the Browser Tab ...

2.  Once the Browser loads, click the Reconnect button, as shown in Illustration 6.


Illustration 6: Click the Reconnect Button ...

Let's set up a Browse scenario that presents many of the details that our client colleagues have specified that they want to see. The Browser is an extremely convenient place to accomplish this and other examinations. (More in-depth details about use of the Browser, a formidable tool within itself, are developed within other articles of this series, whose objectives, typically within "verification" procedures, are to examine or analyze values and other details within the cube.)

3.  Within the cube tree appearing inside the Metadata pane, expand Measures.

4.  Expand the Reseller Sales folder. as depicted in Illustration 7.


Illustration 7: Exposing Measures within the Cube Tree

5.  Drag the Reseller Sales Amount measure, followed by the Reseller Order Quantity measure, to the area of the Browser canvas marked Drop Totals or Detail Fields Here, as shown in Illustration 8.


Illustration 8: Dragging the Measures to the Browser Canvas ...

6.  Once again, inside the Metadata pane, expand the Employee folder.

7.  Right-click the Employee attribute hierarchy that appears in the expanding tree.

8.  Select Add to Row Area from the context menu that appears.

The expanded Employee attribute hierarchy appears within the browser's row axis, as depicted in Illustration 9.


Illustration 9: Adding Employee Attribute Hierarchy to the Cube Browser Row Axis

9.  Expand the Product folder within the Metadata pane.

10.  Expand the Category attribute hierarchy that appears within the expanded Product folder.

11.  Right-click the Category level that appears within the expanded Category attribute hierarchy.

12.  Select Add to Row Area from the context menu that appears.

Category appears, collapsed, within the browser's row axis, to the immediate right of the expanded Employee members (for each of which drilldown has become enabled, as evidenced by the "plus" ("+") icon to its immediate left), as shown in Illustration 10.


Illustration 10: Nesting Category into the Row Axis ...

13.  Once again, inside the Metadata pane, expand the Ship Date folder.

14.  Expand the Calendar folder that appears.

15.  Expand the Ship Date.Calendar hierarchy that appears within the expanded Calendar folder, as depicted in Illustration 11.


Illustration 11: Getting to the Calendar Quarter Level ...

16.  Right-click the Calendar Quarter level that appears.

17.  Select Add to Column Area from the context menu that appears.

Calendar Quarter appears, with the individual quarters appearing nested (and collapsed) underneath, within the browser's column axis.

18.  Expand the Reseller folder within the Metadata pane.

19.  Right-click the Reseller Type hierarchy that appears within the expanded Reseller folder.

20.  Select Add to Column Area from the context menu that appears.

The Business Type level of the Reseller hierarchy appears, with the subordinate Reseller level to its immediate right within the browser's column axis. The two newly added levels appear, collapsed, to the immediate right of the expanded Calendar Quarter level, as shown in Illustration 12.


Illustration 12: The Column Axis with Our Additions (Partial View)

Having set up the axes as desired, we are ready to take a look at two primary Drillthrough Action considerations, availability and operation. First, let's examine when the Action is available, and when it is "disabled," to ascertain that its behavior is in accordance with our efforts to meet the client business requirements we have defined.

21.  In the row axis of the browser, expand Employee member Jillian Carson by clicking the "+" sign to its immediate left.

The four Product Categories and a Total appear nested within the drilled down Employee.

22.  In the Reseller Sales Amount column, to the immediate right of the Accessories Category for Jillian Carson, right click the cell containing the value.

The context menu that appears contains a single Action, named Reseller Details, as depicted in Illustration 13.


Illustration 13: A Sole Action, Reseller Details, is Available for Accessories ...

The fact that we do not see our new Drillthrough Action, Reseller Sales Information, provides instant confirmation that our Condition within the Action definition is effective. Recall that the purpose of the Condition was to render the Action unavailable for the Reseller measures as they relate to the Accessories Category; we imposed this Condition in response to the expressed business requirement of our client colleagues to "disable" the new Drillthrough Action for the Accessories Category. (The same results would be noted on the context menu for the other measure, Reseller Order Quantity, within the context of the Accessories Category.)

Next, let's verify that the Reseller Sales Information Drillthrough Action is available for non-Accessories Categories.

23.  In the Reseller Sales Amount column, once again, this time to the immediate right of the Bikes Category for Jillian Carson, right-click the cell containing the value.

The context menu that appears this time contains our new Reseller Sales Information Action, (along with the Reseller Details Action previously noted), as shown in Illustration 14.


Illustration 14: The Reseller Sales Information Action is Available ...

This appears to confirm that our new Drillthrough Action is available for the selected Reseller Sales Amount value, as we intended.

24.  Finally, right-click the Reseller Order Quantity value, in the cell to the immediate right of the Reseller Sales Amount column we currently occupy.

Our new Drillthrough Action, Reseller Sales Information, appears, once again. We have therefore verified that the availability requirement for the Action we have created is met. At this point, we will verify the operation of the new Drillthrough Action by initiating a drillthrough from our current position.

25.  Click Reseller Sales Information on the context menu.

Our click initiates a Drillthrough Action, upon the Reseller Order Quantity recorded for the Bikes Product Category, for the third Calendar Quarter of 2001.

We experience a brief pause (the duration of which, unsurprisingly, depends upon machine resources and other factors), and then the Data Sample Viewer appears, as depicted in Illustration 15.


Illustration 15: The Data Sample Viewer Appears

A close examination of the Data Sample Viewer reveals that the columns it contains match those we specified within the Drillthrough Columns table on the Cube Designer Actions tab earlier.

We verify, therefore, that our efforts to meet the requirements of our client colleagues have been successful.

26.  Click Close to dismiss the Data Sample Viewer.

27.  Further inspect the availability and operation of the Reseller Sales Information Drillthrough Action, performing additional browses, as desired.

28.  Select File --> Save All from the main menu, to save our work through this point, as shown in Illustration 16.


Illustration 16: Saving All Work from Our Session

29.  Select File --> Exit, when ready, to leave the Business Intelligence Development Studio.

Conclusion

In this article, we explored Drillthrough Actions, a new and improved means of analyzing the values that make up the aggregations presented within our Analysis Services cubes. Our focus upon the Drillthrough Action included an introduction to its features, capabilities and possible uses. We then obtained some hands-on exposure to this new Action type, creating a Drillthrough Action within the sample AdventureWorks development environment that can be installed with MSSQL Server 2005.

As a part of our practical examination of the Drillthrough Action, after an overview of its uses and the data it presents, we focused upon the use of an MDX Condition expression to restrict its availability within the Cube Browser, and, by extension, within client applications which a Drillthrough Action might be created to support. Having concluded an examination of the settings involved in creating our working Drillthrough Action, we verified, within the Cube Browser, the effectiveness of the new Drillthrough Action, from the tandem perspectives of availability of the Action and operation of the Action, in meeting the business requirements of a hypothetical client.

» 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