HANA scripting - input parameters

[Former Member]

Hi developers,

I facing one issue and I am looking for answer, maybe you have found a solution for this.

In a calculation script I would like to use input parameters to push the filter down to the lowest level and get only the data into the memory what is responding to the input from the user(the variables option seems to read all the data and filter only after that in my case). There is on option in the input parameter - is mandatory - but as I have seen this is controlling only the popup screen.

I would like to have a solution where the input parameters are optional. The script looks like this

The P_KUNNR and P_ERDAT would be the 2 input parameters but I would like to have this optional. So if the P_KUNNR has a value than the script is filtering, but if it is empty, the script would get all the KUNNR what fits the P_ERDAT filter. at the moment if I run the script and do not enter any value for P_KUNNR, I got error msg from the Studio that Required variable $$P_KUNNR$$ is not set and the result is empty

	lt_sales =
	SELECT
		vbak.vbeln as "VBELN",
		vbak.netwr as "NET",
		vbak.kunnr as "KUNNR",
		vbak.erdat as "ERDAT",
		vbak.auart as "SOAUART",
		vbap.posnr as "POSNR",
		vbap.matnr as "MATNR",
		vbkd_h.bstkd as "H_BSTKD",

WHERE VBAK.KUNNR = :p_kunnr and VBAK.ERDAT >= :p_erdat_from

Do you know any solution for this issue?

Thanks in advance

BR
Tamas

[Former Member]

Will the variable usage not suit your requirement.

I created a simple script based CV which takes only input as "p_erdat" and defined the 'KUNNR' in terms of variable (non-mandatory)

---

/********* Begin Procedure Script ************/

BEGIN

            var_out = SELECT

   vbak.vbeln as "VBELN",

    vbak.netwr as "NET",

   vbak.kunnr as "KUNNR",

    vbak.erdat as "ERDAT" from VBAK WHERE "VBAK"."ERDAT" >= :p_erdat;

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

And defined the variable for KUNNR.. Is it mandatory that both parameters should be coming as input parameters in your case?

regards,

Rahul

[Former Member]

I tried earlier the variable setup, but somehow the performance was way worse compared to the input parameter. I think the DB is materializing my entire view and the filter is applied only after that.

I have a main select and 1 projection additionally on that because I need some calculation on the values.

due to the performance I tried to do it with input parameters.

BR
Tamas

[henrique_pinto]

You probably thought it was faster from comparing a filtered view (with input param) against an unfiltered one (with non-mandatory variable).

If you want to go for good performance, then you should do the modeling right.

I.e., add the fact table to an Analytic View, join with the necessary attribute views and create the variable in Analytic View level. You don't even need to go for a Calc View.

If you do it, everything will run on the OLAP engine, not even going through the calc engine, and hence you'll get top notch performance.

[Former Member]

I created a analytic view with VBAP/VBAK tables (sales) and one attribute view is link to that with 1:1

The attribute view is for characteristics using IBIN/IBINVALUES/IBSYMBOL tables filtered for one specific ATINN characteristics.

I created variables in the analytic view for KUNNR and ERDAT and the speed was around 20 seconds.

The previous SQL statement, I wrote, containing the same plus additional pricing conditions/10 more characteristics and more business data is running in 3-5 seconds for the same kunnr and erdat.

Did I forget to put any filter somewhere? I am not sure.

[henrique_pinto]

Well, in the calc view above, you didn't have any joins.

That is enough of a reason for a lower execution time.

Also, make sure you're indeed filling the variables so that the filter is actually considered, or else it'll just bring everything even if you have defined the variables.

Other than that, make sure all tables are fully loaded in memory.

If you still observe a poor performance in the OLAP engine, then it might be something else more deep down. Some tuning might be necessary.

[Former Member]

in the example I copied only couple of lines from the 400lines of SQL statement.

what do you mean with this below?

Also, make sure you're indeed filling the variables so that the filter is actually considered, or else it'll just bring everything even if you have defined the variables.

When I run data preview on the analytic view the system is prompting for the input and in the result I see only the requested data.

In the model the variable is linked to the field I want to filter. Is there something else I have to do?

thanks for the help in advance

[henrique_pinto]

BTW, how are you joining VBAK & VBAP in the Analytic View?

A test you could do is creating one AN and joining them in the Data Foundation vs. creating two ANs and joining them in a graphic Calc View.

Maybe, if you have filters, joining them after the filters are applied (i.e. 2 ANs in a calc) might yield a better result.

How many records do you have in these tables?

20 seconds for such a simple model seems too much.

[Former Member]

IBINVALUES has 600.000.000 records

VBAP 6.000.000

KONV has 400.000.000

I cannot survive with one AN because the report is showing the sales orders plus related deliveries and invoices created for the deliveries.

I could get the sales and deliveries but the invoices are linked to the deliveries and in AN i can have only 1 level connection. So I cannot have there the sales linked to delivery what is linked to invoices.

the vbak is joined via vbeln to vbap - referencial and 1:N - header / item

I tried it in one AN linked all tables, but the speed was similar for multiple run. All tables should be in memory. If you want I can send my SQL to you to get better view about the issue.

[henrique_pinto]

Could you send the complete SQLScript code for the calc view that meets your requirements?

I suppose the one above is not the one you is actually using, since it doesn't mention IBINVALUES or KONV.

[Former Member]

you can find the file attached.

this is sql select, without sqlscript.

sdn_report.txt.zip

[henrique_pinto]

You should create it as a variable instead of an input parameter.

Check item "6.2.8 Assigning Variables" of the HANA Developer Guide:

http://help.sap.com/hana/hana_dev_en.pdf