Defining aggregate rules for semi-additive measures

For measures of dimensionally modeled relational metadata, you can define an aggregate rule for each related dimension. These rules are in addition to the Regular Aggregate property and are used to specify how semi-additive measures are to be aggregated with respect to information from that dimension. A semi-additive measure is one that is to be summed for some dimensions, but should not be summed across some other dimensions. For the dimensions over which the measure is not additive, a different aggregation rule must be specified.

If a measure expression contains an aggregation function, the aggregation rule for that measure is ignored.

Aggregate rules are applied in this order:

  • The Regular Aggregate property is applied to dimensions that are included in the query but are not referenced in the Aggregate Rules dialog box
  • The aggregation that is specified in the Aggregate Rules dialog box is applied to their specified dimensions, in the order that you specified the rules
  • The report-level aggregation that is specified in the query

For example, inventory is recorded in the data warehouse with opening and closing balances at the month grain. If you need to report on inventory for different periods of time, you apply an aggregate that takes the value from the appropriate time slice within the period. For an opening balance, the value is the last balance of the previous month. For a closing balance, the value is the last balance of the current month. In this example, the inventory measure has a Regular Aggregate property of total and an aggregate rule for the time dimension with a value of last.

You can have only one aggregate rule for each dimension.

Note: There must be a direct relationship between the underlying query subjects from which the measure and dimension are constructed. For example, consider the Inventory measure dimension and the Time dimension:

scope relationship between inventory dimension and time dimension

You want to define an aggregate rule for Closing inventory that generates the last closing inventory recorded for any given period of time:

aggregate rules dialog box

For IBM® Cognos® to apply the aggregate rule of last on Closing inventory based on the Time dimension, there must be a direct relationship between the underlying query subjects used to construct the Time dimension and the Closing inventory measure. This is in addition to the scope relationship defined between the Inventory measure dimension and the Time dimension:

direct relationship between query subjects in inventory dimension and time dimension
Note: In some cases, differences in the default behavior may exist between DQM and CQM. For information about how you can control these differences, see CQM and DQM return different default results for aggregate rules.

If a relationship does not exist, running a query that contains the measure Closing inventory generates the following error:

RQP-DEF-0564 Unable to determine fact grain of semi-additive dimension '[Dimensional view].[Time dimension]' for measure '[Dimensional view].[Inventory].[Closing inventory]'. Verify that a relationship is defined between the underlying query subjects from which the dimensions are constructed.

Semi-additive measures are not supported for sparse data. Sparse data is multidimensional data in which many of the combinations of dimension values do not have related fact data.

For SAP BW metadata, the Aggregate Rules property value is read-only. The Semi-Aggregate property is used instead.

Procedure

  1. Click the measure you want to work with.
  2. In the Properties pane, click the ellipsis (...) button in the Aggregate Rules property.
  3. To add a dimension for this measure, click Add and select the dimension.

    You can include a shortcut to a dimension if there is a scope relationship between the measure dimension and the regular dimension.

  4. To specify the aggregate operation, click the ellipsis (...) button in the Aggregation Rules box.

    The following operations are supported:

    • Sum
    • Minimum
    • Maximum
    • Average
    • First
    • Last
  5. If you want to change the order of the aggregate rules, use the arrow buttons.
  6. Click OK.

Results

After you set an aggregate rule, the icon for the measure changes.