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 Nov 13, 2006

Actions in Analysis Services 2005: The Drillthrough Action - Page 2

By William Pearson


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:


Return Columns




Product, Category, Subcategory


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:

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