Input Parameters and Variables in SAP HANA Modeling

[Former Member]

Usage in various views:

• In Attribute Views, no dynamic filtering or parameterization is possible.
• In Analytical Views, Input parameters are used for parameterization and Variables are used for filtering.
• In Graphical Calculation Views, Input parameters can be used for filtering in Projection and Aggregation nodes; they can be used for parameterization in all the nodes. Variables are used for filtering in output node.
• In SQL Script based Calculation Views, Input parameters can be used for filtering and parameterization where ever needed in the script. Variables are used for filtering in the output node.

Input Parameters

Input Parameters are used in Analytical and Calculation views when currency conversion and calculated columns needs to be parameterized. In Calculation views (Graphical and SQL Script based), apart from parameterization, input parameters can also be used to filter data. If a Calculation view is created using an Analytical view with input Parameters, those will be available in the Calculation views also. Input parameters can be either mandatory or non-mandatory. It should be noted that at run-time if we dont provide a value for non-mandatory input parameters, it will lead to incorrect results. If it is mandatory we need to provide a value either at run time or by assigning a default value while creation, else it leads to errors. The default values can be maintained in the form of Constants or Expression and it is always a good practice to maintain them.

Points to be noted when using Input Parameters:

• In Graphical calculation views, input parameters can be used for filtering data in Aggregation and Projection nodes. This gives us the option to filter data at lowest possible nodes and hence improve the performance. Input parameters are interpreted as “Having” clause which filters the query after SQL has retrieved, assembled and sorted the results.
• In SQL Script based Calculation views, input parameters can be used to filter data at lowest possible queries and in “Where” clause which results in the optimal performance.
• When SQL based reporting tools are used, we call the Calculation views by passing the values of input parameters in the following way:
  • Numeric values are passed as
    SELECT field1, field2 FROM CALCVIEW1(‘PLACEHOLDER’ = (“INPUTPARAMETER1”,’VALUE1,VALUE2,VALUE3’));
  • String values are passed as
    SELECT field1, field2 FROM CALCVIEW1(‘PLACEHOLDER’ = (“INPUTPARAMETER1”, ‘”VAL1”,”VAL2”,”VAL3”’));

Variables

Variables are assigned to Attributes of the Analytical and Calculation views at design time to filter data based on the values you provide for the variable at run time. Values are provided to the variables either by entering them manually or by selecting them from the value help dialog. Variables are interpreted as the “Where” clause of the SQL
statement. Each type of variable can be either mandatory or non-mandatory. For a mandatory variable, you need to provide a value at run time or assign a default value(in the form of Constants or Expression) at design time. For a non-mandatory variable, if value is not provided at runtime, unfiltered data is displayed.

Points to be noted when using Input Parameters:

• Through SQL based reporting tools, we don’t have provision to pass values to a variable. Only Input parameters can be used to filter/parameterize.
• Variables are interpreted as the “Where” clause. “Where” clause uses SQLs built-in set operations and indexes and therefore is the fastest way to filter result sets. These can be designed only on the higher nodes (semantic/output nodes) of the Analytical and Calculation views(Graphical and SQL Script based).