How to run native SAP HANA objects (e.g. procedures) in SAP-BW Process Chains
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.
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
This data should be loaded into BW (ADSO) in further processing.
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).
Klicke hier, um Ihren eigenen Text einzufügen
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 approach for demonstration purpose in my Stored Procedure (SP) as first step.
This type of data loads would normally be scheduled using the HANA cockpit or HANA Launch Monitoring Console:
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 --BEGIN AUTONOMOUS TRANSACTION could be used to trigger asynchronous mode -- 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; END
Additionally, 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.
Now we are set from a 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. 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.
CLASS-METHODS GET_BIA_HANA_DATA IMPORTING VALUE(IN_DEL) TYPE rs_bool VALUE(IN_SOURCE) TYPE CHAR10 EXPORTING VALUE(OUT_STR) TYPE string.
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.
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 erros=> Send Info Back to ABAP SELECT "Info" INTO OUT_STR FROM :lt_error; END;
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).
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 YBIA_LOAD_HANA *&---------------------------------------------------------------------* *& Copyright BIANALYST GmbH & Co. KG / Joerg Boeke *& No distribution or usage of without Copyright signature *&---------------------------------------------------------------------* 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 see SAP help
My example is the very easy way, but it is working as expected
Due to the missing authorization, I got receive 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.
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, or via aProcess chain
Data in the HANA table is loaded using a Process Chain.
As you can see, we at BIAnalyst www.bianalyst.de live our slogan “Nothing is impossible” and with more than 20 years experience in BW and more than 10 years experience in HANA development.
We can solve your “impossible” problem, too.
Do not hesitate to contact me at firstname.lastname@example.org