SAS OLAP Cube Studio Interface - SAS BI

 

When connected to a sas metadata Server, the main window of the sas olap cube studio displays cube information specific to that metadata server.
All cube administration tasks, such as creating and updating cubes, are initiated from this window.
The main window has six main visual components:

  • the menu bar
  • the toolbar
  • the shortcut bar
  • the navigation tree
  • the status bar
  • the messages window.

Tools, Menus, and Online Help

The toolbar and pull-down menus provide fast access to a set of selected commands. The icons available on the toolbar depend on which window is active from within the interface.

Tools, Menus, and Online Help

The Shortcut Bar

The Shortcut bar is populated with icons for each task an OLAP user would typically perform:

The Shortcut Bar

Definitions

Navigation Tree

The navigation tree displays an organized list of the cubes, OLAP schemas, and tables registered in the selected SAS Metadata Repository.

Navigation Tree

Status Bar

The status bar is located at the bottom of the SAS OLAP Cube Studio application window. It displays short status messages for the application.

Status Bar

Messages Window

The Messages window displays application messages such as:
Application Server Error: Attempted connection to Application Server failed. Verify that the server is running.
To resize the Messages window, use your mouse to drag the top edge of the window up or down. When you close the window, your resized setting is saved.
To toggle the display of the Messages window, select
View Ð Message Window.

Messages Window

CAUTION: Other SAS OLAP Cube Studio elements, such as the Properties dialog box and the SAS Log window, are displayed in the same area as the Messages window. For this reason, when resizing the Messages window, make sure that you leave enough room for SAS OLAP Cube Studio to display those other elements.

MindMajix Youtube Channel

Using the Cube Designer Wizard

The Cube Designer wizard is used to

                                            Checkout SAS BI Interview Questions

  • create and edit cube definitions that are stored in the active metadata repository.
  • build cubes based on the stored definitions.

Create and edit cube definitions that are stored in the active metadata repository

The same Cube Designer Wizard is also available from SAS ETL Studio.
The General window is used to specify the cube’s name and description. You also select a storage location for the cube’s definition and a storage location for the physical cube.

Select a storage location for the cube’s definition

SAS OLAP Server names for cubes, dimensions, hierarchies, measures, and member properties follow these general rules:

  • can be up to 32 characters in length
  • can contain embedded blanks
  • can contain mixed-case letters
  • do not contain periods (.).

These guidelines assume that the SAS Workspace Server is running with the VALIDVARNAME= system option set to ANY.
If a hierarchy is balanced, then all of its branches descend to the same leaf (bottom) level. In addition, each member has a parent level that is positioned immediately above it. However, hierarchies are not always balanced and sometimes they contain missing hierarchy members. This type of hierarchy is a ragged hierarchy. The Ragged Hierarchies tab enables you to set options to handle the missing members (character and numeric) for all levels and hierarchies within the cube.
On the Input window, you specify the data source that provides the input data for your cube by selecting an already registered data source or by defining a new data source.

Specify the data source

The Drill-Through window is used to specify an optional drill-through table. Drill-through tables can be used by client applications to provide a view from processed data into the underlying data source.

Drill-Through window

From this wizard window you launch the Dimension Designer wizard to define the cube’s dimensions and their associated hierarchies and levels.

Dimension Designer wizard

You can have a maximum of 128 dimensions per cube.

Dimensions, Hierarchies, and Levels

Dimensions, Hierarchies, and Levels

For example, in a Time dimension, a hierarchy might consist of Year, Quarter, Month, and Day (which are the levels).

Using the Cube Designer Wizard

The Member Property window is where you can add, modify, and delete member properties for the levels in the cube.

Cube Designer- Member property

The maximum number of member properties per level is 256.
In the Generated Aggregations window, you define aggregations to be generated for the cube in addition to whether the NWAY aggregation (the crossing of all dimension levels) is automatically generated.

Cube Designer- Generated aggregation

The finish window is where you review the details of the cube that you just defined and choose whether to only save the cube’s definition to the active metadata repository, or to save the cube’s definition and build the cube.

Cube Designer- Finish

In this window, you can also save the PROC OLAP code that is created by the wizard.

SAS OLAP Cube Studio Case Study Tasks

Goal: Build a cube in order to examine trends in length of delivery over time and for different types of orders.

SAS OLAP Cube Studio Case Study Tasks

The OrionStar cube will be built from the OrderFact detail table and consists of two dimensions and several measures.

Building a Cube

This demonstration illustrates building a cube using SAS OLAP Cube Studio.

Building a Cube

Invoke the SAS OLAP Cube Studio by selecting Start Ð All Programs Ð SAS Ð SAS OLAP Cube Studio 1.
2. Select the metadata profile BIArchitecture, then select OK

Metadata Profile

3. If prompted, enter the username and password provided by the instructor.

Username & Password

Select OK

The OLAP Cube Studio desktop opens:

OLAP Cube Studio

      4. Select the Cube Designer from the Shortcut bar to access the Cube Designer wizard.

Cube Designer

Select Next to access the general window of the Cube Designer.
5. Specify general information for the cube to be created:
a. Enter OrionStar in the Cube Name field (use no spaces).
b. Provide a description, if desired (optional).
c. Select the Foundation repository in the Repository feld.
d. Select SASMain – OLAP Schema in the OLAP Schema field.
e. Select Browse to navigate to C:WorkshopwinsassbiovrDataMartsCubes in the Path field (the folder Cubes must be created).
The path to where the cube is to be created is not a libref but a physical file location. Within the specified path, the cube is stored in a directory that uses the name of the cube. For example, if you enter the path specification above, then the cube will be stored in C:WorkshopwinsassbiovrDataMartsCubesOrionStar.
f. Select Detail Table as the input type in the Input

 Cube Designer- General

g. Select Advanced to get to the Advanced Cube Options window. The Ragged Hierarchies tab enables you to set options to handle the missing members globally for all levels and hierarchies.

Explore SAS BI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!
  • Select A blank space for the Character Missing Member option on the Ragged Hierarchies
  • Select A single period for the Numeric Missing  Member option on the Ragged Hierarchies

Advanced cube options

Select OK to close the Advanced Cube Options.
Select Next to access the Cube Designer – Input window.
6. In the Select a  Detail  Table list, select Foundation to see the registered data sources, select OrderFact as the input source.

Cube Designer- Input

Select Nextto access the Cube Designer – Drill-Through window.
7. Activate the No table for Drill-Through radio button (this is the default).

Cube Designer- Drill Through

Select Next to access the Cube Designer – Dimension Tables window.
8. Select Add to access the Dimension Designer – General
a. Specify Time in the Name field.
b. Enter a Caption of Time Dimension
c. Select a Type of TIME.
You can have only one time dimension per cube.

Dimension Designer- General

Select Next to access the Dimension Designer – Levels window.
9. Select Order_Year, Order_Qtr, and Order_Month as the levels:

Dimension Designer-Levels

10. Select Next to review the Level Properties.

Dimension Designer- Level Properties

Select Next to access the Define a Hierarchy window.
11. Select Add to define a hierarchy.
12. Specify a Name of Time, a Caption of Time Hierarchy  (Year,  Quarter,  Month) and choose the 3 available levels of Order_Year, Order_Qtr, and Order_Month.
The ordering of the variables in this list is important.

Dimension Designer- Define a Hierarchy

Select OK to close the Define a Hierarchy window.
Select Finish
13. Add a second dimension.
a. Select Screenshot_1388to access the Dimension Designer – General
b. Specify Product in the Name
c. Enter a Caption of Product Dimension.
d. Select a Type of STANDARD.

Dimension Designer-General

e. Select Next to access the Dimension Designer – Levels window.
f. Select Order_Type and Product_ID as the

Dimension Designer- Levelsloading="lazy"

g. Select Next to review the Level
h. Select Next to access the Define a Hierarchy
i. Select Add to define a herarchy.
j. Specify a Name of Product, a Caption of Product Hierarchy  (Order  Type, Product  ID) and choose all the available levels (Order_Type and Product_ID).

Dimension Designer- Define a hierarchy

k. Select OK to close the Define a Hierarchy window.
l. Select Finish
m. Select Next to access the Cube Designer – Select Measures window.
14. Select the statistic and measures to be the in the cube:
a. Expand Quantity from the Available list and move Average to the Selected list.
b. Expand Total_Retail_Price in the Available list and move Sum to the Selected list.
c. Expand Delivery_Time in the Available list and move Average, Minimum, and Maximum to the Selected list.

Cube Designer- Select Maximum

Select Next to access the Cube Designer – Measure Details window.
15. Specify properties for the selected measures:
a. Select Delivery TimeAVG in the Default Measure field.
b. Scroll to right and change the format of both AVG statistics to be 8.2.
c. Change the format of the MAX and MIN statistics to be 8.1.

Cube Designer- Details

Select Next to access the Cube Designer – Member Property window.
16. In the Cube Designer – Member Property window, member properties can be added, modified and deleted for the levels in the cube.

Select Next to access the Cube Designer – Generated Aggregations window.
17. In the Cube Designer – Generated Aggregations window, you define aggregations to be generated for the cube in addition to any automatically generated aggregations.
Select Next to access the Cube Designer Finish window.
18. To complete the cube design, write the metadata to the repository and create the cube, verify the information is correct, and select Finish

Cube Designer- Finish

A status window displays as the cube is being built.

Creating cube

19. Check the Log Frame for messages and to verify that the cube was created successfully.

Log Frame

20. If the cube was created successfully, it will be listed in the Cube group:

Listed in the Cube group

21. Select File Ð Exit to close SAS OLAP Cube Studio.
22. A SAS OLAP cube is stored as a set of files on the operating system – the OrionStar cube is stored at C:WorkshopwinsassbiovrDataMartsCubesOrionStar:

Workshop winsassbiovr DataMarts Cubes OrionStar

 

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

read less
  1. Share:
SAS BI Articles