Housekeeping Part 3 – BPC Tables
If you have a BPC implementation on your BW Netweaver system then you have a whole other ecosystem to worry about, and you will need to work closely with the BPC team and the basis team in order to keep various BPC tables from growing very large. Again, there is too much in this area to cover fully in this blog posting, so I have just picked out a few topics to concentrate on.
SAP Note 1705431 covers all the key housekeeping activities that are needed on BPC 10.x on Netweaver.
Temporary Data Manager Data
Data manager generates temporary data during data loads, and the temporary data is stored in a table. If the data load process aborted unexpectedly, junk data will be left in database without being cleaned.
In order to get a list of this data, execute the program UJD_BACKEND_DATA_MAINTENANCE from transaction SE80, make selections, and a list of junk data records will be shown with an advised action for each row.
This table keeps track of all the documents that are imported/uploaded into BPC using data manager. It is good practice to delete all the old uploaded files after a retention period to be agreed with the business, otherwise the UJF_DOC_CLUSTER table can grow very large, depending on the number & size of the files you are importing into BPC.
You can use this program in SE38 to delete entries from the table:
The program will allow you to select which BPC environment you want to work on, and also the age of the data records that you want to delete.
Audit Log Data
It is necessary to archive and subsequently trim BPC audit log data, because if you don’t the BPC audit log tables will become very large and will end up taking up a lot of space on your database. I will give you a quick overview of the steps which you need to undertake in order to get this done, but unfortunately I don’t have enough space in this posting to take you through the process in detail.
BPC Audit Data Archiving is a 7-stage process. The stages are:
- Agree the 2 key data retention periods with the business:
- The retention period for audit log data in the main audit log tables
- The retention period for archived audit log data before it is finally deleted.
- Set the data retention period for archiving the audit data via the BPC Admin console
- Configure Audit reporting to use archived data as well as current data
- Run the data archiving BPC package via Package Manager to archive the audit data older than the retention period from the main audit log tables into the archive tables
- Delete the archived data that is older than the agreed retention period from the archive tables with an ABAP Program
- Set up a BPC package schedule so that the data archiving BPC package runs weekly
- Set up a background job to run the UJU_DELETE_AUDIT_DATA ABAP Program so that the archived data is trimmed once a week after the package has run.
The retention period that you specify in step 1b is compared against the timestamp in each data record, so if you specify a retention period of 365 days, the program compares the timestamp of each record in the archive table against today’s date + the specified retention period to work out what to delete & what to leave. So a retention period of 365 days in the archive table clearing job would delete all data with a timestamp greater than 365 days, *not* data that had been in the archive table for more than 365 days.
Archived audit log data is still visible in the audit log viewer in the BPC web interface. The audit log data only becomes unavailable when it is finally deleted from the archive table.
Program UJ0_GET_GEN_TABNAME can be used to identify the audit tables in the system, and then the sizes can be ascertained via DB02 (or if you just want the row count you can get that via SE16).