Script calculation view - dynamic input parameters syntax

[Former Member]

Hi,

Currently we are developing a data model for CRM on HANA where we have to use some input parameters to filter the information. We have created a graphical calculation view where we use the input parameters DATEFROM and DATETO.

We need to call this CV in another scripted calculation view where we want dynamically fill these input parameters. We have found in other topic a solution with following syntax:

SELECT *  FROM "_SYS_BIC"."ZCMOBJECT" (PLACEHOLDER."$$DATEFROM$$" => ADD_DAYS (CURRENT_DATE, -1),

PLACEHOLDER."$$DATETO$$" => ADD_DAYS (CURRENT_DATE, -10));

However with this syntax our performance is a little poor because the graphical view takes around 8 - 9 seconds and with this syntax we get 25 - 30 seconds.

If we use the following syntax the performance is the same like when we run the graphical calculation view:

SELECT * FROM "_SYS_BIC"."ZCMOBJECT" ('PLACEHOLDER' = ('$$DATEFROM$$', '20150301' ), 'PLACEHOLDER' = ('$$DATETO$$', '20150311'));

We also got problems with CPU consumption with the first approach.

Does anyone have any problems with this solution?

PS. An additional note is related with a limitation because we have to do some calculations with function WORKDAYS_BETWEEN and this is only available in scripted CVs and that's the reason to use this scripted calculation view.

Thank you very much for your time.

Best regards,

Pedro Amaro

[Former Member]

Hi Pedro,

Can you have that function in a separate variable and pass those variable inside the select query placeholders ?   Hope this may fix your performance issue.

lv_fromdate := ADD_DAYS (CURRENT_DATE, -10)

lv_todate := ADD_DAYS (CURRENT_DATE, -1)

SELECT *  FROM "_SYS_BIC"."zplb2c.crm.report.objections/ZCMOBJECTIONS"

(PLACEHOLDER."$$DATEFROM$$" =>  :lv_fromdate ,

PLACEHOLDER."$$DATETO$$" => :lv_todate );

Thanks

Siva

[Former Member]

Hi Pedro,

Was that a typo in your 2nd place holder ? I am seeing the value -10 here which would subtract 10 days from current date for your Date To placeholder.

SELECT *  FROM "_SYS_BIC"."ZCMOBJECT" (PLACEHOLDER."$$DATEFROM$$" => ADD_DAYS (CURRENT_DATE, -1),

PLACEHOLDER."$$DATETO$$" => ADD_DAYS (CURRENT_DATE, -10));

Regards

Siva

[Former Member]

Hi Siva,

Yes, you are right, the correct statement is the following:

SELECT *  FROM "_SYS_BIC"."zplb2c.crm.report.objections/ZCMOBJECTIONS"

(PLACEHOLDER."$$DATEFROM$$" => ADD_DAYS (CURRENT_DATE, -10),

PLACEHOLDER."$$DATETO$$" => ADD_DAYS (CURRENT_DATE, -1));

The input parameter DATEFROM is equal current date - 10 and DATETO is equal yesterday.

Thank you very much.

Pedro Amaro

[lbreddemann]

Why do you think the syntax is what makes your performance bad?

Especially when you state that changing the syntax doesn't do anything about the performance?

Most likely the performance issue is related to your calc view design.

But as we don't know what you do in your calc view, we cannot help you further here.

BTW: workdays_between should be available in graphical calc views, too - if I am not completely off chart right now (even if it is not in the list of functions in the editor - just type it into the formula).

- Lars

[Former Member]

Hi Lars,

Thank you for your input.

As I said with first syntax I got worst results, in terms of performance and CPU consumption.

With the syntax that's used by standard in HANA I got a good performance and this is my issue with any other change in my statement:

In following image you can find my statements and the server processing time for both queries (my input parameters are filled with same dates):

We're in revision 73 of HANA and with this version I always got error the same error when use workdays_between formula in graph calculation views:

Please let me know if I'm doing something wrong.

Thank you very much.

[lbreddemann]

Hmm... I couldn't reproduce the issue with the different parameter syntax on Rev. 92.

As rev. 73 is pretty old anyhow, I recommend to upgrade when possible.

About the WORKDAYS_BETWEEN: sorry - this one is indeed not available in the expression editor.

BTW: this function is a SAP ECC related function that only works based on the SAP ECC factory calender tables. Without those tables the function doesn't work properly - just to give you heads up here.

- Lars

[Former Member]

Hi Lars,

Thanks for the reply.

We've done analysis with plan visualizer tool and with this we made some changes to improve the performance of our view.

But it's strange that with different syntax the performance is worse when we use the second syntax.

It would be a great improvement if the formula workdays_between does exist in future SP's in graph CVs. We'll propose this improvement to SAP.

Thanks,

Pedro Amaro