In the article SAP HANA Modeling Introduction and SAP HANA Calculation View we explained the basics of SAP HANA data modeling. We also learnt how to create modeling views in Build Your First SAP HANA Model in 10 Minutes
In this article we will learn what are Variables and Input Parameters in HANA and how do they work.
Variables and Input Parameters in HANA:
Sometimes we need to filter the data based on user's input. This is where Input Paramters and Variables come into the pictures.
Input parameters and variables are used to filter the result of modeling views.
In this article we will learn what are Variables and Input Parameters in HANA and how do they work.
Variables and Input Parameters in HANA:
Sometimes we need to filter the data based on user's input. This is where Input Paramters and Variables come into the pictures.
Input parameters and variables are used to filter the result of modeling views.
Let us first see what is Variable and how does it work.
Introduction to variables:
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.
Sounds confusing?? Let us take a simple example.
In the article Create Graphical Calculation View in 10 minutes, we created a calculation view which has 4 columns PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME and SALES_AMOUNT.
REGION_NAME contains different regions like “America”, “Asia” and “Europe”. The calculation view gives all the region’s details in output.
Now suppose we want to filter the data on region basis. The calculation view will prompt the user to specify Region Name and the output data will be filtered based on that.
For example if the user provides the value as “Asia”, the calculation view will show data only for “Asia” region.
How to create and use variables in modeling view:
Now we will see how to implement this using variable.
Note: We will refer to the calculation created in the article Create Graphical Calculation View in 10 minutes.
1. Open the calculation view. On Right-Bottom side you will see a pane called “Variables/Input Parameters”. Click on the down arrow of “+” icon and select “Variable”
2. Specify Name of the Variable. Click on the drop down arrow besides “Attribute” and select REGION_NAME. Click on ok.
3. Activate the calculation view. Now click on the data preview.
4. A pop-up window will appear. Click on Open Value Help besides “From” and select “Asia” region. Click on ok.
5. Go to Raw Data tab to see complete output. It contains data only for “Asia” region.
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:
There are 4 system tables under the schema _SYS_BI which contains information about Variables.
- BIMC_VARIABLE
- BIMC_VARIABLE_ASSIGNMENT
- BIMC_VARIABLE_VIEW
- BIMC_VARIABLE_VALUE
Introduction to Input Parameters:
Sometimes you might not want a variable to just restrict the data of a view. But you also want to take input from the user and process it, returning dynamic data based on the user selection.
Input Parameters makes this possible.
How to create and use input parameters in modeling view:
Example Scenario:
Let us consider the same calculation view we created before which has following attributes and measures.
Attributes: PRODUCT_NAME, REGION_ID, REGION_NAME, SUB_REGION_NAME
Measures: SALES_AMOUNT
Suppose we want the end user to decide whether SALES_AMOUNT or NET_AMOUNT should be shown in the output.
We can create a input parameter of type “Static List” which will have 2 values “SalesAmount” and “NetAmount”.
The calculation view will prompt for the input parameter. The user will choose either “SalesAmount” or “NetAmount”. Based on this selection, the output will be shown.
Let us create the input parameter.
1. Open the same calculation view. On Right-Bottom side you will see a pane called “Variables/Input Parameters”. Click on the down arrow of “+” icon and select “Input Parameter”.
2. Provide Name, description and select the data type as INTEGER.
3. Select the Aggregation block and right click on “Calculated Columns” to create a new column.
4. Specify the name as “NET_AMOUNT”, Data Type as double. In the expression editor provide the expression as
"SALES_AMOUNT" - ( "SALES_AMOUNT" * $$DISCOUNT$$ /100)
Note that we are using Input parameter DISCOUNT by using syntax $$DISCOUNT$$
5. Activate the calculation view.
6. Click on data preview. A pop-up window will be opened. Provide the Region Name as ‘Asia’ and Discount value as 10.
7. The NET_AMOUNT will be calculated as per the given DISCOUNT value.
Important Features of Input Parameters:
- Input Parameters can contain any value the reporting user has to enter to parameterize the result. Therefore, a data type must be specified for each Input Parameter.
- Input Parameters are passed by Placeholders and used in Formulas.
Input Parameter Types:
The following types of Input variables are supported.
Currency:
Use this during currency conversion where the end user should specify a source or target currency.
Date:
Use this to retrieve a date from the end user using a calendar type input box.
Static List:
Use this when the end user should have a set list of values to choose from.
Attribute Value:
When an Input Variable has this type, it serves the same purpose as a normal Variable.
None:
If none of the above applies you do not have to specify an Input Variable type. The Type can be left blank.
How to Pass Input Parameters and Variables in SQL Query:
Open the data preview of calculation view as mentioned in previous step.
Click on the “Show Log” and then double click on the message highlighted below. A new window will be opened which contains the SQL query.
The generated query is:
SELECT TOP 200 "PRODUCT_NAME", "REGION_NAME", "SUB_REGION_NAME", "NET_AMOUNT", sum("SALES_AMOUNT") AS "SALES_AMOUNT"
FROM "_SYS_BIC"."sap-hana-tutorial/SalesReport"
('PLACEHOLDER' = ('$$DISCOUNT$$', '10'))
WHERE ("REGION_NAME" IN ('Asia') )
GROUP BY "PRODUCT_NAME", "REGION_NAME", "SUB_REGION_NAME", "NET_AMOUNT"
Note that in your case package name might be different and query might be slightly different.
Input Parameter is passed using PLACEHOLDER:
The value of Input Parameter is passed using PLACEHOLDER clause.
('PLACEHOLDER' = ('$$DISCOUNT$$', '10'))
WHERE:
The value of Variable is passed using WHERE clause.
WHERE ("REGION_NAME" IN ('Asia') )
No comments:
Post a Comment