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.
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.
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.
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:
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:
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:
Table 1: Selections to Add to the Drillthrough Columns Table
Product, Category, Subcategory
Reseller, Business Type
Calendar Quarter of Year
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:
the Operation of the Drillthrough Action within the Cube Browser
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:
Click the Browser
tab to initialize the Browser for the Designer, as depicted
in Illustration 5.
Illustration 5: Click
the Browser Tab ...
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.)
cube tree appearing inside the Metadata pane, expand Measures.
Expand the Reseller
Sales folder. as depicted in Illustration 7.
Illustration 7: Exposing
Measures within the Cube Tree
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: Dragging
the Measures to the Browser Canvas ...
inside the Metadata pane, expand the Employee folder.
the Employee attribute hierarchy that appears in the expanding tree.
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
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
15. Expand the Ship Date.Calendar hierarchy
that appears within the expanded Calendar folder, as depicted in Illustration
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
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
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
Illustration 13: A Sole
Action, Reseller Details, is Available for Accessories ...
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.)
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 ...
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
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
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.
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.