Technical Articles
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. |
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.
- 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. - 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. - 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 parameteri_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. - 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.