HANDLING DELETES DATA IN TARGET TABLE
PURPOSE OF THIS DOCUMENTATION:
This documentation helps us to handle deleted data in target table using MAP OPERATION and TABLE COMPARISION transformations.
- Following are the steps to proceed things in a particular manner.
Below flow is used for Handling delete data in target table.
- Step 1:
Source Data looks like below:
- Step 2:Query Transformation(QRY_IND)
We created the DELETE_IND column by mapping ‘N’ as default which is used to identify deleted data
- Step 3: Table comparison
Table comparison would compare the source and target Table and gives an output containing inserts and updates as Input row type for the MAP_Operation Transformation.
Note: We should enable detect deleted rows option to identify the deleted records in the target table so that we can handle those records in the target table by performing further steps
- Step 4: MAP OPERATION:(INSERT AND UPDATE):
M_I_U map operation operational types should look like below.
- Step 5: MAP OPERATION (DELETE NORMAL) :
M_D_N map operation operational types should look like below where you convert the deletes detected in the Table_Comparission as normals through Map_Operation.
- Step 6: QRY_DEL()
Map ‘Y’ to the DELETE_IND column which would show as an indicator as ‘Y’ for deleted records
- Step 7: MAP OPERATION (NORMAL UPDATE)
- Step 8: We use MERGE TRANSFORMATION so that we can merge the two map operational data
- Step 9: Target table data
I executed the job by deleting 4th & 5th records from a source which would show in the target table with ‘Y’ as delete indicator as below.
Conclusion: we can see deleted records with delete indicator ‘Y’. the records with delete indicator in ‘N’ are exist in target.
I'm reading this one and I'm not quite sure what it's use is. It seems like a lot of work to mark something to deleted. (Modify a table)
Is there a good time to use this instead of just coding the modify to the table? I may have just missed it. Perhaps it's just a simple example for when we want to do something more difficult. Have you used this in a project? If you have, can you share what the requirement was briefly. I know mark record as deleted. But not sure why you did it this way. I know there are a million ways to do one thing in SAP.
I can only agree with you. This approach is unneccesarily complicated. Why do people want to reinvent the wheel? You can achieve exactly the same result with a single Map_Operation and no Merge. Check the SAP Data Services Reference Guide for the purpose and functionality of the mapping tab in the Map_Operation transform.