Scenario
During this learning journey, master data and transactional data have been loaded into SAP BW/4HANA. As SAP BW/4HANA runs on an SAP HANA database, it's needed to understand delta merge in SAP HANA and how it can be triggered from SAP BW/4HANA.
Concept of Delta Merge in SAP HANA
The column store in an SAP HANA database uses efficient compression algorithms that keep all relevant application data in memory. Write operations on this compressed data are costly, as they require reorganizing the storage structure. Updating and inserting data into a sorted column store table is a very costly activity, as the sort order has to be regenerated and thus the whole table is reorganized each time.
SAP has addressed this challenge by separating these tables into a main storage (read-optimized, sorted columns) and delta storage (write-optimized, non-sorted columns or rows). All changes go into a separate area called the delta storage. The delta storage exists only in main memory. Only delta log entries are written to the persistence layer when delta entries are inserted.
So, data modifications are initially saved in a delta storage that is optimized for write access. However, most of the data is saved in a highly compressed format in the main storage, which is optimized in terms of required memory space and read performance. A delta merge is used to transfer modifications from the delta storage to the main storage.
First, an asynchronous check is performed to see whether a delta merge is required. If a threshold value is exceeded, the merge is carried out in the delta storage. When a read access is executed, the data is read from the main storage and the delta storage, and the results are merged together.
The figure above shows the different phases of the delta merge:
Before Merge: All write operations go to storage Delta1 and read operations read from storages Main1 and Delta1
During Merge: While the merge operation is running, all changes go into the second delta storage, Delta2. Read operations read from the original main storage, Main1, and from both delta storages, Delta1 and Delta2. Uncommitted changes in Delta1 are copied to Delta2. The content of Main1 and the committed entries in Delta1 are merged into the new main storage, Main2.
After Merge: After the merge, Main1 and Delta1 storages are deleted.
The picture above shows how, on the level on a table in an SAP HANA database, the memory consumption in the main storage increases and how the memory consumption in the delta storage decreases after the delta merge.
Options in SAP BW/4HANA Related to SAP HANA Delta Merge
SAP BW/4HANA runs on an SAP HANA database and delta merge is a database activity. However, there are options available in SAP BW/4HANA related to the delta merge.
There are two options available in SAP BW/4HANA to trigger the delta merge:
Enable Trigger Database Merge in the Data Transfer Process.
The Data Transfer Process has an Update tab that contains the Trigger Database Merge checkbox. This controls the delta merge, once the DTP request has been successfully processed. This checkbox is selected in the default setting.
Trigger the delta merge via a process type.
In exceptional cases only, which result in load distribution problems, SAP recommends deselecting the checkbox in the DTP and using the Trigger Delta Merge process type to trigger the delta merge. An example of this is an object that data is loaded into from multiple sources. The delta merge check is only performed at the end of the entire loading process.
Note
Make sure that the DTP or process type always triggers a delta merge. If no delta merge takes place, the data remains in delta storage. Over time, this results in storage problems and has a negative impact on read performance.