Calling HANA Stored Procedure from Abap class and scheduling it through Process chain (using AMDP – Abap managed database procedure)

This blog will explain below –

1. Creation of Repository Stored Procedure
2. Calling Stored Procedure through Abap class
3. Calling Abap class in Abap program and scheduling through process chain

The environment here is BW on HANA. The data modelling was done in SAP HANA view and there was a need to read the output of the view and persist it in the HANA table. Stored Procedure was written to read the data from HANA view and insert the data into HANA table.
Scheduling was required to be done from SAP BW Process chain to keep the daily loads monitoring simple and integrated at one place.

The Steps performed have been explained below –
​
1. Repository Stored Procedure ‘DATA_PERSIST’ is created under TEST Package through HANA development perspective. This stored procedure reads the data from HANA view and inserts the data in a table which is under “XYZ” schema.
It is preferred to create the Stored Procedure in _SYS_BIC schema. In the procedure, the default schema can be given. Here, the default schema given is “XYZ” as Table1 and Table2 are created under this schema.

There is an input parameter which is created on Calmonth. Here, the calmonth value will be passed and then HANA view results will be calculated based on this.

This is read write procedure as can be seen in below script. The read only statement has been removed.

In the first part of the procedure, ‘STARTIME’ variable is used to hold the current timestamp value in ‘YYYYMMDD HH24:MI:SS’ format. The second variable is created to hold the value ‘COMMIT’.

Note – AMDP(Abap managed database procedure) does not support statement such as Commit, rollback. Hence, the same is implemented through a work around.

In the second part, the data is inserted in table1. Table1 is used for logging and tracking the run date of the procedure, time taken by procedure to run.

Later, we are reading the data from HANA view ABC_CV based on the calmonth which will be passed to stored procedure. (This will be shown in Abap class in Step2). The result is inserted in the Table2. Table1 and Table2 are under XYZ schema.

At the end of the stored procedure, Table1 (logging table) is updated with the end timestamp of the procedure. This helps to keep a tracking of each run of the stored procedure.

2. In this step, an Abap class is created to call the stored procedure. This has been created in HANA studio through the Abap perspective.

Under the class Method “Datapersist”, cursor C_DATE is declared which holds the last 15 months calmonth. The requirement here is to store only last 15 months data. This data is dropped on daily basis and 15 months data is loaded daily. There is no delta/change recorded mechanism implemented.

Another Stored procedure “_SYS_BIC”.”TEST::DATA_TRUNCATE” is called which deletes the data from Table2. This drops the data from table2.

Using the For loop, calmonth is passed one by one to the stored procedure. So, using this the stored procedure is called 15 times, by passing calmonth one by one. This is done as the HANA view is very complex and generates very high volume of data. While running, HANA view may consume high memory and CPU so the run is split based on each month instead of running for 15 months at the same time.

Once the data is loaded, another stored procedure, “_SYS_BIC”.”TEST::DATA_MERGE” is called. This is to merge delta of Table2, to move the data from delta storage to the main storage in HANA as recommended by SAP.

Below, is the code written in Abap class. In Abap class, the stored procedure can be either called or the whole code can be written in Abap class.

3. An Abap Program Z_Data_daily is developed under BW developer perspective, to call the Abap class/method as shown below. Now this, Abap program can be schedule to run through process chain in SAP BW.