Previous Page

|

Next Page

Cube Building and Modifying Examples

Building a Cube from a Detail Table

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 recent product marketing campaign. You establish measures and summaries of various aspects of the data, such as product statistics, geographic location of potential customers, and revenue summaries.

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

At the Cube Designer - Input page, select a detail table for your cube. If one does not exist for your data, select Define Table, and then define the source that you will import your metadata from. The following display shows the input table Detail that is selected for the example cube.

Drill-Through Table

At the Cube Designer - Drill-Through dialog box, you can 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, select Define Table, and then define the source that you will import your metadata from.

Table Options

The Table Options button is available in both the Cube Designer - Input and the Cube Designer - Drill-Through dialog boxes. It opens the Table Options dialog box. It enables you to specify data set options that are used to open the data set. For example, you could enter a WHERE clause or subsetting information that is then applied to the selected table when it is opened. 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 SAS Language Reference: Concepts.

Define Dimensions, Levels, and Hierarchies

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, select Add.

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.

Select Next. This opens the Dimension Designer - Level page. Next, select Add to open the Add Levels page, as seen in the following display.

Select the levels 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 select Add to open the Define a Hierarchy page and individually select the levels for the hierarchy.

Or you can select 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. See the following displays.

This display shows the Add button when selected.

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 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, as shown in the following display.

Modify any measure attributes such as measure captions and formats at the Cube Designer - Measure Details page, as shown in 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 - Member Property dialog box, as seen in the following display.

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

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 select one of the cube creation options, as shown in the following display.

You can choose to do one of the following:

1. save the metadata and create the cube

2. save the metadata but do not create the cube

You can also select whether to save the generated PROC OLAP code. Select Export Code. This opens the Export Code dialog box, as shown in the following display.

You can select to save either the long or short form of the code, or both. Enter the file location(s) where you want to save the resulting code. Select OK when finished to return to the Cube Designer - Finish page.

On the Cube Designer - Finish page, select Finish to complete the wizard. If the cube builds successfully, you will receive progress messages about the cube build, as seen in the following display.

Save a Cube's PROC OLAP Code

In SAS OLAP Cube Studio, you can elect to save the PROC OLAP code that is generated when a cube is built. The code is saved to a text file that you specify. The information saved in the file includes the following items:

• the SAS LIBNAME statement

• any FMTSEARCH statements

• any additional SAS code

• the PROC OLAP statement

• the METASVR statement

• all other PROC OLAP statements

You can access the Save PROC OLAP Code dialog box by using one of the following methods:

1. On the tree view in SAS OLAP Cube Studio, right-click on a cube and select Export Code.

2. In the Cube Designer - Finish page in the Cube Designer wizard, right-click Export Code.

The Export Code dialog box opens. You can select 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. Select 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.

Previous Page

|

Next Page

|

Top of Page

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.