2918552 - [BW Central KBA] Selective Deletion

This KBA offers troubleshooting steps for BW selective deletion, and technical details about the feature.

How to perform Selective Deletion? 

Background execution 

1. In transaction RSA1, find the InfoProvider
2. Click on "Manage" > Tab "Contents" > Button "Selective" > Button "Deletion Selections"
3. Determine your deletion criteria (records to be deleted) > Button "Execute"
4. Deletion report will be generated (name of Generated programs always starts with GP)
5. Click on button "Start", so Job name BI_INDX* will be triggered
6. Once Job BI_INDX* is finished, selective deletion is completed

Dialog execution

Transaction DELETE_FACTS or Report RSDRD_DELETE_FACTS

• "Direct deletion": Define deletion criteria, then perform the deletion itself;
• "Generate selection program": Generate a custom report or a GP (if you didn't specify a "Name of report") that receives criteria via manual input, or using variants;
• "Generate deletion program": Generate a custom report or a GP (if you didn't specify a "Name of report") with static criteria deletion. The deletion criteria is specified only once when creating the report. You cannot use variants.

These reports can also be scheduled in background execution, or executed using process chains.
Obs: GP (generated programs) reports are not intended for permanent usage. If you need re-usage of generated reports, create Z/custom report specifying a "Name of report". GP reports might be dropped/deleted without prior notification.

BW Archiving

The Deletion phase uses selective deletion to delete data which was copied into NLS/Secondary storage. This way the archived data is removed from BW's database.

OBS: Make sure to delete records using filters and not deleting millions and millions of records at once that will eventually fail into memory dumps such as TSV_TNEW_PAGE_ALLOC_FAILED and TIME_OUT.

How is the Selective Deletion performed?

DELETE statement method

DELETE statement is performed on InfoProvider's table

Copy/Rename method

1. BW copies the data that should be kept in InfoProvider into a temporary table
2. Then drops the original table
3. Finally renames the temporary table accordingly, to replace original table

If the ratio between the records to be deleted and total number of records overcomes a pre-determined threshold, copy and rename is preferable rather than usual DELETE statement. Why this approach? Copying the data that won't be deleted into a temporary table (using CREATE TABLE ... AS SELECT ...) can be carried out more efficiently than deletion using the DELETE statement, which is one of the most expensive operations.
The default ratio is that triggers copy/rename instead delete statement is 10%. That means more than 10% of total records are going to be deleted. You can adjust parameter BW_SELDEL_PERC_THRES defined in table RSADMIN using report SAP_RSADMIN_MAINTAIN. Example: If the parameter is set to "50" and 5 of 100 records are deleted (5% < 50%), BW deletes using DELETE. If, for instance, 110 records of 200 existing records are deleted (55% > 50%), BW deletes using COPY/RENAME. More details available in SAP Note 792435 - Selective deletion: Using temporary tables.

• SAP NetWeaver - all version
• SAP BW/4HANA - all version

how-to, how to, delete, filters, RSA1, DSO, info, object, infoobject, infoProvider, deletions, selection, RAISE_EXCEPTION, SAPLRSDRD, LRSDRDU09, RSDRD_SEL_DELETION, X_MESSAGE, GP, RSDRD, delete_facts, ADSO, RSDRD_SD_710_PAKSIZE, RSDRD_SD_710_PARDEG, Perform selective deletion, PC is not deleting the data correctly , Cannot delete data from ADSO, data deletion , , KBA , BW-WHM-DBA-SDEL , Selective deletion , Problem