Selective Deletion scenarios in SAP BW and possible solutions

[former_member184494]

A novel way to do selective deletion and not impact your change logs.

In many cases we have issues where we want to selectively delete records from DSO and want them to be deleted from the change log as well..

One interesting approach would be to consider using BW load processes to achieve the same.

Lets take an example :
Lets say we have ODS1 which gets loaded daily with Sales Data. Some of the records here are records for internal sales which have to be deleted.There is a cube which is being used for reporting which gets loaded daily from this DSO.

Approach 1 :
The usual way is to have a selective deletion on the DSO and then do a full load to the cube .

Process Steps :

1. Load data to ODS1
2. Activate data in ODS1
3. Selectively delete data in ODS1
4. Drop data in Cube
5. Load data into cube
6. Build the aggregates

Pros :

1. Simple method
2. Easy to implement

Cons :

1. Repeated load of data to cube
2. Aggregates have to be rebuilt every day
3. High database activity
4. Becomes self defeating with large data volumes
5. With large data volumes – Selective deletion takes longer and longer times

Approach 2 :

Here you can look at selectively deleting the data from the DSO and then  selectively deleting from the cube as well and reloading the new data  alone using selective full loads from the DSO to cube

Process Steps

1. Load data into ODS1
2. Activate data in ODS1
3. Selectively delete data in ODS1
4. Selectively delete data in cube for affected time period / Delete Overlapping request
5. Load selective full updates from ODS1 to cube
6. Rebuild the aggregates

Pros :

1. Slightly complex but still easy to achieve
2. All can be done using Process chains and some simple ABAP in the infopackage

Cons :

1. Aggregates have to be rebuilt every day
2. High database activity
3. Maintenance and validation heavy
4. Gets tougher to manage load times with higher data volumes

Approach 3:
Use the BW load process and some ABAP programs to negate the values.

Process Steps

1. Load data to ODS1
2. Activate the data in ODS1
3. Create a copy of ODS1 as a transaction ODS (ODS2)
4. Have a program run in the process chain that identifies these records to be deleted and writes them to the active table of ODS2
5. In the program set all the key figures to zero
6. Load ODS1 from ODS2 using a full update
7. Use the 0recordmode in the update to delete the records
8. Continue deltas into the cube
9. Drop data from ODS2

Pros :

1. BW load processes are used and no deletion takes place
2. Change log integrity is preserved
3. Incremental building of aggregates can be done
4. One time validation is required post which the whole process can be automated.
5. This can be automated using a process chain

 Cons :

1. Requires an ABAP program
2. Time to implement is slightly longer than the previous approaches
3. Initial validation is required to make sure that only selected records are deleted and no additional records get deleted

A data flow for the same is available below.

We had done this for some really large DSOs and the process worked fine and the time taken was much lesser than Approach 1 or Appproach 2. Initiallly we did think that the ABAP program was going to take too much time and that it was not worth the effort but then some efficient ABAp programming and some big time failures in Approach 1 and 2 convinced us to go down Approach 3!!!