Setting Up Dimension-Specific Aggregate Rules for Logical Columns

The majority of measures have the same aggregation rule for each dimension. However, some measures can have different aggregation rules for different dimensions. For example, bank balances might be averaged over time but summed over the individual accounts. The Oracle BI Server allows you to configure dimension-specific aggregation rules. You can specify one aggregation rule for a given dimension and specify other rules to apply to other dimensions.

You need to configure dimensions in the Business Model and Mapping layer to set up dimension-specific aggregation. For more information about setting up aggregate navigation, refer to Setting Up Fragmentation Content in an Oracle BI Repository for Aggregate Navigation.

To specify dimension-specific aggregation rules for a single logical column

1. In the Business Model and Mapping layer, double-click a logical column.
2. In the Logical Column dialog box, click the Aggregation tab.
3. In the Aggregation tab, select the Based on dimensions check box.
   The Browse dialog box automatically opens.
4. In the Browse dialog box, click New, select a dimension over which you want to aggregate, and then click OK.
5. In the Aggregation tab, from the Formula drop-down list, select a rule.
   NOTE:  After selecting rules for specified dimensions, set the aggregation rule for any remaining dimensions by using the dimension labeled Other.
6. If you need to create more complex formulas, click the ellipsis button to the right of the Formula column to open the Expression Builder.
7. If you have multiple dimensions, to change the order in which the dimension-specific rules are performed, click Up or Down.
   When calculating the measure, aggregation rules are applied in the order (top to bottom) established in the dialog box.
8. Click OK

To specify dimension-specific aggregation rules for multiple logical fact columns

1. In the Business Model and Mapping layer, select multiple logical fact columns.
2. Right-click and select Set Aggregation.
   If the fact column has an aggregation rule, Set Aggregation will not appear in the menu.
3. In the Aggregation dialog box, select or clear the All columns the same check box.
   The check box is checked by default. When checked, you can set aggregation rules that will apply to all selected columns. If you clear the check box, you can set aggregation rules separately for each selected column.
4. In the Aggregation tab, click the Use advanced options check box.
5. In the Browse dialog box, select a dimension over which you want to perform aggregation, and then click OK.
   After setting up the rule for a dimension, specify aggregation rules for any other dimensions in the entry labeled Other.
6. Click the ellipsis button to the right of the Formula column.
7. In the Expression Builder - Aggregate dialog box, from the Formula drop-down list, select the aggregation to perform over the dimension.
8. To change the order in which the dimension-specific rules are performed, click Up or Down, and then click OK.
   When calculating the measure, aggregation rules are applied in the order (top to bottom) established in the dialog box.