Data Extraction from SAP ECC ODATA to Azure

When it comes to SAP integration with Azure, a typical use case is to bring in the SAP ECC data to Azure for analytics and reporting. The SAP ECC data must be exposed via the ODATA service. This step is a prerequisite for our data extraction solution and hence should not be skipped.

In this blog we will focus on the data extraction from SAP ECC ODATA to the Azure SQL database. Here is the high-level flow of the approach:

Expose the SAP ECC data via ODATA Service (Prerequisite Step. Details not covered in this blog)
Create the Azure data Factory components that are required for pipeline
Create the pipeline in Azure data factory(V2) for the data extraction from SAP ECC ODATA to the Azure SQL database

The key components of the Azure Data Factory are:

Linked Services that defines the link where the data must be sourced from/to.
Data Sets represents the structures within the data stores. A source dataset denotes the input for an activity in the pipeline. A sink dataset denotes the output for the activity.
Pipeline is a logical group of activities that performs the job of moving data from/to.

For the Source Dataset creation, connect to the Azure Data Factory(V2) and select the create dataset option as below:

Then select the SAP ECC data store from the data store list.

In the connection tab of the data set, select the option to create a new linked server. The linked server window will pop-up. Provide the ODATA service URL, user name and password for connecting to the ODATA service in that window.

After configuring the linked server, give the ODATA resource path in the connection tab.

In our scenario the output is Azure SQL table. So we have to create a table structure in the database to store the data.

Also, we must set up the sink data set in the Azure Data Factory for Azure SQL database.

Similar to the source dataset configuration, we need to create a linked server for the sink as well. But here the linked server would point to the Azure SQL database.

We must then create a pipeline for the data extraction from SAP ECC ODATA to the Azure SQL database. The pipeline can be designed either with only one copy activity for full load or a complex one to handle condition-based delta.

Pipeline for Full Load:

Connect to the Azure data factory(V2) and select create pipeline option. Then drag & drop the copy activity and configure the source and sink as below:

The pipeline can be executed through a trigger or by selecting the debug button. The data will be transferred from SAP ECC ODATA to Azure once we execute the pipeline.

Pipeline for Condition-based Delta Load:

The source query in copy activity must be enhanced to handle dynamic expression for handling delta load. We can achieve the delta extraction solution using the below high-level approach.