How to Extract Data from a Web API into BW/4HANA
This blog provides a step-by-step guide on how to extract data from a cloud-based application into SAP BW/4HANA system.
Challenges and objectives
The BI & Analytics team was challenged to establish an HR KPI dashboard in SAP Analytics Cloud. This dashboard should contain a Time entry management and an Efficiency analysis.
To gather the relevant data, different SAP and non-SAP source systems had to be connected to SAP BW/4 system. The Time entry management system is part of the HR Management Suite of the organisation. The Efficiency Analysis is evaluated based on the actual and target time entries. The complete HR KPI dashboard is created on a hybrid system architecture, whereas most of the components are running in the cloud.
This blog focuses on one of the data integration topics. Employee master data and the time entries are kept in different systems and had to be integrated into SAP BW/4 system. The employee start date is needed for the calculation of the Utilisation Rate, which is the central KPI of the Efficiency analysis.
The employee and the start date need to be extracted from a cloud-based performance management solution which offers a Web API to retrieve the relevant data. The following chapter describes the steps how this master data is transferred from the Web API into the BW/4 system.
Before implementing or developing the solution, ensure that the following prerequisites are met:
- SAP BW/4HANA system
- Installation of Eclipse with BW modelling and ABAP perspective
- Installation of Postman to test the API calls
- Network connectivity to access the Web API
- Knowledge of the Web API endpoint and its data structure
- Valid API access token or credentials for authentication
Overview about the solution
The core of the technical solution is an ABAP program in an SAP BW/4HANA system which retrieves data from a Web API and stores the data after processing into an aDSO. Fortunately, SAP provides us with several useful ABAP classes and function modules to achieve our goal with less effort.
The first part of the ABAP program embraces the establishing of the connection, calling the Web API and retrieving the required data. For this, the ABAP class CL_HTTP_CLIENT comes into play whose useful methods facilitate all necessary steps. Given that the API call was successful, the retrieved data in JSON format is then available for further processing.
This leads to the second part of the ABAP program which covers the deserialization of the retrieved data. Deserialization can be best described as a conversion from the JSON format into strings. Once again, an ABAP class from SAP comes handy for this task, namely /UI2/CL_JSON.
The storage of the data is done in the last part of the code. For this, we have decided to use an aDSO of type Direct Update. Alternatives might work as well, but again, SAP gives us the useful function module RSDSO_DU_WRITE_API at hand. Also, the data in an aDSO is immediately available for reporting, which avoids unnecessary data staging. As a side note, the program also handles pagination if this is a requirement of yours.
The final and easiest step was the integration of the program into a regularly scheduled process chain to get the data in the aDSO frequently updated.
Create a program in the Eclipse ABAP perspective
Give a name and description to the program
The program adopts an ETL structured methodology comprising four primary processes:
PREPARE, RETRIEVE, TRANSFORM, and CONSOLIDATE.
PREPARE: This step involves initialising data structures and creating an HTTP client to interact with the Web API. It sets request headers and prepares the HTTP GET request to fetch user data.
Once the program is created, declare the internal table and data types
Define the API URL initial Parameters and URL Path
Create an instance of type CL_HTTP_CLIENT to perform the HTTP communication in ABAP
Set the HTTP request method and header fields
• RETRIEVE: The program sends the HTTP request to the Web API and receives the JSON response. It also checks the HTTP status code to ensure a successful response.
Send the HTTP request. The timeout setting is optional.
Receive the response.
Get the status of the response to handle errors. If between 200 and 299 (successful), then we save the response into a variable. Otherwise, we handle the network error.
TRANSFORM: In this step, the program splits the data twice and extracts relevant JSON data (user records and the value used for pagination) from the response and uses the class /UI2/CL_JSON and the method deserialize to convert it into an ABAP internal table. The temporary internal table is populated with the user records.
Split the response result data at the variable “endingCursor”. Then split it again at the “data” variable and save the results
Use the class /ui2/cl_json to deserialize the response into the temporary internal table
Append the lines of the temporary internal table to the main table. Then, clear the temporary table for re-use
Set the API URL to the new “endingCursor” value.
Close the HTTP connection.
CONSOLIDATE: The final step involves writing the data from the internal table into a specified Direct Update DataStore Object (aDSO). This is done by using the function ‘RSDSO_DU_WRITE_API’, which takes the aDSO name and the internal table containing the data as input parameters.
Create an aDSO of type Direct Update
Create fields matching the internal table structure and activate the aDSO.
In the program, store the name of the aDSO into a variable
Write the data from the main internal table to the aDSO with the function “RSDSO_DU_WRITE_API”.
In summary, using this method we could achieve the goal of extracting data from a Web API and transforming it into an internal table for consolidation. It offers a simple and straightforward approach, which can be preferred for scenarios where JSON data can be easily handled through direct deserialization.
Using a direct JSON deserialization approach with the ABAP class /UI2/CL_JSON and the method deserialize to convert the JSON response into an ABAP internal table. It directly extracts the relevant data from the JSON and maps it to the internal table.
• Data Conversion
Directly deserializes the JSON response into an internal table, providing a straightforward approach to handle JSON data without any intermediate steps.
• Integration with Direct Update DataStore Object (aDSO)
Writes the data directly from the ABAP internal table to the aDSO, as it directly extracts and prepares the relevant data from JSON.
Is a simple and a direct approach to handle JSON data extraction and transformation. It involves fewer intermediate steps, making it easier to implement and maintain.