Specifying Drillthrough Options
Drillthrough is an operation in which an end user selects a single cell from a regular, virtual, or linked cube and retrieves a result set from the source data for that cell in order to get more detailed information. For end users to drill through, their client applications must support this capability.
By default, a drillthrough result set is derived from only the table rows that were evaluated to calculate the value of the selected cube cell. For example, in a three-dimension cube, if the selected cube cell is the intersection of 1998 and Mexico, and the displayed dataset is sliced by all Customers and the measure Sales, the result set from drillthrough is generated from only:
- Fact table rows that are for both 1998 and Mexico.
- Time dimension table rows that store 1998.
- Location dimension table rows that store Mexico.
- All rows in the Customers dimension table.
The result set is summarized using the joins in the cube's schema.
Note Drillthrough is not supported for cells that have values based on expressions such as calculated member cells or ones that are associated with custom member formulas. This includes cells that display values for the level members of a mining model dimension.
Drillthrough permissions are granted through membership in cube roles. The only end users who can drill through are those in cube roles that have been granted this ability. You can grant this ability in Cube Role Manager or through programming with Decision Support Objects (DSO). For information about setting drillthrough options using DSO, see AllowDrillThrough (MDStore Interface).
To enable or disable drillthrough for a regular cube, open Cube Editor. On the Tools menu, click Drillthrough Options. Then, in the Drillthrough Options dialog box, select or clear the Enable drillthrough check box. You can choose, on the Columns tab, which columns to display on drillthrough.
To enable or disable drillthrough for a virtual cube, open Virtual Cube Editor. In the tree pane, select the virtual cube icon, and then, in the properties pane, set the Enable Drillthrough property to either True or False. For linked cubes, enable or disable drillthrough by changing the settings of the source cube or cubes on the publishing server.
If drillthrough is enabled for a cube, you can test drillthrough while you browse the cube's data in Cube Browser, Cube Editor, or Virtual Cube Editor. To do this, right-click a cube cell, and then click Drill Through.
Specifying Drillthrough Options for Regular Cubes
You can enable drillthrough for individual regular cubes. For each cube, you can select the columns and tables that are included in the result set returned by a drillthrough operation. These columns can be from any table in the cube's data source, including those that are not part of the cube's schema. If drillthrough is enabled, at least one column must be selected.
Because a drillthrough operation can consume an extremely large amount of resources, enable drillthrough only after careful consideration. Grant cube roles the ability to drill through only after testing drillthrough with the cube.
One way to limit the resources used by a drillthrough is to specify a drillthrough filter. This filter is a WHERE clause expression added to the SQL SELECT statement that generates the result set. The filter can limit the number of rows in the result set.
If you want to enable drillthrough for a multiple-partition cube, before you begin the following procedure, make sure that the columns that you want to display in the result set exist in the tables for all of the cube's partitions. (The Drillthrough Options dialog box, where drillthrough is enabled, displays the column names and table names for only the cube's default partition.) Qualifying fact table names do not need to be the same in all partitions. If necessary, Microsoft® SQL Server™ 2000 Analysis Services automatically changes the query to reference the appropriate fact table name for each partition.
Note If a cube contains multiple partitions, drillthrough within the cube returns multiple result sets, one per partition. A client application might attempt to merge these result sets before presentation to the end user, thus yielding unexpected results.
To specify drillthrough options for a regular cube, use Cube Editor and the Drillthrough Options dialog box.
To specify drillthrough options for a regular cube