Aggregation exceptions are a classic concept that we frequently encounter on our projects. Although they are mastered by many of us, I will allow myself a quick reminder before explaining how to implement them in two SAP tools (SAP HANA Studio and Query Bex).

 

1. Difference between an aggregation exception and a standard aggregation.

An aggregation is by definition an aggregation of indicators, usually a sum, a Max or a Min. When we use a standard aggregation, it means that we aggregate the indicators by summing them for example. SAP automatically associates the SUM option with the aggregation field because it is generally the most used. However, a standard aggregation only treats the rows individually without taking into account the analysis axes.

Example simple aggregation

Example simple aggregation on total (1: Product, 2: Quantity associated to the product, 3: Is 1 if the quantity of the product is > 10 or 0 if the condition is not met)

Above, the products are grouped and summed in the last row "TOTAL". Using standard aggregation, the rule in the third column treats the total row individually.

The aggregation exception also aggregates indicators but according to a defined reference characteristic (often a time characteristic, but it can also be a product type or a material group for example). It performs its processing and displays the result, keeping the reference characteristic in mind but not displaying it.

Aggregation exception

Example aggregation exception

Above, we use the same example as for the simple aggregation but using the product as the reference characteristic to create our aggregation exception on column 3. We then sum the result (row TOTAL) while keeping in mind the granularity of the reference characteristic.

 

2. Aggregation exception in SAP HANA studio

In this section, we will use a similar dataset to which we add cities where the shops with these products are located.

In a HANA view, when we aggregate our data set, using the "keep flag" option allows us to perform our aggregation exception.
Enabling the keep flag option on one or more attributes in an aggregation allows us to ensure granularity on these attributes during calculations.
For this example, we create a calculated column QTY_excpt_agreg, corresponding to quantities greater than 10.

Keep Flag, properties, outpout

Output and Properties of the aggregation node, we set the Keep Flag of the product attribute to True.

Thus, when performing a data preview of the view, we can study the differences in behaviour between the two types of aggregation.

Exception aggregation: data

Level of product detail

 

Simple aggregation

Aggregate result and without the Keep Flag

 

Aggregation exception

Aggregate result with Keep Flag

 

The results without the keep flag show that the calculation of QTY_excpt_agreg will be done on the aggregated quantities. Lyon, Nice and Paris all have a total of more than 10 for the quantity column, so the result is 10. If we activate the Keep Flag, we can see that the calculation will be done with the granularity of the Product attribute. Lyon, for example, has a total of 37 in quantity, but only two products that exceed the 10 threshold.

 

3. Aggregation exception in Query Bex

You can also choose to perform aggregation exceptions in the Query Bex. When creating a Calculated Keyfigure in, for example, a composite provider, the Aggregation tab allows you to list the reference characteristics. Note that it is imperative to enter an aggregation exception type in order to use the reference characteristics.

Key figure

Creating the Key Figure

 

Query aggregation of exceptions

Selection of the type of aggregation and the desired reference characteristics

Then simply enter the desired calculation formula in the Calculations tab.

Unfortunately, poorly implemented aggregation exceptions increase the execution time of your query. So we will show in the following how and to what extent it is possible to reduce these execution times.

 

4. Pushdown in HANA

By pushing aggregation into the Hana database, we optimise the execution times of queries containing aggregation exceptions. This is possible by RSRT via the query properties "Operations in BWA/HANA" and setting the option to 6 or directly in the "Runtime Profile Properties" of your query. Such an action calls on the HANA engine, which is more powerful and promises a faster execution speed than the OLAP engine.

RSRT: push down Hana

Query properties menu via the query monitor 'rsrt'.

 

Query push down aggregation

Runtime Properties accessible directly in the query.

However, certain conditions are required to perform a pushdown of or exceptions to aggregations.
We must therefore know and respect these conditions in order to use this option.

 

5. Cases in which the aggregation exception will not be pushed down in HANA

In the following three cases, the push down will not be possible (at the expense of execution time performance):

- The composite provider contains one or more ambiguous joins.
Ambiguous joins are joins between tables in which the Analytic Engine must ensure that the key figures are not duplicated when the join is not unique. This is a generic problem with cardinalities like a:n, n:A or n:m.

Table

Table 1

 

Table 2

Table 2

 

Join Table

Join table 1/table 2

The expected result after the join is 10 and not 20; this can be achieved with the Analytic Engine.

- The reference feature(s) have CMP problems (compounding problem).
If an InfoObject is used in conjunction with another InfoObject as a reference feature, then both InfoObjects must be mapped to the set of sources used in the query. Otherwise, a CMP problem will prevent the pushdown.
(For more information on CMP problems, please read this SAP wiki )

- SIDs are not processed in the target field.
In the composite provider, it is possible to check the "User Confirmed Referential Integrity" flag, which allows it to make a join with the SID base table.

Referential integrity

The pushdown of the aggregation exception can be done in HANA once all these conditions are validated.
We can verify the absence of pushdown problems in rsrt by displaying the statistical data of our query (article to be published soon).

 

Conclusion

Aggregation exceptions are simple to understand and can be very useful when used properly.

That is why it is important to understand them theoretically so that you can take advantage of them when the time comes.

 

The following two tabs change content below.

Aymeric Alos

Aymeric is a junior consultant specialising in Business Intelligence. Dynamic and proactive, he listens to the needs and expectations of our clients. He has strong skills on the latest SAP reporting tools and on HANA.
Share This