- About the Series
- Introduction to Drillthrough
- Overview of Drillthrough In Analysis Services
- Enabling Drillthrough for an OLAP Cube
- Reprocessing the Cube to Include Drillthrough
- Drilling through from Analysis Services
- Drilling through from an MDX Query
- The Drillthrough-enabled MDX Syntax in Action
- Running the DTS Package and Viewing Query Results
- Next in Our Series
Like this article? We recommend
Like this article? We recommend
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:
Start Analysis Manager (Start, Programs, Microsoft SQL Serve, Analysis Services, Analysis Manager).
Expand the Analysis Servers folder by clicking the + sign on its left.
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 1 Navigate to the FoodMart2000 database in Analysis
Manager.Expand the FoodMart2000 database by clicking on the + sign on its left.
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 2 Sample cubes provided with the Analysis Services
installation.Right-click the HR cube and then click Edit from the context menu.
The Cube Editor appears.
)
Figure 1)
Figure 2Let's make sure that we have a common display showing at this stage.
Click the Data tab (the lower-right half of the Analysis Manager screen).
Drag the Department and Time dimensions to the row and column axes, respectively, to match the display shown in Figure 3.
Figure 3 The Data viewing pane after dimensions are placed.Select the Tools top menu item.
Select Drillthrough Options on the cascading menu, as shown in Figure 4.
Figure 4 Select Drillthrough Options (circled in red).The Cube Drillthrough Options dialog box appears.
Check the Enable drillthrough box by clicking it.
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 5 Cube Drillthrough Options dialog box (partial view).
)
Figure 3)
Figure 4)
Figure 5In 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:
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 6 Filter tab of the Cube Drillthrough Options dialog box.
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 7 Drillthrough Settings warning dialog box.
Click OK.
We now need to process the cube to implant our drillthrough setup.