Aggregate Awareness in BO (@Aggregate_Aware in BO Designer)

Aggregate Aware is one of the powerful functions in BO and very common / famous function in Business Objects Designer and reports. It will speeds up the execution of queries, improving the performance of SQL transactions using database Aggregate Tables.

Note: When you have Daily & Monthly tables with same set of columns, you can use @aggregate_aware function and create a single object for both and it will reduce your universe support and maintenance cost. It’s not necessary to have only aggregate tables. It will be used for either Measure or dimension objects.

•    @Aggregate_aware(sum(MonthlyTable.measure),sum(DailyTable.measure))
•    @Aggregate_aware(MonthlyTable.dimesion,DailyTable.Dimension)

How aggregate Awareness helping to improve your report performance and process to follow for getting the benefits in your reports.

Universe Structure without Aggregate tables:

Example, we have 5 Dimension tables and one fact table which holds complete data transactional data in data warehouse. Based on the dimension in your report to get the data is really huge load to that fact table and the report won’t finish as you expected. Fact table hold the data for Daily transaction for each, customer, Product and branch and this can be expressed as follows.

365 Days X 5 Products X 2 Country X 100 Customer = 365000 Rows

Below is the Sample Data set for the Fact table.
AA1When you create reports for Daily / Monthly / Quarterly / yearly from this fact table along with some dimension, your report will read complete table data sets and database engine must add up a large number of rows.

Below is the universe structure for Fact and Dimension model of your current data model.
AA2To avoid the complexity of complete table scan/read/execution, we can create summarized tables for Monthly, Quarterly and Yearly in your database from the Fact table. Summarized tables will have calculated data for that particular time periods and your report will read only that particular table to get the data.

Universe Structure with Aggregate tables:

Summary (Aggregated Tables) tables created in Database level and those are physically stored the data in pre calculated manner.  Keeping these aggregated tables in BO universe designer you can created an aggregated aware objects using @Aggregate_aware function. Aggregate Awareness function will decide which table to choose on the fly based on your time Period (daily, Monthly, Quarterly & Yearly) selection to query the data from either one of your aggregated tables.

Below is the universe structure with aggregated tables.
AA3Each aggregated tables will have join with other dimensions tables and along with Time Dim data Sets (see below) by Month, Quarter and Year.

AA4Below is the detailed schema how the aggregated tables are joined with Time Dimension.

AA5Once you created joins between the aggregated tables with other dimensions tables, you have to create the Aggregate Objects and need to define the compatible & incompatible objects for those aggregated tables.

Create Aggregate Objects

Before creating the aggregate objects, level of aggregation should be identified and as follows:

  • YearSalesFact.Revenue is the highest level of aggregation.
  • QuarterSalesFact.Revenue is the next level.
  • MonthSalesFact.Revenue is the next level.
  • SalesFact.Revenue is the lowest level of aggregation.

Define the @Aggregate_aware function object as mentioned below.

@Aggregate_Aware(sum(agg_table_1), … sum(agg_table_n)) —> agg_table_1 is the aggregate with the highest level of aggregation, and agg_table_n the aggregate with the lowest level.

Object Name: Sales Revenue
Select : @Aggregate_Aware(sum(YearSalesFact.Revenue), sum(QuarterSalesFact.Revenue),
Sum(MonthSalesFact.Revenue), sum(SalesFact.Revenue ))
AA6Define Compatible and In-Compatible Objects:

Compatible / Incompatible objects must be specified for each aggregate table in the universe. The set of
Compatible / Incompatible objects specify determines which aggregate tables are disregarded during the generation of SQL in the report.

Compatible / Incompatible definition can be done in Universe Designer, Tools – Aggregate Navigation option.
AA7An object is either compatible or incompatible with respect to the Aggregate table. The rules for compatibility 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.

As per above example, below is the details of in-compatible objects.

Year Object is in-compatible for Day, Week, Month & Quarter (Since Year is having highest level of aggregated data)aa81Quarter Object is in-compatible for Day, Week and Month.

aa91Month Object is in-compatible for Day, Week.

aa101Day is in-compatible for aggregated objects.

aa111Report Query SQL generation:

When you create a report based on Yearly Sales, add Year and Sales Revenue object in the Query panel and BO would decide to the take the data from YearSalesFact which is @aggregate_aware functions capability.
AA12SQL query will looks as below.
AA13SQL generation will change based on the Time Period object you select in the Query.  You can see huge performance changes when use Aggregate Aware function in your report.


21 thoughts on “Aggregate Awareness in BO (@Aggregate_Aware in BO Designer)

  1. Hi Madhes,

    Thanks for posting the article.

    I have one question, in your Universe schema, the [Year SalesFact] table is not connected to [TimeDim] table and [AddressDim] table, how do they joined at last?

    Best Regards,

    • Hi Dawei,

      YearSalesFact table has join with TimeDim table (Ref – third pic in the post) same as like for other 2 fact (month & Quarter) tables. We need to define the incompatibility for YearSalesFact table to get Yearly sales.

      Thanks & Regrds

  2. After reading this post I can say Aggregate Awareness is the most easy topic among rest ones.
    Thanks a Ton !!!

  3. I am having a query , Can we have aggregate awareness function for Dimension objects ?
    If yes , how we can define level of aggregation for the same ?
    I mostly appreciate if explain with an example or any real time scenario.
    Thanks In Advance.

    • Hi Vishal,

      Yes we can use Aggregate Aware function for dimension objects. For example, you have daily & Monthly data in 2 different tables (Daily_tab, Mon_tab). You can create dimension objects from those tables using aggregate_aware(Mon_tab.column,daily_tab.column). It will pick either Monthly or daily column depends on the Measure you are adding into the report query panel. by default, Monthly objects will placed in the query panel.


  4. Hi Madhes,

    Thanks for posting the article.

    I have one question, in your Universe schema,
    what is the source fact table in the data source? Only SalesFact table?
    Is the the source fact table in the data source include Year SalesFact table?
    Do we need to create Year SalesFact manually?

    Best Regards,

    • Hi Peter, creating aggregated fact is purely depends on your requirement. You can create whatever the level you want in your report.. Example Yealysales, halfyearlysales, quarterly sales. Etc..

  5. Hi,
    Shouldn’t the images show the opposite checked boxes? because in the Aggregate Navigation you select (or check) the objects that are incompatible, and in the images on this article you write one thing but select the other.

  6. I noticed the same thing as mentioned by Amir.

    One question: How can you join YearSalesFact with TimeDim on Year column?
    As per last screenshot join between these two tables (YearSalesFact.Year = TimeDim.Year) will create Cartesian product. Either distinct year should be selected from TimeDim or have separate year dimention. Isn’t It?

    Thanks for informative post.

    • Hi Waqas/Madhes,
      As i am trying aggregate aware.. i am facing the same cartesian issue with this join (YearSalesFact.Year = TimeDim.Year) as time dimension table have 365 rows having same year. How can we avoid this, what you think would be the best approach if i dont want seperate table for day , year , month etc.. .

  7. Hi Madhes…
    Nice Article…. I appreciate it.
    Would like to know what are the types of aggregate awareness are there. I have a technical discussion with client for a project and this question was been asked to me and i was dumb for few seconds. So could you help me in this.

  8. Hi Madhes,

    Thanks for the article.

    Have quick question here. if a report have year and month object together how the aggregate awareness will work? will it go to fact table only?

    • Hi Kumar,

      Fact table should have timeid (fk) and respective primary key in time dimension table along year,month, week,etc..

      When you have aggregation, it will perform based on the dimensions used in report.

  9. Nice Article. One question on aggregate tables join. Once we add aggregate tables, with what all tables do we need to join aggregate tables. How can we decide on that? Could uoumplease explain in your example.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.