Extract SAP ERP data to cloud

Introduction

Most of SAP ERP (and especially SAP) clients are facing the question related to the reporting on their ERP Data

There are several options on this topic, and if looking closer the the SAP use case, we can have at least following options

1. Use of SAP Datasphere – not part of this current blog (dedicated one is coming)

2. Extract SAP ERP data to an external solution

3. Provide reporting directly on ERP data

Following picture provide an overall vision of the use cases 2 and 3 . The challenge is to provide Data warehousing solution for SAP and non-SAP data and a solution to run reports directly. The objective of this article is to focus on data extraction from SAP systems

1) Data replication on DB table level

One of the option is to use a replication solution do send the content of individual tables to an external solution. This can be achieved by using 

• SAP SLT as a simple a robust solution – however license impacts to be discussed with SAP - most of the time it can require a special agreement for S/4 (as Enterprise full use license)

• Other solution is to write custom extension programs with ABAP and/or CDS statements. In this case (as we are passing via the ABAP layer) there should not be additional licenses, but as always, this needs to be confirmed with your SAP sales representative.

As the below picture illustrates, the SAP data model is complex. When extracting individual tables, we have to rebuild all the dependencies of these tables in the target external Datawarehouse solution. This could be considered in the beginning as a simple modelling activity. Customer feedbacks are showing that these ERP remodeling projects can became more complex over the time, which impacts directly the maintainability of the solution. Several customers have given up this table replication based approach. It is also important to underline the necessity to ensure a delta replication of new/changed data with the possibility to reinitiate (reload) at any time the data (with selection criteria) from SAP ERP to the external Datawarehouse solutions.

2) Data replication based on SAP business objects (as Sales order, Purchase order…)

A second option for SAP data extraction is to use predefined business objects (business content) delivered by SAP.

It provides the advantage to extract a set of data in a normalized structure (supporting in most of the cases the delta and reinit methods)

Historically SAP proposed the ABAP based BW extractors for this feature.  In SAP S/4 several delta extraction enabled CDS views are provided (partially replacing historical BW extractors).

Both extractors and CDS views can be extended. Extractors with append and user exits, CDS with custom fields and BADIs

This illustration present a first section of the CDS Dependency tree of the CDS View Sales Order Item manage.

It shows the value added of SAP delivered content of this business objects – it provides a significant accelerator for data modelling and extraction of SAP Data

2.1) Data extraction of SAP data using ODP (Operational Data Provisioning)

Based on previous sections, we are now adding the SAP ODP layer to the architecture. ODP’s main feature is to provide the delta queue management of delta enabled extractors (BW, CDS or even SLT).

This component is part of the standard NetWeaver ABAP stack. It can be used for SAP (like BW, SAP Data intelligence, SAP DataSphere) and non-SAP solutions to retrieve SAP data in an incremental way.

Attention: SAP note 3255746 provide some limitations for ODP extraction. The limitation of this note is applicable to the RFC integration using the service OPERATIONALDATAPROVISIONINGOUT. Regarding API based integration of ODP to external systems the note confirms that we still can use it.

“The Operational Data Provisioning (ODP) framework for data extraction and replication is exposed by an official and externally-available OData API and can still be used” 

2.2) How it works?

In the below illustration an ODP enabled service on top of an existing

• BW DataSource/Extractor (Sales Document Item Data) and

• ABAP Core Data Service ‘CDS’ (GL Account Line Item without ledger logic)

Once the services are published to ODP (can be done is SAP via Tcode SEGW), they can be consumed by the receiving application. Microsoft provides a CDC (Change Data Capture) connector  on SAP, but it can be done also with a custom script to initiate the delta que and to schedule regular delta extractions. The delta queue will be visible in the SAP transaction ODQMON.

Once the service is created in TC SEGW and activated in TC /n/iwfnd/maint_service, it can be consumed. Below picture presents the OData service call via the GW Client in delta mode and the payload with the results. In the Monitor Delta Queues transaction (ODQMON) we can see also the delta queue of the ODP service.

3) AI powered data modelling

Still having on our mind to reuse as much as possible the SAP data modelling provided by the standard SAP CDS view, there is an alternate solution. 

The backgrounds of this solution are the followings: 

 - The changes in SAP license conditions for data extractions from SAP to non-SAP systems might be changed. An independence would be secure the target DATA/BI roadmap for companies

 - SAP standard CDS views needs to be extended. The recommendation is to make a copy of them and add custom fields and custom business rules to add business logics and calculations. With this approach we would in a certain way deviate from the Keep the Core Clean advise from SAP. The current solution In this secition propose to add custom adaptation to the third-party Data Platform.

This AI based solution is described in detail here

4) Key Challenges and conclusion

One of the main topics is to address are listed below

- Use SAP standard modelling provided by CDS views

- Initiate the data transfer from S/4, especially for high data volume ERP systems

- Manage the delta loads to one or several destination

- Reload data if needed (as an initiation or full extraction)
- Setup a waterproof monitoring of the end-to-end flows SAP - Middleware – Target DWH solution with alerting and dashboards (try to use solutions like Splunk or ELK)

- Define and document error handling procedures

We strongly recommend starting this type of data acquisition project with a proof of concept and proof of solutions. Then deploy one to three key flows to the production and ensure the run and maintenance of these flows priori to start other data transfer activities

Summary : SAP ERP Data acquisition for DWH purposes is a common challenge to most of the customers. In addition to SAP  standard solutions (BTP DI, DataSphere, BW/4), there are several options to manage it to customer’s own DWH solutions. Our return of experience shows that the data modelling of complex SAP table dependencies is a key obstacle for DWH teams. This blogs aims to provide orientations to facilitate the DWH data modelling by using SAP standard business objects for data extraction.

Please always present your data architecture scenarios to your local SAP sales representative. Get a written validation that your current licenses covers entirely the proposed architecture scenarios, or you have to acquire additional licenses.