SAP’s Operational Data provisioning is an evolutionary leap in the way how SAP extractors work and how they can be used as a subscription-based services for multiple ETL tools across the enterprise.
In this blog, we will demonstrate how easy it is to integrate SAP Data Services to use extractors for data extraction through ODP API framework. This is possible from SAP Data Services upwards of release 4.0
Before trying to integrate, we first need to ODP enable the extractor the process for which can be found here
- To extract data from ECC system using extractors you should have a Datastore configured as source. Double click on the Datastore to see the list of the ODP enabled objects. the necessary ODP extractors form the list by right click -> import. You can find the list of extractors released for ODP replication form the SAP note 2232584.
- Here we will use 0FI_AP_30 & 2LIS_11_VAHDR extractors (One direct extract and another LO extraction). When using any LO extraction, be sure to fill the setup tables on initial load. When importing give a project name which cannot be changed later. Each of these projects will be a separate subscription in the source system, which you can view in the T-CODE ODQMON.
- Query vs CDC mode: When importing any ODP, you can choose between query mode or CDC mode.
- In query mode extractor, you can give the required condition in the where clause of the query transform which will be pushed down. Before that, check whether that column supports that selection condition in the column attributes. When importing CDC based ODP extractor in query mode, the CDC specific columns. DI_OPERATION_TYPE and DI_SEQUENCE_NUMBER will not be available because this is treated as full load and not a delta load.
- In Change-data Capture (CDC) mode extractor, you can give the conditions in the selection screen. You can give any filters as needed while importing and this cannot be changed. For all CDC enabled extractors, there will be two additional CDC columns DI_SEQUENCE_NUMBER and DI_OPERATION_TYPE. This is similar to providing the selection conditions on a delta infopackage.
- Map_CDC_Operation: Map_CDC_Operation transform is used to transfer the DML changes in the source data and deliver it to the target tables. A sample data flow like using Map_CDC_Operation will look like below:
The input columns, DI_SEQUENCE_NUMBER will hold the order of the input records and DI_OPERATION_TYPE column will hold the code for insert/update/delete. The possible values for DI_OPERATION_TYPE are
- I for INSERT
- D for DELETE
- B for before-image of an UPDATE
- U for after-image of an UPDATE
This will be mapped to ‘Sequencing Column’ and ‘Row Operation Column’ respectively. The input already sorted by sequencing column check box is selected by default. If this is unchecked, then BODS will have to sort the input records again and this will impact the performance.
4. Double click on source for more options you will get the below options. You can give variable values to filter the data extracted based on datetime. If initial load is set to yes, then it will bring in all the data.
5. This extraction can be checked in the t-code ODQMON in the Source ECC system where each project will be a separate subscription
This way, you can utilize your existing extractors and extract data from SAP ECC and load it to non-SAP targets like Azure, Other databases etc. The advantage of this approach is that the same extractor logic can be used by SAP DS and your BW system if you have one