Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

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 Serve, Analysis Services, Analysis Manager).

  2. Expand the Analysis Servers folder by clicking the + sign on 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, as shown in Figure 1.

    Figure 1Figure 1 Navigate to the FoodMart2000 database in Analysis Manager.

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

  5. Expand the Cubes folder (shown in Figure 2) by clicking the + sign on its left.

    The cubes appear, similar to those shown in Figure 2.

    Figure 2Figure 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.

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

  2. Drag the Department and Time dimensions to the row and column axes, respectively, to match the display shown in Figure 3.

    Figure 3Figure 3 The Data viewing pane after dimensions are placed.

  3. Select the Tools top menu item.

  4. Select Drillthrough Options on the cascading menu, as shown in Figure 4.

    Figure 4Figure 4 Select Drillthrough Options (circled in red).

    The Cube Drillthrough Options dialog box appears.

  5. Check the Enable drillthrough box by clicking it.

  6. 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 box appears as partially shown in Figure 5.

    Figure 5Figure 5 Cube Drillthrough Options dialog box (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:

  1. Click the Filter tab of the Cube Drillthrough Options dialog box.

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

Figure 6Figure 6 Filter tab of the Cube Drillthrough Options dialog box.

  1. Click OK.

The Drillthrough Settings dialog box, shown in Figure 7, warns us that the cube must be saved for the changes we have just made to take effect.

Figure 7Figure 7 Drillthrough Settings warning dialog box.

  1. Click OK.

We now need to process the cube to implant our drillthrough setup.

  • + Share This
  • 🔖 Save To Your Account