cancel
Showing results for 
Search instead for 
Did you mean: 

Characteristic relatioships with SQL script

Former Member
0 Kudos

Hi Gurus,

I try to create a very simple characteristic relationship based on SQL script (I'm on SAPKW74008 level).

I have done the next steps:

1. created a class with IF_RSPLS_CR_EXIT_HDB interface and CL_RSPLS_CR_EXIT_BASE superclass

2. in method GET_SQLSCRIPT_INFO filled schema and procedure name

3. created DERIVE procedure in HANA studio

4. in DBACOCKPIT maintained indexserver.ini file where planningengine has allow_non_readonly_sqlscript = yes

5. in my SQL procedure there is no "READS SQL"

6. created a characteristic relatioship based on my class with derivation

Still I get the next errors:

Could you please advise ?

P.S. Planning function type based on SQL script, as described here, works fine (in the beginning I had the same errors, but changing indexserver.ini file solved it in opposite to characteristics relatioship)

Thank You,

BR,

Accepted Solutions (0)

Answers (1)

Answers (1)

sheldon_piao
Employee
Employee
0 Kudos

Hi Andrey,

Have you used the report RSPLS_SQL_SCRIPT_TOOL to create HANA table name and use it in your script ?

Best Regards,
Sheldon.

Former Member
0 Kudos

Hi Sheldon,

I have done it.

Let's say I have a CALMON, CALYEAR, CURRENCY and PLANT in aggregation level where in the cube I have in addition ZPLANT which I want to derive with characteristic relatioship from PLANT (the example is very simple and "stupid" enought, but I want this scenario working, tnen I will be able to extend it to really complex scenario).

So I have created a table type via RSPLS_SQL_SCRIPT_TOOL:

CREATE TYPE SAPHANAABAP.ZAL0004 AS TABLE ( 

  CALMONTH NVARCHAR (000006),

  CALYEAR NVARCHAR (000004),

  CURRENCY NVARCHAR (000005),

  PLANT NVARCHAR (000008),

  ZPALNT NVARCHAR (000008))

Then I have created the procedure:

CREATE PROCEDURE SAPHANAABAP.DERIVE_PLANT( IN I_VIEW SAPHANAABAP.ZAL0004,

  OUT E_T_TABLE  SAPHANAABAP.ZAL0004 ) LANGUAGE SQLSCRIPT AS

BEGIN

    E_T_TABLE = select

      calmonth,

      calyear,

      currency,

      plant,

      plant AS "ZPLANT"

     from :I_VIEW;

END

Still get the error as mentioned above.

BR,

0 Kudos

Hi Andrey,

you can check whether you get something like

Condition 'PleConfig::getEnableSQLScriptCmd()'
failed.

Execution of SQLScript commands in PlanningEngine is
disabled!

in the HANA log files.

Besides

allow_non_readonly_sqlscript

there exists also the parameter

enable_sqlscript_cmd

Is the latter used/or set?

Regards,

Gregor

Former Member
0 Kudos

Hi Gregor,

I have no such an error PleConfig::getEnableSQLScriptCmd().

In addition I don't use enable_sqlscript_cmd. Should I use it?

Unexpectedly, now I have got another error in the same planning function:

System error in program CL_RSPLS_DELTA_BUFFER_B and form _CR_DERIVE-02-

System error in program CL_RSPLS_CR_CONTROLLER and form IF_RSPLS_CR_CONTROLLER~DERIVE_HDB-03-

PE error 38.001:  Condition 'theStructureInfo.existsAttr( lMappedName ) || theStructureInfo.existsKf( lMappedName )' failed. Att

Could you advise me ?

BR,

sheldon_piao
Employee
Employee
0 Kudos

Hi Andrey,

Is there no any key figure in the involved aggregation level ?

I found the designed table type but cannot see any key figure:

--------------------

CREATE TYPE SAPHANAABAP.ZAL0004 AS TABLE ( 

  CALMONTH NVARCHAR (000006),

  CALYEAR NVARCHAR (000004),

  CURRENCY NVARCHAR (000005),

  PLANT NVARCHAR (000008),

  ZPALNT NVARCHAR (000008))
--------------------

I suggest you use the following part of the report RSPLS_SQL_SCRIPT_TOOL to create the table type(do not change the result from it):

Then double confirm the result is ok or not.

Best regards,
Sheldon.

Former Member
0 Kudos

Hi Sheldon,

You don't see KF because I simplified my example and didn't provide here the entire definition.

Sure I have a KF and I do create a tabel type via RSPLS_SQL_SCRIPT_TOOL.

BR,

sheldon_piao
Employee
Employee
0 Kudos

Hi Andrey,

I have to say I need to debug the issue if it is possible to allow me to check it in the system directly.

Best Regards,
Sheldon.

Former Member
0 Kudos

Hi Sheldon,

It is possible.

Since I don't have your email could you contact me on andrey.uryukin@gmail.com ?

BR,

Andrey

sheldon_piao
Employee
Employee
0 Kudos

Hi Andrey,

Is it possible to create a oss incident so that we can communicate the issue in details ?
If it is a real bug, we can request developers to solve it.

Best Regards,
Sheldon.

Former Member
0 Kudos

Hi,

On one hand, this issue happens on SAP standalone Instance on AWS cloud where we do the tests before real installation of 7.4 on our servers.

On other hand, my OSS user is assosiated with another (real) system then the system that has an issue (AWS cloud instance).

Is that ok if I open the incedent for AWS cloud instance or it's impossible ?

BR,

sheldon_piao
Employee
Employee
0 Kudos

Hi,

I am not sure about it.
Maybe you can try to create the connection or request network team about it.

Best Regards,
Sheldon.

Former Member
0 Kudos

Hi Sheldon,

Thank you.

I will try.

Meanwhile, may be this log records will tell you something:

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

[14205]{400039}[66/-1] 2015-02-19 10:25:52.133695 f pleAPI           ple_sqlscript_cmd.cpp(00405) :

Condition 'theStructureInfo.existsAttr( lMappedName ) || theStructureInfo.existsKf( lMappedName )' failed.

Attribute 'CALMONTH' of table type 'SAPHANAABAP'.'ZAL0007' does not exist in session: '20150219102552_1037540'!

[14205]{400039}[-1/-1] 2015-02-19 10:25:52.133972 e pleCompile       ple_command_queue.cpp(00547) :

Condition 'theStructureInfo.existsAttr( lMappedName ) || theStructureInfo.existsKf( lMappedName )' failed.

Attribute 'CALMONTH' of table type 'SAPHANAABAP'.'ZAL0007' does not exist in session: '20150219102552_1037540'!

/HDB/IMP/NewDB100_REL/src/sys/src/planning_engine/ple_sqlscript_cmd.cpp(405)

[4774]{400039}[31/-1] 2015-02-19 10:25:53.327063 f pleAPI           ple_command_srv.cpp(00158) :

Condition 'aSession.is_valid()' failed.

Session '20150219102552_1037540' does not exist (anymore) during execution of command PleLookUp.

[4774]{400039}[31/-1] 2015-02-19 10:25:53.327213 e pleCompile       ple_command_srv.cpp(00191) : Invalidate the session '20150219102552_1037540' due to an exception.

Condition 'aSession.is_valid()' failed.

Session '20150219102552_1037540' does not exist (anymore) during execution of command PleLookUp.

/HDB/IMP/NewDB100_REL/src/sys/src/planning_engine/ple_command_srv.cpp(158)

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

BR,

sheldon_piao
Employee
Employee
0 Kudos

Hi,
The following means there is no CALMONTH.
Maybe you can debug it by yourself via script RSR_DEBUGGING_SCRIPT_STORE and then confirm whether it exists or not:


Attribute 'CALMONTH' of table type 'SAPHANAABAP'.'ZAL0007' does not exist in session: '20150219102552_1037540'!

Former Member
0 Kudos

Hi,

I find out that my characteristic relationship works in one scenario and doesn't in other.

1. Here it works - if I change the value of KF in BEX and press save then CR works fine and delta line comes to the cube with ZPLANT derived with sqlscript above.

2. Here it doesn't work - if I execute any sequence (for example copy from version A to B, and I expect all the records in B version to be with ZPLANT derived via CR).

I tried to debug via RSR_DEBUGGING_SCRIPT_STORE but seems like in scenario 2 the store is empty. It can explain the error:

"Attribute 'CALMONTH' of table type 'SAPHANAABAP'.'ZAL0007' does not exist in session: '20150219102552_1037540'!"

But why the store is empty in this case I didn't find out.

BR,

sheldon_piao
Employee
Employee
0 Kudos

Hi,

The issue is not easy to guess without access to the system.
You may create an incident and tell me the incident number so that I can take care of it.

Best regards,
Sheldon.

0 Kudos

Did you find a solution for the problem? I have got the same one here....

BR

PankajVPatil
Participant
0 Kudos

Hi Andrey,

I am getting the same set of errors (back to back) like yours. Can you please let me know what was the resolution ?

Thanks,

Pankaj