Applying Aggregation in SAP Analytics Cloud

former_member751461 · ‎2020 Mar 18

Applying Aggregation to Models in SAP Analytics Cloud begins in the Modeler. The Modeler enables you to prepare your data in various ways prior to analysis. This helps you save time if you have specific aggregation requirements that you want to apply to multiple stories based on one model rather than creating your calculations at the story level. Applying aggregation rules at the story level becomes less efficient if you have more than one story.

There are two types of aggregation in SAP Analytics Cloud:

Standard Aggregation

Exception Aggregation

Standard aggregation

In accounting, aggregation is when you add up the total of two or more figures. For example, say you run a boutique clothing store and you’re doing an inventory count. You may want to add up the total number of sweaters sold in the past month. You would start by adding up all the sweaters at the end of the month and compare it with the total number you had at the beginning of the month.

You may have a spreadsheet that resembles the following:

Standard aggregation is simply adding up all the figures in the ‘Units Sold’ column.

Exception aggregation

Continuing with the above example, suppose instead of counting sweaters, you want to know the customer satisfaction (CSAT) scores of all your online shoppers. After collecting this data, you may have a table such as this:

Customer satisfaction scores for online

If you were to use standard aggregation to add up all the CSAT scores, you would end up with a total, but this is not what you want. What you want is an average CSAT score. To do this calculation, you would need an exception aggregation to add up the total CSAT scores and then divide it by the number of CSAT scores.

After finding the average of all CSAT scores, you may notice that many of your customers are repeat visitors. Since customer IDs are all unique, you could create another exception aggregation calculation to find out what is the average CSAT score based on unique customer ID.

Applying exception aggregation

There are two ways you can apply an exception aggregation and different scenarios on when you would use each method. Aggregation can be applied from a:

Model

Story

Applying exception aggregation from a model is a convenient way to allow those calculations to be available for others who may build stories based on your model.

Applying exception aggregation from a story is useful for when you don’t plan on creating multiple stories based on one model, or if you forgot to include a particular calculation in your model. In these scenarios, SAP Analytics Cloud enables you to create a one-time exception aggregation that can be used for that one story.

Exception aggregation type

We talked about creating exception aggregation for finding averages, but there are many exception aggregation types in SAP Analytics Cloud, and they all perform different functions. In essence, exception aggregation type defines how an exception should be calculated.

SUM — Adds all values together
Example: You can use SUM for calculating the total value of something such as units sold.

COUNT — Counts members in a group, which is different than calculating SUM.
Example: You may want to know how many products you are selling per country.

COUNT excl. 0, NULL — Counts members in a group, excluding zero and NULL values
Example: You may want to know how many products you are selling per country, excluding all the times you gave your product away for free.

MIN — Displays the minimum value
Example: You may want to know what was the lowest number of units sold per day.

MAX — Displays the maximum value
Example: You may want to know what was the highest number of units sold per day.

AVG — Calculates the average of all aggregated values
Example: If you sell a product with fluctuating prices, you may want to know what was the average price that product sells for. You may want to compare this with the MIN and MAX to determine if your MAX or MIN were outliers.

AVG excl. 0, NULL — Calculates the average of all aggregated values, excluding zero and NULL values
Example: You may want to determine the average price your product sold for, excluding all the times you gave your product away for free so that those numbers do not skew your average

FIRST — Shows the first (oldest) value in the selected time period
Example: If you have a list of contractors, you may want to know what was their rate the first time you hired them. You could then compare this with subsequent years.

LAST — Shows the last (most recent) value in the selected time period
Example: You may have a product where the price changes, LAST tells you the last price for the time range you select. You may want to know what was the last price your product sold for in 2016?

For the FIRST and LAST, you need to include a time period. The first and last of when — this month, this quarter, this year?

For a MIN, AVG, and MAX, you need to include a dimension for which you are trying to find the minimum, average, and maximum. For instance, if you want to know what your customers thought of Product A, then you would calculate the AVG CSAT score for the dimension which includes Product A. It wouldn’t make sense to include additional product dimensions in your average CSAT calculation since they are two different products.

Exception aggregation dimension

Exception aggregation dimension defines what dimensions are exceptions. If you select an exception aggregation type, you must also select an exception aggregation dimension.

In the example below, we want to create an exception aggregation for the number of products sold by sales manager. But, instead of looking at total number of sales per month, we want to look at what is the average units sold per day.

First, we select AVG from the Exception Aggregation Type, then select ‘Sales Manager’ from the list of our Exception Aggregation Dimensions.

Next, we need to choose a dimension from the dimension selector dialog. This shows us all the dimensions in the model.

Applying exception aggregation in SAP Analytics Cloud

Now that we have a basic understanding of some of the terms and key players, let’s take a look at an example of exception aggregation in SAP Analytics Cloud.

By default, the measures in our models use the aggregation type of ‘SUM’. In some cases, the SUM aggregation isn’t appropriate.

In this example, we have added several employees to our planning model. Our data for this measure includes the total number of individuals employed during each time period, not just the new hires, so we don’t want to sum these values or we will get inaccurate results.

Account Type

Next, we will review the account settings we need to apply to do this.

In the Account Type column, the available options are:

INC = Income

EXP = Expense

AST = Asset

LEQ = Liability

NFIN = Non-financial

The following chart shows which aggregation type should be used with each account type.

Rate type

In this case, the number of employees is a non-financial measure so we’ll select that. We can leave the rate type blank because rate types only apply only to values based on currency.
Units & Currencies
In the Units & Currencies column, the options are:

Blank (no unit will be specified)

%

Currency

We’ll choose the ‘Label’ option so we can specify the unit we’d like to display. In this case, we’ll apply the label ‘Employees’.

Aggregation type

When setting up an exception aggregation in Modeler, you need to specify the aggregation type. Aggregation type specifies how values are calculated. There are three possible aggregation types.

Sum: the sum of all calculated items

Label: the description is shown in the Story with no data

None: Used in instances (e.g. hierarchies) where it doesn’t make sense to aggregate items

In this case, we will select the None option.

In some cases, we want the aggregation behavior to be different for one or more specific dimensions. We can use the exception aggregation type and exception aggregation dimension columns together to achieve this.

In the exception aggregation type column, we can specify an aggregation type. In the exception aggregation dimension column, we can specify the dimensions for which to use that aggregation type.

In this case, we want to set up an exception aggregation for the time dimension. This way, no matter what time granularity we're viewing, a number of employees value displays.

In the exception aggregation type column, our options are:

SUM

MIN

MAX

AVG

AVG excl. 0

FIRST

LAST

We'll choose LAST to display the number of employees at the end of the selected time period. Note that when we choose LAST, time is automatically added to the exception aggregation dimension column. For measures not based on formulas, only a single exception aggregation dimension can be specified.

Now we’ll save the model and look at the results in a table.

Note the number of employees value that displays for all time periods is the value for 2015, which is the last year for which we have data. This is because we choose LAST as the exception aggregation type.

When we expand the hierarchy to see the quarterly results for 2015, and then the monthly results for Q4, the values that display at higher levels in the hierarchy always reflect the number of employees at the end of the relevant time period.