Previous Page

|

Next Page

Cube Building and Modifying Examples

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 may 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 accessed in Microsoft Excel 2007. In SAS OLAP Cube Studio, open a cube in the Cube Designer wizard. In the Cube Designer - Drill-Through page, assign a drill-through table. This is often the same table that is used as the input table for the cube. Complete the Cube Designer wizard, saving the drill-through table assignment to the cube. See the following display:

In Microsoft Excel 2007, select Data  From Other sources  From Data Connection Wizard. This opens the Data Connection Wizard. Select the option Other/Advanced. Select Next.

On the Data Link Properties page, select the Provider tab. In the list of data provider options, select the option: SAS OLAP Data Provider 9.2. Select Next. This takes you to the Connection tab.

On the Connection tab, enter the Data Source, User name and Password for the SAS OLAP server that you are accessing.

At this point, you can choose to test the connection to the SAS OLAP Server. Select the Test Connection button. The following message is displayed if the connection is successful. You can then select OK on the Data Link Properties dialog box.

If the connection to the OLAP Server is successful, you can select the cube you want to view drill-through data for. On the Select Database and Table page, select the needed database and a cube. Select Next.

On the Save Data Connection File and Finish page, you can enter optional information about the cube so that you can reference and access the cube in a later session. You can enter a Description, Friendly Name, and Search Keywords for the cube. Select Finish to complete the Data Connection Wizard.

The Import Data dialog box opens. Select to view the cube data in a PivotTable Report or in a PivotChart and a PivotTable Report. Select OK.

The Data Link Properties open. Enter your password and select OK.

The pivot table and, if selected, the pivot chart open. From here you can select fields to add to your pivot table and pivot chart.

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.

On the SAS menu, select the OLAP Options menu.

On the OLAP Options menu, select the Drill through Details option.

The detail data for the cell in the pivot table is displayed on a second sheet in Excel.

Previous Page

|

Next Page

|

Top of Page

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.