Consolidation

Consolidation uses rollups to combine records with identical non-measure values into a single record, thereby reducing cube size and improving run-time performance in your OLAP reporting component.

Records may have identical non-measure values in the following circumstances:

1. The source contains transactions with identical non-measure values.

   For example, two sales of the same product are made to the same customer on the same day.

2. The degree of detail permits it.

   For example, the Degree of detail for a column associated with the time dimension is set to Month, so Day values in the source transactions are ignored during consolidation.

3. A dimension is omitted from the cube.

   For example, two sales of the same product are made at different stores on the same day. If stores are omitted from the cube, the sales records have identical non-measure values.

4. Categories in the cube are summarized or suppressed.

   For example, two sales of the same product are made to the same customer on the same day, but the colors differ. If colors are omitted from the cube by using either of these dimension view options, the sales records have identical non-measure values.

In cases 1, 2 and 3, consolidation uses Duplicates rollup to combine records with identical values in their non-measure columns. In case 4, unless Time state rollup is selected on the Measure property sheet, consolidation uses Regular rollup to combine records with values made identical through the use of dimension views.

Notes

When consolidating data, the following additional considerations apply:

• A cube will not be consolidated if Time state rollup is defined for it, or if other roll-up actions will conflict with consolidation.

• Consolidation occurs automatically if cubes use auto-partitioning.

• You can specify which type of consolidation to use: either Yes (with sort) or Yes (presort).

• The order in which rollups are done affects cube output: Duplicates rollup precedes Regular rollup.

Tip: To combine records without affecting the cube, you can use Regular rollup and Time state rollup without consolidation. These combinations only affect how measure values are aggregated at run time. For example, you can use a Last period time state rollup for inventory data, so that your reports show only the last measure values for each time period.

Example - Consolidating Data Using Duplicates Rollup (Sum)

You want to specify that duplicate records be summed as they are rolled up through the levels in your time dimension.

Suppose your source data contains daily transaction records such as the following, which are used to create the Date and Region dimensions.

Because the Degree of detail is set to Month, day values are ignored. Records for the same month and store are consolidated (summed).

The results after Duplicates rollup (Sum) are as follows.

After Regular rollup (Average), the monthly records for each Store in the East region are consolidated again.

Your cube has a dimension view where store values are aggregated to the East level.

Set a Duplicates Rollup Function for Measures

The Duplicates rollup function specifies how records containing identical non-measure values (category names) are aggregated. The records may or may not have different measure values.

The default Duplicates rollup for each measure is None (Regular rollup). However, you can change the setting to Sum, Minimum, Maximum, Average, First, or Last.

You can explicitly request consolidation by changing the Consolidate setting on the General tab of the PowerCube property sheet.

If values for calculated measures are generated before rollup, Transformer cannot perform consolidation. However, you can overcome this problem by using calculated columns. Because such values are always calculated before rollup, consolidation is again possible.

Steps

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

2. In the Duplicates rollup box, select a rollup function, and click OK.

   If you select Average, you can weight the average by selecting a measure from the Duplicate weight box.

3. If you have not yet built your cube, from the Run menu, click Create PowerCubes.

4. Open the property sheet for your cube and click the General tab.

5. In the Consolidate box, click Yes (with sort) or Yes (presort).

Example - Consolidating Data Using a Duplicates Rollup of Average

Although duplicate categories have their measure values summed by default, in some cases, you may want to specify a different rollup function, such as Average.

For example, suppose the following before-rollup records are duplicates.

The results after Duplicates rollup (Average) are as follows: