Sdílet prostřednictvím

  • Článek

Defining and Using a Drillthrough Action

Dimensioning fact data by a fact dimension without correctly filtering the data that the query returns can cause slow query performance. To avoid this, you can define a drillthrough action that restricts the total number of rows that are returned. This will significantly improve query performance.

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 the Action Organizer pane and the 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.

    Actions tab of Cube Designer

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

    A blank action template appears in the display pane.

    Blank Action template 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 appear at this point in this procedure.

    Drillthrough Columns box

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

    Additional Properties box

  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.

    Additional Properties 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 the Reconnect button.

  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, and 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.

    Orders shipped to Adam Powell

  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. Then click Find Table, click InternetSales, and click OK.

  5. Create new attributes based on the following columns:

    • OrderDateKey

    • DueDateKey

    • ShipDateKey

  6. Change the Name property for the Order Date Key attribute to Order Date Then, click the browse button for the Name Column property, and in the Name Column dialog box, select Date as the source table and select SimpleDate as the source column. Click OK.

  7. Change the Name property for the Due Date Key attribute to Due Date, and then, by using the same method as the Order Date Key attribute, change the Name Column property for this attribute to Date.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 Date.SimpleDate (WChar).

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

  10. In the Drillthrough Columns box, select the check boxes to 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.

    Drillthrough Columns box

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 the Reconnect button.

  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. This includes the order date, due date, and ship date information, as shown in the following image.

    Orders shipped to Adam Powell

  4. Click Close to close the Data Sample Viewer.