in BI Strategy & Execution, Data Management, Universe Design

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

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

Free Web Intelligence Best Practices Guide

We put together a Best Practices Guide for Web Intelligence Development that includes over 20 pages of tips and techniques for developing business-ready reports. Plus we'll show you the common functions that have a negative impact on performance, how to create interactive reports, the best ways to standardize look-and-feel, and much more!

GET YOUR FREE COPY OF THE GUIDE TODAY!

Powered by ConvertKit

Write a Comment

Comment

13 Comments

  1. hi Mike,
    The information posted here is very useful for a BO developper. More credit goes you for the way in which you have presented it.

  2. Great post..I was so confused of this AA until I read your post.
    Now I understood this concept perfectly.

    Thanks for the your post.

  3. Thanks for the very Helpful and efficient Post on AA..
    Too good with the presentation…
    Also , looking for the future post on universe design.

  4. Hi,

    I did get correct results with out dong the 3rd step – ‘setting up the incompatable objects’. Why?

    Is 3rd step absolutely required?

    Mathai

Webmentions

  • Aggregate Awareness! What is that? (Part 1 of 2) | Altek Solutions Business Intelligence Blog September 24, 2015

    […] here to see the second part of our Aggregate Awareness series to see how to implement your new summary […]