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.

The Shortcut Bar

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

Navigation Tree

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

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.

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.

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.

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.

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.

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.

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.

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

You can have a maximum of 128 dimensions per cube.

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.

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.

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.

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.

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.

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 

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

Select 

The OLAP Cube Studio desktop opens:

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

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

 

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

• 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

Select  to close the Advanced Cube Options.
Select  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.

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

Select  to access the Cube Designer – Dimension Tables window.
8. Select  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.

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

10. Select  to review the Level Properties.

Select  to access the Define a Hierarchy window.
11. Select  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.

Select  to close the Define a Hierarchy window.
Select 
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.

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

g. Select  to review the Level
h. Select  to access the Define a Hierarchy
i. Select  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).

k. Select  to close the Define a Hierarchy window.
l. Select 
m. Select  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.

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

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

A status window displays as the cube is being built.

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

20. If the cube was created successfully, it will be 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: