Maintaining OLAP Groups and OLAP Summary Data Stores

Example: Using an Input Column for Multiple Summary Roles

Suppose you want to use a specific column from an input source as both a class column and an analysis column for OLAP summarization. For example, consider an input table that contains the following columns:

[untitled graphic]

You determine that the OLAP data store needs to store two crossings:

The most detailed level of summarization (an NWAY crossing) uses the class columns: style, bedrooms, and baths. The analysis columns would be price and sqfeet.
At a higher level of summarization, you need a crossing based only on the class column style. The analysis columns would be price, sqfeet, bedrooms, and baths.

Therefore, the input columns bedrooms and baths are used as both class columns and analysis columns. To produce the appropriate results:

Import the columns from the input table to the OLAP data store and add two columns: bedsmin and bathsmin.
Assign OLAP summary roles to the imported columns and to the added columns. That is, the imported columns baths and bedrooms are class columns and the added columns bedsmin and bathsmin are statistic columns.
Assign the desired statistics to the statistic columns. For example, assign the MIN statistic to the bedsmin and bathsmin columns.
Map the input columns to the appropriate output columns. For example, the input column baths is mapped to the output columns baths, which is a class column, and bathsmin, which is a statistic column.

Import and Add Columns to OLAP Summary Data Store

The Columns tab in the object's properties window specifies the columns to be included in an OLAP summary data store, which for this example is the OLAP Table Properties window. This example assumes that the appropriate columns from the input source have been imported as follows:

[untitled graphic]

To add a column that does not exist in the input data, define the column's attributes, and then click Add. (Note that you can also select an existing column, change its name, and then click Add to easily copy a column.) The result of adding columns bathsmin and bedsmin is as follows:

[untitled graphic]

Assign OLAP Summary Roles to the Columns

On the Columns tab, click OLAP to open the OLAP Column Roles window from which you assign OLAP summary roles. The assigned summary roles are as follows:

[untitled graphic]

The following screen displays the defined crossings for the data store:

[untitled graphic]

Define Mapping Process in Process Editor Job

Column mapping is defined as one-to-one mapping. To map the columns:

From the Column Mapping tab in the Mapping Process Properties window, click 1 to 1 Mappings, which opens the One-to-One Column Mapping window, and then click Quick Map, which produces the following results. Note that the two added columns bathsmin and bedsmin are not mapped.
Select the input column baths from Source Column, select the output column bathsmin from Target Column, and then click the right arrow to map the two columns.
Select the input column bedrooms from Source Column, select the output column bedsmin from Target Column, and then click the right arrow to map the two columns.

When you click OK, you are returned to the Column Mapping tab in the Mapping Process Properties window as follows:

[untitled graphic]

After the Job is executed and the table is loaded, the results are displayed as follows:

[untitled graphic]

Top of Page