How to Select and Transform Data in SAP HANA Calculation Views using the Projection Node

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 select and transform data from your data sources. For example, you may want to select only the required columns, define calculated columns, apply filters, or rename columns.

In this blog post, we will show you how to use the projection node in graphical calculation views to select and transform data from your data sources. We will also explain how to use parameters and variables to define dynamic filters and calculations.

What is a Projection Node?

A projection node is a type of view node that you can use in graphical calculation views. The purpose of a projection node is to select and transform data from a data source.

A projection node is typically used to achieve the following:

To select only the required columns from a data source.
To define calculated columns using expressions or functions.
To define parameters or variables that request values at run-time, such as user-prompts.
To apply filters on the data source using constants or parameters.

You can use projection nodes to optimize your calculation views by reducing the amount of data that is passed to other nodes or to the client query. You can also use projection nodes to enhance your data by adding new columns or applying business logic.

How to Use a Projection Node in Graphical Calculation Views?

To use a projection 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 or Dimension.
Choose Finish. The modeler launches a new graphical calculation view editor with the semantics node and default projection node.
Drag and drop the necessary data source from the tool palette to the scenario area. You can add only one data source per projection node.
Connect the data source to the projection node.
Select the projection 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.
If you want to define calculated columns, select Calculated Column from the tool palette and drag and drop it to the Details pane of the projection node. In the Calculated Column dialog box, enter a name, a label, a description, a type, a length, and an expression for your calculated column. Choose OK.
If you want to define parameters or variables, select Parameters/Variables from the tool palette and drag and drop it to the Details pane of the projection node. In the Parameters/Variables dialog box, enter a name, a label, a description, a type, a default value, and an optional value list for your parameter or variable. Choose OK.
If you want to apply filters on your data source, select Filter Expression from the tool palette and drag and drop it to the Details pane of the projection node. In the Filter Expression dialog box, enter an expression for your filter condition using constants or parameters. Choose OK.
Save and activate your calculation view.

How to Use Parameters and Variables in Projection Nodes?

Parameters and variables are placeholders that request values at run-time from users or applications. You can use them in projection nodes to define dynamic filters or calculations.

Parameters are input-only placeholders that can be used in any type of calculation view (cube or dimension). Variables are input-output placeholders that can be used only in dimension calculation views.

To use parameters or variables in projection nodes, you need to follow these steps:

Open the calculation view in the view editor.
Select a projection node that contains a data source.
In the Details pane, select Parameters/Variables from the tool palette and drag and drop it below your data source.
In the Parameters/Variables dialog box, enter a name, a label, a description, a type (string, integer, decimal, date, time, timestamp), a default value (optional), and an optional value list (optional) for your parameter or variable.
Choose OK.
If you want to use your parameter or variable in a filter expression, select Filter Expression from the tool palette and drag and drop it below your parameter or variable.
In the Filter Expression dialog box, enter an expression for your filter condition using your parameter or variable name preceded by a colon (:). For example: :p_date or :v_customer.
Choose OK.
If you want to use your parameter or variable in a calculated column, select Calculated Column from the tool palette and drag and drop it below your parameter or variable.
In the Calculated Column dialog box, enter a name, a label, a description, a type, a length, and an expression for your calculated column using your parameter or variable name preceded by a colon (:). For example: :p_rate * "price" or :v_discount - "cost".
Choose OK.

Conclusion

In this blog post, we have learned how to use the projection node in graphical calculation views to select and transform data from your data sources. We have also explained how to use parameters and variables to define dynamic filters and calculations.

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.