Mapping Variables and Input Parameters

In some cases, your calculation view needs to pass information provided by the user, via a variable or input parameter, to another object. This could be another calculation view or even a procedure or function.

This is called parameter mapping and is an important feature of SAP HANA calculation view modeling.

There are four types of parameter mapping:.

To enable parameter mapping, you must use the Input Parameter/Variables Mapping feature. You can find this feature in the Parameters tab in the calculation view.

When you open the mapping pane, you must first select the type of mapping you want to work with using the dropdown selector.

When you make your type selection, you will then see (on the left side) the input parameters and variables that are defined in the calculation views from all lower layers in the stack, which are related to the mapping type you selected. On the right side, you will see the input parameters and variables that are defined in the current calculation view (the one you are editing).

You simply drag a line between the left and right side to map them. There is also an auto-map feature which means that if the names are the same, the mapping is done with a single click. The auto-map feature generates the input parameters or variables for the current view with the same name as the source variables and also maps them. This means that you do not have to manually create the input parameters or variables in the current view.

From SAP HANA Cloud QRC 1/2024 onwards, it is possible to define an input parameter and at the same time propagate this parameter to other views within the same HDB module that consume this calculation view. Similarly, it is possible to delete an input parameter and propagate the deletion to the consuming calculation views.

In many cases, calculation views use other calculation views as data sources. This is not necessarily confined to two levels; we can go on and layer the calculation views to create a stacked model. When you execute a calculation view in which variables or input parameters are defined, it is possible to pass their values (entered by the end user at runtime) to the lower level calculation views. In fact, the input parameters and variables at the lower levels are usually ignored unless you define input parameters and variables at the top level and map them to the input parameters and variables in the lower levels.

Pushing filters down to the source views using parameter mapping is a common scenario. To enable this, choose the type Data Sources from the dropdown list in the Manage Mapping dialog.

Watch the following video to see an example of data sources parameter mapping:

Another common scenario is when you want to push parameters down from the main calculation view to a calculated column in a lower view to support a calculation. Again, this would be the type Data Sources.

Note that in the type of mapping Data Source, you only map input parameters to input parameters. In other words, a Variable defined in an underlying Calculation View cannot be mapped to a Variable defined in the current Calculation View. However, it is possible to access these variables from the Extract Semantics feature and copy them to the current view. To do that, you right-click the data source in the calculation scenario and choose Extract Semantics. Then choose the Variables tab and select the ones you want to copy to the semantics of your Calculation View.

If a data source for your calculation view is an SQL view and the SQL view requires parameters, you can provide the values from input parameters of the consuming calculation view.

This technique is often used to push down the filters down to the SQL view in order to reduce the workload of the Calculation View.

From SAP HANA Cloud QRC 1/2022 onwards, it is possible to pass input parameters from a Calculation View to an SQL View or a Calculation View that is accessed remotely as a Virtual Table. This ensures that filters are pushed down and executed as close to the data sources as possible.

This new feature requires Smart Data Access (SDA), relying on the odbc adapter – it is not possible with remote sources accessed through the Smart Data Integration (SDI) adapter. The scenario supports remote SAP HANA databases running on SAP HANA Cloud, or SAP HANA On-Premise version 2.0 minimum.

In some cases, you need to calculate the value of an input parameter, not just take the value entered by the user. In this scenario you use a procedure or a scalar function to return the needed value. This procedure or function will then have an input parameter that should be the value entered by the user.

Watch the following video to see an example of procedure parameter mapping:

Another important use case for mapping input parameters and variables is to enable dynamic value help views.

When you define input parameters and variables, the default data source that generates the value help list is taken from the calculation view itself. So, essentially you are getting an unrestricted list of all possible values to choose from. However, you can also redirect the value help to use a list from another table or view. The main reason we do this is to expose a restricted value help list.

This is also good practice for performance because the value help is not competing with the main calculation view for data. For example, you could create a calculation view on a table that contains all possible products. Here, your calculation view could include a fixed filter expression that restricts the products to a specific product group. This means that the value help list presents only products of a specific product group to the user.

What if you wanted to change the product group? You could go back to the calculation view and change the fixed filter expression, but this would be inefficient.

What we should do is replace the fixed value in the filter expression with a variable based on product group. Then, we should map this variable to a variable we define in the main calculation view for the product group. This means that when a user is prompted for a product group in the main view, the value chosen is passed through the mapping to the value help calculation view, so that the products are filtered by the product group that was chosen. The list of products is then presented as the value help for the Product column. This is also known as cascading prompts. Cascading is not restricted to two levels; you can also cascade prompts across multiple levels. For example, you could prompt for product family, which then restricts the list of Product Groups, which in turn restricts the list of Products, and so on.

To implement value help parameter mapping, you must select the option Views for value help for variables/input parameters from the dropdown list in the Manage Mapping dialog.

Watch the following video to see an example of cascading prompt:

The last scenario is probably less common but can be useful. The result sent by your calculation view to your client reporting tool can be further filtered by the user. You might want the user to have a value help on certain attributes to allow easier selection. This can be achieved with assigning a value help to some attributes in the semantics node.

Again, you can use an external view with a variable to restrict the values presented to the user. You then need to map this variable with a variable on the main calculation view.

Watch the following video to see an example of view for value help for attribute parameter mapping: