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 :
- Go to SE11 TCODE in BW and create a table to store WDSO (Write-optimized DataStore object) details .
- 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.
- Now Go to TCODE SE38 in BW system .
- Create a report program and write following code and execute the program .
- 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 .
