Analytic and calculation views can contain variables, that can be bound to specific attributes. When calling the view, you can send values for those variables. These variables can be used, for example, to filter the results.
Variables are bound to columns and are used for filtering using WHERE clauses. As such, they can only contain the values available in the Columns they relate to.
Important features of variables:
· You use variables to filter data at runtime. You assign values to these variables by entering the value manually, or by selecting it from the drop-down list.
· You can also define whether the Variable is Mandatory or if it should have a Default Value.
· You can apply variables only in analytic and calculation views.
Variable Types:
The following types of Variables are supported:
Single Value: Use this to apply a filter to a Single Value.
Interval: Use this where you want the user to specify a set start and end to a selected Interval.
Range: Use this when you want the end user to be able to use operators such as “Greater Than” or “Less Than”.
System Tables to see Variable Information:
SAP HANA includes the following set of tables that contain information about the variables defined for views: There are 4 system tables under the schema _SYS_BI which contains information about Variables.
1. BIMC_VARIABLE
2. BIMC_VARIABLE_ASSIGNMENT
3. BIMC_VARIABLE_VIEW
4. BIMC_VARIABLE_VALUE
Input Parameters:
You use input parameters to define internal parameterization of the view to obtain the desired functionality when you run the view.
Input parameters in HANA are used to filter the data using the user input value as a source to perform additional calculations which helps to solve dynamic data population scenarios.
Difference between Variable and Input parameter:
|
Variable
|
Input Parameter
|
|
Variables are defined on attributes to filter the data based on user input during runtime
|
Input parameters defined to take user input during runtime and use it for data filtering or for dynamic calculations.
|
|
Variables apply filter after execution of all nodes till the semantics (at the top level)
|
Input parameters can apply filters at any projection level
|
|
Cannot be used as input for calculated columns and restricted columns
|
Can be used as input for calculated and restricted columns
|
|
It allows multiple values as input
|
It allows multiple values as input only at calculation view but not at analytic view level
|
Example:
Here I use 3 tables named as Product, Sales_IP, Region.
Step 1: Create a Calculation view by right click the Package and click New and select Calculation View.
Step 2: A Pop-up will appear and provide details which is required and click Finish.
Step 3: Drag a join node into the Scenario pane named as Join_1, in that drag and drop the required tables. Here I use SALES_IP and REGION. And make a link between using join based on the relationship.
Step 4: Drag another join node into the scenario pane named as Join_2, in that drag and drop the required tables. Here I use Join_1 and REGION table. And make a link between using join based on the relationship.
Step 5: And then connect the Join_2 to the Aggregation node. Finally, it looks like as shown as below:
Step 6: Now we have to create an Input parameter, from the output pane, right-click the Input parameter and click New. A Pop-up will appear and provide necessary details as shown as below and click ok.
Step 7: Now we have to create a calculated column to know the Net amount which is discounted amount from the Sales Amount. From the output pane, right-click the calculated column and click New. A Pop-up will appear and provide necessary details as shown as below and click ok.
Step 8: Then now create a variable to filter the region. From the details pane, click Parameters/Variables tab and click create an icon. A pop-up will appear and provide necessary details as shown as below:
Step 9: Click ok and validate and Activate the view.
Step 10: Result
When
we click data preview a pop-up will appear to give a value for variable and
input parameter as shown as below:
