Previous Page

|

Next Page

Maintaining OLAP Groups and OLAP Summary Data Stores

Example: Creating Summary Data for a MOLAP Application

Overview

MOLAP supports OLAP performed on a multidimensional database, such as a SAS MDDB. Using the Sales Detail Grp Detail Logical Table discussed in this chapter, consider the design of an OLAP application that calls for storage strategy of one SAS MDDB; the MOLAP application will store all the necessary crossings.

To produce the appropriate summary data for the MOLAP application:

1. Create an OLAP Group of type MOLAP in which you assign the group name and the group type of MOLAP.

2. Create one OLAP MDDB in which you import all of the columns from the detail data source, assign the OLAP summary roles (class and statistic columns), define structure definitions (OLAP Cube, dimensions, and hierarchies), and define multiple crossings.

3. Define a Process Editor Job for the OLAP MDDB.

Note:   The following explanations describe the metadata and methods used to achieve the desired results. It is assumed that the appropriate Data Warehouse Environment, Data Warehouse, Subject, and Detail Logical Table exist.  

Define OLAP Group Properties

In the SAS/Warehouse Administrator Explorer, position the cursor on the Subject Toy Sales, click the right mouse button, select Add Item, and then OLAP Group. In the Explorer window, a new OLAP Group is added under the Subject as follows:

To update the default metadata for the OLAP Group, position the cursor on its icon, click the right mouse button, and select Properties. The OLAP Group Properties window displays for you to enter the appropriate information.

General Tab

specifies the group name MOLAP Group, description, group type MOLAP, owner, and administrator.

Define OLAP MDDB Properties

For the MOLAP application, the OLAP MDDB will store 12 crossings. In the SAS/Warehouse Administrator Explorer, position the cursor on the OLAP Group MOLAP Group, click the right mouse button, select Add Item, and then OLAP MDDB. In the Explorer window, a new OLAP MDDB is added under the OLAP Group as follows:

To update the default metadata for the OLAP MDDB Properties window, position the cursor on its icon, click the right mouse button, and select Properties. The OLAP MDDB Properties window displays for you to enter the appropriate information.

General Tab	specifies the MDDB name Sum 12 OLAP MDDB, description, owner, and administrator.
Columns Tab	specifies the columns to be included in the OLAP MDDB. To import columns from an input source, click Import to display a list of input sources, and then select one (for example Detail Logical Tables). The Import Column Metadata window displays. From the Import Column Metadata window, which lists the available input sources, select the Detail Logical Table Sales Detail Grp to display its available columns. Select the appropriate columns listed under Columns, which for the OLAP MDDB are all 34 columns in the Detail Logical View. Use the double arrows to move them to Selected Columns, and then click OK. You are returned to the Columns tab in the OLAP MDDB Properties window, which lists the imported columns as follows:
Physical Storage Tab	specifies physical storage attributes. This example specifies the storage format MDDB and the load technique Refresh. Click Define to open the SAS MDDB Properties window. Location Tab specifies where the SAS MDDB is stored.

Assign OLAP Summary Roles for the MDDB

Assign Class Columns

From the Columns tab in the OLAP MDDB Properties window, click OLAP to open the OLAP Column Roles window from which you assign specific OLAP summary roles.

To assign class columns:

1. Select the columns from the list under Columns.

2. Select the Class Columns summary role label in the organization chart under OLAP Roles.

3. Click the right arrow to add the columns to the summary role.

For this example, class columns are assigned to all columns except three, which will be assigned as statistic columns. The default sort order ASCENDING is used for all class columns.

Assign Statistic Columns

To assign statistic columns:

1. Select the columns from the list under Columns.

2. Select the Statistic Columns summary role label in the organization chart under OLAP Roles.

3. Click the right arrow to add the columns to the summary role.

For this example, statistic column is assigned to the remaining three columns, which are the numeric columns costs, units_so, and revenue. All three are assigned the default statistic SUM.

Define OLAP Structure Definitions for the MDDB

Add an OLAP Cube

From the OLAP Column Roles window, add an OLAP Cube by selecting the OLAP Cube label in the organization chart. Then click the right mouse button, and select New.

Define Dimensions

Dimension objects need to be defined for each anticipated dimension. As discussed in Determining Dimensions in the Data, this example defines the following dimensions:

• Geography

• Time

• Product

• CustomerGender

• CustomerAge

• CustomerIncome

• Drop

• Promotion

To define a dimension for the OLAP Cube, select the Dimensions label in the organization chart, click the right mouse button, and then select New.

To specify a name and a description for a dimension, click the right mouse button on the Dimension object, select Properties, and then update the default metadata. The next window displays the defined dimensions:

Define Hierarchies

Hierarchies are defined for each anticipated hierarchy, which is a member of a dimension. For example, the Time dimension will include these hierarchies:

• TimeWeek

• TimeQuarter

• TimeSeason

To define a hierarchy for a dimension, select the Hierarchies label in the organization chart associated with the appropriate dimension (for example, Time) click the right mouse button, and then select New.

To specify a name (for example, TimeWeek) and a description, click the right mouse button on the Hierarchy object, select Properties, and then update the default metadata.

Then, to specify the appropriate column(s) for the hierarchy (such as year, week, and date) select the class columns from the list under Columns, select the Columns label associated with the hierarchy object in the organization chart, and click the right arrow to add the columns to the object.

The following window displays the defined hierarchies with appropriate columns for the Time dimension:

Define Crossings for the MDDB

For the MOLAP application, the OLAP MDDB will store the following crossings:

• an NWAY crossing, which consists of all the assigned class columns

• one crossing for the 12 most likely accessed class columns

• 11 crossings, with each one having one less column than the crossing with the 12 class columns.

Note:   For a MOLAP application, you must create an NWAY crossing for all the class columns defined for the MDDB, which consists of 31 columns. Even though the detail data is available from a permanent data store, a MOLAP application (unlike a HOLAP application) cannot access all the detail data without it being referenced in a crossing.  

To define the NWAY crossing, in the organization chart on the Crossings label, click the right mouse button, and select Create NWAY Crossing. The resulting NWAY crossing follows:

To create the crossing to represent the 12 most likely accessed class columns, in the organization chart on the Crossings label, click the right mouse button and select New. For an explanation about how these 12 class columns were determined, see Determining Crossing(s). The new crossing is added under the Crossings label as follows:

To assign a name to the crossing, click the right mouse button on the crossing and select Properties.

To specify the class columns for the crossing:

1. Select the columns from the Columns list.

2. Select the Columns label in the organization chart under the added crossing.

3. Click the right arrow to add the columns to the crossing.

The following window displays the added crossing with the name 12 Columns Crossing and the added columns:

To create the remaining 11 crossings, in the organization chart on the crossing 12 Columns Crossing, click the right mouse button, and select Create Stairstep Crossings. Eleven crossings are added, with each one having one less column than the crossing before it.

Define Process Editor Job

In the Process Editor Job, the OLAP MDDB SUM 12 OLAP MDDB is specified as the output target. The Detail Logical Table Sales Detail Grp is specified as the input source. The following Process Editor window displays the Process Flow for the Job:

The processes defined in the Job are summarized as follows:

Mapping Process

The source code to map columns is generated by SAS/Warehouse Administrator, rather than user-written, as shown in the Source Code tab: Column mapping is defined as one-to-one mapping. To produce one-to-one mapping, first click 1 to 1 Mappings on the Column Mapping tab in the Mapping Process Properties window, which opens the One-to-One Column Mapping window. Then, click Quick Map, which produces the following results:

Load Step Process

The source code is generated by SAS/Warehouse Administrator, rather than user-written, as shown in the MDDB Load Process Attributes window:

For more information about Process Editor Jobs, see Maintaining Jobs. For more information about processes, see Maintaining Processes.

Previous Page

|

Next Page

|

Top of Page

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