Defining and Using a Drillthrough Action

In Lesson 5 in this tutorial, you learned to define a fact dimension so that users can dimension measures by the members of the fact dimension and return details about specific fact measures, such as order detail information. Dimensioning fact data by a fact dimension without correctly filtering the data that the query returns can cause slow query performance for all users, which unnecessarily frustrates users. You can eliminate this frustration by defining a drillthrough action to return the same kind of information but to restrict the total number of rows that are returned, which will significantly improve query performance fro all.

In the tasks in this topic, you define a drillthrough action to return order detail information for sales to customers over the Internet.

Defining the Drillthrough Action Properties

To define the drillthrough action properties

1. In Cube Designer for the Analysis Services Tutorial cube, click the Actions tab.

   The Actions tab includes several panes. On the left side of the tab are an Action Organizer pane and a Calculation Tools pane. The pane to the right of these two panes is the Display pane, which contains the details of the action that is selected in the Action Organizer pane.

   The following image shows the Actions tab of Cube Designer.

   

2. On the toolbar of the Actions tab, click New Drillthrough Action.

   A blank Action template appears in the Display pane.

   

3. In the Name box, change the name of this action to Internet Sales Details Drillthrough Action.

4. In the Measure group members list, select Internet Sales.

5. In the Drillthrough Columns box, select Internet Sales Order Details in the Dimensions list.

6. In the Return Columns list, select the Item Description and the Order Number check boxes, and then click OK. The following image shows the Action template as it should look at this point in this procedure.

   

7. Expand the Additional Properties box, as shown in the following image.

   

8. In the Maximum Rows box, type 10.

9. In the Caption box, type Drillthrough to Order Details….

   These settings limit the number of rows returned and specify the caption that appears in the client application menu. The following image shows these settings in the AdditionalProperties box.

   

Using the Drillthrough Action

To use the drillthrough action

1. On the Build menu, click Deploy Analysis Services Tutorial.

2. When deployment has successfully completed, click the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

3. Remove all hierarchies and measures from the Data pane and all dimension members from the Filter pane.

4. Add the Internet Sales-Sales Amount measure to the data area.

5. Add the Customer Geography user-defined hierarchy from the Location folder in the Customer dimension to the Filter pane.

6. In the Filter Expression list, expand All Customers, expand Australia, expand Queensland, expand Brisbane, expand 4000, select the check box for Adam Powell, and then click OK.

   The total sales of products by Adventure Works Cycles to Adam Powell are displayed in the data area.

7. Click the data cell in the Data pane, then right-click that data cell and click Drillthrough to Order Details.

   The details of the orders that were shipped to Adam Powell are displayed in the Data Sample Viewer, as shown in the following image. However, some additional details would also be useful, such as the order date, due date, and ship date. In the next procedure, you will add these additional details.

   

8. Click Close to close the Data Sample Viewer window.

Modifying the Drillthrough Action

To modify the drillthrough action

1. Open Dimension Designer for the Internet Sales Order Details dimension.

   Notice that only three attributes have been defined for this dimension.

2. In the Data Source View pane, right-click an open area, and then click Show All Tables.

3. On the Format menu, point to Autolayout and then click Diagram.

4. Locate the InternetSales (dbo.FactInternetSales) table by right-clicking in an open area of the Data Source View pane, clicking Find Table and clicking dbo.FactInternetSales and clicking OK.

5. Create new attributes based on the following columns:

   • OrderDateKey
   • DueDateKey
   • ShipDateKey

6. Change the Name property for the Due Date Key attribute to Due Date, and then change the Name Column property for this attribute to DimTime.SimpleDate (WChar).

7. Change the Name property for the Order Date Key attribute to Order Date, and then change the Name Column property for this attribute to DimTime.SimpleDate (WChar).

8. Change the Name property for the Ship Date Key attribute to Ship Date, and then change the Name Column property for this attribute to DimTime.SimpleDate (WChar).

9. Switch to the Actions tab of Cube Designer for the Analysis Services Tutorial cube.

10. In the Drillthrough Columns box, add the following columns to the Return Columns list and then click OK:

    • Order Date
    • Due Date
    • Ship Date

    The following image shows these columns selected.

    

Reviewing the Modified Drillthrough Action

To review the modified drillthrough action

1. On the Build menu, click Deploy Analysis Services Tutorial.

2. When deployment has successfully completed, switch to the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

3. Click the single data cell, and then right-click that cell and click Drillthrough to Order Details.

   The details of these orders shipped to Adam Powell are displayed in the Data Sample Viewer, including their order date, due date, and ship date information, as shown in the following image.

   

4. Click Close to close the Data Sample Viewer.

Next Lesson

Lesson 9: Defining Perspectives and Translations

See Also

Tasks

Defining a Fact Relationship

Other Resources

Actions
Defining and Configuring an Action
Dimension Relationships
Defining a Fact Relationship and Fact Relationship Properties

Help and Information

Getting SQL Server 2005 Assistance