Skip to Content
Technical Articles
Author's profile photo Sebastian Gesiarz

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

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/871ca871e84a44cf82e48f2099208574.html

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/72e16c936fb94cffb71ce90edd5f8f8e.html

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.

 

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.