Design option to delete the records from Info provider with flat file source system.

.  Business Scenario:

     In BW implementations, there is a lot of importance to the data extraction from legacy systems through flat files. We extract the master data and transaction data in structured format from ECC systems to BW. However when we are moving the operational systems from legacy tools to ECC, we can see lot of data extraction from external systems.

  In phase of moving our applications from legacy to ECC systems, we need to extract historical as well as every day transactions to BW through flatfiles. Most of the time, we need to bring the data from legacy applications to BW as full loads until and unless the source applications are capable to generate deltas. When the tools are not capable to provide the deltas as like as ECC systems, we build the DSO layer as the staging layer. The DSO that we use in the staging layer help us to generate the after images and before images to the next layer. But at the same time a single DSO is not alone capable to identify the deleted records from the source files that we receive.

  Here is the solution that helps us to generate the delta records fom staging layer of DSOS to next layer.

The below picture gives the data flow overview:

.  Sample data

In order to understand the business scenario better, the below example is discussed with some sample data and behavior of delta mechanism.

Note: Sample data provided is only for requirement understanding; it does not contain any data from the client (Actual Data).

Sample Transaction Data in the first load:

Step 1: Introduce another DSO ZIS_D601 between PSA and the current DSO (i.e.  Between PSA and ZIS_D701). Here DSO ZIS_D601 is exactly same as like as ZIS_D701, but with one extra info object ZTIMESTMP in the data field.

Step 2: Create transformation from new DSO ZIS_D601 between ZIS_D701. Here we can see 1:1 mapping between source and target, except ZTIMESTMP of source. ZTIMESTMP of source is not mapped to any of the field of target DSO.
Step 3: Create a z- table to store the value of ZTIMESTMP to be inserted in all the records of one data load to ZIS_D601. So here all the data packages of the load will be same value available in ZIS_D601. Here I have created a table ZTVARVC.

Step 4: Create a small program to update an entry in table ZTVARVC with NAME = “INSIGHT_ELIMS_TIMESTAMP”, which will be equal to concatenation of local Date and local Time. Check the appendix A for the code.
Here is the sample record.

Step 5: Create an end routine in the transformation of ZIS_D601 from PSA (the transformation created in step 1), for updating ZTIMESTMP Info object for all the records of ZIS_601. In the end routine, pass the value of above table entry to ZTIMESTMP.
Here is the code that we need to use in the end routine.

Step 6: Create the start routine in transformations from ZIS_D601 to ZIS_D701 (the transformation created in step 2). Here in the start routine, check the value of ZTIMESTMP with the value of table entry in table ZTVARVC. If the value is not same in the record, we need to make record mode equal to “R”.
We need to make filter on ZTIMESTMP in the DTP by using few lines ABAP code to fetch the records which are not equal to the value in the table ZTVARVC. But still we need to make record mode equal to “R” for all these records. Please see Appendix B for the code to be used in DTP.
Here is the code that we need to use in the end routine.

Step 7: Create a process chain as shown below. Here the first step will be ABAP program to update the entry in table ZTVARVC with NAME = “INSIGHT_ELIMS_TIMESTAMP”, which will be equal to concatenation of local Date and local Time.

6.   Appendix A

*&———————————————————————*
*& Report  ZGL_ZTVARVC_UPDATE_INS_ELIM
*&
*&———————————————————————*
*&
*&
*&———————————————————————*

*this program will update the Table ZTVARVC->LOW field value to timestamp Value for Insight Elim.

REPORT  zgl_ztvarvc_timestamp_set.

PARAMETERS ZNAME type ZTVARVC–NAME .

  DATA: lv_timestamp TYPE /bic/oiztimestmp.

  CONCATENATE sy–datlo sy–timlo INTO lv_timestamp.

*else

DATA wa_tab TYPE ztvarvc.

    wa_tab–name = ZNAME.

    wa_tab–TYPE = ‘P’.

    wa_tab–NUMB = ‘1’.

    wa_tab–SIGN = ‘I’.

    wa_tab–OPTI = ‘EQ’.

    wa_tab–low = lv_timestamp.

  modify ztvarvc from wa_tab.

IF SY–SUBRC = 0.

  COMMIT WORK AND WAIT.
ENDIF.

7.   Appendix B

DTP filter on
TIMESTAMP between ZIS_D601 & ZIS_D701.

  DATA: s_ztvarvc TYPE ztvarvc.

  SELECT SINGLE * INTO s_ztvarvc FROM ztvarvc

            WHERE name = ‘INSIGHT_ELIMS_TIMESTAMP’.

  IF sy–subrc = 0.

    DATA temp TYPE /bic/oiztimestmp.

    temp = s_ztvarvc–low.

    l_t_range–iobjnm = ‘ZTIMESTMP’.

    l_t_range–fieldname = ‘/BIC/ZTIMESTMP’.

    l_t_range–sign = ‘E’.

    l_t_range–option = ‘EQ’.

    l_t_range–low = temp.

    APPEND l_t_range.

  ENDIF.