Building Cubes and Administering Cubes

Defining Drill-Through Tables

Drill-through tables enable you to display, at query time, the unsummarized detail data that a table cell or selected cells were summarized from. Using the drill-through capability, you can view the specific observations from the underlying data that make up an aggregate value. Many OLAP applications give you the ability to select a cell or a range of cells and then view the input data that the cell data were summarized from. Drill-through capability enables companies to access data that is not stored on an OLAP server and make it accessible to end users of an OLAP application. When the OLAP server receives a request for this additional data at query time, it automatically submits a query and retrieves the data from a data warehouse or from an OLTP (online transaction processing) system. In order to provide this capability, a drill-through table must be specified for a cube.

A drill-through table can be a view, data set, or other data file that contains data that is used to define a cube. For you to successfully use a drill-through table, it must have the same columns with the same attributes as the table that the cube was loaded from. Detail data tables are most commonly used as drill-through tables. However, if the cube was loaded from a star schema, a view that fully joins the star schema can be used as a drill-through table.

In the Cube Designer wizard, you can select a drill-through table on the Cube Designer - Drill-Through page. Select a table from the Available tables list and move it to the Selected table list.

You can also use the OLAP procedure, DRILLTHROUGH_TABLE | DT_TABLE | DT_TBL= option to define a drill-through table for a cube. To understand how drill-through is implemented in a SAS OLAP cube, see the cube building example Implementing Drill-through to Detail Data in a SAS OLAP Cube.

Note: When you select a data table for drill-through, you may need to consider user access and security restrictions for that table. For further information see Security for Drill-through Tables. [cautionend]

Top of Page