Altek Solutions Business Intelligence Blog

Finally after three projects and three months later, we are back to finish up our Aggregate Awareness series. In part 1 of our blog, we discussed the definition of aggregates and how to create summary tables. Today we are going to finish the process by showing how to implement these items into a SAP BusinessObjects Universe.

There are going to be three key steps that will need to be done to get Aggregate Awareness up and running in your universe. The first will be to add the summary tables and any objects that are not already in your universe. Normally with Aggregate tables, you will not be linking them in to any of your other tables in the universe. They will just be hanging out by themselves on an island. FYI, when you have tables like this, your integrity check will throw errors so don’t get too frustrated, it is to be expected.

SAP BusinessObjects Universe

Aggregate Awareness Function

The second step is to set up the Aggregate Awareness function in your objects. Before we get into the syntax, here is a quick overview of how the function works. The AA function takes multiple fields from multiple tables that have been inserted into your universe and determines which one to use depending on objects selected for a query by the end user.

Although it sounds confusing it is actually pretty easy to implement. The first thing is to determine which objects should get the AA function. The answer to this is that any object that is in both your summary tables and your fact tables. In my example, I will be adding AA to the following objects:

• Company
• City
• State
• Sales Cost
• Margin
• Quantity Sold

For Company, City, State, Margin, I will be adding AA for two fields while for Sales Cost and Quantity Sold I will need to add AA for 3 fields. So to get started, the syntax is @aggregate_aware([Field1], [Field2], …) and as a tip, when you populate the fields you want to use, you need to put the smallest / quickest responding table to the left while the biggest / slowest responding table will go to the right. Here are two examples from my universe.

Company

@Aggregate_Aware(“Company Summary”.Company,”Fact Table”.Company)

Sales Cost

@Aggregate_Aware(sum(“Company Summary”.”Sales Cost”),sum(“Date Summary”.”Sales Cost”),sum(“Fact Table”.”Sales Cost”))

Aggregate Awareness Syntax

Incompatible Objects

After you set up the AA function, the next and final step is to set up your incompatible objects. An incompatible object is an object that will not work for a specific table. So really what you are doing is setting up objects that if selected will eliminate tables that you cannot use from your query. To access the incompatible objects, go to Tools > Aggregate Navigation.

Aggregate Navigation

Once there you will need to set up incompatible objects ( and filters) for each table that you have in your universe. In my example, I have set up incompatible objects for Company Summary as Year, Quarter, Month, Date, Order Date, Order Number and Customer Name. For my Date Summary table, I set up Order Date, Order Number, Customer Name,  Company, City, State,  and Margin. Once this is set up you should be able to export your universe and check out how it works in WebI.

AA in Action!

Now all that is left is to create a query and watch AA do its magic. In the pictures below, I have created 3 queries and show the SQL with each one. As I stated above, Aggregate Awareness will automatically determine which tables to pull data from depending on the objects you select in your query.  This approach is a quick and easy way to gain performance in your reporting as well implementing a feature that the end user will appreciate but not even know that it is there.

Fact Table Query

Company Summary Query

Date Summary Query

Related Posts:

1. Aggregate Awareness! What is that? (Part 1 of 2)
2. Top 5 Misconceptions About Business Objects Universes
3. Why Ad Hoc Reporting Won’t Work (part 3): I don’t want people poking around in data they shouldn’t have access to
4. Creating a Pass through Prompt in Designer (Top N, Industry Standards, etc.)