Persisting output from HANA View
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:
Name your Stored Procedure and select target Schema:
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> ( )
SQL SECURITY INVOKER
DEFAULT SCHEMA <Selected Schema>
Write your procedure logic
DECLARE var_commit VARCHAR(100) := ‘COMMIT’;
DECLARE var_rollback VARCHAR(100) := ‘ROLLBACK’ ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
–ex_message := ‘Error Code ‘||::SQL_ERROR_CODE||::SQL_ERROR_MESSAGE;
delete from <table name>;
Insert into <table name>
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:
And name file – selecting basic Template, it will generate a basic xs job that you can use as starting point:
“description”: “Read stock value”,
“description”: “Read current stock value”,
“xscron“: “* * * * 9 * 59”,
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.