Data Extraction from SAP ECC using Delta token – Azure Data Factory

Very often, we have a data warehouse, where we would like to integrate data from many data sources. Like any OLTP system, SAP ERP Central Component (ECC) has its own challenges, which restricts us from hooking on to the underlying data directly. One of the recommended ways of exposing data is through an ODATA service. In this blog, we will discuss how to consume from the ODATA service and delta handling in Azure Data Factory.

For this use case, we exposed ECC data though SAP’s Operation Data Provisioning service, which would, in turn, provide an ODATA endpoint. The ODATA service will return a delta token at the end of each request, which will be used later to get only the delta records and so on. Also, we would break the response into multiple pages by enabling pagination. Follow this link to understand the usage syntax.

Azure Data Factory is an on-demand data orchestration tool, with native connectors to many storage and transformation services. To deal with ODATA we could either use the ODATA connector, HTTP Connector or REST API connector. The ODATA connector was able to parse the response automatically but it didn’t have the provision to pass additional headers and enable pagination. Similarly, HTTP didn’t have the provision to enable pagination. The Rest API connector has both options available. Based on these considerations, we would recommend using the Rest API connector to extract ECC data.

Data Extraction from SAP ECC using Delta token – Azure Data Factory

How do we read the data?

Base URL of SAP’s ODATA would be as below;

https://<Host>:<>/sap/opu/odPortata/SAP/<Service Name>/<Entity Set Name>/<Query Options>

For the initial load, we would be invoking this URL with “odata.track-changes” headers. This would return all the records in the entity with the delta URL. This delta URL is unique to each user. For future reads, we will be using this delta URL to get the delta records. But, this meant we would have to store the response into a file and read again to get the delta URL. Instead, to avoid this extra read we decided to use the below URL, which would give us the history of all delta token generated by the user.

https://<Host>:<Port>/sap/opu/odata/sap/<Service-Name>/<Delta Set Name>

This API could return the delta URL in chronological order and we would use the latest delta token for each run. The same link would explain the usage of these endpoints.

Alternatively, you could use query parameters to get the delta, if the table has an “updated date” column.

This is how data orchestration process flow would be