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.
- In the Business Model and Mapping layer, double-click a logical column.
- In the Logical Column dialog box, click the Aggregation tab.
- In the Aggregation tab, select the Based on dimensions check box.
- In the Browse dialog box, click New, select a dimension over which you want to aggregate, and then click OK.
- In the Aggregation tab, from the Formula drop-down list, select a rule.
- If you need to create more complex formulas, click the ellipsis button to the right of the Formula column to open the Expression Builder.
- If you have multiple dimensions, to change the order in which the dimension-specific rules are performed, click Up or Down.
- Click OK
- In the Business Model and Mapping layer, select multiple logical fact columns.
- Right-click and select Set Aggregation.
- In the Aggregation dialog box, select or clear the All columns the same check box.
- In the Aggregation tab, click the Use advanced options check box.
- In the Browse dialog box, select a dimension over which you want to perform aggregation, and then click OK.
- Click the ellipsis button to the right of the Formula column.
- In the Expression Builder - Aggregate dialog box, from the Formula drop-down list, select the aggregation to perform over the dimension.
- To change the order in which the dimension-specific rules are performed, click Up or Down, and then click OK.