All About Aggregate Awaraness-Real Time Examples

Aggregate Awareness in BO

Aggregate Aware is one of the powerful functions in BO and very common / famous function in Business Objects Designer and reports. It will speeds up the execution of queries, improving the performance of SQL transactions using database Aggregate Tables.

Note: When you have Daily & Monthly tables with same set of columns, you can use @aggregate_aware function and create a single object for both and it will reduce your universe support and maintenance cost. It’s not necessary to have only aggregate tables. It will be used for either Measure or dimension objects.

•    @Aggregate_aware(sum(MonthlyTable.measure),sum(DailyTable.measure))

•    @Aggregate_aware(MonthlyTable.dimesion,DailyTable.Dimension)

How aggregate Awareness helping to improve your report performance and process to follow for getting the benefits in your reports.

Universe Structure without Aggregate tables:

Example, we have 5 Dimension tables and one fact table which holds complete data transactional data in data warehouse. Based on the dimension in your report to get the data is really huge load to that fact table and the report won’t finish as you expected. Fact table hold the data for Daily transaction for each, customer, Product and branch and this can be expressed as follows.

365 Days X 5 Products X 2 Country X 100 Customer = 365000 Rows

Below is the Sample Data set for the Fact table.
When you create reports for Daily / Monthly / Quarterly / yearly from this fact table along with some dimension, your report will read complete table data sets and database engine must add up a large number of rows.

Below is the universe structure for Fact and Dimension model of your current data model.
To avoid the complexity of complete table scan/read/execution, we can create summarized tables for Monthly, Quarterly and Yearly in your database from the Fact table. Summarized tables will have calculated data for that particular time periods and your report will read only that particular table to get the data.

Universe Structure with Aggregate tables:

Summary (Aggregated Tables) tables created in Database level and those are physically stored the data in pre calculated manner.  Keeping these aggregated tables in BO universe designer you can created an aggregated aware objects using @Aggregate_aware function. Aggregate Awareness function will decide which table to choose on the fly based on your time Period (daily, Monthly, Quarterly & Yearly) selection to query the data from either one of your aggregated tables.

Below is the universe structure with aggregated tables.
Each aggregated tables will have join with other dimensions tables and along with Time Dim data Sets (see below) by Month, Quarter and Year.

Below is the detailed schema how the aggregated tables are joined with Time Dimension.

Once you created joins between the aggregated tables with other dimensions tables, you have to create the Aggregate Objects and need to define the compatible & incompatible objects for those aggregated tables.

Create Aggregate Objects

Before creating the aggregate objects, level of aggregation should be identified and as follows:

• YearSalesFact.Revenue is the highest level of aggregation.
• QuarterSalesFact.Revenue is the next level.
• MonthSalesFact.Revenue is the next level.
• SalesFact.Revenue is the lowest level of aggregation.

Define the @Aggregate_aware function object as mentioned below.

@Aggregate_Aware(sum(agg_table_1), … sum(agg_table_n)) —> agg_table_1 is the aggregate with the highest level of aggregation, and agg_table_n the aggregate with the lowest level.

Object Name: Sales Revenue
Select : @Aggregate_Aware(sum(YearSalesFact.Revenue), sum(QuarterSalesFact.Revenue),
Sum(MonthSalesFact.Revenue), sum(SalesFact.Revenue ))
Define Compatible and In-Compatible Objects:

Compatible / Incompatible objects must be specified for each aggregate table in the universe. The set of
Compatible / Incompatible objects specify determines which aggregate tables are disregarded during the generation of SQL in the report.

Compatible / Incompatible definition can be done in Universe Designer, Tools – Aggregate Navigation option.
An object is either compatible or incompatible with respect to the Aggregate table. The rules for compatibility as follows:

• When an object is at the same or higher level of aggregation as the table, it is compatible with the table.
• When an object is at a lower level of aggregation than the table (or if it is not at all related to the table), it is incompatible with the table.

As per above example, below is the details of in-compatible objects.

Year Object is in-compatible for Day, Week, Month & Quarter (Since Year is having highest level of aggregated data)

Quarter Object is in-compatible for Day, Week and Month.

Month Object is in-compatible for Day, Week.

Day is in-compatible for aggregated objects.

Report Query SQL generation:

When you create a report based on Yearly Sales, add Year and Sales Revenue object in the Query panel and BO would decide to the take the data from YearSalesFact which is @aggregate_aware functions capability.
SQL query will looks as below.
SQL generation will change based on the Time Period object you select in the Query.  You can see huge performance changes when use Aggregate Aware function in your report.

Basic Example;

Say you have three tables: tByYear, tByMonth, tDetails 

In the tables, you have the columns: 

tByYear.Year 

tByYear.OrdAmountYr 

tByMonth.YrMonth 

tByMonth.OrdAmountMo 

tDetails.OrderDate 

tDetails.OrderAmount 

You would create three dimension objects, one for each of the date parts above, plust your one measure object "Order Amount" in the universe would be defined as: 

@AggregateAware( Sum(tByYear.OrdAmountYr), Sum(tByMonth.OrdAmountMo), Sum(tDetails.OrderAmount) ) 

This formula, plus the proper Agg Aware Navigation in Designer, sets you up for the following... 

If you select only the obects for tByYear.Year and "Order Amount", the SQL generated will be automatically for Sum(tByYear.OrdAmountYr). 

If you select only the obects for tByMonth.YrMonth and "Order Amount", the SQL generated will be automatically for Sum(tByMonth.OrdAmountMo). 

If you select only the obects for tDetails.OrderDate and "Order Amount", the SQL generated will be automatically for Sum(tDetails.OrderAmount).

Detailed Example:

Lets say I have two tables customer and daily fact  with below data:

If I create the report of customers daily sales, it will join customer and daily fact table using cid column and will give me 24 rows of data.

But if my requirement is to create only quarterly sales or just monthly sales than the data will still traverse through 24 rows of daily fact table.

For eg.  Monthly sales will give me 12 rows of data while quarterly will be 4 rows of data. But this is still getting data from detail fact which has 24 rows, which if hold million facts can slow down the performance.

So we create materialised views or summary tables in our database for better performance.

And I created monthly and quarterly fact in my sample database. Below are the aggregated tables that I can use in my universe.

Now, the question is, we have aggregate tables but how to use the same in my universe based on user queries dynamically.

Aggregate awareness functionality will help me in achieving the same.

It is a two step process:

1    Use aggregate aware function in objects that needs to be made aggregate aware.
2       Setting incompatibilities of objects with tables in aggregate navigation section.

Below is the structure of my universe now:

Now, I have made time dimensions i.e. Sales Dt, Sales Month and Sales Qtr and measue Sales as aggregate aware.

Aggregate function has syntax like:

@Aggregate_Aware(sum(aggregate table1),...,sum(aggregate tableN)) Defines a measure object using precalculated aggregate tables.

 Where table1 is highly aggregated(in our case quarterly fact),........and tableN is the least aggregate or    detailed one(in our case daily fact)

If you observe the definition in the select statement of Sales measure here, it is from highly aggregated table to detail one , same is done for the dimension table as well.

Now month is present in only two tables, hence only monthly and daily fact is used.

Sales Qtr object, quarter is present in all three fact tables; hence all the three are used.

We have used aggregate function in these tables, so we hope that system automatically will decide which table to refer dynamically, when a user selects or tries to run a query.

That means if user wants daily data than daily fact should be used, if monthly than monthly fact or if quarterly sales than quarterly fact.

Now, we have to set the incompatibilities.

This is done from Aggregate Navigation section under tools, in menu bar.

We have to make objects incompatible to tables.

For eg. Sales dt is incompatible to quarter and months table, similarly Sales month object is incompatible to quarter table.

You have to click the table and check the box in front of object to make it incompatible with the selected table.

Lets see how the queries behave, when you select different set of objects:

Scenario 1:

I selected customer name and sales, and if you see the below query , it is behaving same as the one i expect it, as it is taking data from quarterly fact table.

Scenario 2:  I added sales month object to it, and will remove quarter object.(it should only now point to monthly facts)

And if you observe, it is now using monthly facts only.

Scenario3:  Using sales dt object.

This also worked correctly.

Note: if aggregate navigation have not been used than the measure sales will always point to quarterly fact, even if you use sales dt object in your query, which is wrong behavior.

Also it is required to link aggregate fact to dimension tables, else it would fetch Cartesian product for table which are not joined. In efashion universe the aggregate table are not joined to other because the table itself has all the dimension objects, which is not always the case. In real time aggregate fact will hold aggregate measures and foreign keys of dimension table only, that is why it has to be joined to dimension tables.