SAP BW/4HANA – Data Extraction using OData ODP
Author – Jonathan Haigh , Senior Analytics Consultant, NTT DATA Business Solutions UK&I
Historically, we have quite often used Open Hub to extract data from SAP BW, though as this came with an additional licence cost many SAP BW customers would lean towards other methods instead. With the birth of BW/4HANA and the fact that Open Hub is included within the licence model this now presents opportunities for taking data out of SAP BW without the additional cost.
An alternative to using OpenHub is to use OData and the ODP interface. The use of Open Hub relied upon the BW system processing the data and then pushing that to its destination for data collection by whatever means. When using OData you have the ability to pull the data, thus reducing some of the dependency of the BW processing, though naturally the data will still need to be collected and persisted in the BW InfoProviders. Data could be pulled using other methods, for example, generated calculation views off InfoProviders, however I will not be detailing this option.
In this blog I aim to demonstrate the simple steps you need to perform to enable access to a BW InfoProvider that can be consumed as full data loads or full with subsequent delta loads.
Pre-requisites
- SAP BW7.4 or higher release (my example is using BW/4HANA 2.0)
- SAP Gateway Foundation is either required locally or in a separate SAP NetWeaver instance
- Open Hub License (if you’re not running BW/4HANA)
- OData Client to call the service
- BW InfoProvider with persisted data
Configuration Steps
First we need to create a new project for our OData service in transaction SEGW, give the project a technical name (must begin with Z to enable generation of objects in a customer namespace) and description, assign it to a relevant package for your system and leave the other settings as the defaults and then continue.
Now you have a project, the next step is to define the data model for OData access, so we access the context menu for the data model and select the menu path Redefine à ODP Extraction.
This will access the Wizard for ‘OData Access for Operational Data Provisioning’. Next, enter the ODP Context as BW/4HANA and this will enable you to search for your DataProvider in the ‘ODP Name’ field. Once you’ve found your ODP object, click the ‘Add ODP’ button.
Your ODP object(s) should now appear in the list. If you’re wanting to add other objects for ODP such as master data, texts or hierarchies then you are able to do that in the same dialogue prompt. Once you have your ODP’s added, click the Next button to continue through the wizard.
Next step is to give your model and service a description; you can change the name if you wish but I’ve accepted the defaults in the below.
In the final step, we highlight all the top level check boxes to determine what will be available via the OData Service and click Finish.
N.B. If you wanted to restrict some of the aDSO fields then deselect those fields under the ‘Factsof{service}’ Entity Type.
Now we just have to generate the runtime objects, which is available via the icon or from the context menu.
Accept the defaults and click on the green tick to generate the ABAP objects.
The final part is to add the service using transaction /IWFND/MAINT_SERVICE – select Add Service
Enter the following to find your OData Service and click ‘Get Services’
You should then see your service, select it and then click ‘Add Selected Services’.
Accept the defaults and change the package if you want to transport this development. That concludes the setup of the OData Service
Testing the OData Service
You can use the native SAP Gateway Client to test the service but for my testing I chose to use Postman – a third party client install.
For this test we will setup the initial load to track delta changes then will load some changes to the aDSO to demonstrate how the delta changes are extracted. Note, to allow for delta extractions the OData Client will need to be able to process header entries.
To call the service we generated we need to use the following URL:
http://<Host>:<Port>/sap/opu/odata/sap/ZORDERITEMS_2_SRV/FactsOfORDERITMS
N.B. you will need to populate the host and port relevant to your system.
Using Postman we paste in the URL, and then we also need to add a username and password in the authorisation tab. Finally, we need to add the following header:
Key = PREFER Value = odata.track-changes
Then we can hit Send to retrieve our data.
The resulting data is then available to view.
At the end of the message we can see our delta token.
This will need to be used for the next call in order to collect just the delta records. The URL we need to use for the next call is
http://<Host<Port>/sap/opu/odata/sap/ZORDERITEMS_2_SRV/DeltaLinksOfFactsOfORDERITMS(‘D20211116152515_000030000’)/ChangesAfter
The result of this call then delivers the new data with the before and after image. The recordmode will need to be considered in the target database or the ODQ fields ODQ_ENTITIYCNTR and ODQ_CHANGEMODE, to understand if they are changed or new records.
If we investigate the Delta Queue Monitor in the BW Gui we are able to see our two data requests, one being the delta initial and the other being the changed / new records.
If there is a subsequent load failure from the OData Client then by using the relevant Delta Token you are still able to retrieve all changes from a point in time.
If you need to analyse the Delta Changes that have been requested then you can achieve this by using the EntitySet ‘DeltaLinksOfFactsOf[service]’
In my tests I have only been dealing with small datasets, so should you wish to manage the data extraction by using packages then you should add an additional header value.
Header = Prefer Value = odata.maxpagesize=<n>
If there is a need to restrict the data being pulled from the SAP BW system then you can also pass through a filter parameter in the URL, the syntax for this is detailed below. The filter is detailed after the entity set in the URL, you are also able to define multiple filters if required.
/sap/opu/odata/sap/ZORDERITEMS_2_SRV/FactsOfORDERITMS?$filter=PRODUCTID eq ‘MB-1034’
Conclusion
This method of extraction offers the consumer a great deal of flexibility and the ability to pull the data, reducing but not eliminating the dependency on the BW Application.
The steps required to expose the data via an OData Service are extremely simple. The BW developer is in complete control of what is exposed both at a DataProvider level and field level so any fields can be excluded, or the filter option in the URL could be deployed.
No ABAP skills are required for this, and all the methods and classes are generated as runtime objects so this interface offers a genuine clear alternative to generating flat files via Open Hub or consuming OData BW Queries.
The fact that you are able to consume the ODP delta functionality also means you do not have to over-engineer your BW application to deliver delta capabilities for data extraction, it is available for free using the OData for ODP Extraction, if you’re using BW/4HANA or indeed you have purchased the OpenHub licence for earlier BW versions.
If you’re interested in learning more or wish to discuss any of this technical content in more detail then please get in touch