Distributing data from BW System using ODP-Based Data Extraction and OData

[mostafaelbarbar]

Introduction

I was asked what are the possible ways to distribute BW data to external systems and 3^rd 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).

System Prerequisites 

• 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

Steps

• 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

In this step, we can add associated ODP using + button and in this case all entities related to the selected ODP will be addressed ,so in this case we are adding ODP of aDSO /IMO/D_FIGL12 which is fact data then we can associated attributes and texts data too.

GL account and Company code attributes had been added and can be accessed as entity sets, for simplicity we will remove the ODP associations using - 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
  
  
  
  1. Header Name: Prefer
  
  
  2. 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

Note that at the end of the response of a request in delta mode contains a relative link to the next delta query. This can be used with the next HTTP request, in order to retrieve data, which has been changed since the delta was created.

• 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.

Conclusion

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

[maged_sap]

Very useful article.. Thanks a lot.

[ashraf-hashem]

Very insightful, thank u for sharing

[karim_mohamed2]

Thanks for sharing

[former_member682163]

Very Helpful thanks for sharing

[praneeth_nagalla]

Very useful document. Thanks for sharing.

[former_member182912]

Thanks for sharing, Does anyone knows if the data travels encrypted when the OData Service is called?

[former_member268343]

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.

[mostafaelbarbar]

yes

[Jan_van_Ansem]

Hello Mostafa,

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:

• If the source is delta-enabled and contains data, the delta-init pulls all data across.
  Is it possible to run a delta init without data?
• When I create project in the Gateway Builder and add several entities, the model only contains the delta configuration items for the first entity (DeltaLinksOf....)
  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!
Jan.

[mostafaelbarbar]

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

• 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)

[mostafaelbarbar]

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

 

https://help.sap.com/viewer/ccc9cdbdc6cd4eceaf1e5485b1bf8f4b/7.5.9/en-US/50f4ee6253134d3cafa25b9444f...

[Jan_van_Ansem]

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 🙂

 

 

[dgallegog]

Thanks for sharing.

I am having delta error:

Request URI: /sap/opu/odata/sap/ZDIGL_M06_SRV/DeltaLinksOfFactsOfZDIGL_M06('D20220304153511_000018000')/ChangesAfter

Status_code: 400

Status_reason: Bad Request

<?xml version="1.0" encoding="UTF-8"?>

-<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

<code>RSODP_ODATA/014</code>

<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.

Regards.

David

[former_member251313]

Hi David, where you ever able to resolve this?