Remote Subscription of Type UPSERT

Remote subscriptions of type UPSERT provide a view of the current data in the target table of the replication in SAP BW.

A remote subscription of type UPSERT replicates data in real-time from a remote source object to a table on the local SAP HANA database. Type UPSERT means that all types of data changes (inserts, updates, and deletions) are written to the target table with an UPSERT SQL command. For keys that have been deleted from the remote source object, the statement writes the value D to column SDI_CHANGE_TYPE in the target table. In other cases, the statement writes the value A to column SDI_CHANGE_TYPE. The commit time stamp of the transaction is written to column SDI_CHANGE_TIME.

Use

This type of real-time replication can be used in order to accelerate reporting on an Open ODS view that has a SAP HANA DataSource as its source. It also allows delta extraction and data streaming from a SAP HANA DataSource based on the time stamps in column SDI_CHANGE_TIME.

Dependencies

If you select both remote subscription types UPSERT and INSERT at the same time for a DataSource, the delta procedure is determined by remote subscription type INSERT.

If you only select remote subscription type UPSERT for a DataSource, the delta procedure changes automatically to AIMD After images with deletion indicator. Three further fields are then added to the DataSource:

  • SDI_CHANGE_TIME: Returns the commit time stamp of the last data change

  • SDI_CHANGE_TYPE: Displays the type of the last data change

  • SDI_RECORD_MODE: Translates the change type into corresponding values of the technical characteristic 0RECORDMODE

When the DataSource is activated for remote subscription type UPSERT , four additional SAP HANA- objects are created:

  • The remote subscription

  • A target tabele for the real-time replication

    The primary key of the target table is the same key as the primary key of the remote source object and the virtual table. The additional columns for the delta extraction are not part of the primary key.

  • Two SQL views on the target table – one view for the query access and the full extraction, and another view for delta and delta initialization requests

    The view for the query access and full extraction automatically filters out the deletions (rows with value D in column SDI_CHANGE_TYPE).

In the target table, the additional columns have the following properties:

  • SDI_CHANGE_TIME has data type TIMESTAMP.

  • SDI_CHANGE_TYPE has data type NVARCHAR(1) and value D for deleted record, and A (as in Autocorrect) for inserted or updated records.

  • SDI_RECORD_MODE is not part of the target table. It is calculated in the generated SQL view.

In the SQL view, the additional columns have the following properties:

  • SDI_CHANGE_TIME is converted into a NUMC(21) value by removal of the decimal point from the DECIMAL(14,7) display. The time stamp 2015-11-11 11:11:11, for example, which is displayed in SAP HANA as decimal value 20151111111111.0000000, is converted into the numerical character value '201511111111110000000' so that it can be consumed more easily in ABAP.

  • SDI_CHANGE_TYPE is passed on as CHAR(1). As already mentioned however, value D only occurs in the view for delta extraction, as deleted records are not relevant for query access and full extraction.

  • SDI_RECORD_MODE is calculated for the SDI_CHANGE_TYPE value D as 0RECORDMODE value D (delete image) and as ' ' (blank, after-image therefore) for all other SDI_CHANGE_TYPE values.