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.
- Keep in mind that some processes take a long time to run and take processes. Better run when server is under light workload.
- 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.
How to check the size of the database & tables
Full database and individual table sizes can be checked via DB02 transaction. In that transaction there are several functions that are useful.
Full database overview
You can have a look at the overall server size by going to the "Overview". Then select "History" and either days/weeks or months :
- Size(Gb) : Total size of the BW server storage. Includes both free and used space
- Chg.Size : Change of size of the total size between this measurement and the previous one in Go
- Free(Gb) : Size of the free available space.
- Chg.Free : Change of the free space size between this measurement and the previous one in Go.
- % Used : Percentage of total space used = (Size(Gb) - Free (Gb)) / Size (Gb)
You can calculate the used space by simply doing : Size(Gb) - Free(Gb)
Tables sizes
You can check the individual table sizes via the "Segments" section.
In "Overview" --> Top Sizes you have the top 100 largest tables :
Top 100 in Overview
Note that in the Overview Top Sizes, it shows the size of the individual tables. This is not a very complete approach as many tables are actually much larger once you add indexes, logs and partitions
- Detailed Analysis : this shows the individual table sizes - Use "Detailed Analysis Aggregated" instead.
- Detailed Analysis (Aggregated) : Shows the tables size including all related objects (Index, Lobs, Partitions)
Tips on DB02 table sizes
To measure the real size of a table, it is not enough to check only the table itself. You may be underestimating the size.
To have the full picture, you need to check all objects relative to that table :
- Indexes : most large tables have 1 or more index which can take a lot of space
- Lob Segments : In cases a lob segment is attached to the table. The lob segment is used to store the actual large data and therefore it could look like the table itself is very small... but the lob could be very large. Ex : The BW workbooks list is stored in the RSRWBSTORE table, but the actual workbooks data is stored in the attached Lob Segment
- Partitions : Large tables (and info-providers dimensions) can be split in many different partitions. You need to include all partitions.
Main system tables descriptions
Table Reorg - Updating the size of the tables after clean up
When you delete data in tables, the tables size do not always change. This depends on the configuration of the table.
Usually if a table is dropped (ie. completly deleted, not just emptied) then the change will be reflected in DB02 the next day.
But in most cases, there is no change. it is necessary to do a "Re-organisation" of the table in order to reflect the change.
The BW team cannot do this re-org so it is necessary to request it to the SAP technical team.
Before running the procedure
Delete inactive user accounts
Before launching any of the following clean up actions, it is better if all the BW users accounts in production that have not been used for a long time (<6 months - Excluding IT & non-dialog users) be deleted from the system. This is because in some cases, data cannot be deleted if the user still exists.
Take a snapshot of Database sizes before process
In order to be able after the process to verify the changes of sizes, you need to take a snapshot of the database sizes before running the process.
1 - Go to DB02
2 - Select the "Segments" and "Detailed Analysis" options
3 - Extract the list of top largest tables sizes and save it
DB02 not updated
DB02 should be updated daily - If it is not up to date, contact the SAP Technical team and ask then to correct the scheduling of the job.
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
Repeat
You need to repeat the select all + delete several times if necessary as the initial screen only shows a few bookmarks
V7 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 |
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 |
Delete un-used broadcast settings
1 - Go to RSRD_ADMIN
2 - Click on Settings
3 - Remove any values in Created By.
4 - In Last Executed On, select 01.01.2000 to Current Date - 365
5 - Select the settings & delete
| Main parameter | Value |
|---|---|
Last Executed On | 01.01.2000 to Current Date - 365 |
Statistics
Delete Temporary Tables - Optional
Explanation
When to do
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 RSBACTHHEADER RSBATCHCTRL |
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
WBP
1 - Go to transaction SE38
2 - Run program RSDQ_DYNP_GP_CLEANUP
3 - Check the PDOIT
4 - Execute the program
1 - Go to transaction SE38
2 - Run program ZSAP_TEMPOREP_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 RSPCCHAINATTR |
Deletion of Log Files - Job Status
Already scheduled
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 process chain
- Enter parameter for each DSO including in the process chain. The time should be 14 days. It can be 0 if the DSO is loaded by full
- Run the Changelog deletion task twice.
- One with the "Delete Activation Requests Only, no Load Requests"
- One with nothing checked.
- One with the "Delete Activation Requests Only, no Load Requests"
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
Function Module
Note that function module RSDDCVER_DIM_UNUSED can also be used for this. But it will not provide any logs.
9 - Once the check is finished, go back, select the test line (on the right) and then "Correct Error" - Then select YES for Carry out Repairs.
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 14 days
- Full load is 14 days (normally 1 day is enough but to easier maintain to know each load is full or delta, we can delete after 14 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
| References |
|---|
| How to Delete Error PSA |
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.
Copy of ODS
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 the largest secondary indexes
1 - Go to transaction DB02
2 - Select Detailed Analysis
3 - Input the following values (<> *0 will exclude the primary indexes).
4 - Sort by size(MB). You now have the list of the largest secondary indexes in the system
Now for each of the large Secondary Indexes, you need to check if it is still useful.
Checking what is in the index
1 - Go to RSA1 transaction
2 - Select the DSO/ODS, right click and display
3 - You can see in the "Index" group the existing indexes and which characteristics are included
Checking if the index is still useful
Now that you know what is inside the index, you need to conduct several checks to see if it is useful.
If any of these conditions is fulfilled then do not delete the index :
- Check that the index is not completely included in the ODS Key. The index is not useful if it's all included in the ODS key as there is already the primary index.
- Check if the index is used in a query. The query must have a FILTER or VARIABLE on the index characteristics. The query could be :
- Directly in the ODS --> Check using query designer.
- In a multiprovider on top of the ODS --> Check using query designer.
- Check if the index is used as the join KEY for an infoset
- Check if there is an ABAP program that does a select on the ODS table with "WHERE" conditions that all exist in the index.
- Check if there is an infopackage/dts which selects on fields from the index - In this case, if the ODS is very large, it could speed up the loading.
If none of these conditions are fulfilled, then the ODS Secondary Index can be deleted.
You can delete it from the RSA1 ODS modification view.
Note that a transport order is needed.
Delete un-used & obsolete infoproviders & info-objects
Info-providers and info-objects that are no longer useful should be deleted.
In this case, it is necessary to request approval from the object owner :
- Reporting CC Manager if technical object
- Reporting coordinator of the relevant process for other objects
One way of identifying the obsolete objects is to check the date of last loading.
Database re-organisations
In many cases, removing data from a table will not decrease the table size, nor the tablespace size, nor the total database size.
It's necessary to request a re-organisation from IBM in order to really reflect the size change.
Several types of re-organisations can be requested :
- Table re-org : The table will be re-organized to take less space. This won't affect the tablespace of database size. It will simply review the size taken by the table and it's index to free some space.
- Note that even if the table is empty, there is a minimum size defined for each table in the system. A table with 0 lines will therefore have the minimum size after re-org.
- Tablespace re-org : The full tablespace is re-organised. In this case it will move out and copy back all tables and reduce the size of the tablespace, It will effectively decrease the size of the database accordingly (reducing the "free" space).
- Full database re-org : This does a full re-org on all the tablespaces.