OBIEE - Aggregation Rules and Functions

The following list describes the aggregation rules that are available for measure columns in the "Layout pane" and for columns in the "Edit Column Formula dialog: Column Formula tab". The list also includes functions that you can use when creating a calculated item.

• Default — Applies the default aggregation rule as in the Oracle BI repository or by the original author of the analysis. Not available for calculated items.
• Server Determined — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed within Presentation Services for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items.
• Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.
• Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.
• Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.
• Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.
• First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
• Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
• Count — Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the number of rows with nonnull values for that column are returned.
• Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.
• None — Applies no aggregation. Not available for calculated items.
• Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items.
• Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog or for calculated items. Only available for attribute columns.

Aggregates, source control and Monitoring

I have been fortunate enough to have some time to do some housekeeping on the OBIEE systems.

Monitoring.

I think it's fair to say that we had very little monitoring in place, and we knew things were bad when users phoned to say that their pages were 'slow'. Now to get a user to phone you takes some doing, usually they'll live with it, so things must get pretty bad before they do. Luckily this is only n our exploratory areas, otherwise people would be questioning just what it is we do.

Source Control

No-one like interrupting their work to perform a manual step, like source control. it is one of those things that somehow gets missed the one time you really need it. I've been looking at ways to automate this, behind the scenes, totally hands off using SVN (Subversion)

Aggregates 

Need I say more. Level heirarchies and aggregates go hand in hand. If you can provide the least number of rows back to satisfy your answers report, it will run a lot faster that running an aggregate operation over raw data.

Expect a couple of posts over the coming days detailing just what I've been doing.