OBIEE - Aggregation Rules

Talking of aggregation rules in OBIEE, people uses Sum, Average and Count most commonly. Some of them are not even aware in what scenario the other aggregation rules can be used. In this post I am going to show a real-life business scenario for use of some of the other aggregation rules.


Take a look at following accounting data. There's a fact holding Opening Balance, Debit Amount, Credit Amount and Closing balance for accounts on daily basis. We have a Time dimension with hierarchy Year-Month-Week-Day.





What aggregation rule will apply to "Opening Balance" measure? Ideally if I look at "Wk2", it should give me value $2,359 i.e. opening balance of first day of "Wk2". Similary, if I look at Jan 2010, it should give me value $1,000 i.e opening balance of the month. This cannot be achieved with commonly used Sum or Average aggregation rules.


Some people suggests, we can create Year, Month and Week level aggregate tables and design aggregate navigation. Technically this is right, but what if a new level (Quarter) gets introduce at later point. You will require changes at many places.


Now let see how we can solve this scenario using aggregation rules. On the aggregation tab select "Based on dimnsions" checkbox.

A list will show up with "Other" dimension with "Sum" aggregation for it. Leave this as is. Cilck on "New" button at the bottom of the dailog.

In the dimension list select Time dimension.






In the formula dropdown select formula with aggregation rule of "FIRST".






Defining "FIRST" aggregation formula on Time dimension ensures that, for the non-granular members (such as Year, Month, Week), value of its first granular member is returned.


Why "Sum" aggregation needed for "Other" dimension? The example data above just shows information of one account, but ideally we will have more such accounts and we want to sum up value of opening balance of all account to know the total opening balance at a particular time.


Finally, we can handle closing balance too in similar way by choosing "LAST" aggregation formula.