Skip to Content
0

Aggregate Awareness using Derived Tables is returning incorrect results

May 14, 2017 at 06:44 PM

150

avatar image
Former Member

Hello,

I am working on SAP BO 4.1 SP6 and using the Information Design Tool to build the Universe.

I am trying to achieve Aggregate Awareness creating two Derived Tables in Data Foundation that have Aggregation in measures at two levels(major and minor) as per the requirement. The Derived tables have been joined to the dimension table with at least one key or more with Inner Join.

The construct is Aggregate_Aware is somewhat like: @Aggregate_Aware(Sum(Derived_Table_Major.Field_Name),Sum(Derived_Table_Minor.Field_Name),Normal_Table.Field_Name)

Within the Business Layer, I have set the Aggregate Navigation further to build incompatibity with aggregate table. I have created the measure with above formula in Select clause.

When I create a BO Query in Query Panel by pulling the required measure along with relevant dimension in Web Intelligence document, somehow the aggregation is not working as desired and when the different combination of dimensions are added the right aggregate table is not being picked which in sense the incompatibility is not getting detected.

My first question is whether Derived tables can be used for Aggregate Awareness; my second question is whether three tables can be used inside @Aggregate_Aware or is there a limit of two tables only; finally, my last question is whether there is a need to build three BO Query per Aggregation level in Web Intelligence to achieve the right results or query engine itself detects aggregation levels and builds correct queries. Please help me with your answers and also any other suggestions are welcome.

Regards,

Amogh

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mahboob Mohammed May 15, 2017 at 12:49 PM
0

Hi Amogh,

  1. Yes, you may use Derived table as an aggregate table (although, I haven't seen anyone doing that). But, one use of Aggregate tables is to improve performance, because they have precalculated data , which won't happen if you use Derived table, as the sql behind a derived table is executed when the query is run. You may be better off creating a materialized view instead, which has precalculated data.
  2. Yes, 3 aggregate tables can be used.
  3. No, you don't need to create 3 different queries. If the Aggregate Navigation is setup correctly and @Aggregateaware functions is specified correctly in the universe objects, BusinessObjects takes care of which aggregate table to look at during the execution of query.

Simple blog on Aggregate Awareness
https://blogs.sap.com/2014/05/12/aggregate-awareness-in-universe/

Thanks,
Mahboob Mohammed

Share
10 |10000 characters needed characters left characters exceeded