Char relationship method to invoke SQLScript procedure does not get triggered

[PankajVPatil]

Hello Experts,

I am on SAPKW74014 and trying to implement the char relationships via the SQLScript procedure on a direct update DSO.

Have created the following :

1) Created a class that implements the IF_RSPLS_CR_EXIT_HDB interface and CL_RSPLS_CR_EXIT_BASE as super class.

2) Implemented the 2 GET_SQLSCRIPT* methods.

     a) in the *INFO method passing the schema name, name of procedure to derive chars and the structure name.

     b) in the *PARAMETERS methods have passed a structure whose name have assigned in *INFO method and that i have locally created in BW. this is similar to the structure of the table type created in HANA. 

3) created a derive procedure in HANA and is activated successfully. The procedure contains input, output parameters and logic is simple fiscal period to calendar mapping.

4) The "allow_non_readonly_sqlscript" is set to true and "enable_sqlscript_cmd" is set to on in DBACOCKPIT.

5) Created a CR that references the class.

With the above configuration in place, a save from input form do not trigger either of the *GET_SQLSCRIPT methods.

So what is it am missing ? How do I invoke the procedure as part of the CR trigger ? Any other settings to check ?

Thanks,

Pankaj

[PankajVPatil · Accepted solution]

Hello Experts,

To further analyze this, have created 2 CRs. Both contain the identical SQLScript setup, only that one of it contains a ABAP back up code. So when the input form runs with the CR having ABAP backup, the ABAP DERIVE method gets execute and the char derivation happens correctly. In either case, the SQL methods do not get invoke. To understand the root cause, i performed the ST12 trace for runs when the either CR was active and tried to compare the 2 traces.

In the debugging got to know that there are 2 standard classes *CR_CONTROLLER and *CR_MAPPER which has a DERIVE_ALL method and that is executed at run time. These 2 classes also have a DERIVE_HDB method that holds the downstream code to trigger the SQL methods from the CR Exit class.

Now, there is another class CL_RSPLS_BUFFER_B which has a _INIT_CR_CONTROLLER method getting invoke and which has an input parameter I_STORE that holds the key to let system decide whether to run via ABAP or HDB procedure. For my runs, the I_STORE always holds the value A meaning; to run via ABAP and hence DERIVE_ALL is run. For the SQL method's invoke I_STORE should have a value of T.

So basically the question is how to control the I_STORE parameter ?

Please feel free to let me know in case I should be looking at some thing more specific from the ST12 traces apart from the above and/or in general anything more useful in this matter.

Thanks,

Pankaj

[PankajVPatil · Accepted solution]

Hi,

Instead of the input form I have created a planning sequence and now the GET_SQLSCRIPT* methods invoke to trigger the procedure.

In Derivation in HANA | SCN answered by is helpful to understand when we need to leverage HANA so have followed the steps on similar idea.

However after executing the planning sequence (PS) i am getting the errors as below.

This is during the first time run of the PS.

And in all concurrent runs get the below error.

can you please confirm what is that I need to do to get the error resolved ?

Thanks,

Pankaj

[PankajVPatil · Accepted solution]

Hello everyone,

While debugging the issues above, I get a feeling that in the GET_SQLSCRIPT_PARAMETERS method need to pass the input values explicitly to the assigned structure E_S_PARAMETER.

The issue is that there is no such input parameter on the method and none of the attributes of the class hold this input data. So how do I access the same in the method ?

Thanks,

Pankaj

[gregor_dieckmann · Accepted solution]

Hi Pankaj,

you first statement is correct: in the method GET_SQLSCRIPT_PARAMETERS the exit implementation has to set the parameter values in E_S_PARAMETER. At run time, when the system calls the SQL script implementation, these parameters will be used to fill the formal parameters of the SQL script procedure. Thus the procedure has to contain the parameters.

This information is all contained in the documentation, cf.

SAP HANA-Specific Interfaces for Characteristic Relationships and Data Slices - Planning Business Da...

and

Parameters for the SQLScript Procedures - Planning Business Data with BW Integrated Planning - SAP L...

Regards,

Gregor

[PankajVPatil · Accepted solution]

Hi Gregor,

Thanks for the response.

I had already gone through these links, although this time reading more closely understand that -

1) Structure of the IN and OUT parameter table type should exactly be the list of the objects defined in CR (input + output). I have to derive 0CALMONTH, 0CALQUARTER, and 0CALYEAR from 0FISCPER, 0FISCVARNT. So have defined a table type in HANA containing the respective 5 objects.

2) In the 2 methods (GET_SQLSCRIPT*), the E_PARAMETER_NAME and E_S_PARAMETER should be assigned only if need to pass some additional information from ABAP. In this case as I don't have anything additional, I have commented it out.

Now, I run the planning sequence and get the following error. During debugging found that, the said exception is caught in the last step during the SQLScript trigger and execution. All the associated buffers during the run time are filled okay for the logic's processing.

In one of the other SCN Thread on similar topic/error (somehow the reference of it is getting hung in my editor so not able to link it in this thread) found that the note 2069393 needs to be applied.

I want to understand in the context here, is there something I can try out differently ? OR

Is this the product error and applying the said note will resolve the issue ? Btw I am already on SAPKW74014.

Thanks,

Pankaj

[PankajVPatil · Accepted solution]

Hi Gregor,

I followed the steps enlisted in this SCN Thread Planning Functions fails due of data slice (no ... | SCN

In the HANA indexserver trace file, could find the following trace lines which I think could be useful.

what i get from the above lines is that the call to procedure is going in to error but am not sure how to interpret the "insufficient privilege" remark and what should be the next steps ?

Below is the complete procedure.

Thanks for all the help !

Regards,

Pankaj

[gregor_dieckmann · Accepted solution]

Hi Pankaj,

this seems to be an authorization problem ('priviledge') since the SQL script is executed with the system user used by the ABAP database connection. Have created the script via HDB Studio and a user that has much more authorizations?

In general it is recommended to implement exits using AMDP (i.e. use SQL script in ABAP): then the whole life cycle management and all the authorization stuff should be no issue any more.

Regards,

Gregor

[PankajVPatil · Accepted solution]

Hi Gregor,

Thanks for the explanation on Authorizations. We were able to resolve this error.

Regards,

Pankaj

[PankajVPatil · Accepted solution]

Hi Gregor,

I have a procedure which gets called from CR. The procedure basically reads from the cost center master data and assigns the cost center attributes to the output. Additionally certain other objects are assigned/derived as static values. Now the issue is that, one of the object (ZMR_ATTR) which has static assignment do not get deduced in the output, whereas the CC based attribute deduction/assignment works fine.

And I know the CASE statement works fine, as have tested the working of procedure by specifying the input parameters locally in HANA which works okay. So if I pass a '' (read as 2 single quotes) as input value in the ZMR_ATTR object then the output gets the 0000...1 value (output from code) but some how when I run the Planning Sequence, the object outputs blank. I was trying to debug the procedure but for some reason the flow does not break in the HANA debugger.

My question is if the '' (i.e. 2 single quotes) is read by HANA buffer as blank for other objects (i.e. for CC based attributes) then why is it not reading/interpreting the same for the ZMR_ATTR object during the planning sequence's run ?

Procedure code in below pic.

Thanks,

Pankaj

[gregor_dieckmann · Accepted solution]

Hi Pankaj,

you have to be careful with the ABAP and DB type system. PAK always internally uses data types that correspond to the ABAP type system. So check the type of ZMR_ATTR in ABAP and what the initial value (is this what you are interested in?) is. I expect that '' (two single quotes) represents the empty string, but no characteristic value in ABAP is of string type but has a fixed length (and maybe some conversion exists, cf. RSD1 for this).

There is also some debug infrastructure from ABAP available:

- set break-point in CL_RSPLS_BUFFER_B, _CR_DERIVE and search for the call:  l_r_cr_controller_->derive_hdb

- in the ABAP debugger use tab 'Script', press button 'Load Script' and enter as script name: RSR_DEBUGGING_SCRIPT_STORE; then click 'Start Script'; choose 'Display Views' and there choose the top one (the one that corresponds to variable C_R_STORE)

Variable C_R_STORE also contains some information, but this is not self-explaining.

Regards,

Gregor

P.S.

The following link maybe also helpful:

Message was edited by: Gregor Dieckmann

[PankajVPatil · Accepted solution]

Hi Gregor,

C_R_STORE solved my problem. Basically the ZMR_ATTR is set to NUMC in ABAP, while other fields are CHAR and hence the blank was populated as 000..000 as default. See snap below, the corresponding ZMR_ATTR2 being defined as CHAR is basically blank. After I changed the '' with '000..000' in procedure against the WHEN of the ZMR_ATTR object the code gets run okay. Thank you!

Thanks and Regards,

Pankaj

[PankajVPatil · Accepted solution]

Hi Gregor,

After I deployed my CR setup and run a input form with a different sap (BW) id then a host of errors come up due to which the data is not saved. If the same input form is opened by my sap (BW) id, the data is saved correctly.

Now from BW perspective, both the ids have same authorizations however from HANA only my id exists. Also the way CR runs in HANA at execution time is by SAPSR3 id and which is consistent for both the individual SAP BW user ids.

When I remove the IF_RSPLS_CR_EXIT_HDB interface then the input form save works fine from the other id as well, so I know that the issue is with CR configuration only.

FYI, the input form contains top-down disaggregation feature from query on the KF.

The indexserver trace file reveals that the run time views of "result_lookup_invalid", "result_snapshot" etc. are returned erroneous when run from other SAP id. On the Input form, itself get the errors as in pic below. Can you please provide clues to why this may be occurring ?

Thanks & Regards,

Pankaj

[gregor_dieckmann · Accepted solution]

Hi Pankaj,

please check note 2285769.

Regards,

Gregor

[PankajVPatil · Accepted solution]

Hi Gregor,

We implemented the "disable_string_length_checks" parameter from the note 2285769, however now we run in to a different error types as depicted in below pic. The errors are very diverse.

Any way I can systematically trouble shoot it.

Also this time somehow, the flow did not stop at the CL_RSPLS_BUFFER_B classes' method _CR_DERIVE; so interestingly the run is skipping the CR setup completely.

Thanks,

Pankaj