Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
prashujust4u
Participant
3,919
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 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 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:

  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!
8 Comments
adash
Explorer
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
devaanand
Explorer
Thanks Prashanth for your  detailed explanation on identifying the deleted records in the source system tables.

Very much helpful.

Regards,

Deva
prashujust4u
Participant
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
prashujust4u
Participant
0 Kudos
Thank you for your comment!
parivazhagan
Newcomer
0 Kudos
Hi Prashant,

First of all, many thanks for informative blog with all steps involved!  I liked the way you have structured your blog. I am sure it helps others who are in need just like me.

 

If you don't mind me saying, I believe the below piece of code should refer the result structure <ls_res_record>.  Kindly check and update it for anyone who adopt the code. I have come across a problem, and it started working after adjusting the code.
ASSIGN COMPONENT 'IUUC_OPERAT_FLAG' OF STRUCTURE <ls_src_record> TO <lv_operation>.

 

Best regards,

Prasath
prashujust4u
Participant
0 Kudos
Hi Prasath,

Thanks for letting me know! The deletion indicator 'D' should be present in the source record - hence the reason of reading IUUC_OPERAT_FLAG from <ls_src_record>. We currently have this running in our SLT system - so I am curious as to why it did not work for you.

However the deletion indicator can also be read from the result structure since its a 1:1 map. In  your case I am assuming you are reading this deletion indicator from the result structure, then modifying it and then writing it back to the result structure itself. If yes - that will work too!

Glad to know it helped you.

 

Best Regards,

Prashanth D'souza
JoergBoeke
Explorer
0 Kudos
Hi Prashanth,

first of all...it's a great blog and almost everything is working for me.

I am trying to update two fields

  • 1st  the timestamp

  • 2nd the updatemode (I,U.D)


when a record has been inserted and updated as well as deleted.

The Update and Insert work fine, in case of deletion I get the deleted(updated) record with blanked columns except the keys as expected.

But...

I do not get the 'D' from <LV_OPERATION> into my update field. It will appear as 'U' (Updated), I guess due to the fact that I set the Operation to 'U'.

I guess setting it ( see code below) to 'U' it will run through my:

if sy-subrc and <lv_operation> 'U'

code caused by 'D' code, and then just use the 'U' and timestamp for deletion

Any idea how I can use the operation ( see below) to set my insertion column 'REC_MODE' to 'D'?
assign component 'IUUC_OPERAT_FLAG' of structure <ls_tar_record> to <lv_operation>.
if sy-subrc and <lv_operation> 'D'.
  " If Deletion Indicator is detected change the result operation to an Update and set
  "  update time stamp to recognizable value
  "assign component 'IUUC_OPERAT_FLAG' of structure to <lv_operation>.

  assign component 'IUUC_OPERAT_FLAG' of structure <ls_tar_record> to <lv_operation>.
  <lv_operation> 'U'.
  if sy-subrc 0.
    assign component 'INSERT_TS' of structure <ls_tar_record> to <lv_delete>.
    if sy-subrc 0.
      get time stamp field <lv_delete> .
 
    endif.
    assign component 'REC_MODE' of structure <ls_src_record> to <lv_rec_mode>.
    if sy-subrc 0.
      if <lv_rec_mode> is assigned.
        <lv_rec_mode> 'D'.
      endif.
    endif.
  endif.

endif.
mcnonld
Explorer
0 Kudos
Hi Prashant,

First of all, many thanks for informative blog with all steps involved!

I appreciate it if you  could share the 3rd approach. I cant't open the link.

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

I cant't open the link.

Thanks !!

Best regards!

 

YAOBIN

 
Labels in this area