Introduction
Why do we need a house cleaning procedure ?
This procedure explains how to delete data from the BW servers which are no longer useful. There are several reasons for doing that :
- It increases the overall performances of the system (Reading in a table with 1M lines if faster than on a table with 10M lines)
- It reduces the cost of the BW server hosting : the price of the hosting depends on the number of go of storage used.
- It makes maintenance and overall usage of BW easier/clearer for everyone as you no longer have to filter out obsolete objects.
- It shortens the time taken to copy servers (ex : prod to quality) and therefore save time for technical team but also reduces the downtime of the servers for those operations.
For reference, the cost of 1 Go (depends on the SLA level & decreases with time - Empty storage about 20% is not invoiced) :
Storage Cost €/Go | 2015 | 2016 |
|---|---|---|
L1 - WBP | 1.78 | 1.68 |
L2 - PQ1 / PQ2 / WBD | 0.99 | 0.94 |
L3 - WB1 / WBQ / SQ1 / SQ2 / DQ1 / DQ2 | 0.5 | 0.47 |
Overall logic of the house cleaning procedure
In this clean 4 main types of data :
1 - Business Data : The data used by the reports and users. It's all the data found in the info-providers and related objects.
2 - Statistics Data : All the logs and historical saved data that results of the usage and maintenance of BW.
3 - Meta Data : Basically the content created by the users. Queries, workbooks, bookmarks...
4 - Database Platform : All data generated by oracle and SAP internally (Usually not much we can do).
When to run this procedure
The full procedure should only be conducted once a year on each server and mainly on the production servers. It's best to do it just before the production server is copied to quality/simulation in order to shorten the copy process.
Each step can be run individually on different schedules.
Authorization
Before launching any of the following clean up actions, it is better if all the BW users accounts that have not been used for a long time (<6 months) be deleted from the system. This is because in some cases, data cannot be deleted if the user still exists.
Delete un-used Bex Web bookmarks
Explanation
This is to remove bookmarks that result from Web templates in SAP NetWeaver 7.0 format.
How to
1 - Go to transaction Se38
2 - Run program RSRD_ADMIN_BOOKMARKS_3X (For old 3.x version bookmarks)
3 - Select All users. For Created Before/Last Access, put Current Date - 365
4 - Select All bookmarks and delete
You need to repeat the select all + delete several times if necessary as the initial screen only shows a few bookmarks |
RSRD_ADMIN_BOOKMARKS_3X can only delete 3.x version bookmarks. Program RSWR_BOOKMARK_REORG can be used to delete 7.x bookmarks but the problem is that you cannot know if it's still in use - So it's not included in this process. |
| References |
|---|
| Reorganization of Bookmarks |
Delete Bex Broadcaster Bookmarks
Explanation
Deleting bookmark IDs and view IDs that were created by the system for information broadcasting.
Temporary Bookmark IDs / View IDs
Bookmark IDs and view IDs that have been generated temporarily for the system to generate online links. The bookmark IDs or view IDs are no longer used in broadcast settings and do not form part of a URL for any sent online links.
Bookmark IDs / View IDs Used in Sent Links
Bookmark IDs and view IDs that are no longer being used in broadcast settings but have still been sent as a part of a URL for an online link. This means that the bookmark IDs and view IDs can only be deleted when they are no longer being used in a broadcast setting.
How to
1 - Go to transaction SE38
2 - Run program RSRD_BOOKMARK_REORGANISATION
3 - Input "365" in Older Than Days
4 - Uncheck "Test Call (No Delete)" - Otherwise no real deletion will be done
| Main parameter | Value |
|---|---|
Temporary Bookmark ID Older than (In days) | 365 |
Bookmark IDs Old than (In days) | 365 |
| Test Call (No delete) | Uncheck |
| References |
|---|
| Reorganization of Bookmark IDs and View IDs |
Delete un-used workbooks & roles content
Explanation
This program is used to delete from the workbook storage (RSRWBSTORE) all the workbooks that are still stored but are no longer referenced in any favorites or roles. This happens for example for all the workbooks in the favorites of a user account that was deleted.
Note that the program also checks the existing workbooks that have no queries in it. This can happen for workbooks that are in fact simple documentations via excel. That is why those are not deleted (see process below).
How to
1 - Go to transaction SE38
2 - Run program RSWB_ROLES_REORG
3 - Choose "Only Examine BW Roles"
This report has several sections :
| Section | Explanation | Action |
|---|---|---|
| Incorrect References from Workbooks in Roles | References from workbooks in roles that do not exist in workbook storage | Delete all |
| Workbooks without Query Assignment | References to workbooks in roles that do not have a query assignment | DO NOT DELETE |
| Incorrect References from Workbooks in Favorites | References from workbooks in favorites that do not exist in workbook storage | Delete all |
| References from workbooks in favorites that do not exist in workbook storage | References to workbooks in favorites that do not have a query assignment | DO NOT DELETE |
| Unused Workbooks | Workbooks in workbook storage that are not referenced in roles or favorites | Delete all |
| Main tables cleaned |
|---|
| RSRWBSTORE AGR_HIER AGR_HIERT RSRWORKBOOK SMEN_BUFFC RSRWBINDEX RSRWBINDEXT |
Statistics
Delete Temporary Tables - Optional
Explanation
This operation should only be done if temporary tables are taking a lot of space. Check in DB02 all tables starting with "/BI0/0" |
The BW System uses different temporary objects for queries and other processes.
/BI0/01* Temporary Table
/BI0/02* External hierarchies
/BI0/03* Not used any longer.
/BI0/04* Only relevant for BW3x: 04 are names of stored procedures
/BI0/05* Names of triggers that are used while the system compresses requests
- /BI0/06* Reuse temporary
- /BI0/0D* Tables that are used by open hub functionality
- /BI0/0P* Tables used during optimized preprocessing
How to
1 - Go transaction Se38
2 - Run program SAP_DROP_TMPTABLES
| Main tables cleaned |
|---|
| /BI0/0* |
| References |
|---|
SAP Note 1106393 |
Deleting DTP Error Log
How to
The DTP error log can be analysed via program RSB_ANALYZE_ERRORLOG.
To delete the DTP error log :
1 - Go to transaction SE38
2 - Run program RSBM_ERRORLOG_DELETE
3 - Input "30 days" in the "but always older than ... days" parameter
4 - Check "Delete log"
5 - Run - If large, run in Background
| Main parameter | Value |
|---|---|
but always older than ... days | 30 |
| delete log | Checked |
| Main tables cleaned |
|---|
| RSBERRORLOG (+ indexes) |
| References |
|---|
SAP Note 1139396 SAP Note 1106393 |
Deletion of BW Statistics
1 - Go to transaction SE38
2 - Run program RSDDSTAT_DATA_DELETE
3 - Select all checkboxes for data to be deleted
4 - Free Date selection : Current Date - 365
5 - In the Date up until which data is to be deleted, select "Date Selection" and then enter Current date - 365 in the "Up to Day (Incl.)" field.
| Main parameter | Value |
|---|---|
Up to day (Inc) | Current Date - 365 |
| Main tables cleaned |
|---|
All RSDDSTAT* : RSDDSTATLOGGING (+Indexes) |
Corresponding tables cleaned by option :
Option | Tables |
Query Statistics Tables | RSDDSTATHEADER, RSDDSTATDM, RSDDSTATEVDATA, RSDDSTATAGGRDEF, RSDDSTATINFO. |
Statistic Loggin Tables | RSDDSTATLOGGING |
Aggregates/BIA Index Process | RSDDSTATAGGR, RSDDSTATTREXSERV, RSDDSTATTREX, |
InfoCube Statistics (Delete, Compress) | RSDDSTATDELE, RSDDSTATCOND |
Delete RSBATCH
- Go to transaction SE38 (tcode: RSBATCH)
- Run program RSBATCH_DIALOG and select on 'Deletion Selections'
- Select 60 Days and schedule as background mode.
| Main tables cleaned |
|---|
RSBATCHDATA |
Delete RSST logs
1 - Go to transaction SE38
2 - Run program RSTT_TRACE_DELETE
| Main parameter | Value |
|---|---|
Ignore Number of Last Days | 100 |
| Application Area | BW_BEX |
| Main tables cleaned |
|---|
RSTT_CALLSTACK |
Clean DYNPSOURCE table
1 - Go to transaction SE38
2 - Run program RSDQ_DYNP_GP_CLEANUP
3 - Check the PDOIT
4 - Execute the program
| Main tables cleaned |
|---|
DYNPSOURCE + corresponding LOB segments |
Delete Sent Documents - Hidden documents
1 - Go to transaction SE38
2 - Run program RSSODFRE
3 - Select Minimum Age (Days) = 60
4 - Check the 2 delete boxes
5 - Uncheck Test Mode
6 - Execute the program
| Main parameter | Value |
|---|---|
Minimum Age (Days) | 30 |
| Test Mode | Unchecked |
| Delete documents linked | Checked |
| Delete unsent documents | Checked |
| Main tables cleaned |
|---|
SOFFCONT1 BCST_CAM BCST_RE BCST_SR SOC3 SOCS SOES SOFM SOOD SOOS SOST |
Delete Sent Documents - Private Documents
1 - Go to transaction Se38
2 - Run program : RSSO_DELETE_PRIVATE
3 - Select all types of messages (Inbox to Private Trash)
4 - For Inbox, check “All” instead of “Viewed”
5 - Uncheck Test Mode
6 - Run the program
| Main parameter | Value |
|---|---|
Minimum Age (Days) | 60 |
| Inbox | All |
| Test Mode | Unchecked |
| Delete unsent documents | Checked |
Reorg Documents & Sent requests
1 - Go to SE38
2 - Run program RSBCS_REORG
3 - Select "Specify Age (in Days)" and select from 60 to 9999.
4 - Uncheck the "Test Mode"
| Main parameter | Value |
|---|---|
Age in days | 60 to 9999 |
| Test Mode | Unchecked |
Deletion of Log Files - PC Instances
1 - Go to SE38
2 - Run program RSPC_INSTANCE_CLEANUP
3 - In Older than : current date - 365
| Main parameter | Value |
|---|---|
Older Than | Current Date - 365 |
| Delete Logs | Checked |
| Main tables cleaned |
|---|
RSPCINSTANCE RSPCINSTANCET |
Deletion of Log FILES - Process Chain Log
1 - Go to SE38
2 - Run program RSPC_LOG_DELETE
3 - In the "Up to and including date" enter current date - 365
| Main parameter | Value |
|---|---|
Up to and including date | Current Date - 365 |
| Main tables cleaned |
|---|
RSPCLOGCHAIN RSPCPROCESSLOG |
Deletion of Log Files - Job Status
This is already scheduled automatically (at least on WBP) |
1 - Go to SE38
2 - Run program RSBTCDEL2
3 - Run with default values and "Test Run" unchecked
| Main tables cleaned |
|---|
TBTCO |
Deletion of Log Files - Application logs
1 - Go to transaction SE38
2 - Run program SBAL_DELETE
3 - Select Expiry Date "and logs which can be deleted before the expiry date"
4 - In from (date/time) to (date/time) select "01.01.1900" and current date - 730 days
| Main parameter | Value |
|---|---|
Expiry Date | and logs which can be deleted before the expiry date |
| from (date/time) | 01.01.1900 |
| to (date/time) | current date - 730 days |
| Main tables cleaned |
|---|
BALHDR BALDAT |
Deletion of Change Log
How to check the biggest table of Change Log
- Go to Tcode DB02
Select Space --> Additional Function --> BW Analysis. The overall size of change log will be display like picture below.
- Double click on ODS & Changelog line to display detail of each DSO. Sort the size in order to see the biggest table.
- Then, go to check on the flow in process chain. In order to delete the changelog, it has to be deleted by process chain.
How to Delete Change Log
- Add object 'Deletion of Requests from
- Enter parameter for each DSO including in the process chain. The time should be 10 days or less. It can be 0 if the DSO is loaded by full
Note:
- The best practice is modified process chain in development system
- Deletion of changelog should be at the end of process chain.
- Check the big table of changelog once a year and if missing process is found, modify the process chain by adding the deletion of PSA
Business Data
Delete empty, un-used partitions for cubes
For each of the largest partitioned cubes, this can be run to delete useless partitions. This is particularly important after data was deleted from the cubes.
1 - Go to SE38 transaction
2 - Run program SAP_DROP_EMPTY_FPARTITIONS
3 - Input in CUBENAME the name of the cube you want to clean
4 - Execute once with the "Show Option"
5 - Check the result of the analysis
6 - Depending on the result, re-execute the program with "DO_DROP" checked and either "DEL_CNT" or "DEL_DIM"
Deletion of unused dimension entries
How to
As this program run's individually for each infocube, it cannot be launched for all. You need to identify the info-providers with the largest table dimensions and then apply the program to those cubes.
1 - Go to DB02
2 - Double click on "BW Analysis" on the left menu :
3 - Double click on the "Cubes & related objects - Dimension" line
4 - Sort the results by "Size". This provides the largest dimensions in BW. You can then run the program on those infocubes.
5 - Go to transaction RSRV
6 - Select the test "Unused entries in InfoCube dimension" and double click to add it to the tests to run
7 - Double click on the right side "Unused entries in InfoCube dimension" and enter the name of the infocube you want to clean
8 - Click on Execute
Note that function module RSDDCVER_DIM_UNUSED can also be used for this. But it will not provide any logs. |
Deletion of Aggregates
1 - Go to RSA1 transaction
2 - In the left menu, click on Administration --> Monitors --> Aggregates
3 - Analyse each aggregate to see if it is useful
To know if the aggregate can be deleted :
- If the cube is already in BI Acceletor --> Delete the aggregate as BI Accelerator is used first.
- If the valuation of the aggregate is not positive (Ex : |+++ ) then it means it doesn't improve the execution time --> Delete the aggregate
- If the number of "Usage" is very low considering the time since the aggregate creation --> Delete the aggregate since it's not used much
4 - To delete the aggregate, for each cube selected, go to RSA1
5 - Select the cube and right click --> Manage
6 - Go to the Rollup tab and click on "Aggregates"
7 - Select the aggregate, right click and delete
Deletion of PSA
How to check the biggest table of PSA
- Go to Tcode DB02
- Select Space --> Additional Function --> BW Analysis. The overall size of PSA will be display like picture below.
- Double click on PSA line to display detail of each datasource. Sort the size in order to see the biggest table.
- To find the name of datasource. Go to SE16 - RSTSODS and enter BW Object from DB02 into Technical ODS Name. Execute it, the name of datasource will be display
- Then, go to check on the flow in process chain. In order to delete the PSA, it has to be deleted by process chain.
How to Delete PSA
- Add object 'Deletion of Requests from PSA
- Enter parameter for each data source
The time should be:
- Delta loading is 7 days
- Full load is 1 day
Note:- The best practice is modified process chain in development system
- Deletion of PSA should be at the end of process chain.
- Check the big PSA once a year and if missing process is found, modify the process chain
Delete un-necessary ODS Secondary Indexes
Explanation
- Take a lot of storage space
- Slow down the data loadings (each time a raw is inserted, index has to be modified)
- Can slow down the performances if too many indexes that are too similar are created
- Index was not created on the ODS intentionally : the ODS was created via copy of another ODS which had this index.
- The ABAP code that was using the index was changed (fields removed or added)
- The Queries that used this index no longer exist or have been changed.
- Another index was created that overlaps.
When you copy an ODS, the indexes are also copied ! Think about remove them if not needed in the new ODS. It is most of the time useless to create indexes with fields that are already all in the ODS KEY |
Secondary indexes improve performances in a few cases :
- ABAP Lookup : If you have a SELECT statement in an abap code where the "WHERE" has filters on the exact fields in the index (or less)
- If there is a direct selection on the infopackage/dts on those fields exactly.
- If field is used as key for infoset (Although infosets should be avoided)
- If used as a filter in a query that is either directly on the DSO, or a MPR/Infoset using the DSO.
- On a single info-object if it's necessary for a variable to display the ODS values only.
How to
First step is to find all the indexes.
