Posting the data to BW4/HANA aDSO using BW APIs and RFC connection

[Sebastian_Gesiarz]

Problem introduction

As a developer, you would like to expose a service that enables third-party APIs to post the data directly into SAP BW ADSO.

For that reason, you would like to use an RFC enabled BW function module that allows the external source like CPI to read BW parameters, request the data from an outside source through REST, harmonize it and post the data to BW ADSO in a columnar format.

Use-case description

Business

The requirement is to show the planned FTE per Cost Center. A distinction should be made as per the employee joining the company, leaving, and moving the Cost Centers.

Technical

A sample below is to acquire the data from a time-dependent Workday report with DateFrom and DateTo parameters for multiple periods at once and to post the result to BW.

The data from the Workday report is read using CPI, REST, and passing different parameters for each request. CPI calls a BW Function Module to receive the parameters for the request.

Then, based on the Workday connection, CPI calls Workday report as many times as there are sets of parameters.

CPI makes requests synchronously, it transforms the result to ABAP input structure via a Groovy script. Next, it unions it together, and post to BW.

BW4HANA setup

ETL requirement

The data from Workday has to be passed for all the months for which the deltas are expected. It has to be provided in one request as a snapshot enabled aDSO is used.

The Workday report is time-dependent; the FTE data represents employees joining the company, leaving, and shifting Cost Centers for each month.

Setup

CPI calls BW FM through an RFC connection set up in the Cloud Connector. It receives the parameters. CPI calls the BW function module to post the collected data back. Function Module writes the data to an ADSO and runs the process chain to process the data from acquisition to propagation layer.

ADSOs

BW ADSOs with snapshot support enabled are used for the data acquisition layer. It ensures that time-dependent full loads per period are correctly refreshed, and removed transactions no longer exist.

A standard BW ADSO with changelog enabled is used for the propagation layer. It stores the history for the no longer refreshed periods.

Period and employee ID are used as table keys. If a user is present at two Cost Centers at once within the same period because he shifts, then a new record is created on the fly using a table function. A derivation of the status to indicate if an employee changed cost center is made by comparing previous and current Cost Center. SQL solution reference: https://stackoverflow.com/questions/67070499/sql-lag-distinct-to-the-previous-different-value

ADSO settings Documentation: https://help.sap.com/viewer/107a6e8a38b74ede94c833ca3b7b6f51/2021.00/en-US/871ca871e84a44cf82e48f209...

DTPs

A DTP from the first to the second layer processes the data only for the periods refreshed by the source and needed for the transformation logic. The selection can happen in the DTP routine or the BW user-exit variables.

Transformation

Additional logic is added to decide which employees changed the Cost Center retroactively and post those changes in the current month instead of the past.

Function Modules

Now, back to the main topic of the blog. SAP offers the following APIs to post the data back to BW.

For a standard DataStore object with the property Write Change Log:

Type	Target Table	API for Local Use	API for RFC Use	Description
Load	Inbound Table	RSDSO_WRITE_API	RSDSO_WRITE_API_RFC	Loads data from an internal table to the inbound table. The activation can then be started.
Activate	Active Data	RSDSO_ACTIVATE_REQ_API_RFC	RSDSO_ACTIVATE_REQ_API_RFC	Activates loaded requests

For a DataStore object for direct updates (Direct Update DataStore object):

Type	Target Table	API for Local Use	API for RFC Use	Description
Load	Active Data	RSDSO_DU_WRITE_API	RSDSO_DU_WRITE_API_RFC	Loads data from an internal table into the active data table.
Delete	Active Data	RSDSO_DU_DELETE_API_RFC	RSDSO_DU_DELETE_API_RFC	Deletes data from the table of active data. The table of active data can be truncated or selectively deleted.
Status	Active Data	RSDSO_DU_CLEANUP_API_RFC	RSDSO_DU_CLEANUP_API_RFC	Deletes API requests with errors. Red requests block further load requests by DTP or by API.

Source: https://help.sap.com/viewer/107a6e8a38b74ede94c833ca3b7b6f51/2021.00/en-US/72e16c936fb94cffb71ce90e...

The selection is made based on the BW requirements. For example, the acquisition layer ADSO is not of the direct-update type, and we would like to write the data to ADSO inbound table using a wrapper RFC enabled FM. We expect the data to be already in the columnar format and not a string. We would like to validate this data upon activation in BW. It also allows easy debugging of incoming RFC requests.

This brings us down to the option for the function module RSDSO_WRITE_API.

1. Create a wrapper Function Module.
   It needs to throw errors like the API provided by SAP and add errors if a process chain has failed.
   It needs to have an input structure of the active Table of the target ADSO. Please note this table has a record mode field after the key specification. Therefore, it should always be posted as empty.
   The function module needs to pass multiple parameters to CPI.
   

   FUNCTION Z_FM_NAME
   
     IMPORTING
   
       VALUE(IT_DATA) TYPE ZTY_<ADSONAME>1 OPTIONAL
   
     EXPORTING
   
       VALUE(ET_PARAMS) TYPE ZTT_<2columns_table_type>
   
       VALUE(E_LINES_INSERTED) TYPE INT4
   
       VALUE(E_COLD_LINES_INSERTED) TYPE INT4
   
       VALUE(ET_MSG) TYPE RS_T_MSG
   
       VALUE(E_UPD_REQ_TSN) TYPE RSPM_REQUEST_TSN
   
       VALUE(ET_ACT_REQ_TSN) TYPE RSDSO_T_TSN
   
     EXCEPTIONS
   
       WRITE_FAILED
   
       ACTIVATION_FAILED
   
       DATASTORE_NOT_FOUND
   
       CHAIN_FAILED.
   
   
   
   <Content>
   
   
   
   ENDFUNCTION.​

   
   


2. Add a check if the parameters are filled and if the CPI request has data.
   

   FUNCTION Z_FM_NAME
   
     IMPORTING
   
       VALUE(IT_DATA) TYPE ZTY_DH_AD281 OPTIONAL
   
     EXPORTING
   
       VALUE(ET_PARAMS) TYPE ZTY_CHAR10_2COL
   
       VALUE(E_LINES_INSERTED) TYPE INT4
   
       VALUE(E_COLD_LINES_INSERTED) TYPE INT4
   
       VALUE(ET_MSG) TYPE RS_T_MSG
   
       VALUE(E_UPD_REQ_TSN) TYPE RSPM_REQUEST_TSN
   
       VALUE(ET_ACT_REQ_TSN) TYPE RSDSO_T_TSN
   
     EXCEPTIONS
   
       WRITE_FAILED
   
       ACTIVATION_FAILED
   
       DATASTORE_NOT_FOUND
   
       CHAIN_FAILED.
   
   
   
   
   
   
   
     CONSTANTS: lc_adso_open_pos TYPE rsoadsonm VALUE '<ADSO_NAME>',
   
                lc_pc_id         TYPE rspc_chain VALUE '<PC_NAME>'.
   
   
   
   
   
       SELECT
   
          start_date,
   
          end_date
   
       FROM
   
         <parameters_table>
   
       INTO TABLE
   
         @et_params.
   
   
   
   
   
     IF it_data[] IS NOT INITIAL AND et_params[] IS NOT INITIAL.
   
   
   
      
   
   
   
     ENDIF.
   
   
   
   ENDFUNCTION.​

   
   


3. Add executions of the RSDSO_WRITE_API.
   It enables the new master data values (sid generation) specification via a parameter i_allow_new_sids.
   In addition, it allows specification of whether the data is activated after posting via a parameter
   
   i_activate_data.
   It returns a full activation log and the number of inserted & activated lines.
   
   

   FUNCTION Z_FM_NAME
   
     IMPORTING
   
       VALUE(IT_DATA) TYPE ZTY_<ADSO_NAME> OPTIONAL
   
     EXPORTING
   
       VALUE(ET_PARAMS) TYPE ZTY_CHAR10_2COL
   
       VALUE(E_LINES_INSERTED) TYPE INT4
   
       VALUE(E_COLD_LINES_INSERTED) TYPE INT4
   
       VALUE(ET_MSG) TYPE RS_T_MSG
   
       VALUE(E_UPD_REQ_TSN) TYPE RSPM_REQUEST_TSN
   
       VALUE(ET_ACT_REQ_TSN) TYPE RSDSO_T_TSN
   
     EXCEPTIONS
   
       WRITE_FAILED
   
       ACTIVATION_FAILED
   
       DATASTORE_NOT_FOUND
   
       CHAIN_FAILED.
   
   
   
   
   
   
   
     CONSTANTS: lc_adso_open_pos TYPE rsoadsonm VALUE '<ADSO_NAME>',
   
                lc_pc_id         TYPE rspc_chain VALUE '<PC_NAME>'.
   
   
   
   
   
       SELECT
   
          start_date,
   
          end_date
   
       FROM
   
         <parameters_table>
   
       INTO TABLE
   
         @et_params.
   
   
   
   
   
     IF it_data[] IS NOT INITIAL AND et_params[] IS NOT INITIAL.
   
   
   
       CALL FUNCTION 'RSDSO_WRITE_API'
   
         EXPORTING
   
           i_adsonm              = lc_adso_open_pos
   
           i_allow_new_sids      = rs_c_true
   
           i_activate_data       = rs_c_true
   
           it_data               = it_data
   
         IMPORTING
   
           e_lines_inserted      = e_lines_inserted
   
           e_cold_lines_inserted = e_cold_lines_inserted
   
           et_msg                = et_msg
   
           e_upd_req_tsn         = e_upd_req_tsn
   
           et_act_req_tsn        = et_act_req_tsn
   
         EXCEPTIONS
   
           write_failed          = 1
   
           activation_failed     = 2
   
           datastore_not_found   = 3
   
           OTHERS                = 4.
   
       CASE sy-subrc.
   
   
   
   
   
   ENDFUNCTION.

   
   


4. Add error handling and a process chain execution; below is a basic example.
   

   FUNCTION Z_FM_NAME
   
     IMPORTING
   
       VALUE(IT_DATA) TYPE ZTY_<ADSO_NAME> OPTIONAL
   
     EXPORTING
   
       VALUE(ET_PARAMS) TYPE ZTY_CHAR10_2COL
   
       VALUE(E_LINES_INSERTED) TYPE INT4
   
       VALUE(E_COLD_LINES_INSERTED) TYPE INT4
   
       VALUE(ET_MSG) TYPE RS_T_MSG
   
       VALUE(E_UPD_REQ_TSN) TYPE RSPM_REQUEST_TSN
   
       VALUE(ET_ACT_REQ_TSN) TYPE RSDSO_T_TSN
   
     EXCEPTIONS
   
       WRITE_FAILED
   
       ACTIVATION_FAILED
   
       DATASTORE_NOT_FOUND
   
       CHAIN_FAILED.
   
   
   
   
   
   
   
     CONSTANTS: lc_adso_open_pos TYPE rsoadsonm VALUE '<ADSO_NAME>',
   
                lc_pc_id         TYPE rspc_chain VALUE '<PC_NAME>'.
   
   
   
   
   
       SELECT
   
          start_date,
   
          end_date
   
       FROM
   
         <parameters_table>
   
       INTO TABLE
   
         @et_params.
   
   
   
   
   
     IF it_data[] IS NOT INITIAL AND et_params[] IS NOT INITIAL.
   
   
   
       CALL FUNCTION 'RSDSO_WRITE_API'
   
         EXPORTING
   
           i_adsonm              = lc_adso_open_pos
   
           i_allow_new_sids      = rs_c_true
   
           i_activate_data       = rs_c_true
   
           it_data               = it_data
   
         IMPORTING
   
           e_lines_inserted      = e_lines_inserted
   
           e_cold_lines_inserted = e_cold_lines_inserted
   
           et_msg                = et_msg
   
           e_upd_req_tsn         = e_upd_req_tsn
   
           et_act_req_tsn        = et_act_req_tsn
   
         EXCEPTIONS
   
           write_failed          = 1
   
           activation_failed     = 2
   
           datastore_not_found   = 3
   
           OTHERS                = 4.
   
       CASE sy-subrc.
   
         WHEN 0.
   
   
   
           CALL FUNCTION 'RSPC_API_CHAIN_START'
   
             EXPORTING
   
               i_chain = lc_pc_id.
   
   
   
           IF sy-subrc <> 0.
   
             MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING chain_failed.
   
           ENDIF.
   
   
   
         WHEN 1.
   
           MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING write_failed.
   
         WHEN 2.
   
           MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING activation_failed.
   
         WHEN 3.
   
           MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING datastore_not_found.
   
       ENDCASE.
   
   
   
     ENDIF.
   
   
   
   ENDFUNCTION.​

   
   In this example, the CPI request should have the following structure:
   

   <FM_NAME>
   
       <INPUT>
   
           <IT_DATA>
   
               <item>
   
                   <key_field>value</key_field>
   
                   <RECORDMODE></RECORDMODE>
   
                   <field>value</field>
   
               </item>
   
           </IT_DATA>
   
   </FM_NAME>
   
   

   
   

Result

The data acquired by CPI or any other ABAP-based application can be easily pasted as a new request into BW ADSO and processed further by a process chain.