Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives - Page 3

March 3, 2003

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.

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.

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.

9.      Select the Tools top menu item.

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

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:

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.

14.  Click OK.