Enabling Drillthrough for an OLAP Cube

Let's go into the Cube Editor and enable drillthrough for the HR cube, a sample cube provided by the Typical MSSQL Server 2000 Analysis Services. We will start Analysis Services, and navigate to the HR cube, with the following steps:

1.      Start Analysis Manager (Start --> Programs --> Microsoft SQL Server --> Analysis Services --> Analysis Manager).

2.      Expand the Analysis Servers folder by clicking the "+" sign to its left.

3.      Expand your server (typically named the same as the host PC, but determined by the installation / setup), and then expand the FoodMart 2000 database.

Illustration 1: Navigate to the FoodMart2000 Database in Analysis Manager

4.      Expand the FoodMart2000 database by clicking on the "+" sign to its left.

5.      Expand the Cubes folder (seen in the Illustration 2 below), by clicking the "+" sign to its left.

The cubes appear, similar to those shown in Illustration 2 below.

Illustration 2: Sample Cubes provided with the Analysis Services Installation.

6.      Right-click the HR cube, and then click Edit from the context menu.

The Cube Editor appears.

Let's make sure that we have a common display showing at this stage.

7.      Click the Data tab (lower right half of the Analysis Manager screen).

8.      Drag the Department and Time dimensions to the row and column axes, respectively, to match the display shown below.

Illustration 3: The Data Viewing Pane, after Dimensions are Placed

9.      Select the Tools top menu item.

10.  Select Drillthrough Options on the cascading menu, as shown in Illustration 4 below:

Illustration 4: Select Drillthrough Options

The Cube Drillthrough Options dialog appears.

11.  Check the Enable Drillthrough box by clicking it.

12.  Select the following columns for display by clicking the checkboxes to the immediate left of each.

  • pay_date
  • salary_paid
  • overtime_paid
  • overtime_hours
  • full_name

The dialog appears as partially shown below:

Illustration 5: The Cube Drillthrough Options Dialog (partial view)

In our setpoints above, we have enabled drillthrough, and defined what fields from the actual underlying data source will be displayed within a drillthrough view.

We can also set filters on the drillthrough, to restrict the data returned, as follows:

13.  Click the Filter tab of the Cube Drillthrough Options dialog.

Here we can type in a filter to further restrict the data returned in the drillthrough presentation. We will leave this blank, as shown below, for this exercise.

Illustration 6: The Filter Tab of the Cube Drillthrough Options Dialog

14.  Click OK.