Aggregate Awareness in Business Objects Universe Design

Aggregate awareness is a term that describes the ability of a universe to make use of aggregate tables in a database. There are times when you want to run query againts summary tables rather then normal non aggregated tables because of the amount of data calculation is involved.

This improves execution speed of queries and report performance.You must refreshed aggregate table data at the same time you refresh fact tables.

This technique is useful when you are working with data warehouse. (ex:Geography, time or product dimension).

There few steps involve in creating aggregate tables.
1. First you need to identify all combination of aggregate objects
2. Arrange objects in aggregate level ( from highest level of aggregation to level of aggregation).

ex: a. AAYear.Revenue
b. AAQtr.Revenue
c. AAMonth.Revenue
d. Product.Price * Order.Quantity

and then define the Select statement using the @Aggregate_Aware
function (@Aggregate_Aware(sum(agg_table_1), … sum(agg_table_n)) for all aggregate aware objects.

where agg_table_1 is the aggregate with the highest level of aggregation,and agg_table_n the aggregate with the lowest level.

@Aggregate_Aware(sum (AAYEAR.REVENUE), sum(AAQTR.REVENUE),
sum (AAMONTH.REVENUE), sum(PRODUCTS.PRICE*ORDER.QUANT))

You must also specify the incompatible objects for each aggregate table in the universe by useing the “Aggregate Navigation” dialog box (Tools > Aggregate Navigation) to specify the incompatible objects.

With respect to an aggregate table, an object is either compatible or incompatible. The rules for compatibility are as follows:

• When an object is at the same or higher level of aggregation as the table, it is compatible with the table.

• When an object is at a lower level of aggregation than the table (or if it is not at all related to the table), it is incompatible with the table.

When a database contains one or more aggregate tables, you should resolve any loops using contexts.

The final step in setting up aggregate awareness is to test the results in Web Intelligence by running following queries and then
compare the different results:
• Order Year against Sales Revenue.
• Order Quarter against Sales Revenue.
• Order Month against Sales Revenue.
• Customer against Sales Revenue.
• Product against Sales Revenue.

source : Business Objects Universe Design Guide

Leave a Reply