KB484338: SAP HANA direct input parameter support in MicroStrategy

This article outlines the following topics: 

1. Time and Timestamp data types 
2. Decimal values
3. Default answers for IPs
   
1. Single and multiple constants
2. Expressions
4. Semantic type
5. Direct input parameters for MicroStrategy cubes
6. Summary

1. Time and Timestamp data types

For the Time and Timestamp data types, Strategy presents you with a prompt that allows you to specify only the date. The selected date will be in the format of YYYYMMDD and sent to HANA in the PLACEHOLDER syntax. This makes the Time data virtually not supported, whereas for Timestamp, an appropriate setup on HANA will allow you to provide an answer with respect to only the date. 

• Strategy prompt for the Timestamp data type:
  

• Strategy sends a string corresponding to the date:
  

• Depending on the setup on HANA, the string is casted to a valid Timestamp value:
  

• The prompt for the Time data type: 
  

• An effect of passing such string value to the Time type input parameter depends on the specific setup on HANA – it may cause a build error. Either way, you are unable to specify Time via the default value prompt.
  

2. Decimal values

Using the value prompt, you can input a single decimal value if the value prompt type is numeric.

• Input parameter of Decimal data type – you can provide only one answer.
  

• The resulting SQL:
  

• Providing more than one answer is not possible since the data type of the input parameter is numeric.
  

• Embedding values in two single quotation marks is also not possible – this way of passing multiple answers works only for string types.
  

It is possible to pass several numeric values via value prompt if the type is Text. This either requires the input parameter to be a string data type or the default value prompt in logical table object needs to be replaced with the custom value prompt.
 

3. Default answers to IPs

3.1 Single and multiple constants

You can provide default answers to a direct input parameter in SAP HANA to facilitate user experience when querying the CV. In HANA Studio, it will appear automatically when the Data Preview option is used.

Strategy automatically fetches only the first default value provided. When multiple default answers are provided, a workaround on Strategy's side is required to integrate with this setup.

To fetch multiple default answers for direct input parameter to Strategy, you must update the logical table element and replace the default direct prompt with a custom attribute element list prompt, where an attribute used in this prompt is a “LOW” column from BIMC_VARIABLE_RANGE_DEFAULTS (as for SAP HANA 2.0 SPS04).
This workaround is described in SAP HANA column type input parameter support in Strategy.
 

3.2 Expressions 

Strategy is unable to integrate with expressions as default answers for input parameters. Strategy will fetch the expression string rather than a value it produces. In other words, if input parameter contains an expression, ADD_SECONDS(NOW(),-30), as the default answer, HANA Studio will evaluate it to the respective timestamp (current timestamp 30 seconds ago), whereas Strategy will show the user the string itself, ADD_SECONDS(NOW(),-30).
 

4. Semantic type

HANA has a feature which is complementary to a data type of an input parameter, a Semantic type. It modifies the dialog you are presented with for answering the input parameter. For the Date Semantic type, you can choose the appropriate date from a calendar. For the Currency Semantic type, you can choose from currencies available in the TCURC table. Strategy does not use the Semantic type concept in any way. It relies on the data type of the input parameter.
 

5. Direct input parameters for Strategy cubes

There are two types of cubes in Strategy:

• MTDI cubes: Created in Strategy Web
• OLAP cubes: Created in Strategy Developer

OLAP cubes do not support prompted objects at all. The only way to create a cube from a calculation view is via Web.
Out of the three workflows available in Web for creating a cube, creating from the Select Tables option is recommended for integrating with input parameters. Both Build a Query and Type a Query options require user’s manual intervention to send appropriate the SQL (PLACEHOLDER syntax).
In the case that the direct input parameter allows for multiple answers, it is possible to pass multiple answers via value prompt only for string type IP, using a syntax where each item in a list is embedded in two single quotes:

 
The resulting SQL:

Therefore, if a CV requires a list of numeric values, parsing and casting logic need to be done on HANA side to interpret either a string like ‘130,90,25,17’ into separate values, or to cast a list of strings into a list of numeric values.
 

6. Summary and Recommendations

• The Semantic type should correspond to data type. Strategy uses data types for adjusting its default prompt.
• Use the Select Tables option for creating cubes, not Build a Query or Type a Query.
• Fetching multiple default answers requires a workaround on Strategy side.
• To pass multiple answers through a value prompt, the prompt type must be Text.
• The default value prompt type depends on the input parameter data type.
• Answers as expressions are not supported in Strategy.
• Strategy passes date string in the format “YYYYMMDD” as answer to date types.