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 important SLT solution by which you can prevent Hard Deleted records from your Source System triggering the delete in your target HANA database and then identify these records.
Requirement:
Need to preserve S/4HANA table hard-deleted records in Enterprise HANA.
Identify these records in Enterprise HANA by setting IS_DELETED = 'Y'.
Challenge: SLT Replication by default will ensure that both source and target data records match all the time. This means that even deletion (of record) in the Source will be passed on to the Target system and cause a deletion in Target - to ensure data record count matches exactly between Source and Target tables.
We seek to override this default behaviour!
Architecture:
Possible Solutions:
We have 3 approaches:
Approach 2 will not be suitable because we need to identify the deleted records.
Approach 3 will force triggers to be created on the HANA table, requires extra table for storing deleted records and introduces complexity in the landscape.
Approach 1 is the most suitable for our requirement. We need to develop a re-usable generic ABAP Include that can be plugged in to ANY table that needs to preserve its deleted records in the Target.
Solution:
1. Create a generic ABAP Include Program in SE38. In my case I have used ZTABLE_PREVENT_DELETES.
2. Add IS_DELETED column to the Table Structure in Advanced Replication Settings
3. Add a Field Related Rule for this Table to populate the IS_DELETED. Reference the ABAP Include here and pass Table Name as a parameter (with single quotes).
4. Stop and then Start the Table Replication in SLT.
5. After the Initial Load completes and Table switches to 'Replication' mode - try deleting a record from the Source System Table. The HANA Table should preserve the deleted entry and have IS_DELETED = Y marked on that row.
Points to Consider:
Last but not the least I would like to say that I have stood on the shoulders of giants to build this generic solution. Also a big shout out to my Project Team who were very patient with all the innumerable test cases that we ran to ensure this actually works!
Feel free to ask any questions and I would be happy to assist. Thank You and wish you a great day!
Requirement:
Need to preserve S/4HANA table hard-deleted records in Enterprise HANA.
Identify these records in Enterprise HANA by setting IS_DELETED = 'Y'.
Challenge: SLT Replication by default will ensure that both source and target data records match all the time. This means that even deletion (of record) in the Source will be passed on to the Target system and cause a deletion in Target - to ensure data record count matches exactly between Source and Target tables.
We seek to override this default behaviour!
Architecture:
System Architecture
Possible Solutions:
We have 3 approaches:
- SLT: Create a Transformation Rule to handle the Delete Images in Source Side and convert them to Update Image. Then populate a column (eg: IS_DELETED='Y') to identify these hard deleted records.
Reference: https://blogs.sap.com/2014/03/06/how-to-avoid-a-deletion-of-a-record/ - SLT: Ignore the delete trigger. Using this approach the Delete Images will not be passed to HANA – hence you will retain all the records. However you will not be able to identify the deletions on HANA side.
Reference: https://launchpad.support.sap.com/#/notes/0002850265 - HANA: You can create triggers on the HANA Table itself. This requires a New Table to be created. Before the deletion occurs on the replicated table – the trigger will fire and copy the OLD ROW to the new table. Then the deletion will occur on the replicated table.
Reference: https://archive.sap.com/documents/docs/DOC-45991
Approach 2 will not be suitable because we need to identify the deleted records.
Approach 3 will force triggers to be created on the HANA table, requires extra table for storing deleted records and introduces complexity in the landscape.
Approach 1 is the most suitable for our requirement. We need to develop a re-usable generic ABAP Include that can be plugged in to ANY table that needs to preserve its deleted records in the Target.
Solution:
1. Create a generic ABAP Include Program in SE38. In my case I have used ZTABLE_PREVENT_DELETES.
*&---------------------------------------------------------------------*
*& Include ZTABLE_PREVENT_DELETES
*& This program prevents hard deletes in the source system from causing
*& deletion in HANA database. It also sets IS_DELETED column to 'Y' to
*& help identify the hard deleted records from the source system.
*&---------------------------------------------------------------------*
DATA: lv_src_name(30) TYPE c,
lv_res_name(30) TYPE c.
field-symbols: <lv_operation> type any,
<lv_delete> type any,
<ls_src_record> type any,
<ls_res_record> type any.
" This is the SLT Source Record structure being assigned to lv_src_name
CONCATENATE '<wa_s_' i_p1 '>' INTO lv_src_name.
ASSIGN (lv_src_name) TO <ls_src_record>.
" This is the SLT Result/Target Record structure being assigned to lv_res_name
CONCATENATE '<wa_r_' i_p1 '>' INTO lv_res_name.
ASSIGN (lv_res_name) TO <ls_res_record>.
" We are assigning SLT field IUUC_OPERAT_FLAG of this row to lv_operation
ASSIGN COMPONENT 'IUUC_OPERAT_FLAG' OF STRUCTURE <ls_src_record> TO <lv_operation>.
" If Deletion Indicator is detected change the result operation to an Update and set IS_DELETED=Y
IF SY-SUBRC = 0 AND <lv_operation> = 'D'.
ASSIGN COMPONENT 'IUUC_OPERAT_FLAG' OF STRUCTURE <ls_res_record> TO <lv_operation>.
<lv_operation> = 'U'.
ASSIGN COMPONENT 'IS_DELETED' OF STRUCTURE <ls_res_record> TO <lv_delete>.
IF sy-subrc = 0.
<lv_delete> = 'Y'.
ENDIF.
ENDIF.2. Add IS_DELETED column to the Table Structure in Advanced Replication Settings
Addition of IS_DELETED column
3. Add a Field Related Rule for this Table to populate the IS_DELETED. Reference the ABAP Include here and pass Table Name as a parameter (with single quotes).
Add Field Related Rule for IS_DELETED
Add the code for Include and supply Parameter
4. Stop and then Start the Table Replication in SLT.
5. After the Initial Load completes and Table switches to 'Replication' mode - try deleting a record from the Source System Table. The HANA Table should preserve the deleted entry and have IS_DELETED = Y marked on that row.
Deleted Source Record is preserved IS_DELETED = Y
Points to Consider:
- The deleted entry will be preserved in HANA however the non-key fields will be blank. This is because we are overriding the Deletion Image (D). The Deletion Image record in the Logging Table contains only the Key Fields - hence when we change the image to Update (U) only the key fields will be preserved.
- There are few blogs which mention that Event Related Rule (and not Field Related Rule) needs to be created to handle this deletion logic. I have personally tried both and both work. However if you want to make the code generic/re-usable (which everyone should) you will need to pass the Table Name as a parameter - and I have noticed that it worked only in the case of a Field Related Rule.
- Why do we need to Stop and Start the table replication? This is to ensure that the Migration Object is re-created with the latest table structure and code.
- During activation of the ABAP Include you will get errors and warnings - some stating that the <wa_r/s> fields are not recognized OR 'i_p1' is not recognized. IGNORE these errors and ACTIVATE anyway. These errors/warnings simply point to the fact that at design time ABAP is unable to identify where these structures and parameters are defined. At runtime (when table is in replication mode) this Include will be automatically plugged in to the Migration Object Function Group and the values of the <wa_r/s> fields and parameters will be automatically fetched from the Advanced Replication Settings.
Last but not the least I would like to say that I have stood on the shoulders of giants to build this generic solution. Also a big shout out to my Project Team who were very patient with all the innumerable test cases that we ran to ensure this actually works!
Feel free to ask any questions and I would be happy to assist. Thank You and wish you a great day!
- SAP Managed Tags:
8 Comments
