EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  2. Topics

KB483566: Wrong data is observed when multiple elements are selected for the same SAP HANA input parameter


Paramjeet Sidhu

Quality Architect, Senior • MicroStrategy


In SAP HANA, tables have input parameters which can be imported into MicroStrategy as prompts When the user runs the report hitting this table, he/she can answer the prompts and the prompt answer will be part of the SQL generated. However, when multiple elements are selected for the same SAP HANA input parameter, unexpected SQL is generated, which leads to wrong data.

SYMPTOM
When multiple answers are provided for SAP HANA input parameter, Strategy generates following SQL:


select a11.DAY_DATE  DAY_DATE,
sum(a11.QTY_SOLD)  WJXBFS1
from "_SYS_BIC"."tutorial/AN_ORDER_DETAIL_CURR"
('PLACEHOLDER' = ('$$INPUT_CURRCY$$', 'EUR'),
'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '20030109'),
'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '20030113'))
a11
group by a11.DAY_DATE

instead of generating the following expected SQL:


select a11.DAY_DATE  DAY_DATE,
sum(a11.QTY_SOLD)  WJXBFS1
from "_SYS_BIC"."tutorial/AN_ORDER_DETAIL_CURR"
('PLACEHOLDER' = ('$$INPUT_CURRCY$$', 'EUR'),
'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '''20030109'', ''20030113'''))
a11
group by a11.DAY_DATE

STEPS TO REPRODUCE:
1. Use a project that uses SAP HANA type Database Instance.
2. In Warehouse catalog, choose a table without input parameter (For ex: tutorial/AV_TIME) and a table with input parameter (For ex: tutorial/AN_ORDER_DETAIL_CURR).
3. Create Attribute "Date" from tutorial/AV_TIME, "Day_Date" from tutorial/AN_ORDER_DETAIL_CURR.
4. Create fact "Qty Sold" from tutorial/AN_ORDER_DETAIL_CURR and create a metric "QTY_SOLD" based on it.
5. Create an Element prompt on Date (the prompt cannot use an attribute that uses a table with input parameter)
6. Find the table tutorial/AN_ORDER_DETAIL_CURR under Schema Objects and edit.
7. Change the prompt type of Parameter "INPUT_DATE_CURR", replace it with the prompt we created in step 5.
8. Update Schema.
9. Create a report with Day_Date and QTY_SOLD, save.
10. Run the report with default Prompt input for "Currency Input" and choose multiple Dates for the second Prompt input.
11. Show SQL for this report and note that one place holder is used for each element (in red below) that was selected. 

select a11.DAY_DATE  DAY_DATE,
sum(a11.QTY_SOLD)  WJXBFS1
from "_SYS_BIC"."tutorial/AN_ORDER_DETAIL_CURR"
('PLACEHOLDER' = ('$$INPUT_CURRCY$$', 'EUR'),
'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '20030109'),
'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '20030113'))
a11
group by a11.DAY_DATE

CAUSE
 
This is a known issue in Strategy 10.x.
 
ACTION
This issue has been addressed in Strategy 2019 Update 3.
If you are upgrading to Strategy 2019 Update 3 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This property can only be enabled through Workstation. For more details on enabling the setting, refer to Change Data Engine Version.

select a11.DAY_DATE  DAY_DATE,
sum(a11.QTY_SOLD)  WJXBFS1
from "_SYS_BIC"."tutorial/AN_ORDER_DETAIL_CURR"
('PLACEHOLDER' = ('$$INPUT_CURRCY$$', 'EUR'),
'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '''20030109'', ''20030113'''))
a11
group by a11.DAY_DATE

The Strategy Internal Reference Number for the issue discussed in this technical note is KB483566 and DE72791/DE134159.

Appendix


In Strategy 2019, before Update 3, with Data Engine Version 11, when the user supplies more than 2 values to answer the prompt, there is a syntax error due to incorrect quoting.
SYMPTOM
In the case above, if 3 answers are provided for SAP HANA input parameter, Strategy generates following SQL:


select a11.DAY_DATE  DAY_DATE, sum(a11.QTY_SOLD)  WJXBFS1 from "_SYS_BIC"."tutorial/AN_ORDER_DETAIL_CURR" ('PLACEHOLDER' = ('$$INPUT_CURRCY$$', 'EUR'), 'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '''20030109'', ''20030113,''20030109''')) a11 group by a11.DAY_DATE

instead of generating the following expected SQL:


select a11.DAY_DATE  DAY_DATE, sum(a11.QTY_SOLD)  WJXBFS1 from "_SYS_BIC"."tutorial/AN_ORDER_DETAIL_CURR" ('PLACEHOLDER' = ('$$INPUT_CURRCY$$', 'EUR'), 'PLACEHOLDER' = ('$$INPUT_DATE_CURR$$', '''20030109'', ''20030113'',''20030109''')) a11 group by a11.DAY_DATE


Notice that the second value misses the ending quotation mark.
It can cause a syntax error and fail the report. This issue is fixed in Strategy 2019 Update 3 and  Strategy 2020.


Comment

0 comments

Details

Knowledge Article

Published:

August 22, 2019

Last Updated:

October 18, 2019