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: