How to run native SAP HANA objects (e.g., procedures) in SAP BW Process Chains (PC)
Have you ever considered running HANA artifacts such as HANA Flow Graphs, Procedures etc. direct in BW Process Chains?
Instead of using the BW scheduling and HANA scheduling tools separately and trying to get things synchronized between the systems. It would be possible to do all your scheduling using just BW processed chains and running HANA objects from there. This would enable a single point of entry for all your monitoring and scheduling requirements.
In this Blog, I will show you how to setup the whole process.
This process, based on a HANA SDI SOAP implementation, reads web-based data into a HANA table using a HANA flow graph.
Step 1
I created a HANA flow graph reading virtual SDI function as a data source (HANA virtual function), transforming the incoming data and inserting the date into the desired HANA template table
HANA Flowgraph in IDE
This data should be loaded into BW (ADSO) in further processing.
NOTE:
If you set the Write Type of tables in HANA Flow graph to “Insert”, the table will be cleared automatically, and you won’t have to delete the data manually (if desired).
If you don’t change this setting, all loads will be stored in the table with an additional task-ID (internal key) and you may need to delete it manually (I have this deletion approach, for demonstration purpose, in my Stored Procedure (SP) as first step.
generated table properties
This type of data loads normally will be scheduled on HANA cockpit or HANA Launch Monitoring Console
Launch Monitor in IDE
Step 2
We would however prefer to process this HANA artefact in an ABAP step in our BW process chain(s). For this purpose we create aHANA procedure that can be called via SAP ABAP class.
PROCEDURE "BIANALYST"."BIA_DEMO.HANA::BIA_PROC_BY_ABAP_PC" ( IN DEL_TAB nvarchar(1), IN SOURCE NVARCHAR (10) )
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
DEFAULT SCHEMA "BIANALYST"
AS
BEGIN
-- Use such approach to call and do whatever you like in HANA
--This Procedure will be called by SAP-BW Process Chain ABAP Report
if DEL_TAB = 'X' THEN
if source = 'TRFN' then
--drop all data in TRFN table (HANA table) that will be uploaded by Process Chain in ABAP BW but HANA functionality:)
delete from BIANALYST.BIA_TRFN_DATA;
call "BIANALYST"."BIA_DEMO.HANA::BIA_LOAD_TRFN_DATA";
ELSEIF source = 'QUERY' then
ELSEIF source = 'ABAP' then
ELSEIF source = 'HANA' then
ELSEIF source = 'AMDP' then
ELSEIF source = 'INFOPROV' then
END IF ;
END IF;
ENDAdditionally, all flow graphs are generating Stored Procedures (SP) which can also call directly from ABAP classes.
In our case, I created a procedure to combine several loads in a single Stored Procedure with all its tasks to have less objects and use parameters to trigger the individual load.
My procedure therefore has Input parameters to set up the specific task from the outside world.
Step 3
Now we are set from HANA point of view.
What is missing is a way to start the procedure by ABAP report. This can be achieved by defining an ABAP class using a HANA method.
CLASS zcl_bia_abap_call_hana DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
class-METHODS GET_BIA_HANA_DATA
importing value(IN_DEL) type rs_bool
value(IN_SOURCE) type CHAR10
exporting value(OUT_STR) type string.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_bia_abap_call_hana IMPLEMENTATION.
"ABAP Class using HANA mehtod to close the gap between ABAP world and HANA world
METHOD GET_BIA_HANA_DATA by database procedure
for hdb language sqlscript.
DECLARE LV_INFO NVARCHAR(100);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
lt_error = SELECT ::SQL_ERROR_CODE as "SQL Fehler",
::SQL_ERROR_MESSAGE as "Info" FROM PUBLIC.DUMMY;
--In case of errors=> Send Info back to ABAP
select "Info" INTO OUT_STR from :lt_error ;
END;
--CAll the HANA Procedure / Flowgraphs or whatsoever to fullfill its HANA tasks :)
call "BIANALYST"."BIA_DEMO.HANA::BIA_PROC_BY_ABAP_PC"(:IN_DEL,:IN_SOURCE ) ;
ENDMETHOD.
My Class is using the two-input parameter, which are being used in my stored procedure in HANA as well.
ABAP CLASS
Additionally, we have a third parameter OUT_STR we will use to return any problems occurring from the HANA point of view (e.g., SQL errors, Authorization problems etc.).
The next step is optional, but I think that it is valuable for your monitoring to spot and evaluate possible errors.
I am using the simple variant by passing a string only to the process chain.
For more details, how to define specific SQL exit handlers, see SAP Online help
To receive such errors, we have to declare an error message handler as shown below and past e.g., the Info back to the ABAP calling class.
Class Method
As you can see in upper code example, my error handler is looking for SQL exceptions and storing (if occurred) thus values in internal table “lt_error”.
In my example, I am just returning the error message I called “Info” to my output parameter as string.
This is just a simple way to evaluate on ABAP side, whether an error occurred (string is empty, or not)
Step 4
We can now complete our process chain by including an ABAP step to run the report. The ABAP step triggers the HANA object and evaluates any possible SQL problems that may occur.
Report manual start
*&---------------------------------------------------------------------*
*& Report YBIA_LOAD_HANA
*&---------------------------------------------------------------------*
*& BIANALYST GmbH & Co. KG / Joerg Boeke
*&
*&---------------------------------------------------------------------*
REPORT ybia_load_hana.
DATA:
lc_bia_load TYPE REF TO zcl_bia_abap_call_hana,
ls_hana_info TYPE string.
SELECTION-SCREEN BEGIN OF BLOCK bia WITH FRAME TITLE text1.
"Load data via HANA Procedure in DB instead App-Server
SELECTION-SCREEN COMMENT:
/20(75) t1 MODIF ID c01,
/20(75) t2 MODIF ID c02,
/20(75) t3 MODIF ID c03,
/20(75) t4 MODIF ID c04,
/20(75) t5 MODIF ID c05,
/20(75) t6 MODIF ID c06,
/20(75) t7 MODIF ID c07.
SELECTION-SCREEN SKIP.
PARAMETERS:
p_del TYPE rs_bool DEFAULT 'X', " delete HANA table content
p_sour TYPE char10. " Load specific source
SELECTION-SCREEN END OF BLOCK bia .
INITIALIZATION.
%_p_del_%_app_%-text = 'Delete HANA table content (X)'.
%_p_sour_%_app_%-text = 'Load defined Source'.
text1 = 'BIAnalyst HANA execution via ABAP'.
t1 = 'BIANALYST GmbH & CO. KG Selection:'.
t2 = '1. "TRFN" Load Transformation TCT data'.
t3 = '2. "QUERY" Load Transformation Query data'.
t4 = '3. "ABAP" Load Transformation ABAP data'.
t5 = '4. "HANA" Load Transformation HANA data'.
t6 = '5. "AMDP" Load Transformation AMDP data'.
t7 = '6. "INFOPROV" Load Transformation Meta data'.
******************************************************
**************Start Main Program**********************
******************************************************
START-OF-SELECTION.
CREATE OBJECT lc_bia_load .
"Step 1:
"Start HANA Workflow or Procedure or functionality
" and see if everything is fine or in case of errors follow step 2
CALL METHOD lc_bia_load->get_bia_hana_data
EXPORTING
in_del = p_del
in_source = p_sour
IMPORTING
out_str = ls_hana_info.
"Step 2:
" Evaluate the return Param and raise a return status Green or Red
IF strlen( ls_hana_info ) = 0.
"GREEN Returncode /Message
MESSAGE 'Alles OK' TYPE 'S' DISPLAY LIKE 'S'.
ELSE.
"RED Returncode /Message
"MESSAGE e000(sy-msgno).
MESSAGE ls_hana_info TYPE 'E' DISPLAY LIKE 'E'.
ENDIF.As you can see in ABAP source code, in step 1 we pass the PARAM’s (manual or by help of variants in PC) to the HANA Method call. In step 2 we simply evaluate if an error occurred.
As you can see here, I used the simple approach of generating a message. If you like you might create a message class of your own.
More information via SAP help
https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapmessage.htm
My example is the very easy way, but it is working as expected
Due to the missing authorization, I got an SQL Exit event in HANA that was bubbled back up to the process chain which triggered as expected an error and the status changed to red.
The message itself is being sent, using my output parameter, to the Error message and can be displayed in load / failure status as shown below.
HANA log messages will tunnel through BW
Step 5
The final step is to implement the HANA call e.g., to load data from Web (SOAP) via SDI Flowgraph into a HANA table, before a DTP process reads that data into an ADSO.
As you can see in my example, I forced the HANA object to trigger an SQL event (not having sufficient authorization to delete data from HANA table) that is turning the load to red condition
Data has been loaded via ABAP manually
Message in Process Chain
Or via Process chain
Result in ECLIPSE
As you can see in the upper figure, data in the SAP HANA table is loaded using a BW Process Chain.
As mentioned in the beginning, it’s up to you whether you like to monitor the HANA executions in HANA Cockpit or combine it with regular BW loads.
Don’t get me wrong. I don’t like to use process chains for all regular DB-driven tasks, but in combination with BW loads, it is a nice to have feature.
My little example should demonstrate that the “classic” BW loads can be enhanced with HANA options.
Whenever possible, I try to optimize the BW loads by shifting workload from application server (ABAP) to the HANA database.
Hope you like this article and you might have a similar need where you can implement it.
