Version 2020

Calculation View Input Parameters

SAP HANA can parameterize modeling views. To execute certain SQL on top of a modeling view with input parameters defined in it, an answer or value must be provided before a query can run. The prompts are defined at the view level..There are two types of prompts: variables and input parameters.

Variables are used to filter data in a view based on a column when a query executes. The filtering, in SQL, translates to a condition in the WHERE clause. From HANA Studio, for example, running a query (data preview option) on a view with variables results in a prompt dialog. Upon providing answers, the qualification is included in the WHERE clause of the SQL statement. Variables are logically equivalent to prompted filters in MicroStrategy.

In other words, the variable values are passed to the HANA engine via the WHERE clause of the SQL statement. Variables are not known by the engine, and only by the clients.

The image below shows how variables display to users in HANA Studio (HANA’s client software):

These variables are rendered in the HANA Studio generated SQL:

SELECT …
FROM "_SYS_BIC"."mstr.tutorial/CL_VARIABLES_MLINE"
WHERE ("YEAR_ID" IN ('2010')) AND 
      ("MONTH_ID" BETWEEN 
      ('200901') and
      ('200911') ) AND ("DAY_DATE" > ('2009-01-08')  )
GROUP BY …

Input Parameters

Input parameters are used as a means to provide data used as input for internal calculations defined in the view. This data can be used to resolve calculated columns, for currency conversion (e.g. return data in EURO or USD), or for other operations. Input parameters, from HANA Studio, are rendered in the same way as variables when executing a query on a view with input parameters (using the Data Preview option). A dialog appears for users to provide answers to the prompts. In the SQL, these answers are included in special clauses in the FROM clause of the SELECT statement.

As opposed to variables, an input parameter defines an internal parameterization of the view. This means the SAP HANA engine needs to know it and uses the parameter value during the execution (e.g. to calculate a formula for a calculated measure). The parameter value is passed to the engine via the PLACEHOLDER clause of the SQL statement.

The following screenshot shows how input parameters display to users in SAP HANA Studio (HANA’s client software) when performing a data preview on a view with parameters:

SQL generated by MicroStrategy depends on the “Input Parameter Multiple Answer Pattern” Property in the Database.pds file. Syntax generated by MicroStrategy should correspond to the syntax expected by HANA.

For example:

select    "a11"."CompanyCode"  "CompanyCode" 
from    "_SYS_BIC"."public/CALCVIEW3"      
('PLACEHOLDER' = ('$$input$$', '''0100'', ''0102'', ''0103'''))               "a11"

The following table is a summary of input parameters and variables:

 

Variables

Input Parameters

Special SQL

No (WHERE clause)

Yes (PLACEHOLDER clauses in FROM clause after information view name

HANA Engine Awareness

No

Yes

Sample use cases for input parameters:

  • Currency conversion. Several parts of the currency conversion can be parameterized: source and target currency, type of exchange rate and date of exchange rate.
  • Creation of Transformation Metrics from SAP HANA. Instead of creating transformation metrics in MSTR, model these from SAP HANA through calculation views.

Input parameters are imported by MicroStrategy as table-level prompts for any information view that supports input parameters (analytical and calculation views).

By default, MicroStrategy creates value prompts for each imported input parameter (since parameters are answered by providing only one value, whether it is numeric, date, text, etc.). Input parameter properties, such as default answer value, whether it is mandatory or not, are imported as part of the prompt definition. Moreover, these value prompts created by default can be replaced with other types of prompts, such as attribute element prompts, to allow users to choose values instead of making them entering plain values.

When a report uses an attribute or metric modeled on top of the parameterized table (an information view with input parameters), these table-level prompts will appear as regular MicroStrategy prompts to the end users. MicroStrategy will also generate the appropriate SQL constructs in order to pass the parameter values on to SAP HANA’s information views.

Among the enhancements made to the product to enable input parameter support are the following:

  • Warehouse Catalog allows previewing the parameters defined for an input parameter.

  • Table Editor allows viewing, modifying and replacing default prompts for each of the input parameters defined for the table being edited. A Parameters tab exists in the Table Editor.

Variables are not supported in MicroStrategy.