Creating Calculated Key Figures with Exception Aggregation

Objectives

After completing this lesson, you will be able to:

  • Create a query with exception aggregation

Calculated Key Figures with Exception Aggregation

In a query, it is possible to choose how key figure values are aggregated. You can use different characteristics and characteristic values in your aggregation definition. To calculate the value of each key figure in the query, the data from the InfoProvider must be aggregated to the detail level of the query. Formulas may also need to be calculated.

Standard Aggregation

  • When performing standard aggregation, key figures are aggregated according to the Summation (SUM), Minimum (MIN), or Maximum (MAX) rules.

  • Standard aggregation is independent from specific characteristics; it is applied to all characteristics that are not in the drilldown, irrespective of their sequence.

Exception Aggregation

  • Can only be implemented in the properties of a formula or a calculated key figure, where basic and restricted key figures can be included.

  • Needs one or more reference characteristics to define the granularity with which the aggregation rule is applied.

Exception Aggregation Rules

Exception Aggregation Rules

The following table describes some examples of rules for exception aggregation.

Abbreviation

Aggregation Rule

Description

AVG

Average of all values

The average of all values is displayed.

CNT

Count all values

The number of available values is displayed in the result line.

MAXMaximum

The highest value of all displayed values in this column is displayed in the result line.

MINMinimum

The lowest value of all displayed values in this column is displayed in the result line.

VARVariance

If you drill down to the reference characteristic, the variance of the displayed values is displayed in the result line.

Configuring Exception Aggregation

There are two approaches to defining exception aggregation:

  1. In the modeling settings of a key figure InfoObject.

    You can configure exception aggregation during modeling of key figures if the exception aggregation is going to be applied to all queries.

    For Non-Cumulative key figures, you must configure exception aggregation (for example, Last Value) to avoid incorrect aggregation over time characteristics.

  2. In the properties of formulas or calculated key figures in SAP BW Query Designer in SAP HANA Studio.

    Add or change exception aggregation settings for each query by creating a formula object, adding operands, and defining the type of exception aggregation for the reference characteristics.

Multi-Dimensional Exception Aggregation

In an exception aggregation, you can use up to five reference characteristics, so that the aggregation rule will be applied in a single calculation step that combines all of the reference characteristics. For example, you want to define a calculated key figure, Sales per Employee and Product and Year.

Define multi-dimensional aggregation by defining a single calculated key figure that contains all three characteristics as reference characteristics.

Exception aggregation rules are performed on single records, without the need to calculate the sub-steps, improving performance.

With multi-dimensional exception aggregation, it is also possible to create new analyzes - for example, to calculate average values for the combination of characteristics.

Nested Exception Aggregation

Nested exception aggregation is necessary when you want to use an exception aggregation for a value that already has been calculated with an exception aggregation.

For example, you want to display the average number of products that have been sold for each customer:

  1. To count the number of products per customer, create a formula Count of Products with exception aggregation as count and the reference characteristic as product. This will provide the number of products for each customer in your result.

  2. To calculate the average number of products per customer, create a second formula, and use the formula Count of Products as the operand.

  3. Define an exception aggregation average with the reference characteristic customer.

This is a nested exception aggregation that will return the average number of products per customer.

How to Understand Exception Aggregation

In the following video, you learn the basics for Exception Aggregation.

How to Define Business Scenario using Exception Aggregation

In the following video, we present a business scenario using Exception Aggregation.

Log in to track your progress & complete quizzes