Skip to Content
Author's profile photo Former Member

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:

/wp-content/uploads/2013/10/pic1_300248.png

.  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:

/wp-content/uploads/2013/10/pic2_300249.png

/wp-content/uploads/2013/10/pic2_300249.png

/wp-content/uploads/2013/10/pic2_300249.png

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.

/wp-content/uploads/2013/10/pic2_300249.png

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.

/wp-content/uploads/2013/10/pic2_300249.png

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.

/wp-content/uploads/2013/10/pic2_300249.png

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.
/wp-content/uploads/2013/10/pic2_300249.png

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.

/wp-content/uploads/2013/10/pic2_300249.png

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.


/wp-content/uploads/2013/10/pic2_300249.png

 
 

Company Code

 

Profit Center

 

G/L Account

 

Debit/Credit Amount

 


 
 
 

/wp-content/uploads/2013/10/pic2_300249.png

/wp-content/uploads/2013/10/pic2_300249.png

Company Code

 

Profit Center

 

G/L Account

 

Debit/Credit Amount

 

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 ZTVARVCNAME .

  DATA: lv_timestamp TYPE /bic/oiztimestmp.

  CONCATENATE sydatlo sytimlo INTO lv_timestamp.

*else

DATA wa_tab TYPE ztvarvc.

    wa_tabname = ZNAME.

    wa_tabTYPE = ‘P’.

    wa_tabNUMB = ‘1’.

    wa_tabSIGN = ‘I’.

    wa_tabOPTI = ‘EQ’.

    wa_tablow = lv_timestamp.

  modify ztvarvc from wa_tab.

IF SYSUBRC = 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 sysubrc = 0.

    DATA temp TYPE /bic/oiztimestmp.

    temp = s_ztvarvclow.

    l_t_rangeiobjnm = ‘ZTIMESTMP’.

    l_t_rangefieldname = ‘/BIC/ZTIMESTMP’.

    l_t_rangesign = ‘E’.

    l_t_rangeoption = ‘EQ’.

    l_t_rangelow = temp.

    APPEND l_t_range.

  ENDIF.

Assigned Tags

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

      Good workaround. We ve similar scenario will try to implement it.. Thanks for sharing.

      Regards

      Shabnam

      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      After a long time had a good document to read..I really appreciate the time and effort you put in to make this doc.

      Something concrete rather than presenting just the Basics of BW.

      Thanks for sharing this with us..Expect more such documents and blogs from your side.

      Regards,

      AL

      Author's profile photo Sandeep a
      Sandeep a

      Thanks for sharing this with us

      Regards

      Sandeep


      Author's profile photo Raman Korrapati
      Raman Korrapati

      Good presentation with real scenario. Thanks for sharing Anil.