Example: Building a Cube from a Star Schema | SAS 9.1.3 ETL Studio: Users Guide

< Day Day Up >

This example demonstrates how to use the Cube Designer to create a cube that is based on a star schema. The example is based on the scenario that is described in "What Are the Time and Place Dependencies of Product Sales?" on page 32.

Preparation

For the current example, assume that the following statements are true:

A warehouse project plan identified the need for a SAS cube to support OLAP reporting.
The cube will be based on a star schema in which ORDER_FACT is the central fact table, and CUSTOMER_DIM, GEOGRAPHY_DIM, ORGANIZATION_DIM, and TIME_DIM are the dimension tables. For details about this star schema, see "Identifying Targets" on page 34.
The star schema has already been created, and metadata for the star schema has already been added to a metadata repository.
The metadata repository is under change management control. For details about cubes and change management, see "Working with Change Management" on page 64.
The prerequisites that are described in "Prerequisites for Cubes" on page 162 have been met.

Start SAS ETL Studio and Open the Appropriate Metadata Profile

Perform the following steps to begin work in SAS ETL Studio:

Start SAS ETL Studio as described in "Start SAS ETL Studio" on page 56.
Open the appropriate metadata profile as described in "Open a Metadata Profile" on page 58. For this example, the appropriate metadata profile would specify the project repository that will enable you to access metadata about the star schema.

You do not need to check out the star schema in order to specify it as the input to the cube. Accordingly, the next task is to display the Cube Designer and enter metadata as prompted by the wizard.

Use the Cube Designer

Perform these steps to create a cube using the Cube Designer. For details about the fields in each window, click the button.

Display the Cube Designer

Perform these steps to display the Cube Designer:

From the menu bar on the SAS ETL Studio desktop, select Tools Target Designer. The Target Designer selection window is displayed.

Display 11.1: Target Designer Selection Window
Select the Cube Designer icon and click . The Cube Designer is displayed.

The next task is to enter general information about the cube.

Enter General Information

In the General window, enter the following information:

Cube Name
Description
Repository
OLAP Schema
Path (physical path where the cube will be stored)
Input Type.

For input type, select Star Schema. Click when finished

The next task is to select the input for the cube.

Select Input for the Cube

In the Input window, select a data source for your cube. For this example, select the ORDER_FACT table, which is the central fact table for a star schema. (If a source table does not exist for your data, you can select Define Table, and then define the source from which you will import metadata.)

Note

If the cube is built from a star schema, then the keys that link the dimension table and the fact table are also defined by using the DIMKEY= and FACTKEY= options. See the SAS OLAP Server Administrator's Guide for further information.

Click when finished. The next task is to select a table for drill-through reporting.

Select a Table for Drill-Through Reporting

In the Drill-Through window, determine whether you will have a drill-through table. In this example, you will not use a drill-through table, so you can select the option No table for Drill-Through.

Click when finished. The next task is to define dimensions, hierarchies, and levels for the cube.

Define Dimensions, Hierarchies, and Levels

In the Dimension Tables window, select dimension tables that are associated with the ORDER_FACT star schema that you specified as the data source for the cube. For this example, select the following tables:
- CUSTOMER_DIM
- GEOGRAPHY_DIM
- ORGANIZATION_DIM
- TIME_DIM.
Now that your basic metadata server and cube information has been entered, define the different dimensions and their respective levels and hierarchies. This example cube has these dimensions and levels:
- Time
  - Year_ID
  - Quarter
  - Month_Name
  - Week_Name
  - Date_ID.

For the Time dimension, the following star schema information is also included:

Table	TIME_DIM
Key	Date_ID
Fact Key	Order_Date

Customers
- Customer_Name
- Customer_Age
- Customer_Gender
- Customer_Group
- Customer_Type.

For the Customers dimension, the following star schema information is also included:

Table	CUSTOMER_DIM
Key	Customer_Id
Fact Key	Customer_Id

Geography
- Continent_Name
- Country
- State
- Region
- Province
- County
- City.

For the Geography dimension, the following star schema information is also included:

Table	GEOGRAPHY_DIM
Key	Street_Id
Fact Key	Street_Id

Organization
- Employee_Name
- Job_Title
- Salary
- Gender
- Company
- Department
- Org_Group
- Section.

For the Organization dimension, the following star schema information is also included:

Table	ORGANIZATION_DIM
Key	Employee_Id
Fact Key	Employee_Id

Define the dimensions for the cube. For each dimension, you define the dimension, its levels, and its hierarchies.

At the Dimensions window, select the Add button. This opens the Dimension Designer—General window. Enter the following information:
- Dimension name
- Caption
- Description
- Type of dimension (standard or time)
- Sort order.
When you define the dimensions for a cube based on a star schema, you will need to provide additional information about the dimensions in the Dimension Designer—General window. On the Star Schema Dimension Tables Definition panel, enter the following information:
- Table
- Key
- Fact Key
- Data Set Options.
Select the necessary dimension levels at the Dimension Designer—Levels window.
Define properties such as format, time type, and sort order at the Dimension Designer—Level Properties window.
Define hierarchies for the levels at the Dimension Designer—Define a Hierarchy window.
Repeat this task for each dimension.

Note

You use the DIMENSION, HIERARCHY, and LEVEL statements here. For time-specific levels in a dimension, the LEVEL statement is required. Also, there can be only one time-specific dimension.

Click when finished. The next task is to specify measures (columns) for the cube.

Specify Measures (Columns) and Measure Details

In the Selected Measures window, select the following columns and associated Sum statistics:
- Total_Retail_Price /Sum
- Quantity /Sum
- CostPrice_Per_Unit/Sum
- Discount /Sum.
Specify detail information for the measures. In the Measure Details window, enter any necessary information for the different measures:
- Caption
- Format
- Units
- Description.
For the measure Total_Retail_Price, enter a format value of DOLLAR12.2. For the measure CostPrice_Per_Unit, enter a format value of DOLLAR10.2.

Click when finished. The next task is to specify member property information for the levels in the cube.

Specify Member Properties

In the Member Property window, select the Add button to create a new member property. In the Define a Member Property window, enter the following information about the member property:
- Name
- Level
- Column
- Format
- Caption
- Description
- Selected Hierarchies.

In this example, the following member properties are created:

Property Name	Level	Column	Caption	Selected Hierarchy
WeekDay_Number_US	date	weekday_no	US WeekDay Number
WeekDay_Number_EU	date	weekday_eu	EU WeekDay Number
Week_Number_EU	week_name	week_no	EU Week Number	YWD
Month_Number	month_name	month_no	Month	YMD
			Number
Month_Number	month_name	month_no	Month	YQMD
			Number
Holiday_US	date	Holiday_US	US
			Holidays

Click when finished. The next task is to specify aggregations for the cube.

Specify Aggregations

Aggregations are summaries of detailed data that is stored with a cube or referred by a cube. They can help reduce the build time that is required for the cube and contribute to faster query response.

In the Generated Aggregations window, select the Add button to specify aggregations and associated levels. Order the levels for the aggregations to follow the hierarchy drill path. The aggregations include the following:
- RegionalCustomerUse
- QuarterlyCustomerUse
- YearlyCustomerUse
- WorldwideStaff
- WorldwideSalaries.
Note
When you create cubes in the Cube Designer, a default aggregation, which is the NWAY aggregation, is automatically created and listed in the Generated Aggregations window.

Click when finished. The next task is to review the metadata that you have entered and create the cube.

Create the Cube

In the Finish window, select whether you want the cube to be physically created after the metadata is saved. When you click Finish, the metadata for the cube is always saved.

If you select Save the metadata and create the cube, the short form of the OLAP procedure code is generated along with the necessary LIBNAME statements, and the code is submitted to a SAS application server. You can also select whether to save the OLAP procedure code that is generated. At the Save PROC OLAP Code window, enter the file location where you want to save the resulting code.

If the cube you created is processed successfully and a cube is built, the cube will appear in the Project tree.

Note

When a SAS OLAP cube is created, a directory for that cube is also created. This directory is assigned the same name as the cube, but in uppercase letters. .

For example, when you save a cube in

   c:\olapcubes

and name the cube

   Campaigns

the cube is saved in the directory

   c:\olapcubes\CAMPAIGNS

Check In the Cube

Under change management, new metadata objects are added to the Project tree on the SAS ETL Studio desktop. You must check in the new table metadata in order to save it to the change-managed repository.

In the Project tree, select the repository icon (such as Project: etlUser1).
From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository.

All metadata objects in the project repository will be checked in to the change-managed repository. The new objects will be visible in the Inventory tree.

< Day Day Up >