7 Using Advanced Join Handling for Change Detection with SAP CDHDR Table
The SAP system table CDHDR
holds information about data changes in SAP business tables like MARA
. Among other data this includes the (concatenated) business key (field CDHDR.OBJECTID
), change type and change timestamp (field CDHDR.UDATE
and UTIME
).
In this example the business key is the material number (MARA.MATNR)
. Starting with v42 the ODI SAP adapter for SAP ERP/ECC supports substring operations in join conditions. This chapter gives an example of how to use a complex join expression between the SAP tables MARA
and CDHDR
to extract only new/modified/deleted MARA
records.
In case of MARA
you can identify relevant changes with the help of CDHDR by applying these filter/join conditions:
-
record type filter on
CDHDR: CDHDR.OBJECTCLASS = 'MATERIAL'
-
time filter on
CDHDR
: the timestamp(CDHDR.UDATE, CDHDR.UTIME)
is greater than the last extraction timestamp and smaller than the current timestamp -
join condition: the first 18 characters of
CDHDR.OBJECTID
matchMARA.MATNR
Note:
For other object classes and their object id composition/ business key, contact your SAP team.The following sections cover all the steps needed for building an ODI scenario for delta extraction: on every execution the package extracts any MARA
changes since last run and applies the changes to the Oracle target table.
Required Tools and Versions
- ODI Version — Oracle Data Integrator Version 12.2.1
- Open Tool Version — Oracle SAP Connector Version 10.1.3.23
- JCo version
- JCo API — 3.0.17 (2017 –07–17)
- JCo Middleware — JavaRfc 2.2.20
- JCo Library — 721.913
- Path to JCo Archive — c:\Oracle\Middleware\Oracle_Home1\odi\sdk\lib\sapjco3.jar
- Path to JCo Library — c:\Oracle\Middleware\Oracle_Home1\odi\sdk\lib\sapjco3.dll
- SAP KMs Build Number - 42
- IKM - IKM Oracle Incremental Update
Creating Variables
Creating Mapping with Advanced Join Condition
CDHDR
and MARA
based on below condition and use two filters to extract the data from SAP and store in target oracle table named MARA_REPLICATED
.
Using Filters Condition in Mapping
You can use filter conditions to select the related data change log entries. Listed below are two filters — OBJECTCLAS
of CDHDR
and the next filter is using few variables related to date and time as mentioned above for extracting updated records.
-
Filter 1 contains below condition:
CDHDR.OBJECTCLAS = 'MATERIAL'
-
Filter 2 contains below condition:
Based on this condition you can retrieve only updated records which is not extracted yet.
(CDHDR.UDATE > #SAP.LAST_EXTR_DATE OR (CDHDR.UDATE = #SAP.LAST_EXTR_DATE AND CDHDR.UTIME > #SAP.LAST_EXTR_TIME)) AND (CDHDR.UDATE < #SAP.CURR_DATE OR (CDHDR.UDATE = #SAP.CURR_DATE AND CDHDR.UTIME <= #SAP.CURR_TIME))
Creating a Package for Extracting Updated Records
Create a package by following the instructions given below which contains few Date and Time range ODI variables and also create a mapping as described above:
There are eight steps in creating the package (termed as Steps Hierarchy) as shown below:
Set Variable: Set the value for LAST_EXTR_TS variable as shown below:
Understanding Last Extract Timestamp Parameter
Before extracting the records, you have to check Last Extract Timestamp parameter so that you can know the exact time when the records were extracted. Default value of Last Extract Timestamp is 00000000:000000 and after first execution it picks its value from History.
Running the Package
Execute Scenario to Extract all the Updated Records
Create_Target_Table
option to True
, as you are retrieving records for the first time, as given below:
-
Modify the Mapping flow
-
Execute Package — Now execute the package to extract all the records which are updated until specific date. When you are executing the package for the first time, it picks its default value from Last Extract Timestamp parameter.
-
Check Extracted Records — Upon successful execution of the Package, related records are inserted to the Oracle Target Database.
-
Extracted Records — Find the extracted records in target database as show below: