The SAP Datasphere Analytic Model Series is intended to provide you with useful guidance on how to utilize the new Analytic Model to leverage the potential of your data landscape. The Analytic Model allows for rich analytical modelling in a targeted modelling environment and will be THE go-to analytic consumption entity for SAP Datasphere.
This article is the fifth in the blog post series and showcases the ability to apply exception aggregations in the analytic model with hands-on examples.
- Blog Post #1: Introducing the Analytic Model in SAP Datasphere
- Blog Post #2: Data Model Introduction
- Blog Post #3: Motivation & Comparison with the Analytical Dataset
- Blog Post #4: SAP Datasphere Analytic Model Series – Calculated and Restricted Measures
- Blog Post #5: Exception Aggregations in SAP Datasphere – Current Blog
Introducing Exception Aggregations
With the introduction of the Analytic Model in SAP Datasphere, the stage is opened for a set of exciting features which are known from the realm of query design in the BI/BW contexts of SAP.
One of these features is the ability to apply exception aggregations.
Creating an Exception Aggregation in the new Analytic Model.
In the first part of this blog, I want to shed some light on exception aggregations and what their purpose is. In the second part, I will show where they can be used in SAP Datasphere.
The examples shown here can be reproduced by using the example data model introduced in part two of the series (Blog: SAP Datasphere Analytic Model Series – Data Model Introduction).
What are Exception Aggregations and what is their purpose?
Exception aggregations can be understood as implicit, or “background” aggregations. They are also the exception from the rule, which is the default aggregation behavior defined for the measure. It is even applied when the dimension is not selected in the drill-down.
Let’s start off by briefly outlining the example data model introduced by my colleague Jai Gupta in part 2 of this Series (Blog: Data Model Introduction), which is used throughout this blog. This data set covers sales opportunity data of employees for various products and customers of the company across different cost centers within sales organizations of the company.
Now let’s come to the business case to be addressed by exception aggregations:
Let us assume, the goal is to assess, which product managers were part of sales opportunities containing many “high value” products. In this case, a high value is every opportunity with a value > 1.000.000 €.
In this data preview of an analytic model
our column containing an exception aggregation is “Count of Products with Value > 1000000”.
But how do we arrive at this value?
In a nutshell: Two processing steps – applied in this order:
- Evaluation of the expression
- Application of the aggregation behavior in the exception aggregation
1 – Evaluation of the expression: Baseline of the exception aggregation is what is displayed in column “Value > 1000000. Here, we evaluate the expression “Value > 1000000”. A Boolean value (1/0) is returned, depending on whether the value is > 1.000.000€ (1), or < 1.000.000€ (0).
Column “Value bigger than 1000000”: Calculated Measure which evaluates if the value exceeds 1.000.000€.
This expression is evaluated based on what we selected in the drill-down.
Without selecting any values, the summed-up Value of 37.391.089 is taken as a reference for the calculation:
With a different drill-down, on “Customer”, you can see how the reference changes:
2 – Application of the aggregation behavior in the exception aggregation:
Now, a “n-step” grouping is applied according to our specification of the exception aggregation:
This is a bit trickier. In SAP HANA SQL logic, this would roughly refer to the following behavior:
SELECT
SUM(<Measure>)
GROUP BY (<Dimension>,
<Exception Aggregation Dimension 1>,
<Exception Aggregation Dimension 2>,
<Exception Aggregation Dimension n>);This would lead to an error when trying to apply it in an SQL statement, because all Dimensions mentioned in the GROUP BY statement must be selected as well. Not in the case of the exception aggregation!
What does this mean for the example case?
In the case of SUM, it means that the Boolean values from the previous calculation step (1) are summed up by the Product ID. Even if the Product ID is NOT in the visible drill-down!
Why is the term exception used?
When defining a measure you have to define a default aggregation behavior for your measure, such as Summation, Average, or Count:
Measure Definition in the Analytic Model.
Reason: When the drill-down state is assessed to determine the correct results to be displayed, the grouping/aggregation must be unambiguous.
The exception aggregation can define an aggregation behavior which is different from what you defined in the default aggregation behavior.
Where can I apply Exception Aggregations in SAP Datasphere?
In SAP Datasphere you can define exception aggregations within the analytic model, which is accessible via the Data Builder.
Navigating through the exception aggregating options
You can define the exception aggregation in the calculated measure, the restricted measure, or the fact source measure itself. If you don’t see the exception aggregation section on the initial screen, scroll down a bit. They can be edited in the model section on the right.
Using the preview functionality of the analytic model, you can easily check the outcome of your analytic model on-the-fly, as it would be displayed in SAP Analytics Cloud.
Exception Aggregation – Wrap-Up
Exception aggregations can cover special reporting use cases where conventional aggregation is not sufficient, and more flexible query requirements need to be addressed.
The exception aggregation is a feature which opens the gateway to exciting new OLAP possibilities which we got to love over the course of the SAP BW journey in the past decades. And now it is in Datasphere as well. Yay!
I hope I was able to convey the logic and approach of this feature and wish you happy modeling!
Further Reading
- Blog: Analytic Model Series #1 – Introducing the Analytic Model in SAP Datasphere
- Blog: Analytic Model Series #2 – Data Model Introduction
- Blog: Analytic Model Series #3 – Motivation & Comparison with the Analytical Dataset
- Blog: Analytic Model Series #4 – Calculated and Restricted Measures
- Analytic Model Documentation in SAP Help Portal
