SAS OLAP Cube Studio and Microsoft Excel - SAS BI

Viewing Cube Data

In sas®9, there are several options for viewing cube data including:

•  SAS Enterprise Guide 3
• the SAS Information Delivery Portal’s Visual Data Explorer portlet
• the SAS Web OLAP Viewer for Java
•  Microsoft Excel Pivot Tables.

This section shows how to use Microsoft Excel to view a sas olap cube.

Setting Cube Permissions

While the metadata about the cube is available to all users by default, the data in the cube itself is not.
Permissions must be added manually for the desired group of users to be able to display the cube data.
These permissions are granted using sas management console.
1.Select Start Ð All Programs Ð SAS Ð SAS Management Console to invoke the SAS Management Console. Login using the BIArchitecture metadata profile and the username and password provided by the instructor.

2. Expand Authorization Manager in the Navigation Tree.
3. Expand Resource Management.
4. Expand By Location.
5. Expand SASMain.
6. Expand SASMain – OLAP Schema.

7. Right-click the OrionStar cube and select Properties from the pop-up menu.
8. Select the Authorization tab in the OrionStar Properties window.
9. Select the PUBLIC group, then click on the check box to grant this group Read permission.

10. Select File to close the OrionStar Properties
11. Select File Ð Exit to close the SAS Management Console.

Importing the Cube into Microsoft Excel

                                              Checkout SAS BI Interview Questions

1. Open an empty Excel spreadsheet by selecting Start Ð All Programs Ð Microsoft Office ÐMicrosoft Excel.
2. Select Data Ð PivotTable and PivotChart Report… to access the PivotTable and PivotChart wizard.
3. Select External data source as the location of the data to analyze and PivotTable as the kind of report to create.

Select 
4. To retrieve information about the external data source, select to access the Choose Data Source window.

5. Select the OLAP Cubes tab, then double-click to access the Create New Data Source window.

6. Specify OrionStar as the name of the data source and choose SAS OLAP Data Provider 9.1 as the OLAP provider.

The name of the data source can be anything you want to call it.
Select connect to access the Data Link Properties window.
7. Fill in the following information:

The name of the data source here must match the name of a data source registered in the metadata.
Select  OK to return to the Create New Data Source window. 
8. In the Create New Data Source window, the third item should indicate a default If not already selected, select OrionStar as the cube for item 4.

Select OK to return to the Choose Data Source window.
9. Select the OrionStar cube on the OLAP Cubes tab.

Select OK to access the PivotTable and PivotChart Wizard.
10. There should now be a message next to GET DATA specifying Data fields have been retrieved.

Select  NEXT to proceed to the third step of the wizard.
11. Accept the defaults location.

Select Finish
12. Reenter the password provided by the instructor.

The PivotTable Field list should now appear in front of your Microsoft Excel spreadsheet.

13. To populate the PivotTable with data from the SAS OLAP cube source:
a. Drag and drop Product Hierarchy from the PivotTable Field list into the Drop  Row  Fields  Here area of the PivotTable.
b. Drag and drop Time Hierarchy from the PivotTable Field list into the Drop  Column  Fields  Here area of the PivotTable.
c. Drag and drop Average Quantity and Average Delivery_Time from the PivotTable Field list into the Drop Data  Items  Here area of the PivotTable.

14. Double-click on the cell that contains the value of 1998 (Year level). This will drill to the next level in the Time Hierarchy, the quarters:

This action can also be performed by right-clicking 1998 and selecting Group and Show Detail Ð Show Detail from the pop-up menu.
15. Select File Ð Exit to close Excel, do not save the changes.