SAP BLOG HANA SDI SOAP integration into SAP BW Process chains

SAP Blog

Kayıtlı Üye
Katılım
22 Ara 2017
Mesajlar
1,852
Tepki puanı
7
Puanları
6

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%20Fowgraph%20in%20IDE


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%20table%20properties


generated table properties​

This type of data loads normally will be scheduled on HANA cockpit or HANA Launch Monitoring Console

Lauch%20Monitor%20IDE


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.



Kod:
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;

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.

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.



Kod:
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%20CLASS


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

SAP Help Portal



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%20Method


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%20manual%20start


Report manual start​

Kod:
*&---------------------------------------------------------------------*
*& 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

MESSAGE - ABAP Keyword Documentation



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.

Log%20messages%20tunnel%20through


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

Bild8-5.png




Data has been loaded via ABAP manually

Process%20Chain
Message%20in%20PC


Message in Process Chain​

Or via Process chain

Result%20in%20ECLIPSE


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.

Okumaya devam et...
 
Üst