SAP Learning Journey

Working with Aggregation Nodes

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Aggregate measures using the aggregation node

Aggregation Node

The purpose of an Aggregation node is to apply aggregate functions to measures based on one or several attributes.

A graphical calculation view supports SUM (the default function), MIN, MAX, and COUNT.

SAP HANA Cloud allows you to apply the following additional aggregate functions to the calculation views:

  • Average

  • Variance

  • Standard deviation

  • Median

Caution

These aggregate functions should be used very carefully in stacked scenarios to avoid incorrect results.

In an Aggregation node, a calculated column is always computed AFTER the aggregate functions. If you need to calculate a column BEFORE aggregating the data from this column, you have to define the calculated column in another node, for example a Projection node, executed BEFORE the aggregation node in the calculation tree.

Defining an Aggregation in the Client Query

When you execute an SQL query on top of a CUBE Calculation View (with or without Star Join), the behavior depends on whether your SQL query includes a GROUP BY clause or not.

  • If your SQL query does not include a GROUP BY clause, each requested measure is aggregated as specified in the Semantics of the Calculation View
  • If your SQL query includes a GROUP BY clause, the aggregate function you specify for each requested measure overwrites the one defined in the S1emantics of the Calculation View. This provides a lot of flexibility, but can be error-prone in some scenarios. It is recommended to use this with care.
Note
The default data preview SQL statement for a CUBE calculation view (with or without star join) in the SAP Business Application Studio uses the second approach, thus showing you which aggregate function is applied to each measure.

Controlling the Behavior of the Aggregation Node

When you work with Aggregation nodes, the list of columns requested by the client query can influence the way the aggregation is executed, especially in complex scenarios.

The following features can help you control the aggregation of measures, in order to build more flexible models:

  • Keep Flag

  • Transparent Filter

Keep Flag

Let’s consider a scenario where a data source contains the details of sales orders, by order ID. The only measures available are quantity and price.

Note

Each order ID relates to one store, one customer, and only one product (in order to simplify the example).

You want to calculate the quantity and total sales for the product Mouse and the month ofFebruary.

Here, the issue is that the columns you request mandate a level of aggregation (Month, Product) that is generic. Hence, the total sales is calculated by multiplying the sum of quantities by the sum of unit prices.

Setting the Keep Flag property to true for the Order ID column forces the calculation to be triggered at the relevant level of granularity, even if this level is not the grouping level defined by the client query.

The Keep Flag option can also be defined on the shared columns in the Star Join node of a CUBE with Star Join calculation view (shared columns are the ones defined in the underlying DIMENSION calculation views).

Transparent Filter

In some scenarios, using a filter (where clause) in a client query forces a column to be used in the Group By columns set.

In this scenario, for example, calculating the number of stores that sold mice to John or Susan triggers an intermediate calculation of the storecount sum by product and by customer, which makes the total by product irrelevant.

Setting the Transparent Filter property to true for all models and nodes that reference the Customer column, will stop the Customer column from being unnecessarily used in the Group By clause.

This property is required in the following situations:

  • When using stacked views where the lower views have distinct count measures.

  • When queries executed on the upper calculation view contain filters on columns that are not projected.

Save progress to your learning plan by logging in or creating an account

Login or Register