Implementing Delta Loads with ODP_SAP

Objectives

After completing this lesson, you will be able to:

  • Implement delta loads with ODP_SAP

DTP Settings for Delta Loads

The Data Transfer Process (DTP) is used to transfer data both to and within SAP BW/4HANA. Transformations and filters can be applied.

The following table lists the possible sources and targets of a DTP:

Sources and Targets of a DTP

Object TypeUsable as DTP SourceUsable as DTP Target
DataSourceyesno
CompositeProvideryesno
Queryyesno
SAP HANA Analysis Processyesno
InfoSourcenono
Characteristic InfoObject (Attribute, Text, Hierarchy, or XXL Attribute)yesyes
DataStore Object (advanced)yesyes
Open Hub Destinationnoyes

In a Data Transfer Process in SAP BW/4HANA, the setting Extraction Mode has the following options:

  • Full: extracts data again, and must therefore be used only if the data volume is small and existing records are overwritten in the target. Full can be used to "repair" data of a specific semantic filter that has already been extracted in a previous delta run with a semantically wrong rule in a transformation.

  • Delta: only loads source records that haven't been loaded with this DTP. The first delta execution is an initialization step that retrieves all records.

The Delta DTP

ODP-SAP Data Transfer Process. In the Extraction window, the Delta Process field is highlighted. In the General window, the Source and Extraction mode are highlighted.

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

DTP Settings for Delta Mechanisms

DTP Settings: In the Extraction window, the Filter section is highlighted. In the General window, in the General Extraction Settings, the following options are highlighted: Extraction Settings and Request Selection.

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:

Splitting Data Loads

OptionFilter conditionWhat is processed?
Processing mode on the DTPDetermined based on the key for "Extraction grouped by" and package sizeDifferent packages in one request
Different delta DTPs to the same targetOnly allowed with non-overlapping conditionsSeveral requests
Different full DTPs to the same targetTechnically any filter conditions (but danger of overwriting records)Several requests
Different delta or full DTPs to different targetsAny filter conditionsSeveral requests
Purpose of Inbound Table in Standard DataStore Object.

When you load data from different sources, or in different packages, there might be updates (provided as after images.) If so, it's important to control the sequence of updates to the active table of the target. In these cases, make sure that you use a Standard DataStore Object, because it provides the inbound table as intermediate storage. The stored records can be sorted by request (timestamp), package and record. Thus, there is a clear sequence of the sources and records and the current value is finally persisted.

Selective Full Extraction

Selective Full Extraction. Pseudo Delta: All records of product A are up to date I nBW/4HANA. A new product B is added in the source. Filter for B. Load and Drop: Only records of the current month can be changed. Delete and load again with filter = current month.

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.)

Executing the Delta Process

Initialization and Delta Update

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
Delta Data Transfer ProcessesInitialization Option 1 - Run a Delta DTP:

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.

Full DTP before DeltaInitialization Option 2 - Run Full DTP and Delta Init with No Data. Step 1 - Create DTP1 with Extraction Mode set to Full. Step 2 - Create DTP2 with Extraction Mode set to Delta and the Delta Init w/o Data checkbox selected. Step 3 - Execute DTP2 with Extraction Mode set to Delta, again.

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.)

Initialization and Delta Update Related to the ODQ

Initializing the Delta Process.

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.
Note
The ODQ uses the following tables:
  1. ODQDATA_C: Contains compressed Init request data.
  2. ODQDATA_F: Contains compressed Full request data.
  3. ODQDATA: Contains compressed Delta request data.

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.
Processing a Delta Update.

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.

Working with the ODQ for Delta Management

ODP-SAP ODQ Request: The Extraction Mode and Storage columns are highlighted.

The ODQ differentiates between full load requests, delta initialization requests, and delta loads. If you look for a specific request, you can filter by the type of request. The data is stored in the following different tables:

  • ODQDATA_F for full requests.
  • ODQDATA_C for Initial delta.
  • ODQDATA for delta loads.

The Delta framework guarantees that all records are uploaded only once and in the correct order. If a delta DTP has been executed once for a DataSource, the corresponding queue is initialized. Therefore, data can only be deleted from the ODQ if it has been extracted successfully with this subscriber.

In the operational delta queue (ODQ) of the source system, the data is deleted during delta queue reorganization. The deletion occurs after a certain retention period has elapsed and after the data has been updated to the SAP BW/4HANA system. Therefore, when you delete requests in the InfoProvider, bear in mind that you may not be able to retrieve the data from a request in the source system anymore.

In the following cases, manual work is necessary if there are errors with Delta DTPs:

  • If errors occur during data processing between the source and the first physical SAP BW/4HANA object, delete the last request in the target (if necessary, switch the status manually to "with error".) When you start the DTP again, data is extracted again from the ODQ to SAP BW/4HANA.

  • If they are not yet activated in the target InfoProvider, you can delete requests that were successfully loaded by a delta DTP in any order. The data records contained in the request set for deletion are not requested from the source anymore. However, with the next delta request, the system continues to request new changes. To ensure that the next delta request returns the data records processed by the request that you want to delete, proceed as follows:

    1. Set the retention period for data in the monitor for operational delta queues. The retention period must be long enough to ensure that the processed data from the request to be deleted is still in the source.
    2. Delete the request in the administration view of the target system, together with the newest request and all requests in between.

    The next delta request will then request all data available in the source again, in accordance with the deleted request and subsequent requests.

  • If the data of deleted requests in the delta queue can no longer be retrieved, delete all the delta requests of the same DTP from the InfoProvider. To start delta initialization, execute a new delta request.

To terminate subscriptions for inactive subscribers, perform the following steps:

  1. If the delta queue records change for a subscriber who no longer retrieves data, you can terminate this subscription.
  2. In the Subscriptions view, select the entries for the subscription and choose Delete.
  3. Once the subscriptions have been terminated, the system displays the requests for the terminated subscriptions in the Request view until such time that the requests are removed from the delta queue during a reorganization run.

Log in to track your progress & complete quizzes