(Glue-2302) Delta Mechanisms

Delta mechanisms (deltas) specify how data is extracted. Currently, the delta functionality is supported only for the extraction from the SAP system to storage.

Deltas serve only as a data source definition and do not specify higher purpose processes like replication, offloading, etc. You may choose among the following delta types:


Full extraction

This type of extraction has no restrictions or further specifications of the source; all data is transferred to a target.

Timestamp

Select the Timestamp field attribute, based on which data is extracted. After each extraction, the highest value of the selected timestamp attribute is stored. In the following extractions, only records with an attribute value higher than the stored value are transferred. The first load is always a Full extraction.

Example

To simplify, in this example we use the only time to express timestamp values (We don't use the complete timestamp with date and time). Therefore, all mentioned times below have the same day in the complete timestamp format.
Before the first extraction, the source table contains values 09:13, 11:10 and 08:15 in the particular column.
By the first extraction all records are transferred and only the value 11:10 is stored as the highest value.
Further records with the values 09:50, 13:10 and 15:05 in the particular column are inserted into the source table. The source table then contains the records with the values 09:13, 11:10, 08:15, 09:50, 13:10 and 15:05.
By the next extraction, only the records with the values 13:10 and 15:05 are transferred and the value 15:05 is stored as the highest value.
Records with the value 09:50 aren't transferred because this value is lower than the stored value 11:10.

Following data elements are supported: TIMESTAMP, TIMESTAMPL, NUMC (length 14).

Additionally, you can set Timestamp offset (in seconds) - value of time shift for the next delta load in seconds. This will help to lower the risk that the new records coming to the source table during extraction will not be captured by delta or delta init loads.

Example:

During the delta or delta init load, the source table was updated with a new record. This record is not part of the extracted data, as it was saved after the data was selected from the source, but at the same time, it will not be transferred with the next delta load due to its timestamp value. For such cases, it's useful to have a safety internal to ensure that all records are being captured by timestamp delta.

Date

Select Date field attribute, based on which the data is extracted, same as by the Timestamp delta type. Similar to the Timestamp delta, the highest value of the selected attribute is stored after each extraction. However, with the difference that records with the values of the current date and higher are ignored. This ensures that data is extracted from a complete day and not only from its part. This behavior is also applied to the first, full extraction.

Example

In this example, the current date is the 13.04.2018.
Before the first extraction, the source table contains the values 11.03.2018, 13.04.2018 and 14.04.2018 in the particular column for Date delta.
By the first extraction, records with the value 11.03.2018 are transferred and the value 11.03.2018 is stored as the highest value.
Records with the value 13.04.2018 aren't transferred, because the current day has the same value. Records with the value 14.04.2018 aren't transferred, because this value is higher than the current day (14.04.2018 > 13.04.2018).
The next day, which is the 14.04.2018, 14 records with the values 10.03.2018, 29.03.2018 and 20.04.2018 are inserted into the source table. The source table then contains the records with the values 10.03.2018, 11.03.201829.03.2018, 13.04.2018, 14.04.2018, and 20.04.2018 .
By the next extraction, only records with the values 13.04.2018 and 29.03.2018 are transferred and the value 13.04.2018 is stored as the highest value.
Records with the value 10.03.2018 aren't transferred, because the value is lower than the stored one (10.03.2018 < 11.03.2018). Records with the value 14.04.2018 aren't transferred, because the current day has the same value (14.04.2018 = 14.04.2018). Records with the value 20.04.2018 aren't transferred because the value is higher than the current day (20.04.2018 > 14.04.2018).

Following data types are supported: DATS 

Value delta

Select a Value field attribute that contains numeric values. Based on this Value field data is extracted, similar to the Date and Timestamp delta. After each extraction, the highest value of the selected field is stored. In the following extractions only records with a value higher than the stored value are transferred. The first load is always a Full extraction.

Distinct value delta

Selection is based on values of already extracted data in a specified delta field. To configure this mode properly you must specify this field. Only entries with new distinct values from this field are transferred during the next execution of the extractor. 

Distinct value delta does not support cluster tables.

Following data types are NOT supported:

  • any RAW datatype
  • long character-like types (STRG, VARC, LCHR)

Delta process for Distinct value delta

  1. Value delta extraction is based on a delta field that you choose. After you select the delta field, the system checks all the distinct values in the particular delta field and transfers the whole records with these distinct values to the target system. The distinct values of the delta field are then stored in a blacklist.

As an example, the table below contains the fields First name, Last name, and Employer ID. The user chooses the 'Employer ID' as the delta field. The system transfers the records (record 1 until 5) with distinctive values (121 until 124) in the field 'Employer ID' to the target system and afterward, it stores these values in the blacklist.

     2. By the next extraction, the system checks all the values in the delta field and selects only values that are not on the blacklist. By the delta execution, the system transfers only records with these distinct values which are not included in the blacklist to the target system and stores the new distinct values of the delta field in the blacklist.

Two more records were added to the above table. The system selects in the delta field 'Employer ID' red marked records with the values which are not in the blacklist (values 125 and 126) and transfers the whole records (records 6 and 7) with these distinct values to the target system. Afterward, the system stores these new distinct values (125,126) to the blacklist.
This extraction process is repeated the same way again by the next Value delta extraction.


Choose this option, if you don't want to transport any historical data but only actual data, which will be generated after the initial delta run.

In the case a new record is inserted into a table with a field value already present in our blacklist table, it will not be extracted. We recommend choosing a delta field that works for your purposes. For example, if you choose in the above example the field 'Last name' as the delta field, by the next extraction the record number 7 'Anushka Lee 126' will not be transferred during the execution of the extractor. 

Trigger delta 

With trigger delta, a special database table keeps a record of new database records, as they're being added. Every time a DML operation is performed on a given table, a procedure stores information about the newly inserted data to help determine the delta.

Please Note

Delta-based on a trigger doesn't support string fields in a table. If a table contains a string field and this field should be activated with the extractor, an error message appears and the extractor isn't activated.


Please Note

Trigger delta is supported for the following databases:

  • MaxDB
  • Oracle
  • HANA
  • DB2(DB4, DB6)
  • MSSQL

BW Delta Mechanisms

Change log delta

This delta reads data based on a Change log table of an InfoProvider. The initial full load reads data from the Active table of an InfoProvider. Then the delta is automatically initialized with activation request values currently available in the Change log table. Next load reads data from the Change log table, reading only records with activation request values that weren't loaded yet. That means only records with a new request values are transferred during the next execution of an extractor.

In the current version only the following InfoProvider types are supported:

  • Standard DSO

Delta properties

Properties tab contains general information about the extractor and the delta details:

  • Delta type
  • Status - actual status of the extractor (Saved or Active)
  • Source - the name of a source InfoProvider
  • Last extraction - date and time of the last extraction
  • Full load - indicates whether a full load was executed

The Properties section shows

  • Truncate target table - if you select this checkbox, all data in the target table is deleted before new extraction.

Set up values that will be exported from the Change log

Select at least one Record mode value which should be exported from the Change log table of the DSO. Record mode is a SAP functionality. You can find more information in the section "0RECORDMODE Values" of the blog Recordmode and delta type concepts.

Delta Tools - Properties tab

Manage

In Manage you may execute data extractions.
Perform delta run executes a standard delta extraction.
Perform full load executes a full load even if it was already performed. By the first full load, the Change log delta is
 initialized. If you execute the full load repeatedly, the delta values won't be updated.
Reset delta value button resets delta value.

Request delta

Request delta reads data based on DTP requests loaded into an InfoProvider. During the initial full load, all data is read from an InfoProvider and the delta is initialized with the highest request value. Then the next load reads data with DTP request values greater than the current maximum. After this, the maximum DTP request value is updated to a new value.

In the current version only the following InfoProvider types are supported:

  • InfoCubes

Delta properties

Properties tab contains general information about the extractor and the delta details:

  • Delta type
  • Status - actual status of the extractor (Saved or Active)
  • Source - the name of an InfoProvider
  • Last extraction - date and time of the last extraction
  • Full load - indicates whether the full load was executed

The Properties section shows

  • Highest loaded value - Greatest loaded value of an InfoProvider DTP request.

Manage

Manage functionality isn't currently supported for the request-based delta extraction. 

0RECORDMODE Values: 

Technical flag with additional information about a particular record change log table.

Empty space : The record provides an after image. The status of the record is transferred after it has been changed or after data has been added.

X: The record provides a before image. The status of the record is transferred before it has been changed or deleted. All attributes for the record that can be aggregated (key figures) must be transferred with a reversed plus/minus sign. These records are ignored in a non-additive (overwriting) update of a DataStore object. The before image complements the after image.

A: The record provides an additive image. This provides the record with differences for all the numeric values that are available. The record can be updated to an InfoCube without restrictions but requires an additional update to be made to a DataStore object.

D: The record must be deleted. Only the key is transferred. This record (and therefore the DataSource too) can only be updated to a DataStore object.

N: The record provides a new image. The content of this record is equivalent to an after image without a before image. A new image should be transferred instead of an after image when a record is created. The new image complements the reverse image.

R: The record provides a reverse image. The content of this record is equivalent to a before image. The only difference occurs when updating a DataStore object: An existing record with the same key is deleted.

Y: Can only be updated to a DataStore object.