Handling multi value input parameters

[patrickbachmann]

Hi folks,

When I pass a SINGLE value to a parameter in my calc view it works nicely and I get very fast results;

SIMPLE CALC VIEW:

var_out  = select MAT.MENGE, MAT.BWART, MAT.MJAHR from "MySchema"."MSEG" MAT where MJAHR = '2005' and BWART = :MoveType;

CALL VIEW LIKE THIS:

SELECT TOP 10000 "BWART", sum("TOTALQUANTITY") AS "TOTALQUANTITY" FROM "_SYS_BIC"."myView" ('PLACEHOLDER' = ('$$MoveType$$', '101')) GROUP BY "BWART"

Now to try passing multiple values into single parameter I'm trying to call like this;

SELECT TOP 10000 "BWART", sum("TOTALQUANTITY") AS "TOTALQUANTITY" FROM "_SYS_BIC"."myView" ('PLACEHOLDER' = ('$$MoveType$$', '''101'',''104''')) GROUP BY "BWART"

BUT for this to work, I have to change the syntax in my calc view to something like this which performs horrendously;

REVISED CALC VIEW:

vartable = select MAT.MENGE, MAT.BWART, MAT.MJAHR from "MySchema"."MSEG" MAT where MJAHR = '2005';

var_out = CE_PROJECTION ( :vartable, ["MENGE" As TOTALQUANTITY, "BWART"], 'in("BWART", :MoveType)');

Notice there is now a projection in addition to the select statement.  This is because it's the only way I can get example from SQL script guide to work for multiple values.  I would expect to be able to do this more simply without projection like the following but it does not work;

REVISED CALC VIEW:

var_out  = select MAT.MENGE, MAT.BWART, MAT.MJAHR from "MySchema"."MSEG" MAT where MJAHR = '2005' and BWART IN (:MoveType);

I can not find any way to do this via the initial select statement, no matter how I arrange the single quotes in my calling SQL.  The only thing that works is via the projection and with the IN() syntax from the SQL guide example and I get very bad response.  My theory is that it is passing over the same data twice when done in this manner.  I'm wondering if anybody can get this to work without the additional projection step AND/OR are there any improvements to multi-value parameter support in upcoming REV8 release?

NOTE: I'm using HANA REV68 for these examples. Also I'm only selecting top 10000 as this is just a test that I want to apply to a much more complicated view if I ever get this to work.

Thanks,

-Patrick

[sagarjoshi · Accepted solution]

Hi Patrick,

I think possible way to handle this is using the APPLY_FILTER technique. Check documentation in SQL Script guide.

This gives complete flexibility to use conditions like IN, BETWEEN ..etc.

I have also checked in Visualize plan that it does filter pushdown.

Example SQL Script code below where IN_KUNNR is a Input parameter

lt_out = select kunnr,name1 from kna1;

var_out = APPLY_FILTER(:lt_out,:IN_KUNNR);

You can call your view using

SELECT TOP 200 "KUNNR", "NAME1" FROM "_SYS_BIC"."Z_TEST" ('PLACEHOLDER' = ('$$IN_KUNNR$$', 'KUNNR IN (''0000000003'',''0000000020'' )'))

Hope this works for your use case.

Thanks,

Sagar

[Former Member]

Hi Patrick,

APPLY_FILTER will actually get all the records and applies the filter on the result set. It is bound to be slow.

Instead if you could rewrite the code using CE functions then apply the filter on the projection directly then you will see vast performance improvement.

lt_out = select kunnr,name1 from kna1;

var_out = APPLY_FILTER(:lt_out,:IN_KUNNR);

Regards

PK

[sagarjoshi]

How did you come to conclusion?

"APPLY_FILTER will actually get all the records and applies the filter on the result set. It is bound to be slow.

Did you check any example and plan visualization where filter was not pushed down?

[patrickbachmann]

Closing this thread.  Many thanks guys!

-Patrick

[Former Member]

Hi Patrick,

I am not able to correctly use APPLY_FILTER functionality and using HANA SPS09.

My Use Case as below: Calculation View

Below line of code gives me the result in the Table1 as given below.

var_ff = CE_AGGREGATION(:var_defects, [COUNT("ContextId") AS "Defects"],["EmployeeName"]);

Table:1

Defects	EmployeeName
4	admin
6	admin1
8	admin2
10	admin3

I want to send the Input parameter say admin'',''admin1 and get result for only that Employee Names.

My below Calculation View is activating correctly but not giving the result.

**************************************************************************************************************************************

BEGIN

  DECLARE SQL_STR VARCHAR(2000);

  var_defects = CE_JOIN_VIEW("_SYS_BIC"."aProdV1/AT_DEFECTS",["EmployeeName","ContextId"]);

  var_ff = CE_AGGREGATION(:var_defects, [COUNT("ContextId") AS "Defects"],["EmployeeName"]);

  /*SQL_STR:='"EmployeeName" IN (''admin'',''admin1'')';/* working one Input value hard coded and gives desired output*/

  SQL_STR:='"EmployeeName" IN (''$$NAME$$'')';/* NAME is INPUT PARAMETER */

  var_out=APPLY_FILTER(:var_ff,:SQL_STR);

END /********* End Procedure Script ************/

*******************************************************************************************************************************************

Please the attached Input.jpg for Input parameter and output.

I want to filter the data by passing input parameter say admin'',''admin1 etc

Please also let me know on escape character used in HANA.(I mean how to pass input parameter)

regards

Raj

[Former Member]

Hi Sagar,

Any update on this thread please?

regards

Raj

[patrickbachmann]

Hi Raj,

Sorry I somehow missed this.  We are still on SPS8 with plans to goto SPS9 in the coming months.  As soon as I'm on SPS9 I will revisit this.  Are you still having problems or did you find a workaround?

-Patrick

[justin_molenaur2]

I saw this just today

Page of this set of slides indicates SPS9 can use IP's with multiple value inputs.

SAP HANA SPS09 - HANA Modeling

Regards,

Justin

[former_member182302]

Hi Patrick,

1) Have a look on the below blog , Even i tried the same so eventually went for this approach.

Graphical approach works but with the projection:

2) Also try APPLY_FILTER as mentioned above by sagar.

Regards,

Krishna Tangudu

[patrickbachmann]

Thanks for the great tidbits guys!  Let me digest this and will update you soon on my findings.

-Patrick

[patrickbachmann]

Ok thus far I have tried the APPLY_FILTER method as suggested by Sagar.  Of course my real example is much more complex view but when using the projection method to apply the multi-value parameter it was previously taking 4mins.  Using the APPLY_FILTER it is now taking just 1min. 

What is still baffling us is that we can not get the time down to about 4 seconds which is how much time it takes to run if we just simply cut and paste all of the SQL from the calculation view and run it directly in SQL editor.  It seems that when calling the view and passing variables to it adds a lot of overhead that we can't seem to eliminate. 

To elaborate, lets say my view is 1000 lines of code;

select

field1,

field2,

field3,

....

field 1000

from VIEW where MovementType IN ('101','102')

If you run this directly in SQL editor it's 4 seconds.

Alternatively if you call the view using either the PROJECTION method (4mins) or the APPLY_FILTER as Sagar recommends (1mins).

I would expect to be able to get the runtime down to the 4seconds somehow. 

-Patrick

[sagarjoshi]

Hi Patrick,

Please note that APPLY_FILTER also has some disadvantages similar to dynamic SQL (documented in SQLScript guide) since where predicate criteria is applied dynamically.

The query plan can be different when your calc view is embedded in another SQLScript and final SQL that is generated may be looking very different when compared to just executing called view from SQL editor.

I would suggest to check following:

1. Check using plan visualization if the APPLY_FILTER criteria is getting pushed down to called views foundation data tables.

2. I have also seen in past that generating SQL plan cache was taking lot of time when APPLY_FILTER was used and subsequent executions were faster. Did you check if this is your problem?

However 1 min seems too long and the underlying problem may be due to missing filter pushdown to data foundation table in called views.

Thanks, Sagar