Aggregate Awareness! What is that? (Part 1 of 2)

Mike Allison —  May 6, 2010 — 3 Comments

In the SAP BusinessObjects community, when someone talks about “AA” they don’t mean the meetings people attend, they are talking about a pretty cool function that is available in Designer. Aggregate Awareness or @aggregate_aware is a term that we use to describe the ability of a universe to utilize aggregate tables in a database. Using the function correctly can greatly improve the performance and user experience. This will be the first of two blogs and will talk about Aggregation and Summary tables. The second post will show how to implement Aggregate Awareness into a SAP BusinessObjects Universe.

What is Aggregation?

At its basic, aggregation is just the process of summarizing information based on the level of detail you want to look at your data. For an example, it is really as simple as taking a years worth of data that is broken out by day and then aggregating or summarizing it to see the results by week, month, quarter or even the whole year.

Aggregation

Monthly Aggregation

Summary Tables

Before we can add Aggregate Awareness into our universe we first need our summary tables. These are generated at the database level and are usually created based off of a fact or transaction table. How you want your data summarized is totally up to you and the requirements of your end users. More than likely you are going to be using Sum and Count for the majority of your aggregation but you can also use the following:

  • Average
  • Maximum
  • Mean
  • Minimum
  • Mode

I put together a quick example to show how to take a fact table and get aggregate tables out of it. In the picture below, we have a  table that consists of order information by date and contains a million records. Depending on the level of detail a report looks at, we can bypass looking at the huge fact table and get our results out of one of the two summary tables.

Summary Tables

Summary Tables

The Company Summary table takes the fact table and aggregates it up to show Sales Cost, Margin and Quantity Sold by Company, City and State. The Date Summary table parses out the Order Date to allow Year, Quarter, Month and Day to be dimensions for Sales Cost and Quantity Sold. An example of the SQL to generate the Company Summary table would be as follows:

“SELECT Company, City, State, Sum(Sales Cost), Sum(Margin), Sum(Quantity Sold) FROM Fact Table GROUP BY Company, City, State”

Aggregation and Summary tables can be your best friend by limiting the amount of records an end user has to report against. Less records equals quicker response time for your report and happy end users.

Go here to see the second part of our Aggregate Awareness series to see how to implement your new summary tables into a universe.


Mike Allison

Posts

Mike Allison is a Managing Consultant with Altek Solutions.

3 responses to Aggregate Awareness! What is that? (Part 1 of 2)

  1. Would like to read the part or is there a complete solution which talks about 1. Summary/Materialized views Oracle Query rewrite vs. 2. How to implement Aggregate aware using BO Universe.
    Thanks

  2. Hi,

    I have 3 tables as follows.

    1. A detail table A with fields A1, A2, A3
    2. A summary table B with fields A2, A3 (same as former table)

    Using aggregate awareness, I get the appropriate results for the aggregate aware objects A2 & A3, as it goes to table B. But when I take A1, A2 and A3, it is supposed to go to table A, but it gives me incompatible objects. I have taken care of this in the Aggregate Navigation but it still gives me the error. But if I enclose it within the aggregate aware function, it works fine. Does this mean that all the objects for the base table must be using the aggregate aware function?

    Thanks & Regards,

  3. Hi George,

    I am guessing the there is some discrepancy with your Aggregate Navigation setup. The objects that are in your detail table and not in your summary table shouldn’t need the AA function. You can try detecting incompatibilities automatically in the aggregate navigation setup to see if that fixes it.

    Best,
    Mike

Leave a Reply