Efficient delta extractions from SAP

Often there are datasets in SAP that grow so large it’s impossible to extract them periodically in a reasonable timeframe. Also, it makes no sense to extract records that are already in the target system and have not been changed since the last extraction. In such circumstances, the logic is to apply delta handling: transferring only the records that have been created or changed since the last extraction.

This sounds simple, but it can be complicated to implement in SAP. The simplest case is when you have a suitable field in your dataset that can be used to identify the delta. For example, a “Last change date” could be such a field. In this case you can simply define a dynamic variant in SAP to only process records for the actual date (in case of a daily job) and you are ready to go – you can use this variant in VirtDB jobs as well.

However, things may not be so easy each time. What if there is no suitable field in your dataset to calculate the deltas? For instance, if you want to export the customer master table (KNA1) from SAP, there is no information stored about when a record was changed.

VirtDB provides a flexible and efficient way to handle these kind of situations. In such case, you can use the standard logging feature of SAP called Change Documents and VirtDB Mass Data Extractor can process the change logs recorded by SAP.

This allows you to:

  • Define incremental loads in SAP using your existing licensed SAP user group (no indirect access license fees are applicable)
  • Reduce the load on the SAP server and the network eliminating unnecessary full extracts
  • Provide more insight for you business users due to more frequently updated data in your BI systems
  • Cut ABAP development and ETL efforts and costs

You can use transaction SCDO to see what change document settings are defined in customization. The default object for customers is “DEBI”:

Here you can see that there are many tables that are relevant for a customer in SAP. KNA1 is one of them. If you now activate this standard logging feature, then all changes to any of these tables will be recorded in tables CDHDR and CDPOS.

Now you can use VirtDB Mass Data Extractor to extract incremental data from KNA1. First, we will schedule a full extraction which will serve as a basis for subsequent incremental loads.

Simply define the table/view to extract, the target system (MS SQL in this example) and the relevant settings (name of the table to create in MS SQL and upload mode 1 = Overwrite).

Once these settings are done, save this as a variant:

We can now schedule the full extraction using the button on the toolbar:

This will call the standard SAP job scheduling interface. Here you can set the start condition for the job: now it will be executed only once, immediately after saving the job.

We are ready to start the SAP job monitor to see what happened. As you can see, the job has finished in 12 seconds.

Check the detailed application log of the extraction:

You can see that all the records were successfully exported to our MS SQL target system. Lets check how they look!

The table was created using all metadata from SAP (data types, primary key etc.) and data was loaded into the table. Additionally, a view is created on the table to provide meaningful descriptions for the fields. Now you can use this view with your any BI tool to analyze SAP ERP data.

Next, we will make a change to the highlighted customer in order to demonstrate the delta handling capabilities of VirtDB. We’ll execute transaction XD02 to change the phone and fax numbers of the customer:

After saving the changes, SAP will store them in tables CDHDR and CDPOS. Following this we need to define a daily delta extraction. Two things have to be changed: first select “Use Change Documents” in the incremental settings box:

The second setting that has to be adjusted is the upload mode. We will choose “Merge”, that will either insert or update the delta records in the MS SQL database (if it already exists or not based on the primary key).

Once these changes are made, let’s save the settings as a variant and specify the date and time range that VirtDB Mass Data Extractor will use for reading the Change Documents. For this, we can use the standard dynamic variant functionality in SAP:

This will always fill the from_date and to_date with the system date, meaning that it will always export only the changed (and new) records for the given day, when the daily job is executed.

The delta job is executed in five seconds:

Let’s see what happened!

As you can see, there was only one record that has been changed today (the one we have made) and it was successfully exported to MS SQL. Querying the contents of the MS SQL table will show you the new telephone and fax numbers for the customer we have changed.

You can see how easy delta handling can be done using VirtDB. No more unnecessary full extractions, no development costs on the SAP or on the target system side.

If you are interested in smart SAP data extraction:

2017-06-21T14:42:29+00:00 April 7th, 2017|1 Comment

About the Author:

One Comment

  1. SAP compliant ETL jobs - VirtDB April 20, 2017 at 3:14 pm - Reply

    […] Previous […]

Leave A Comment