Pushing Data to external DB table and Executing Procedure from SAP BW

Today I am going to discuss about how to push the data to external DB tables from BW  and executing external DB procedure Procedure from BW. External DB we have used is a non HANA DB MS SQL Server.

We have  an requirement scenario where MM and Inventory Management characteristic and Key figures need to be push to third party DB which is  SQL Server for further calculation and process.

Below steps has been implemented –

1. To Load Data use OPENHUB. It will create a tables and load the data from desired source BW object like DSO .

• Create a user with owner privilege in the database. Note: The name of the user should be the name of background used of the process chain where we are going to automate the Data push process. in Our case it is BWREMOTELAP. That can be vary for user.
• Please make sure the following authorization is added to the below role   –

• Using the user BWREMOTELAP created a source system using DB connect by the following syntax as connection parameter  –

MSSQL_SERVER=DB server name    MSSQL_DBNAME=DB name

•    We need to transfer MM and Inventory Management characteristic and Key figures data which is inside a DSO ZSD_TEST.
• Create a Open Hub ZOH_TEST—> Create Transformation from DSO ZSD_TEST.(that can be vary base on your BW object) —> Create DTP.
• When you create the Open Hub and activated it then the table in the external DB will be created and when you run the DTP then the data transfer from the BW to external DB table.

• create a process chain(ZPC_EXT_TRNFS)  and put the Open HUB DTP process in the process chain. Execute it.

1. In our scenario we need to execute some calculations on top of the external table which we crate and loaded into in first step i.e. on Top of the table  OBZOH_TEST. We create procedure on the external DB SQL sever and called the procedure via ABAP program.

• Create two procedure  in the external DB  –   PLB_TEST.DBO.SP_INSERT_EMPLYEE_DATA which is without parameter and another one is PLB_TEST.DBO.SP_INSERT_EMPLYEE_HIERARCHY(p_ymfmdate) which is with parameter to do the required operations.
• Create a ABAP executable program using TCODE SE38 and the following code –

 Code attached below –

*&---------------------------------------------------------------------*
*& Report ZBW_TEST
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZBW_TEST.

DATA: p_ymfmdate    TYPE i,
      lv_temp(8) TYPE c,
      p_out      TYPE i,
      lv_date type sy-datum,
      conn       TYPE dbcon_name,
      conn2      TYPE dbcon_name.
DATA itab TYPE STANDARD TABLE OF dbcon.
*p_ymfmdate = sy-datum.

*p_ymfmdate = sy-datum.
lv_date = sy-datum - 1.
lv_temp = lv_date.   "sy-datum.
p_ymfmdate = lv_temp.
p_out = 0.

START-OF-SELECTION.

  conn = 'CON1'.
*--Open the Database connection
  EXEC SQL.
    connect to :conn
  ENDEXEC.
  IF sy-subrc = 0.
*  BREAK-POINT.
  ELSE.
    MESSAGE 'Error in Opening the connection' TYPE 'E'.
  ENDIF.

*--Call the first Procedure
  EXEC SQL.
    EXECUTE  PLB_TEST.DBO.SP_INSERT_EMPLYEE_DATA
  ENDEXEC.
  IF sy-subrc = 0.
*    BREAK-POINT.
  ELSE.
    MESSAGE 'Error in executing the Procedure' TYPE 'E'.
  ENDIF.
*--Close the Database connection
  EXEC SQL.
    disconnect :conn
  ENDEXEC.
  IF sy-subrc = 0.
*  BREAK-POINT.
  ELSE.
    MESSAGE 'Error in closing the connection' TYPE 'E'.
  ENDIF.
  conn2 = 'CON2'.
*--Open the Database connection
  EXEC SQL.
    connect to :conn2
  ENDEXEC.
  IF sy-subrc = 0.
*  BREAK-POINT.
  ELSE.
    MESSAGE 'Error in Opening the connection' TYPE 'E'.
  ENDIF.
*--Call the Second Procedure
  EXEC SQL.
    EXECUTE  PLB_TEST.DBO.SP_INSERT_EMPLYEE_HIERARCHY(p_ymfmdate)
  ENDEXEC.
  IF sy-subrc = 0.
*    BREAK-POINT.
  ELSE.
    MESSAGE 'Error in executing the Procedure' TYPE 'E'.
  ENDIF.
*--Close the Database connection
  EXEC SQL.
    disconnect :conn2
 ENDEXEC.
  IF sy-subrc = 0.
*  BREAK-POINT.
  ELSE.
    MESSAGE 'Error in closing the connection' TYPE 'E'.
  ENDIF.

• Add in the created process chain(ZPC_EXT_TRNFS) ) after the step 1 which added previously to insert data into table.
• Execute the process chain and schedule it.

Hope this Topic will help others.