Problem Statement/Business Scenario

As a best practice SAP suggests to delete the change log data in the DSOs but what if there is business logic in the transformation/update rule which consume the data from change log? There isn’t any standard process where selective change log requests can be deleted unless you delete them manually
Consider a scenario where we have a staging DSO which feeds data to 3 different cubes, out of the 3 cubes two are delta enabled and the third one is full request every day with snapshot data. If the CL data for full request isn’t deleted then it will grow exponentially in no time.

As per the below mentioned data model the staging DSO feed data to multiple cubes and among them is a snapshot cube to which full data load happen and there is no reason why we should retain the change log requests. In worst case scenario if we are to reload the data from staging DSO to Cube then it will not require the data in change log

Untitled.jpg

Purpose
To reduce the change log there are two ways of doing it – Either delete the requests manually or use an ABAP program to delete the change log with selections (full requests). SAP has provided the provision to delete the change log data via process chain but you cannot delete the requests selectively in case of the data being loaded to multiple data targets. To avoid all manual intervention a custom ABAP report can be created which will automate the process and it can be used as a weekly/monthly/yearly housekeeping activity.

ABAP Report Design
An ABAP program can be created to look for all the requests from table RSSELDONE which has the Request ID, Load Date, Info Package ID, System ID and Update Mode. Based on the info package id, load date and update mode all full requests can be identified and deleted from the change log table. Change Log table details can be got from DSO>Manage> Contents>Change Log.

Untitled.jpg

Example: If we have to retain full requests for 30 days alone then the program should delete all the data from change log based on the requests date which are older than 30 days.

Below is the snapshot of the table RSSELDONE which store all the requests

Untitled.jpg

Untitled.jpg

Thanks

Abhishek Shanbhogue

To report this post you need to login first.

4 Comments

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

  1. Arun Varadarajan

    Abhishek,

    The only issue of deleting from change log based on characteristics other than request number is that you will be doing full table scans on the change log – the only way out is to create additional indices on the change log but that would decrease your load performance.

    The request number is the first field in the DSO and when you delete based on request number , the index is used and I am not very sure but I think the change log also functions like a PSA and it would be partitioned by request number which is why deletion based on request number is the preferred method.

    (0) 
    1. Abhishek Shanbhogue Post author

      Hi Arun

      Your right Change Log behaves similar to the PSA table and the preferred way of deleting the data is by request id. In case if there is a need to delete only selective requests then it can be achieved as I have mentioned in my blog

      I have successfully been able to brign down the CL table from 800GB to mere 40GB in our system based on the design suggested

      Thanks

      Abhishek Shanbhogue

      (0) 

Leave a Reply