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
You would like to show the planned FTE per Cost Center. You would like to include a distinction per employee joining the company, leaving, and moving the Cost Centers. You would like to refresh the data if the change is made retroactively.
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.
You would like to read the data from a Workday report using CPI, REST, and passing different parameters for each request. From CPI, you call a BW Function Module to receive the parameters for the request. You receive multiple sets of parameters to run the Workday report with.
Then, based on the Workday connection, you call Workday report as many times as there are parameters received from BW. You make the requests synchronously, transform them to ABAP input structure via a Groovy script, union them together, and post to BW.
BW4HANA setup
ETL requirement
The data from Workday has to be passed for all the months for which we expect deltas in one request. 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
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:
The following APIs are available 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 requestsc |
The following APIs are available 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.
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_RSDSO_WRITE_API_LEAVER_HIRE
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_RSDSO_WRITE_API_LEAVER_HIRE
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_RSDSO_WRITE_API_LEAVER_HIRE
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_RSDSO_WRITE_API_LEAVER_HIRE
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:
<Z_RSDSO_WRITE_API_LEAVER_HIRE>
<INPUT>
<IT_DATA>
<item>
<key_field>value</key_field>
<RECORDMODE></RECORDMODE>
<field>value</field>
</item>
</IT_DATA>
</Z_RSDSO_WRITE_API_LEAVER_HIRE>
Result
The data acquired by CPI or any other ABAP-based application can be easily pasted into BW ADSO and processed further.
No comments:
Post a Comment