Previous Page

|

Next Page

Maintaining OLAP Groups and OLAP Summary Data Stores

Example: Creating Summary Data for a HOLAP Application

Overview

HOLAP provides access to diverse data sources. An OLAP Group of type HOLAP groups both OLAP Tables and OLAP MDDBs, which together represent the data for one OLAP application.

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 two summary data stores:

• one summary table to store the largest crossing, which has twelve class columns

• one SAS MDDB to store the other eleven crossings.

To produce the appropriate summary data for the HOLAP application:

1. Create an OLAP Group of type HOLAP in which you import all of the columns from the detail data source, assign the OLAP summary roles (class and statistic columns), and define structure definitions (OLAP Cube, dimensions, and hierarchies).

2. Create one OLAP Table in which you import a subset of columns and OLAP summary roles from the group and define one crossing, which consists of twelve class columns.

3. Create one OLAP MDDB in which you import a subset of columns and OLAP summary roles from the group and define eleven crossings.

4. Define a Process Editor Job for the OLAP Group to create the OLAP Table, OLAP MDDB, and a proxy MDDB, which will be used by the HOLAP application to access all the summarized data.

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 HOLAP Group, description, group type HOLAP, owner, and administrator.
Columns Tab	specifies the columns to be included in any OLAP summary data store for the OLAP Group, which do not exist yet. When you specify columns at the group level, you are defining the overall OLAP structure so that you can import the columns into specific OLAP Tables and OLAP MDDBs. You generally specify columns at the group level for the HOLAP group type only. 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 this example are all 34 columns, use the double arrows to move them to Selected Columns, and click OK. You are returned to the Columns tab in the OLAP Group Properties window, which lists the imported columns.
Physical Storage Tab	specifies physical storage attributes. For an OLAP Group, you define physical storage attributes for the HOLAP group type only, which are the attributes for the OLAP Group proxy MDDB. 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 Group

Assign Class Columns

From the Columns tab in the OLAP Group 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 Columns list.

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

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

For this example, class column is assigned to all columns except for 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 Columns list.

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

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 Group

Add an OLAP Cube

An OLAP Cube is defined for the proxy MDDB, which results in one OLAP Cube for the group describing the relationships of all the class columns. For HOLAP, the OLAP Cube is associated with the OLAP Group and is registered in SAS/EIS software as associated with the proxy MDDB.

In 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 at the group level 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 to the OLAP Cube, select the Dimensions label in the organization chart, click the right mouse button, 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 for this example:

Define Hierarchies

Hierarchies are defined for each anticipated hierarchy, which are members 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 for a hierarchy, 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 OLAP Table Properties

For the HOLAP application, the OLAP Table will store the largest crossing, which consists of twelve class columns. In the SAS/Warehouse Administrator Explorer, position the cursor on the OLAP Group HOLAP Group, click the right mouse button, select Add Item, then OLAP Table. In the Explorer window, a new OLAP Table is added under the OLAP Group as follows:

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

General Tab	specifies the table name Sum 12 OLAP Table, description, owner, and administrator.
Columns Tab	specifies the columns to be included in the OLAP Table. The method is similar to specifying the columns as explained for the OLAP Group; however, for the OLAP Table, the columns are imported from the group. To import the columns from the group, click Import to display a list of input sources, and then select OLAP Data Stores. The Import Column Metadata window displays. From the Import Column Metadata window, which lists the available input sources, select OLAP Group HOLAP Group to display its available columns. Select the appropriate columns listed under Columns, which for the OLAP Table are 15 columns from the OLAP Group. Use the double arrows to move them to Selected Columns, and then click OK. You are returned to the Columns tab in the OLAP Table Properties window, which lists the imported columns as follows:
Physical Storage Tab	specifies physical storage attributes for the OLAP Table. This example specifies the storage format SAS and the load technique Refresh. Click Define to open the SAS Table Properties window. Location Tab specifies where the SAS Table is stored.

Import OLAP Summary Roles for the Table

Import Class Columns

This example imports the summary roles already assigned for the OLAP Group. From the Columns tab in the OLAP Table Properties window, click OLAP to open the OLAP Column Roles window.

To import the class columns, in the organization chart, click the right mouse button on the Class Columns label and select Import. The software opens the Selector window.

Select OLAP Group, click Show, select HOLAP Group, and then click OK. The software imports the class columns, and you are returned to the OLAP Column Roles window.

Note:   For each column contained in the group that is not specified for the table, a message will display requiring you to click OK.  

Import Statistic Columns

To import the statistic columns, follow the same steps as explained for importing class columns. That is, in the organization chart, click the right mouse button on the Statistic Columns label and select Import. The software opens the Selector window.

From the Selector window, select OLAP Group, click Show, select HOLAP Group, then click OK. The software imports the statistic columns, and returns you to the OLAP Column Roles window as follows:

Define Crossing for the Table

For the HOLAP application, the OLAP Table will store the largest crossing, which consists of 12 class columns. (For an explanation about how these 12 class columns were determined, see Determining Crossing(s).) The crossing is defined for the OLAP Table as an NWAY crossing, which is a crossing that consists of all the assigned class columns.

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

Note:   For a HOLAP application, you do not need to create an NWAY crossing of all the class columns defined for the OLAP Group, which consists of 31 columns. Because the detail data is available from a permanent data store, the HOLAP application can access all the detail data without it being referenced in a crossing.  

Note:   If an OLAP Table has multiple crossings, the _TYPE_ column will store numeric values that identify which class columns are included in each crossing. If you define more than one crossing for an OLAP Table and do not define a column for this role, SAS/Warehouse Administrator will display a dialog asking whether a column named _TYPE_ can be added. If you say YES, the column will appear in the Columns table and will also be assigned the summary role of _TYPE_. You can rename the column. For an explanation of the values for _TYPE_, see the MEANS procedure in the SAS Procedures Guide.  

Define OLAP MDDB Properties

For the HOLAP application, the OLAP MDDB will store 11 crossings. In the SAS/Warehouse Administrator Explorer, position the cursor on the OLAP Group HOLAP 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 metadata for the OLAP MDDB, 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 11 OLAP MDDB, description, owner, and administrator.
Columns Tab	specifies the columns to be included in the OLAP MDDB. To import the columns from the group is identical to the method explained for the OLAP Table. See Define OLAP Table Properties. Select the appropriate column definitions, which for this example imports 14 columns for the OLAP MDDB from the OLAP Group. Note that pclass is the column included in the OLAP Table but not in the OLAP MDDB. The following window shows the imported columns for the OLAP MDDB:
Physical Storage Tab	specifies the physical storage attributes for the OLAP MDDB. 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.

Import OLAP Summary Roles for the MDDB

To import the class columns and statistic columns, use the same steps as explained for the OLAP Table. See Import OLAP Summary Roles for the Table. The following window displays the imported class columns and statistic columns for the OLAP MDDB:

Define Crossings for the MDDB

For the HOLAP application, the OLAP MDDB will store 11 crossings. The crossings are defined for the OLAP MDDB first as an NWAY crossing, then by creating stairstep crossings from the NWAY 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 remaining 10 crossings, in the organization chart on the NWAY crossing, click the right mouse button, and select Create Stairstep Crossings. Ten crossings are added, with each one having one less column than the crossing before it as follows:

Define Process Editor Job

For the Process Editor Job, the OLAP Group and all its child summary data stores are specified as output targets in a single Job. The Detail Logical Table Sales Detail Grp is specified as the input source for the two summary data stores as well as the OLAP Group.

When an OLAP Group of type HOLAP is specified as an output target, SAS/Warehouse Administrator generates a proxy MDDB. The proxy MDDB is an empty physical file that represents the structure of the data in an OLAP Group and can be used by SAS/EIS software to provide more efficient access to multiple OLAP Tables and OLAP MDDBs. Note that the proxy MDDB automatically creates an NWAY crossing to be used by the HOLAP application.

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 for the Mapping Process Properties window: 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 to create the one-to-one mapping as follows:

Load Step Processes

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.