Skip to Content
Technical Articles
Author's profile photo Prashanth Dsouza

Preserve and Identify Source Deleted Records in HANA via SLT

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:

System%20Architecture

System Architecture

Possible Solutions:
We have 3 approaches:

  1. 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/
  2. 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
  3. 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%20of%20IS_DELETED%20column

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%20Field%20Related%20Rule%20for%20IS_DELETED

Add Field Related Rule for IS_DELETED

Add%20the%20code%20for%20Include%20and%20supply%20Parameter

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%20Source%20Record%20is%20preserved%20IS_DELETED%20%3D%20Y

Deleted Source Record is preserved IS_DELETED = Y

 

Points to Consider:

  1. 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.
  2. 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.
  3. 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.
  4. 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!

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ashutosh Dash
      Ashutosh Dash

      Hi Prashanth,

      Appreciate you for writing a very detailed explanation. I would like to understand the Business Use case behind this. It seems odd to me that a Business User will delete something in source system and still want to see the record in the Reporting coming from Hana. Also, from solution point we only get the key fields retained in Hana. So, will this be any value add for the users in the reports just to see a key field, for example a Document Number with out any KPIs?

       

      Regards

      Ashutosh

      Author's profile photo Prashanth Dsouza
      Prashanth Dsouza
      Blog Post Author

      Hi Ashutosh,

      Thank you for this excellent question!

      To give an example of a business use case - consider a scenario where you are pushing data from the HANA sidecar to further upstream systems (eg: Azure/AWS Data Lake).

      Most of the times for this you would be using a custom framework/solution for transferring delta data from HANA to a data lake. In this case if a record was deleted in the source - both source and HANA would show the same number of records (default behaviour of SLT) HOWEVER how would we identify that we need to delete the record from our Data Lake? In this case Data Lake record count would be higher than HANA DB & Source.

      This is where our solution kicks in. With the IS_DELETED identifier and assuming you already have a Data Record Update Timestamp Column in HANA DB - we will be able to identify the deleted record from the delta data and delete it from the Data Lake as well.

      So in essence from a reporting standpoint - though we may not be interested in reporting on deleted records however this solution ensures that we have the capability to identify such records and ensure that the upstream/downstream systems are kept in sync.

      Hope this explains!

      Thank You!

      Prashanth D'souza

      Author's profile photo Deva Anandakrishnan
      Deva Anandakrishnan

      Thanks Prashanth for your  detailed explanation on identifying the deleted records in the source system tables.

      Very much helpful.

      Regards,

      Deva

      Author's profile photo Prashanth Dsouza
      Prashanth Dsouza
      Blog Post Author

      Thank you for your comment!