Mindmajix

SAS OLAP Cube Studio and Microsoft Excel

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.

Screenshot_1403

      2. Expand Authorization Manager in the Navigation Tree.

      3. Expand Resource Management.

      4. Expand By Location.

      5. Expand SASMain.

      6. Expand SASMain – OLAP Schema.

Screenshot_1404

       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.

Screenshot_1405

      10. Select Screenshot_1207to close the OrionStar Properties

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

Importing the Cube into Microsoft Excel

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

Screenshot_1406

Select Screenshot_1212

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

Screenshot_1408

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

Screenshot_1409

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

Screenshot_1410

The name of the data source can be anything you want to call it.

Select Screenshot_1411to 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)

Screenshot_1412

The name of the data source here must match the name of a data source registered in the metadata.

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

Screenshot_1413

Select Screenshot_1207 to return to the Choose Data Source window.

      9. Select the OrionStar cube on the OLAP Cubes tab.

Screenshot_1414

SelectScreenshot_1207to access the PivotTable and PivotChart Wizard.

      10. There should now be a message next to Screenshot_1415 specifying Data fields have been retrieved.

Screenshot_1416

Select Screenshot_1212 to proceed to the third step of the wizard.

      11. Accept the defaults location.

Screenshot_1417

Select Screenshot_1252

      12. Reenter the password provided by the instructor.

Screenshot_1418

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

Screenshot_1419

      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.

Screenshot_1421

      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:

Screenshot_1420

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.

 

 

 


 

 

0 Responses on SAS OLAP Cube Studio and Microsoft Excel"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.