SAP Table Delta Extract Made Easy - Practical Example with Sales Order Data Extraction

/

Abstract

With the latest release of AecorSoft Integration Service, doing SAP table-based delta extraction is easier than ever. This article uses SAP Sales Order header table VBAK as an example to explain how to conduct the elegant and powerful way to manage delta extraction with ease.

Analysis

In most cases, we rely on timestamp fields to identify delta records. Being one of the most important transactional table in SAP, the Sales Order header table VBAK has a couple of important fields:

 ERDAT  Date on Which Record Was Created

 AEDAT

 Changed On

There are two delta scenarios:

  1. For newly created record, ERDAT has value but AEDAT would be empty;

  2. For changed record, AEDAT is updated while its ERDAT remains as is.

Therefore, the high-level delta logic would be “get all new records as well as changed records from last delta run”, which can be further translated into the pseudo WHERE clause:

WHERE ( ERDAT > = last_run_date AND AEDAT is empty ) OR AEDAT >= last_run_date

Implementation

Practically, the “last_run_date” can be specified as “current date minus 1 day”, to ensure all delta records generated since yesterday’s run would not be missed. Inevitably there would be same records which have been extracted in last run, but a MERGE on the destination database would take care of the duplicated extracts.

In order to get yesterday’s date dynamically at run time, we can leverage SSIS expression to build a date value in the YYYYMMDD format which SAP expects:

(DT_WSTR, 4)(DATEPART("yy" , DATEADD("day", -1, GETDATE()))) + RIGHT("0" + (DT_WSTR, 4)DATEPART("mm" ,DATEADD("day", -1, GETDATE()) ),2) + RIGHT("0" + (DT_WSTR, 4)DATEPART("dd" , DATEADD("day", -1, GETDATE()) ),2)

We will then define a STRING type SSIS user variable var5 and assign this expression to it:

var5 expression.png

Next, change the Table filter to Expert Mode:

expert mode.png

Then, enter the clause:

( AEDAT = '00000000' AND ERDAT >= '@[User::var5]' ) OR AEDAT >= '@[User::var5]'
var5 filter.png

Conclusion

Leveraging the dynamic filters, we can implement a complete sales order delta extraction logic easily. The same pattern can be expanded into delta extraction scenario when TIMS fields are available to fine control the delta data volume. Even without TIMS field, normally it is still acceptable to have delta data volume within a day’s range if we filter new and changed records based upon Today minus 1, and then rely on database MERGE to get the final image of data in near real time.