Blog

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.

Learn more about our SAP BI Training Videos to get ahead in your career.

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  to access the Data Link Properties window.
7. Fill in the following information:

Data Source: OrionStar
User ID: < provided by instructor >
Password: < provided by instructor >
SAS  Machine  DNS  Name: < provided by instructor >
SAS  Service  Name/Port: 5451 (default)
SAS Protocol: ProtocolBridge (default)

Explore SAS BI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

The name of the data source here must match the name of a data source registered in the metadata.
Select  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  to return to the Choose Data Source window.
9. Select the OrionStar cube on the OLAP Cubes tab.

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

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

Select 
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.


RELATED COURSES

Get Updates on Tech posts, Interview & Certification questions and training schedules