How to use the Aggregation node in SAP HANA Calculation Views
SAP HANA calculation views are powerful tools to model complex business scenarios and perform advanced data analysis. They allow you to combine data from multiple sources, apply various calculations and transformations, and define the output structure and semantics of your data.
One of the common tasks when working with calculation views is to aggregate measures based on one or more attributes. For example, you may want to calculate the total sales, average price, or count of orders for each product, customer, or month.
In this blog post, we will show you how to use the aggregation node in graphical calculation views to apply aggregate functions to measures. We will also explain how to control the behavior of the aggregation node and how to define filters on the aggregated columns.
What is an Aggregation Node?
An aggregation node is a type of view node that you can use in graphical calculation views. The purpose of an aggregation node is to apply aggregate functions to measures based on one or more attributes.
A graphical calculation view supports the following aggregate functions:
- SUM (the default function)
- MIN
- MAX
- COUNT
- AVERAGE
- VARIANCE
- STANDARD DEVIATION
- MEDIAN
You can use these functions to summarize your data and perform calculations such as:
- Total revenue by product category
- Minimum and maximum order amount by customer
- Number of orders by month
- Average rating by product
- Variance and standard deviation of sales by region
- Median salary by department
How to Use an Aggregation Node in Graphical Calculation Views?
To use an aggregation node in graphical calculation views, you need to follow these steps:
- Launch SAP HANA Studio and open the content node.
- In the navigation pane, select a package where you want to create the new calculation view.
- In the context menu of the package, select New Calculation View.
- Provide name and description. Select calculation view type as Graphical and data category as Cube.
- Choose Finish. The modeler launches a new graphical calculation view editor with the semantics node and default aggregation node.
- Drag and drop the necessary data sources from the tool palette to the scenario area. You can add one or more data sources depending on your business scenario.
- Connect the data sources to the aggregation node. You can use join nodes or union nodes to combine multiple data sources before aggregating them.
- Select the aggregation node. In the Details pane, select the columns that you want to add to the output of the node. In the context menu, choose Add To Output.
- Select the semantics node. In the Columns tab, select an output column. In the Type dropdown list, select Measure or Attribute.
- If the column is a measure, you can also specify the aggregation type in the Aggregation column. You can choose from SUM, MIN, MAX, COUNT, AVERAGE, VARIANCE, STANDARD DEVIATION, or MEDIAN.
- Save and activate your calculation view.
How to Control the Behavior of the Aggregation Node?
When you work with aggregation nodes, there are some features that can help you control the aggregation of measures, in order to build more flexible models.
Keep Flag
The keep flag property helps retrieve columns from the view node to the result set even if you do not request them in your query. In other words, if you want to include those columns into the SQL group by clause even if you do not select them in the query, then you can set the keep flag property to true for those columns.
This can be useful when you want to calculate a measure at a specific level of granularity, even if your query requests a higher level of aggregation.
For example, let’s say you have a data source that contains the details of sales orders by order ID. The only measures available are quantity and price. Each order ID relates to one store, one customer, and only one product.
You want to calculate the total sales for each product and month. However, if you request only these two columns in your query, then the total sales will be calculated by multiplying the sum of quantities by the sum of unit prices. This may not be accurate if there are different prices for the same product.
To avoid this problem, you can set the keep flag property to true for the order ID column in your aggregation node. This will force the calculation to be triggered at the order ID level, even if this level is not requested in your query.
Transparent Filter
The transparent filter property helps apply filters on columns of projection or aggregation view nodes in calculation views without affecting other nodes in your model.
This can be useful when you want to filter out some data from your calculation view without changing its output structure or semantics.
For example, let’s say you have a calculation view that contains two projection nodes and one aggregation node. The first projection node contains the sales data, the second projection node contains the product data, and the aggregation node calculates the total sales by product category.
You want to filter out the sales data for a specific month, but you do not want to change the output structure of your calculation view. If you apply a normal filter on the first projection node, then the output of the aggregation node will also change, and you will lose some product categories that have no sales in that month.
To avoid this problem, you can set the transparent filter property to true for the month column in your first projection node. This will filter out the sales data for that month, but it will not affect the output of the aggregation node. You will still see all the product categories in your result set.
How to Define Filters on Aggregated Columns?
If you want to define filters on columns of projection or aggregation view nodes in calculation views, you can follow these steps:
- Open the calculation view in the view editor.
- Select a projection or aggregation view node.
- In the Details pane, select a column.
- In the context menu, choose Apply Filter.
- In the Apply Filter dialog box, select an operator and enter a value or an expression for your filter condition.
- Choose OK.
You can also use variables or input parameters to define dynamic filters on your aggregated columns.
Conclusion
In this blog post, we have learned how to use the aggregation node in graphical calculation views to aggregate measures based on one or more attributes. We have also explained how to control the behavior of the aggregation node and how to define filters on the aggregated columns.
We hope this post has helped you understand how to use this powerful feature of SAP HANA calculation views and how to model complex business scenarios with ease.
If you have any questions or feedback, please leave a comment below.
Disclaimer: This content is generated by AI.