Distributing data from BW System using ODP-Based Data Extraction and OData
I was asked what are the possible ways to distribute BW data to external systems and 3rd party applications? and my answer was:
- Using open hub destination
- ODP-Based Data Extraction via OData
But I recommended ODP-Based Data Extraction via OData because it gives us flexibility to be consumed with wide range of technologies and platforms as it is based on the OData communication protocol and also we could perform scalable delta extraction which is very good approach to distribute only new or changed data records to other applications.
In this blog I will explain step by step how to create OData service based on aDSO and how to extract the data from this aDSO (Full/Delta extraction).
- SAP BW7.4 or higher release
- SAP Gateway Foundation is either required locally in the SAP NetWeaver system of the ODP provider or in a separate SAP NetWeaver instance (IW_BEP)
- Open Hub License
- Create New project in SAP Gateway Service Builder using T-Code SEGW and enter the project details
- Redefine the Data Model and choose ODP Extraction
- Enter RFC Destination, ODP Context and ODP name and then press Add ODP button
- Press next and enter the description of Model name and service name.
- Press next, choose to save to local package.
- Select all generated artifacts and press finish.
- Generate runtime artifacts and choose to continue and save to local package.
Now we are having our oData service created and we need to activate it.
- Go to Activate and Maintain services T-Code /IWFND/MAINT_SERVICE.
- Choose add service.
- Enter System Alias: Local and Press get service, Select the service and then press Add selected services.
- Choose local object and continue.
Now the oData is created , active and ready be used by any external system which supports restful API communication.
Testing the OData service for Data extraction
For testing purpose, I delete the data of aDSO /IMO/D_FIGL12
- Select the OData ZFIGL_12_SRV and choose SAP Gateway Client to test it from T-Code /IWFND/MAINT_SERVICE
- Press EntitySets Button and Choose FactsOfD_FIGL12
- Add HTTP header
- Header Name: Prefer
- Value: odata.track-changes
This will enable delta extraction from the aDSO /IMO/D_FIGL12, and if we ignored it, Full data extraction will be executed.
- Press Execute button.
- Check HTTP response
- Run T-Code ODQMON to check the ODP Queue
It shows one request for selected Subscriber and subscriber type, the extraction Mode is Initial Data (Delta Init) and Number of rows is zero as we deleted the data from aDSO /IMO/D_FIGL12.
- Run relevant DTP to load the data to aDSO IMO/D_FIGL12.
- Back again to SAP Gateway Client and Press EntitySets and Choose DeltaLinksOfFactsOfD_FIGL12
- Add the following details after the entity set (‘D20200527232940_000030000’)/ChangesAfter , the final URL should be like /sap/opu/odata/sap/ZFIGL_12_SRV/DeltaLinksOfFactsOfD_FIGL12(‘D20200527232940_000030000’)/ChangesAfter, this should extract data that have been changed since a specific delta token provided in the URL.
- Press Execute button
- Back again to Monitor delta queue request
The delta changes had been extracted from the aDSO after loading the data to it.
ODP-Based Data Extraction via OData because it gives us flexibility to be consumed with wide range of technologies and platforms as it is based on the OData communication protocol and also we could perform scalable delta extraction which is very good approach to distribute only new or changed data records to other applications.
For more information and options please check ODP-Based Data Extraction via OData
Very useful article.. Thanks a lot.
Very insightful, thank u for sharing
Thanks for sharing
Very Helpful thanks for sharing
Very useful document. Thanks for sharing.
Thanks for sharing, Does anyone knows if the data travels encrypted when the OData Service is called?
Hi, in the pre-requesites section you mention this works for BW 7.4. Is it a way to do the same in SAP ECC. I am searching a way to make data extraction from 2LIS * datasource from SAP ECC to Azure.
Thaks in advance.
Sorry , I have missed your comment,
Frist of all you need to replicate the ECC extractor for ODP replication, please check this note for the prerequisites.
then you have to fulfill this prerequisite as well
This is a great step-by-step guide, thanks very much for sharing.
I have two questions about the delta, maybe you can help me with these:
Is it possible to run a delta init without data?
I know entities can be delta enabled (customer, material), because if I put them in separate projects, or as the first entity in my project, the delta works without fail.
I tried manually adding the elements in the Data Model in TC SEGW. I then get the correct entity sets for deltas, but it fails at runtime.
Any ideas how I can create one project, with multiple delta entities?
Thanks in advance for your help!
Hi Jan , you can use this link to check all extraction option , but I don't think that is possible to initialize the delta without data
Hi Mostafa, thanks for your reply. I have played around with various parameters but have not been able to successfully do a delta init without data, only with data.
I'll try a few more things and will post here if I find a solution.
Failing that, I will raise a ticket with SAP to try and get this resolved. It should be easy enough to add a parameter to the odata interface to allow for delta initialisation without data.
To be continued 🙂
Thanks for sharing.
I am having delta error:
Request URI: /sap/opu/odata/sap/ZDIGL_M06_SRV/DeltaLinksOfFactsOfZDIGL_M06('D20220304153511_000018000')/ChangesAfter
Status_reason: Bad Request
<?xml version="1.0" encoding="UTF-8"?>
<message xml:lang="en">Could not prefetch data via extraction API RODPS_REPL_ODP_PREFETCH</message>
Do you have any experience in this kind of error?
Thanks in advance.