Mass Update of SLT Advanced Replication Settings

[prashujust4u]

Hello Everyone! I am an SAP HANA Senior Consultant working closely with SAP SLT to bring data into Enterprise HANA. Today I want to share with you an interesting trick which you can use to update the SLT Advanced Replication Settings for multiple tables in a given configuration.

Requirement: Need to add a new column DW_LOAD_DATE (containing system timestamp) for all tables replicating from S/4HANA to Enterprise HANA. This will enable us to understand the exact timestamp when a particular record was replicated to HANA.

Challenge: Currently we have more than 180 tables in active replication. Adding DW_LOAD_DATE manually to each table in the configuration is very time consuming, error prone and most of all boring.

Possible Solutions:

1. Manual Addition


2. Addition of field via Template Maintenance


3. Mass Update by editing the Exported Advanced Replication Settings Files.

Chosen Solution:

The third option was chosen because it provided a lot of flexibility – each table could be handled independently, and all tables settings can be added/edited easily in excel.

Caution:

Please ensure to take a backup of the Advanced Replication Settings in your local system before you begin incase you inadvertently introduce errors while maintaining the fields in the excel. You can revert to the original config by importing this backed up file.

 

Step 1:

Add DW_LOAD_DATE to any one Table manually in the Advanced Replication settings (TCode: LTRC > GoTo > Advanced Replication Settings). In my case I have chosen ACDOCA table.

Table Settings in Advanced Replication Settings

Add DW_LOAD_DATE field

You will need to identify the Position at which you wish to place the DW_LOAD_DATE column. In my case since these tables were already replicating I simply had to run a SQL on the HANA Database on these tables to identify the position where this field should be placed for a given table.

SELECT TABLE_NAME,COUNT(COLUMN_NAME)+1 AS COLUMN_POSITION
FROM SYS. TABLE_COLUMNS
WHERE TABLE_NAME IN ('ACDOCA');

Map field to TIMESTAMP

Step 2:

Maintain the Rule for this table in Rule Assignment

Rule Settings

Code for adding DW_LOAD_DATE

 

Step 3:

Export the settings to local desktop. File > Export All Settings.

Step 4:

Edit the following csv files to add the DW_LOAD_DATE settings for all the required tables. This can be now very easily done since there will already be an entry for the table you added manually in Step 1. You can use this as reference to populate the values for all the required columns in the csv file.

CSV Files to update table settings

Eg of entries in one csv file

Step 5:

Save the file in the exact same csv format and after you have completed adding all the tables - add all the csv files again to single ZIP file.

Step 6:

Import the ZIP file into the Advanced Replication Settings. File > Import All Settings

Step 7:

You will receive a prompt that the settings will overwrite all the existing settings. Accept this and you should see the tables that you have added/edited in the excel populated under the Advanced Replication Settings.

Conclusion:

Going through the standard screens for adding the DW_LOAD_DATE to each table manually consumes a lot of time AND requires a ton of patience - this method is a simple alternative to add this field/setting to a lot of tables and only requires maintaining the involved csv files.

The other route you could try is doing this via Template Maintenance in the Advanced Replication Settings. However I found more control of handling the code and table structures using the csv method hence I chose this option.

Feel free to ask any questions and I would be happy to assist. Thank You and wish you a great day!