Controlling How Measures Roll Up

The rollup function specifies how measure values are summarized, or rolled up, from child categories to their parent categories. Summarization occurs when your OLAP reporting component displays measure values in categories above the lowest level of detail.

When the type of rollup used is specified in the Description box on the Measure property sheet, other users can reference this information. The three kinds of rollup are as follows:

• Regular rollup combines records with identical values in non-measure columns. It is applied to the time dimension by default unless the Time state rollup option is specified for the measure.

• Time state rollup combines records with identical values in the time dimension according to the Time state rollup function selected.

  For example, level of inventory is recorded for a specific product, at the same warehouse, in the same year, on different dates. If you select Average and the Degree of detail set for the column is Day, the identical records are summed and then divided by the number of day categories in the month. However, if you select Last period, the inventory value for the last day of the month is shown.

• Duplicates rollup is automatically used whenever the PowerCube Consolidate feature is turned on.

If you select both Regular rollup and Time state rollup, regular rollup is performed first, followed by the time state rollup.

If the measure is used as a weighted value, as occurs during measure allocation, you can use only the Sum rollup type.

When a model has more than one time dimension, you cannot use time state rollup.

Set a Regular Rollup Function for Measures

By default, the values for a measure are automatically totalled using the Sum function. Depending on the context, you may change this default rollup to Minimum, Maximum, Average, Count, Count All, Any, or External.

For example, suppose your sales analysis model contains a measure named QTY. You want your OLAP reports to show the average monthly quantity sold for each store. You use Regular rollup (the Average function) to obtain the correct result. Your source data is as follows.

After applying the Average rollup function, the following results appear in your report.

Calculated measures can be computed after rollup or before rollup. When the measures are calculated before rollup, the values are those calculated in the data source.

For the time dimension, when you select Average as your regular rollup type, the records are first summed and the result is then divided by the number of records in the rollup time period.

The formula for a first-quarter average involving 78 records can be represented as follows:

Steps

1. Open the Measure property sheet and click the Rollup tab.

2. In the Regular rollup box, select a rollup function.

   If you select the Average function, you can weight the average by selecting a measure from the Regular weight box. However, the weighting measure must have a rollup type of Default (Sum) or Sum.

3. Click OK.

Create Cubes with External Rollups

In Transformer, you use the rollup function to summarize measure values in the cube. If you have specific data that you do not want changed within the cube, you can use externally rolled up measures to maintain control over which values are stored and displayed.

To create a cube with externally rolled up measures, you first define which measures are to be externally rolled up, you then create the required date categories and, finally, you specify the Degree of detail for allocations, if applicable.

Externally rolled up measures can be used with alternate drill-down paths, special categories, and partitioned cubes, including those partitioned on the time dimension.

Each data record in an externally rolled up transactional data source uses a category code to reference the category in the model. To avoid ambiguity and unpredictable results, ensure that each code uniquely identifies a specific category in the source file. Otherwise, Transformer makes the code unique by adding a tilde (~) character with a numeric suffix, creating a blended expression that cannot be interpreted by other IBM Cognos 8 components.

For example, suppose your cube contains more than one instance of the category code Item, and so renames the second instance Item~1, the third instance Item~2, and so on. These category codes are numbered as they are encountered in the model. This means that the codes do not stay the same, in a predictable sequence, as your organization (and model) evolves. The tilde characters are interpreted as non-numeric and, because these codes do not appear in the data source, they are ignored during the consolidation pass. As a result, duplicate records are overridden and not summed, despite what may be reported in the log file.

To avoid such problems, use one of the following strategies to make these codes unique:

• Ensure that all source values are unique in a dimension.

• Create a calculated column in Transformer, to make your categories unique.

• Edit the .mdl model file to make the category codes unique.

  For more information about using the Model Definition Language (MDL), see the Transformer Developer Guide.

For each special category that has more than one child category, you must provide the externally rolled up value. Otherwise, the special category value will be missing and, by default, be reported as zero. If the special category has only one child, you can either supply a value for the special category or accept the value taken from the child.

Steps

1. After you have imported the structural and transactional data sources into your model, open the Data Source property sheet for the transactional data source and click the General tab.

2. Click the Contains externally rolled up measure values check box and click OK.

3. Add the measures to the Measures list.

4. Open the Dimension property sheet for each dimension that is to have externally rolled up measures applied to it and click the General tab.

5. In the External rollup column box, click Add to specify which transactional column contains the category codes the measure values will map to.

6. In the New Association dialog box, select Source from the Association role drop-down list, and click More to select the external rollup column. Click OK three times.

7. For each externally rolled up measure, open the Measure property sheet and click the Rollup tab.

8. In the Regular rollup box, click External.

   Note: For date dimensions or dimensions without unique source values, specify the column from which category codes can be assigned in order to make these codes unique in the dimension, as follows:

   • Open the Level property sheet for each level in each dimension with externally rolled up measures.

   • In the Associations box, select the source column and click the ellipsis button (...) to open the New Association dialog box.

   • In the New Association dialog box, select the Association role, and then click the More button to select the structural source column to map to the level.

   • If you are using allocations, on the Column property sheet, set the Degree of detail to specify the lowest level to which the associated measure applies.

9. Create the cube by clicking Create PowerCubes from the Run menu.

Set a Time State Rollup Function for Measures

The time state for a measure is its value at a specific point in time, such as the inventory quantity of an item at the end of the month. You set the Degree of detail property to the lowest level of detail for the transaction; that is, the values at the bottom of the hierarchical tree. For example, if your source file contains daily transactional records, the degree of detail is Day.

You can use any of the following Time state rollup functions to summarize the values from your non-measure columns: Minimum, Maximum, First period, Last period, Current period, and Average.

Constraints

There are several constraints that apply when defining time-state rollups:

• Regular rollup is not applied to a time dimension if Time state rollup is selected. Also, you cannot apply summarized views to a time dimension when the model contains a measure with Time state rollup applied.

• Time state rollup cannot be applied to a measure if the model has more than one time dimension.

• If you use Time state rollup but more than one record is associated with each data point, values will be summarized using Regular rollup. For example, if there are two inventory stock counts daily, the two records will be summed even though you specified a Time state rollup function of Last period.

• You can use calculated columns to consolidate multiple measurements. If you do, you should also set the Regular Timing option to Before Rollup. Otherwise, the measures are calculated after rollup (the default), which yields inaccurate results.

• If a record is missing, you may get zeros when you apply a rollup. For example, if you select a time state rollup of Last period, but your source data does not contain records for every day of every month, then whenever values are missing for the last day of the month, the OLAP report will show a zero.

• A time-state rollup of Average may also result in zeros, but you can specify that any missing values display as NA (or na) in the OLAP reporting components.

• If you select a time-state rollup of Average, the Days in week selection affects the results. If the Degree of detail is Day, and your Days in week selection excludes weekends, the records for each month are summed and then divided by the number of categories (non-weekend days) in that month.

  

Steps

1. Open the Measure property sheet and click the Rollup tab.

2. In the Time state rollup box, select a rollup function.

   If you select the Average function, you can weight the average by selecting a measure from the Time state weight box. However, the measure associated with the weighted values must have a Rollup function of Default (Sum) or Sum.

3. Click OK.

Example - Setting a Last Period Time State Rollup for Inventory Counts

Your source data contains warehouse inventory counts for the middle and end of each month. You want an OLAP report that shows inventory at the end of each quarter.

Your source data is as follows:

You select Last period from the Time state rollup drop-down list of functions for the quarter date level.

Your OLAP report shows the following quarterly data, and the year-level rollup shows the last inventory count recorded.

Ignore Null and Missing Values in Specified Time State Rollups

You can specify that null and missing values be ignored when applying average or weighted average time-state rollups.

You can achieve the same result using the Model Definition Language (MDL), by setting the IgnoreMissingValue keyword to TRUE when you create or update the definition for a supported measure type.

However, Transformer only supports this feature if you specify that missing values be treated as NA on the Measure property sheet. Also, you must retain the default setting for First period, Last period, and Current period. That is, null and missing values cannot be excluded from the rollup calculations for these measure types.

Steps

1. Open the Measure property sheet and click the General tab.

2. In the Missing value box, select NA.

3. Click the Rollup tab.

4. Select the Ignore missing values in average and weighted average time-state rollups check box and click OK.

   Note: If the rollup measure is of type First period, Last period, or Current period, the Ignore missing values in average and weighted average time-state rollups check box is disabled. Missing (null) data values are always excluded from Minimum and Maximum calculations for rollups, whether they are set to display as 0 or na in the OLAP reporting components.

Set Regular and Time State Rollup Together

You can use Regular rollup and Time state rollup together to summarize measure values in your OLAP reports. When both are selected, regular rollup is performed first, followed by time-state rollup.

Constraints

There are several constraints that apply when defining these two types of rollup together:

• Transformer cannot perform Time state rollup for a measure when there is more than one time dimension in the model.

• You cannot apply summarized views to a time dimension if the model contains a measure with Time state rollup applied.

• For the time dimension, if you select Average for both Regular and Time state rollup, Transformer performs Regular rollup first. It then sums the remaining consolidated records and divides by the number of leaf categories in the time period. Lastly, it sums the remaining records and divides by the number of months in the quarter or days in the month.

Steps

1. Open the Measure property sheet and click the Rollup tab.

2. In the Regular rollup box, select a rollup function.

   If you select the Average function, you can weight the average by selecting a measure from the Regular weight box. However, the weighting measure must have a rollup function of Default (Sum) or Sum.

3. In the Time state rollup box, click a rollup function.

   If you select the Average function, you can weight the average by selecting a measure from the Time state weight box. However, the weighting measure must have a rollup function of Default (Sum) or Sum.

4. Click OK.

Example - Jointly Setting Regular and Time-State Rollup

Your source data contains the inventory levels for the middle and end of each month in all warehouses. You want your OLAP report to show the total inventory at the end of each quarter. To obtain the correct result, you define a view that removes the warehouse category, and then use Regular rollup (Sum) and Time state rollup (Last period).

Your source data is as follows:

You select Regular rollup for each period, and then apply Time state rollup.

The first rollup, Sum, yields QTY values of 900 for 20061115 and 800 for 20061130. The second rollup, Last period, yields a QTY of 800 for 200612.