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.

Aggregation in sap hana calculation view

Let’s first check the data preview for this simple view. This is just to see the data we will be working with.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

Confirm the removal of this field. Press “Yes”.

Aggregation in sap hana calculation view

The field gets removed as shown below.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view
Let’s set this value to Avg (Average) as per our requirement.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

In the aggregation node also, let’s select this field as shown below.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

To fix this, go to the semantics node and under the “View Properties” tab, switch the data category to DIMENSION as shown below.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

As you can see below, projection_1 supplies a lot of duplicates to the next level.

Aggregation in sap hana calculation view

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.

Aggregation in sap hana calculation view

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.

<<Previous Tutorial                                                                                                              Next Tutorial>>

Tagged , , , , , , , , , , .

18 Comments

  1. Hi Shyamuthaman,
    Thank you so much for your effort.

    by switching the data category to DIMENSION, it removed the duplicate. Can you please help me to understand this setting? Thanks.

    • Hi,
      No, switching the data category to DIMENSION did not remove the duplicates. The “Aggregation” block removed the duplicates.
      Setting the data category to DIMENSION only told HANA that there are no measures in the output for this view. It had nothing to do with removing duplicates.

  2. Hi Shyam, I am working on a calculation view of type cube. After performing union on 3 projections, I would like to get the data aggregated and on that aggregated data, I have to perform a join. To do that, I have made use of aggregation after union and then joined the same to another projection. But the issue is the data isn’t getting aggregated after union, it is getting aggregated only at the final aggregation node. Due to that I am unable to achieve the required functionality. Can you help me on this?

    • In the aggregation node, probably all you did was select the “measure” fields. Instead of that right click on them and “Add as Aggregated Column” . It should work then.

  3. Hi Shyam,
    I have a question regarding aggregation type FORMULA. Whenever we add a calculated column, i notice that the HANA assigns aggregation type FORMULA automatically. What is this type? How does it behave? Will it aggregate as a sum?

    • This is because of the fact that in CUBE type calc views, there are measures which are aggregated before sending to output. Without measures, it’s not usually required.

  4. Hi Sir,
    1.Actually when we change data category to dimension the default node is project node then how data will aggregated ?
    2. If we insert only master data in aggregation node (not default node )without any measures ? what will happen.

    Please let me know ?

  5. Hi Sir,

    How can we implement the query (select count(*) from table_name/calculation_view_name) using Graphical Calculation View?

    I have tried following approach.
    1) Selected the Calculation_view for which i need the total row count in Projection.
    2) Created a Calculated column(ROW_COUNT) with integer 1 in it.
    3) Added this column as an aggregated column in aggregation node keeping aggregation type as count

    This approach was not working and giving me incorrect results.

    Can you please suggest any approach?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *