Implementing Drill-through to Detail Data in a SAS OLAP Cube

Many SAS OLAP applications give users the ability to select a cell or a range of cells and then view the input data that the cell data was summarized from. SAS OLAP enables you to assign a drill-through table to a cube. You can then access the underlying cell data.
Note: When you select a data table for drill-through, you might need to consider user access and security restrictions for that table. For further information see Security for Drill-through Tables.
In this example, a cube is assigned a drill-through table, and then the cube is accessed in Microsoft Excel 2007. In SAS OLAP Cube Studio, open a cube in the Cube Designer wizard. In the Cube Designer – Input page, assign a drill-through table. This is often the same table that is used as the input table for the cube. Click Finish to save the drill-through table assignment to the cube. See the following display.
Assign a Drill-through Table
In Microsoft Excel 2007, select Datathen selectFrom Other sourcesthen selectFrom Data Connection Wizard. This opens the Data Connection Wizard. Select the option Other/Advanced. Select Next.
Data Connection Wizard
On the Data Link Properties page, select the Provider tab. In the list of data provider options, select the appropriate release of the SAS OLAP Data Provider. Click OK to return to the Data Link Properties dialog box.
Data Link Properties – Provider Tab
On the Connection tab, enter the Data Source, User name and Password for your SAS OLAP Server.
Connection Tab
Click Test Connection to test the connection to the SAS OLAP Server. Click OK to respond to the Connection Succeeded message and return to the Data Link Properties dialog box. Click OK to return to the Data Connection Wizard.
On the Select Database and Table page, select a database and a cube, and then click Next.
Select Database and Table
On the Save Data Connection File and Finish page, enter data that will help you and others access the cube and understand its purpose. You can enter a Description, Friendly Name, and Search Keywords for the cube. Click Finish to complete the Data Connection Wizard.
Save Data Connection File and Finish
The Import Data dialog box appears. Select to view the cube data in a PivotTable Report or in a PivotChart and a PivotTable Report. Click OK.
Import Data Dialog Box
The Data Link Properties dialog box appears. Enter your password and click OK.
Enter Password
The pivot table opens, along with the pivot chart, if it is selected. From here you can select fields to add to your pivot table and pivot chart.
Pivot Table
As you select fields to add, the pivot table and pivot chart become populated. You can now view the drill-through data for a particular cell in the pivot table. Select the cell that you want to drill down to.
Select a Cell for Drill-Down
On the SAS menu, select the OLAP Options menu.
SAS Menu
On the OLAP Options menu, select the Drill through Details option.
Menu Options
The detail data for the cell in the pivot table is displayed on a second sheet in Excel.
Detail Data for Cell in Pivot Table
Copyright © SAS Institute Inc. All rights reserved.