Building a Cube from a Detail Table

Overview

A detail, or base, table is a table whose data pertains to a single area of interest. It is any table defined in a SAS Metadata Repository that contains the measures and levels for a cube. You can build an OLAP cube from a detail table by using the Cube Designer wizard in SAS OLAP Cube Studio. In this example, you use data from a product marketing campaign. You establish measures and summaries of product statistics, geographic location of potential customers, and revenue.

Enter General Cube Information

After you have established a connection profile, you can begin to create a cube. Select File

New

Cube. On the Cube Designer – General page, enter the basic cube information. For this example input type, you select Detail Table. The following display shows fields that you enter information for.

Enter information in the following fields:
- Name
- Description
- OLAP schema
- Location (SAS folder)
- Physical cube path (path in the file system to store the cube)
- Work path (path for temporary work files)
- Input Type (detail table)

Select a Detail Table

Overview

On the Cube Designer – Input page, select a base or detail table for your cube. If one does not exist for your data, click Register Table, and then define the source from which you will import your metadata.

Drill-Through Table

On the Cube Designer – Input page, you can also select or define 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.

If a drill-through table does not exist for your data, click Register Table, and then define the source from which you will import your metadata. The following display shows the base table and drill-through table that are selected for this cube.

Table Options

On the Cube Designer – Input page, you can specify the data set options that are used to open either the detail table or drill-through table for your cube. Click the Table Select Button

button next to either the Base table or Drill-through trees. The Table Options button opens the Table Options dialog box, which enables you to specify data set options that are used to open the data set. For example, you could enter a WHERE clause or other information to subset the selected table. The options are stored as part of the cube and then reapplied when the data is accessed at run time. You can also specify data set options in the Dimension Designer – General dialog box (for use with star schemas) and the Stored Aggregates dialog box (for use with summarized tables). For more information, see “Data Set Options” in the SAS Language Reference: Concepts.

Define Dimensions, Levels, and Hierarchies

Overview

Now that your basic metadata server and cube information has been entered, you can define the different dimensions and their respective levels and hierarchies. For this example, the following dimensions are created:

Products
Dates
Geography
Customers
Orders

At the Cube Designer – Dimensions page, click Add New Dimension.

This opens the Dimension Designer – General page, as seen in the following display.

Enter the information in the following fields:

Name
Caption
Description
Type (STANDARD, GEO, or TIME)
Sort Order

Click Next to open the Dimension Designer – Level page. Select Add to open the Add Levels page, as seen in the following display.

Select the levels that you want to add to the dimension. Select OK to return to the Dimension Designer – Level page, where the selected levels are listed. You can now define properties such as format, time type, and sort order for the levels that you have selected. See the following display.

Next, define hierarchies for the levels on the Dimension Designer – Hierarchy page. You can click Add to open the Define a Hierarchy page and individually select the levels for the hierarchy.

Or you can click Finish on the Dimension Designer - Hierarchy page to accept the order of the levels that are defined on the previous Dimension Designer – Level page. If you select this option, the hierarchy is assigned the same name as the dimension. See the following display.

Repeat this process for each dimension. After you create each dimension, it is listed in the Dimensions panel of the Cube Designer – Dimensions page. See the following display.

Creating a Time Dimension

When you create the Dates dimension, you must specify the TIME dimension type on the Dimension Designer – General page. See the following display.

Specifying the TIME dimension type enables Add supplied time hierarchies on the Dimension Designer – Level page. The Add button is converted to a drop-down list of options. The Add levels and Add supplied time hierarchies options are now available for selection.

Select Add supplied time hierarchies. This opens the Add Supplied dialog box. Select from the list of supplied time hierarchies to create the time levels. This also creates the hierarchies for the dimension. See the following display.

You can then define properties such as time type and sort order for the levels that you have selected. See the following display.

The hierarchy or hierarchies that are selected on the Add Supplied dialog box are listed in the Hierarchies panel on the Dimension Designer – Hierarchy page. If there is only one hierarchy, as with this example, the hierarchy name is changed to match the dimension name. See the following display.

Define Member Properties

You can now define the member properties for any needed cube members. A member property is an attribute of a dimension member. A member property is also an optional cube feature that is created in a dimension to provide users with additional information about members. For this example, you can define the customer gender as a member property. Define member properties in the Cube Designer – Dimensions page. Select the Customer ID level, and then click the Add Member Property button, as seen in the following display.

Cube Designer – Dimensions, Add Member Property

At the Define a Member Property page, enter the member property column name, format, and caption.

Define Measures

You can now define the measures for the cube. In this example, you define measures for the CostPrice Per Unit. Define the measures for the cube at the Cube Designer – Select Measures page.

Modify any measure attributes such as measure captions and formats as shown in the following display.

Define Aggregations

You can now define the aggregations for the cube. Aggregations are summaries of detailed data that are stored with a cube or referred to by a cube. They can help contribute to faster query response. Define the aggregations for the cube from the Cube Designer – Aggregations page, as shown in the following display.

Select Add to specify a user-defined aggregation. This opens the Add Aggregation dialog box, as shown in the following display. In this dialog box, you can select levels to add to the aggregation that you are defining.

Select OK to return to the Cube Designer – Aggregations page, where the new aggregation is listed. Select Next to go to the Cube Designer – Finish page.

Build the Cube

You can now build the cube. On the Cube Designer – Finish page, review the settings for the cube, and then click Finish. The Finish dialog box is displayed as shown in the following display.

Choose to build the physical cube or to generate cube metadata without building the physical cube. Click OK to save your selection and display the Summary of Selections page, as shown in the following display.

On the Summary of Selections page, you can review the specifications for your cube. At this point, you can click Back to make changes, Finish to build the cube, or Export Code, as shown in the following display.

The Export Code dialog box displays the current settings that determine whether, how, and where you store generated SAS code. To export code for a new cube or to export code for an existing cube without saving permission conditions, click Create the standard OLAP procedure code.

To generate shorter OLAP procedure code and to preserve existing permission conditions, click Create shorter procedure code representing the last successful build.

To not save the generated code, deselect both of the check boxes.

Enter the file location(s) where you want to save your generated code, and then click OK .

SAS OLAP Code Saved in Export

When you export the code that is generated in a cube build, you save the following types of code:

the SAS LIBNAME statement
any FMTSEARCH statements
any additional SAS code
the PROC OLAP statement
the METASVR statement
all other PROC OLAP statements

Code for permission conditions is either preserved or deleted from the export file, as determined in the Export Code dialog box.

You can access the Export Code dialog box by using one of the following methods:

On the tree view in SAS OLAP Cube Studio, right-click on a cube and click Export Code.
In the Cube Designer – Finish page, click Export Code.

The Export Code dialog box appears. You can choose to save either the long or short form of the code, or both. Enter the file location or locations where you want to save the resulting code. Click OK when finished.

PROC OLAP Statements and Options for a Detail Table

The PROC OLAP code that is generated when a detail cube is built is listed below. A detail cube is unique in that it uses the DATA= option to specify the data source for the cube. The statements each have options that are either required or optional, depending on the cube structure.

Statements and Options Used to Load Cubes from a Detail Table

Statements	Options	Required or Optional
PROC OLAP	DATA=	Required
	CUBE=	Required
	PATH=	Required
	DESC=	Optional
	NO_NWAY	Optional
	WORKPATH=	Optional
	DT_TABLE	Optional
METASVR	OLAP_SCHEMA=	Required
	REPOSITORY=	Optional
	HOST=	Optional
	PORT=	Optional
	PROTOCOL=	Optional
	USERID=	Optional
	PW=	Optional
DIMENSION	HIERARCHIES=	Required
	DESC=	Optional
	CAPTION=	Optional
	TYPE=TIME	Required only for TIME dimensions
	SORT_ORDER=	Optional
LEVEL		The LEVEL statement is optional unless you want to specify time periods for each level in a TIME dimension. If you specify a time period for one level, then you must specify a time period for all levels. To specify a time period, you use the TYPE= option.
HIERARCHY	LEVELS=	Required
	DESC=	Optional
	CAPTION=	Optional
MEASURE	STAT=	Required
	COLUMN \| ANALYSIS=	Required
	AGGR_COLUMN=	Required if you use the AGGREGATION statement with the TABLE= option
	DESC=	Optional
	CAPTION=	Optional
	UNITS=	Optional
	FORMAT=	Optional
	DEFAULT=	Optional
PROPERTY	prop-name	Required
	LEVEL=	Required
AGGREGATION		The AGGREGATION statement is optional unless you are creating additional aggregations. In that case, you must specify the names of the contiguous levels to be used to create the aggregation. Use the TABLE= option for cubes that contain aggregated data from tables other than the input data source.