Skip to Content

This blog is result of a couple of questions I posted here on SCN and I wanted to share my findings. Background is based on a problem we faced with a HANA calculation view that was not performing as we’d like to see it and there didn’t seem ways around performance issue. It was not volatile data in terms of changes so we decided best option would be to store the data on a load schedule in our HANA system. Question was then how to do that in HANA. As a background, I can say that we are on SPS09.

Process that we went with was to create a custom table that would be filled with stored procedure that would be scheduled to run a load frequency through xs admin. I’ll share the steps.

Creating the custom table would be based on the definitions that we had in HANA view. You can manually create the table in the schema that you’d like to store it, but alternative would be to run following script in SQL console:

create column table <schema>.<table name> as (select * from “_SYS_BIC”.<package>/<HANA view>)

This generates table based on how HANA view is defined – names, datatypes and lengths etc.

Creating a stored procedure is done by going to HANA Development perspective and going to Repositories. Navigate to the package that you’d like to store HANA Stored Procedure in. Right click package and click New and then Other to go to Wizard. Search for Stored Procedure and click Next:

/wp-content/uploads/2015/07/storedprocedure_747556.png

Name your Stored Procedure and select target Schema:

/wp-content/uploads/2015/07/storedprocedure1_747560.png

It will generate parts of code based on what you have selected. Basic logic that we put into procedure was:

PROCEDURE “_SYS_BIC”.“<package>::<stored procedure> ( )

       LANGUAGE SQLSCRIPT

       SQL SECURITY INVOKER

       DEFAULT SCHEMA <Selected Schema>

       AS

BEGIN

/*****************************

       Write your procedure logic

*****************************/

DECLARE var_commit  VARCHAR(100) := ‘COMMIT’;

DECLARE var_rollback VARCHAR(100) := ‘ROLLBACK’ ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

       exec (:var_rollback);

       –ex_message := ‘Error Code ‘||::SQL_ERROR_CODE||::SQL_ERROR_MESSAGE;

END;

delete from <table name>;

Insert into <table name>

(“Field_1”,

“Field_2”,

“Field_3”)

SELECT

(“Field_1”,

“Field_2”,

“Field_3”)

FROM

_SYS_BIC.<package>/<hana view>;

exec (:var_commit);

END;

This stored procedure can then be activated and scheduled using an xsjob file from xs admin console. To create an XS Job file, you can go through wizard in HANA Development perspective and select XS Job Scheduler File:

/wp-content/uploads/2015/07/xsjob1_747561.png

And name file – selecting basic Template, it will generate a basic xs job that you can use as starting point:

/wp-content/uploads/2015/07/xsjob2_747562.png

{

“description”: “Read stock value”,

“action”: “sample.test:job.xsjs::my_readStock”,

“schedules”: [

       {

“description”: “Read current stock value”,

xscron“: “* * * * 9 * 59”,

“parameter”: {

“stock”: “SAP.DE”

}

       }

    ]

}

Make the necessary changes to accommodate for your location of stored procedure and xscron parameters. More details on xs job options on SAP Help: http://help.sap.com/saphelp_hanaplatform/helpdata/en/15/e72de0ed4a4ce1aa1ad7483dbe6b37/frameset.htm

To maintain scheduling, you can go through steps 5-7 on SAP Help: http://help.sap.com/saphelp_hanaplatform/helpdata/en/62/15446213334d9fa96c662c18fb66f7/content.htm

The data from calculation view should now be persisted in HANA Custom Table. This custom table based on hana view can now be used in other HANA views.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply