Blog

  • Home
  • SAS BI
  • SAS OLAP Cube Studio and Microsoft Excel - SAS BI

SAS OLAP Cube Studio and Microsoft Excel - SAS BI

  • (4.0)
  • | 1670 Ratings

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.

metadata profile

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

display message

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.

public group

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.

pivot table

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

data source window

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

choose data source

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

create new data source

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:

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)

data link properties

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

create new data source

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

choose data source

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.

pivot wizard

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

pivot wizard 2

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

data link properties 2

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

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.

Drop Data

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:

Time Hierarchy

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.


Subscribe For Free Demo

Free Demo for Corporate & Online Trainings.

Vinod M
About The Author

Vinod M is a Big data expert writer at Mindmajix and contributes in-depth articles on various Big Data Technologies. He also has experience in writing for Docker, Hadoop, Microservices, Commvault, and few BI tools. You can be in touch with him via LinkedIn and Twitter.


DMCA.com Protection Status

Close
Close