Implementing Delta Loads with ODP_SAP

The DTP is set to run in Delta mode as a default when the underlying source supports delta.

In a DTP, various settings can be defined related to delta loads:

• Filter: Restricts the amount of data read in the source.

  For filters, use characteristics with values that are not changed in the source records.

  Filter conditions for different Full DTPs may overlap, but filter conditions for different Delta DTPs of the same target must be totally disjoint to avoid duplicating records. A delta DTP with overlapping conditions cannot be activated.

• Only Retrieve last request: If the source contains a sequence of full loads and the latest request contains all information, use this option.
• Extract all green requests: When reading from a Staging DataStore Object that has only one inbound table, only requests are extracted that are older than the first yellow or red request. If you set this flag, green requests with yellow or red requests between them are extracted, too. The leading practice is to not set it: that is, keep the request sequence.
• Only get delta once: When using a delta enabled DataSource, data can only be loaded once even if it is deleted from the target. This may be needed if the source does not provide a delete record when source data is deleted. Then, the data set of the SAP BW/4HANA data can be adjusted to the source by dropping data sets in the target before loading the source data again. This setting then avoids loading outdated historical data. The leading practice is to deselect this option in usual scenarios. (Requests are only loaded once anyway.)
• Perform Delta Initialization Without Data: Sets a flag that data is processed without processing it. (Use this option if the target already contains the data, or if no historical data is needed.)
• Parallel Processing: The DTP extracts and processes different packages in parallel. The leading practice is to avoid it if consistency depends on the sequence of records, otherwise, use it.

To improve the performance of the data transfer process, you can combine the following options for parallel processing:

If the source does not provide a delta mechanism, use smart selection criteria in a full DTP to reduce the amount of data to be loaded. For instance, if you know that only some records for a new product have been added, load only these values. Similarly, you can schedule a DTP daily with a "moving filter" for the date of the previous day. It is possible to derive filter values through an ABAP routine, and even in the SAP HANA runtime.

If records of the current month may be changed, deleted or added, but records of the previous month cannot be changed for business reasons, implement a load … delete … load scenario. Use a filter for the current month. This scenario is shown on the right side of the preceding figure. The deletion is necessary if you want to reflect the deletion of records in the source, but the source doesn't send a corresponding record mode. Deletion is fast if the DataStore Object (advanced) is partitioned by month. (The database executes a DROP PARTITION statement instead of a DELETE FROM statement.)

If you must load historical data from a source system for the first time, you have a few options available regarding the initial data loads. Which one you pick depends on the data volume and the DataSource, for example. The initial data load could involve many terabytes for several years of data so you need the most efficient solution.

You can consider the following two options:

• Delta Data Transfer Processes
• Full before Delta

Running Delta Data Transfer Processes provides the simplest scenario for a low data volume. All you must do is create one DTP to run in Delta mode. The first time you run it, the ODQ performs a Delta Initialization and in the second run, the ODQ does a Delta load.

In high-volume scenarios, this option must be considered. The benefit of loading the historical data with a Full DTP is that it is sometimes faster than a DTP set to Delta Extraction Mode. Another reason may be that you can have different filter settings for the Full and Delta updates. For example, with a Full load, you can filter to more current data that could possibly change, maybe the last two years. Then, you start the delta process and back fill the target with data that is older than two years (in this hypothetical scenario.) One danger is that between Full load and Delta Initialization, data may have been changed and these changes were not captured.

For this scenario, the following steps are needed:

1. Create DTP1 with Extraction Mode set to Full.
2. Create DTP2 with Extraction Mode set to Delta and the Delta Init w/o Data checkbox selected. (This only starts delta recording.)
3. Execute DTP2 with Extraction Mode set to Delta, again (Now, the Delta Init w/o Data checkbox has no effect, delta data is loaded.)

The following process occurs when you execute a DTP with an initialization run:

1. All the data that corresponds to the selection criteria determined in the Data Transfer Process (DTP) is requested.
2. A request in the operational delta queue (ODQ) for the DataSource is generated in the source system in table ODQDATA_C.

In many applications, document processing has to stop when the delta process is initialized. In this case, remember the following points:

• Only after the delta process has been initialized successfully (including generating the request in the operational delta queue for the DataSource) you can update new or changed (delta) data records to the operational delta queue.
• The delta type determines whether or not delta records can be updated directly to the operational delta queue from the application or the DataSource extractor.

A delta update requests only those data records that were created or changed (or deleted) since the last load for the DataSource in the source system. The operational delta queue is the central temporary storage location for these delta data records. The delta records are stored in the ODQDATA table.

The properties of the delta process for the DataSource determine how the delta data records arrive in the operational delta queue (delta type) and how the changes are recorded (record mode and serialization). Details about these aspects are provided under properties of delta processes.