Persisting output from HANA View

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> ( )

       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:

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

{

"description": "Read stock value",

"action": "sample.test:job.xsjs::my_readStock",

"schedules": [

       {

"description": "Read current stock value",

"xscron": "* * * * 9 * 59",

"parameter": {

"stock": "SAP.DE"

}

       }

    ]

}

Source: scn.sap.com