Automation of deletion of unrequired history data from the BW system

[Former Member]

         Target Readers

SAP BI Developers and Technical Architects.

Introduction

This document helps in providing an approach for the deletion of unrequired history data from the system . This helps in data volume reduction and hence leads to value addition to the business.Data volume continuously increases with time and there can be lot of old data which is no more of use but still occupying space in the system . This solution provides an insight on how we can automate the deletion of such data.

Prerequisites.

           Knowledge of SAP BI and ABAP.

Problem Statement and Solution

            The approach is explained in following steps .

1. Firstly we need to analyze and identify the cubes which are occupying maximum space in the system .This can be done using tcode DB02 -->Space-->

        Additional functions --> BW Analysis.

   2.   Make a list of cubes from which history data can be deleted .Also we need to consider how old data to be deleted and the info object to be referred for          selective deletion .For e.g data older than 2 years to be deleted on the basis of 0CALMONTH.

   3. Create a master data info object to store the above parameters .Suppose master data info object is gcube_par with following attributes :

Description	Info Object	Sample Value
Cube name	gcube_par	ZCUBE01
Info object name	gn_iobjnm	0CALMONTH
Indicator of time reference (M when selective deletion is on the basis of calmonth or fisper, D when it is on the basis of calday) .	iindic	M
Number indicating how much old data to be deleted in years	gnum0300	2

    4. Create a report with the following code:

  Types: Begin of s_cube ,
          cube type /bic/oigcube_par,

          rec_del type f,
          end of s_cube .
  Data: l_thx_sel type rsdrd_thx_sel,
          wa_sx_sel type line of rsdrd_thx_sel,

     // wa_sx_sel will contain InfoObject which is to be referred for selective  deletion and the range of values for that info object .

l_t_range type rsdrd_t_range with header line,
t_cube type standard table of /bic/mgcube_par with header line,
year(4) type c,
rec_del type f,
wa_output type s_cube ,
t_output type standard table of s_cube ,
l_t_msg type rs_t_msg.

    // Reading master data for cube parameters

select * into table t_cube from /bic/pgcube_par where /bic/gcube_par <> ''.
loop at t_cube.
    year = sy-datum+0(4) - t_cube-/bic/gnum0300.

    // e.g sy-datum is 20110404 and gnum0300 is 2 in master data then year = 2009

     // Defining range as per time ref infobject.

   if t_cube-/bic/iindic = 'M'.
     if t_cube-gn_iobjnm = '0CALMONTH'.
       concatenate year sy-datum+4(2) into l_t_range-high. 

       // high range would be 200904 i.e 2 years old from current month

       l_t_range-low = '200001'.

     // Data will be deleted for 0CALMONTH ranging from 200001 to 200904 . Similar calculation of range for 0FISCPER and 0CALDAY .

     elseif t_cube-gn_iobjnm = '0FISCPER'.
      concatenate year '0' sy-datum+4(2) into l_t_range-high.
      l_t_range-low = '2000001'.
     endif.
   elseif t_cube-/bic/iindic = 'D'.
     concatenate year sy-datum+4(4) into l_t_range-high.
     l_t_range-low = '20000101'.
endif.

wa_sx_sel-iobjnm = t_cube-gn_iobjnm. // Info object referred for selective deletion in master data
l_t_range-sign = 'I'.
l_t_range-option = 'BT'.
l_t_range-keyfl  = 'X'.
append l_t_range.
wa_sx_sel-t_range = l_t_range[]. // Range of month or day calculated above
insert wa_sx_sel INTO TABLE l_thx_sel.
refresh: l_t_range[].
clear: wa_sx_sel.

CALL FUNCTION 'RSDRD_SEL_DELETION'
EXPORTING
i_datatarget      = t_cube-/bic/gcube_par
i_thx_sel         = l_thx_sel[]
i_authority_check = 'X'
i_no_logging            = ''
i_parallel_degree       = 1
i_no_commit             = ''
i_work_on_partitions    = ''
i_rebuild_bia           = ''
i_write_application_log = ''
IMPORTING
e_cnt = rec_del
CHANGING
c_t_msg                     = l_t_msg .

  ENDLOOP.

  Conclusion

  This program can be executed yearly for selective deletion of history data .Master data plays an important role in this approach and should be maintained   very carefully .Modifications can be done to this solution  as per additional requirement .