Nowadays, there is an increasing need to integrate data coming from various systems. It’s a common situation that a portion of data resides in the cloud, while another dataset is stored in on-premises systems.
Let’s consider a scenario where the SAP BW/4HANA system is responsible for the consolidation of data from regional ERP systems. This could refer to Sales data for example. In the same case, the Forecasted Sales data is stored in the Azure Cloud.
In the desired architecture, we want to bring the SAP actuals to the Azure environment. This will enable the comparison of Forecasts with Actuals and improve the forecasting process.
As shown below, SAP BW/4HANA exposes the data to Azure through the Azure Data Factory, for data replication.
%201024x302-(Compressify.io).jpg?width=676&height=199&name=How%20to%20Replicate%20Data%20from%20SAP%20to%20Azure%20(graph%201)%201024x302-(Compressify.io).jpg)
The Actuals and Forecasts are stored using different data granularity. Therefore, we need to perform transformations of both datasets to make data matching, comparison, and analysis possible.
Once the SAP and Azure data is combined, we can additionally compare the Sales data with the previously forecasted values, to validate the quality of the Forecasting Engine. To improve the performance of such data comparison, the merged data is stored in the Azure BLOB for further analysis.
We can achieve this by using the Azure Data Factory, which allows us to source data from the SAP system, transform it, merge it with the Azure data, and save the results in the Azure BLOB.
Azure Data Factory
-1024x565-(Compressify.io).jpg?width=676&height=373&name=How%20to%20Replicate%20Data%20from%20SAP%20to%20Azure%20(graph%202)-1024x565-(Compressify.io).jpg)
Azure Data Factory – technical point of view
From the technical point of view, the following objects will take part in this scenario:
-
SAP BW Composite Provider
-
SAP BW External SAP HANA View
-
Integration Runtime (ODBC)
-
Azure Data Factory
-
Azure BLOB
In the example scenario, it took 9.5 minutes to transfer 14 million records. The subsequent ETL operations of the SAP and Azure data took another 8.5 minutes.
-1024x389-(Compressify.io).jpg?width=676&height=257&name=How%20to%20Replicate%20Data%20from%20SAP%20to%20Azure%20(graph%203)-1024x389-(Compressify.io).jpg)
Except for the resulting CSV file stored on the Azure BLOB, we can visualize the data in a dashboard using Power BI, showing the comparison between the Forecasts and Actuals.
.jpg?width=676&height=395&name=How%20to%20Replicate%20Data%20from%20SAP%20to%20Azure-1024x598-(Compressify.io).jpg)
In this scenario, we were able to verify the integration of SAP data into the Azure systems. It has been tested and confirmed that the extraction of SAP data and its transformation in Azure is possible in a reasonable time.
The solution is recommended for scenarios that do not require real-time access to the source data, where the standard ETL process – scheduled by the Azure pipeline, is sufficient. The Azure Data Factory provides a set of comprehensive tools to orchestrate, monitor, and manage the entire data flow, including the built-in connectors to various source systems, like SAP.
