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.
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:
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.
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.