Mass Update of SLT Advanced Replication Settings
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.
- Manual Addition
- Addition of field via Template Maintenance
- Mass Update by editing the Exported Advanced Replication Settings Files.
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.
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.
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.
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’);
Maintain the Rule for this table in Rule Assignment
Export the settings to local desktop. File > Export All Settings.
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.
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.
Import the ZIP file into the Advanced Replication Settings. File > Import All Settings
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.
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!
Thanks for the article. I got some clients that always complain about replication time and this way we can know the exact time when the data reach the target
A new initial load is necessary?
Yes you are correct - new Initial Load would be necessary to populate the DW_LOAD_DATE for the records already replicated in Target HANA system.
When I click on 'Table Settings in Advanced Replication Settings', I don't get the screen you are getting.
How did you get to the screen which displays following details.
Table Structure, Table Settings, Mapping Values.
You first need to right click on Table Settings and add a table there. In my case I have added ACDOCA. Once you do that you should see the same screen as displayed.