Skip to Content

Introduction :

A Data Store Object(DSO) stores data (master and transaction data )in flat database table (atomic level) .There are mainly 3 types of DSO .

     1.Standard DataStore Object 

     2.Write-Optimized DataStore Objects .

     3.DataStore Objects for Direct Update .

For different types of DSO different tables are maintained .

     1.Standard DataStore Object (New, Active and Change Log tables )

     2.Write-Optimized DataStore Objects (WDSO) (Active table only ) .

     3.DataStore Objects for Direct Update (Active table only ) .

This blog is about WDSO active table data deletion .The benifits of using a WDSO in EDW layer are as follow.

  • Write-Optimised DSO is basically used to stage data quickly for further process and as we don’t need to activate data after loading additional effort of generating SIDs, aggregation and data-record based delta is not required .
  • A unique technical key is generated for the WDSO have 3 more fields than standard DSO (Request ID 0REQUEST, Data Package ID 0DATAPAKID,Record Number 0RECORD).
  • The data granularity is more in WDSO as it stores whole data (duplicate records also as technical key is unique) without aggregation .
  • The immediate availability of data is main reason to use WDSO as before applying complex business logic in data it get collected in one Object (WDSO ) at document level .
  • SAP recommend use of WDSO at Acquisition layer (from PSA to WDSO) for efficient staging of data in BW .

Business Flow :

We follow a 3 layer standard architecture in our project .

We have data flow as following  :

Reporting layer      ->      Cube and Multiprovider 

Integration layer    ->      Standard DSO

Acquisition layer    ->      WDSO(Write-optimized DataStore object)

Scenario:

1.  Every day a huge amount of data get loaded for reporting purpose (Tera-Data Loads).

2.  WDSO (Write-optimized DataStore object)is only used for staging purpose of Tera data.

3.  Due to space issues we want to delete unwanted data from system and this data is already got updated to targets so have no use .

Our requirement :

  • We have space issue in production system.
  • For cleaning up space in Production system we decided to delete all data loaded in WDSO (Write-optimized DataStore object) older than 60 days from today.
  • This include  –

   

1.  Deletion of data from active table of WDSO (Write-optimized DataStore object).

2.  Deletion of request from Manage Tab of WDSO (Write-optimized DataStore object).

  • These load requests are already updated in target and we don’t want to delete data from target .

Solution:

  • To achieve this we created a table and manually made entries for all WDSO name and  their active table names
  • After this we have created a report program which is looping through that table and deleting all data older than 60 days .
  • We have also done deletion of all WDSO (Write-optimized DataStore object) load request from RSICCONT table by matching WDSO (Write-optimized DataStore object) name and Timestamp less than 60 days.

   

Steps :

  1. Go to SE11 TCODE in BW and create a table to store WDSO (Write-optimized DataStore object) details .
  2. After table creation , Go to Utilities ->Table Contents ->Create Entries and create entries (Click on new entries and add entries )with details of WDSO (Write-optimized DataStore object) you want to work with.
  3. Now Go to TCODE SE38 in BW system .
  4. Create a report program and write following code and execute the program .
  5. Go to WDSO (Write-optimized DataStore object) and check Manage tab and active table .All request before 60 days are got deleted .

       

Sample Report Program (ABAP Code) :

 *&———————————————————————*

*& Report  ZWDSO_SEL_DELETE
*&*&———————————————————————*
*& Report program to delete data loads before 60 days of system date   *&from Write-optimized DataStore object (WDSO) .

*&———————————————————————*

report  zwdso_sel_delete.

*Name of table to store WDSO (Write-optimized DataStore object) information with which we are working
tables : zwdso_del .

data :it_zwdso_del type standard table of zwdso_del with header line ,
      wa_zwdso_del like line of it_zwdso_del .
data :v_start_time  type c length 17,
      v_tab type /saptrx/tablename,
      v_start_date like sy-datum .

select * from zwdso_del into table it_zwdso_del.

* Get timestamp for 60 days before of current system date
*———————————————————————-*
v_start_date = sy-datum.
subtract 60 from v_start_date.
concatenate  v_start_date   sy-uzeit  into v_start_time.

*delete data for 60 days before of current system date from Active tables of DSO’s 
**———————————————————————-*

loop at it_zwdso_del into wa_zwdso_del.

 

*this will delete request from Manage tab 
   delete from  rsiccont where icube = wa_zwdso_del- wdso_name and timestamp LT v_start_time .

*this will delete data from active table 
   delete from (wa_zwdso_del-wdso_table) where rstt_tsmp lt v_start_time.
endloop.

*&————————————————————————–*
*& End of Program ZWDSO_SEL_DELETE
*&————————————————————————–*

Advantages of this approach : 

  • This program will only delete data from WDSO while data updated to targets will have no effect .
  • In normal scenario a request can’t get delete without deleting updated data from target so we used this approach .
  • In our WDSO if we have no field that have information of load date , we can’t do selective deletion based on number of days  .
To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Klaus Kuehnle
    Dear Jaya,

    In the settings of Write Optimized DataStore Objects, there is a flag called “Check Delta Consistency”; if this flag is cleared, then requests, although they are already transferred to some connected data target, can be deleted in compliance with official recommendations. So, there is no need for tricks. If, however, this flag is set, then the user who set this flag apparently wants the system to take care that no request that is already transferred to some connected data target gets deleted. In this case, it appears unwise to fool the system by deleting requests directly from the database table.

    Best regards
    Klaus

    (0) 
    1. Jaya Tiwari Post author
      Hi Klaus ,

      Yes you are right but We have requirement of an automated process to delete updated request from staging WDSO.
      For this we need to delete data from Active table of WDSO so if after deleting data we still have request entries in manage tab that is again not a correct thing .So we deleted entries from that table as well .
      Main purpose of this whole activity was cleanup of production system and we want automation of this process .
      I tried to make it simple so that even a person not having knowledge of ABAP can use this and definitely this is just a way to do this and there are more possible options .
      As per our requirement this was the most optimal method I found .

      Regards,
      Jaya Tiwari

      (0) 
      1. DEEPAK KANSAL
        Hi Jaya,

        Thanks, This was pretty helpful blog, Do you have any workaround for, how we can achieve the same in Standard DSO and Deletion of Request from Cube Older than 60 days..

        (0) 
        1. Jaya Tiwari Post author
          Hi Deepak,

          This can be used for Std DSO as well .You can use same table for that but you need to delete data from New, Active and Change Log tables in that case and from RSICCONT table .
          For deletion from cube you have a process available in process chain which can delete requests from specific cube .

          (0) 
          1. DEEPAK KANSAL
            Jaya,

            I tried to find the field in Write Optimized DSO as mentioned by you RSTT_TSMP, this field is not available as standard field of  any WDSO, as far as i know there are only three more fields mentioned above and there is no field called RSTT_TSMP?
            Were you referencing any field which stores Date in DSO or what, am still confused.

            Thanks in advance

            Deepak

            (0) 
            1. Jaya Tiwari Post author
              You try this code .
              This will select all request older than specific number of days and then delete data from WDSO tables based on request number .

              *deletion based on request number if time field not present in WDSO
                report  zwdso_sel_delete.
              *Name of table to store DSO information with which we are working
                tables : zwdso_del ,RSICCONT .

                data :it_zwdso_del type standard table of zwdso_del with header line ,
                    wa_zwdso_del like line of it_zwdso_del .
              *table to select request numbers based on timestamp
                data: it_ZWDSO_Request type table of RSICCONT,
              wa_ZWDSO_Request type RSICCONT.

              *Range table declaration
                data: s_request type c length 30.
                data : i_rng_request  like range of s_request .
                data : rng_request  like line of i_rng_request  .

                data  :v_start_time  type c length 17,
                       v_tab type /saptrx/tablename,
                       v_start_date like sy-datum .

                select * from zwdso_del into table it_zwdso_del.

              * Get timestamp for 120 days before of current system date
              *———————————————————————————————*
                v_start_date = sy-datum.
                subtract 120 from v_start_date.
                concatenate  v_start_date   sy-uzeit  into v_start_time.

              *delete data for 120 days before of current system date from Active tables of DSO’s i
              **———————————————————————————————–*

              loop at it_zwdso_del into wa_zwdso_del.
              *select request number and details from RSICCONT table

              Select * from RSICCONT
              into table  it_ZWDSO_Request
              where icube = wa_zwdso_del-wdso_name and timestamp LT v_start_time .

              *pass all request number to a range table

              clear : rng_request  .
              rng_request-sign = ‘I’.
              rng_request-option = ‘EQ’.
                  loop at it_ZWDSO_Request into wa_ZWDSO_Request.
                    rng_request-low  =  ( wa_ZWDSO_Request-RNR ).
                    append rng_request to i_rng_request.
                  endloop.

              *delete request from manage tab

                   delete from  rsiccont where icube = wa_zwdso_del-wdso_name and timestamp LT v_start_time .

              *delete request from WDSO using request numbers from range table

                   delete from (wa_zwdso_del-wdso_table) where REQUEST IN i_rng_request.
                   Refresh : it_ZWDSO_Request , i_rng_request .

              endloop.

              (0) 
      2. Alok Kashyap

        Jaya,

        I do concur with Klaus– there is really no need to delete request from WDSO..

        If I understand your scenario correctly then our intention is just to retain data aged not more than 60 days in WDSO for which one line of code is sufficient after variable declaration- not even required internal tables & all…

        delete <Active table> between l_start and l_end

        where l_start = sy-datum – 60.

                   l_end = sy-datum – 9999999 [ or check the history]

        From WDSO to cube we can have a delta DTP based on some selection (same date, e.g. posting date,could be used here for which WDSO data has been deleted) in order to ensure that we are not pulling history

        If you wish to regularize this logic you can do that in PC and also include delete overlapping request based on same selection followed by cube load

        (0) 

Leave a Reply