Aggregation node in SAP HANA Calculation View
Welcome again good folks to the next SAP HANA tutorial where we learn about the humble Aggregation node in SAP HANA. An aggregation node is usually attached to the semantics node by default if you are building a transaction calculation view(This means that the data category CUBE was selected when initially creating the view). Additional aggregation nodes can be added if required.
Learning aggregation with an example
Let’s start with a basic view that is already built. It takes all the data from EMP_SCORE_FINANCE table and sends it to the output without any additional logic.
Let’s first check the data preview for this simple view. This is just to see the data we will be working with.
Now, come back to the Aggregation view and click it . Click on the orange circle as always to remove the DATE field as shown below.
Confirm the removal of this field. Press “Yes”.
The field gets removed as shown below.
As seen below, since the date field was removed the score gets aggregated and summed up. This is because the default aggregation setting is SUM. But there are other types of aggregations that can also be performed. Let’s look at the different types of aggregations.
Click on the aggregation node and then clock on the SCORE measure. At the bottom, now the properties section opens up. At the far bottom, you find an Aggregation setting. This is by default set on to SUM as explained earlier. Thus, whenever values aggregate, they add up according to this default setting.
1
Let’s channel our curiosity and try to switch this setting so that we get the average of all available SCORE values for each employee. All the available values for aggregation types are as shown below. The common ones are COUNT, MIN, MAX, AVG which are used to find the count, minimum value, maximum value and average values of measures respectively. VAR and STDDEV are Variance and Standard deviations for advanced statistical analysis in rare cases.
Let’s set this value to Avg (Average) as per our requirement.
Save and activate this view. Now, execute a data preview. As seen below, an average value has been displayed in the output. Since SCORE was an INTEGER datatype field, no decimals were retained in the output.
Removing duplicate rows with aggregation
Measures aren’t the only fields capable of getting aggregated. The aggregation node also helps remove duplicates. When two rows in the incoming data set contain the same data, the aggregation node also works as a duplicate row remover. For example, from Projection_1 let’s disable all fields except EMP_ID.
In the aggregation node also, let’s select this field as shown below.
Save and activate the view. It may throw the below error if you have been following the same steps I’ve been doing. This error says that “No measure defined in a reporting enabled view”. This is due to the fact that the data category chosen while creating this view was CUBE and now it has no measure since we have taken SCORE field out of the output.
To fix this, go to the semantics node and under the “View Properties” tab, switch the data category to DIMENSION as shown below.
Save and activate this view now.
Before we do a data preview on the entire view, let’s data preview the output of the first Projection node. To do this, right click on the projection and click on Data preview as shown below.
As you can see below, projection_1 supplies a lot of duplicates to the next level.
Now go back and run a data preview on the entire view. You should get the below result. All the duplicate values have been removed.
1
I hope this tutorial gave you a clear understanding of how an aggregation node works in SAP HANA. Let me know in the comments section below if something was unclear.
As always, if you feel this website helped you learn something, please share it across social media by using the share button below. It takes money and a lot of time and patience to keep this website running for free to everyone. Support me in this initiative in keeping it free by sharing it with others. Also, subscribe to our newsletter and browser notifications to stay updated on the latest tutorials.
Stay Motivated.