How to Clean up BW Info cube Dimension Tables?
We are using the RSRV transactiondata test named "Entries Not Used in the Dimension of an InfoCube" to clean up unused entries in a BW infocubes dimensiontable.
The program ZSAP_DIM_CLEANER takes 3 parameters and 2 checkmarks.
Parameters:
- PCUBE is the technical name of the cube (e.g. ICUB),
- PDIM is the dimension of that cube you want to clean up (e.g. ICUB3, which is dimension 3 of cube ICUB) and
- PSIZE is the number of rows you want to delete in 1 paket. After 1 paket is DELETEd, a COMMIT is issued and the next paket is DELETEd.
Flags:
- REPAIR when checked really does DELETEd, when unchecked, just checks and shows you the result. No problem to uncheck this and just run to get an idea how long reading may take.
- DEBUG when checked stops before the read and delete is done so you have a chance to follow the details.
The program is very small and can easily be understood. Take a look and discuss with your DBA for questions.
Comments:
Secondary Indexes of the Dimensiontable
As deletion against existing indexes may slow down the deletion substantially, it may be advantageous to drop all secondary indexes on the respective dimensiontable (DIMTAB).
All dimension tables have a similar layout: One key column named DIMID, used to join the DIMTAB with the facttables, and other indices on the SID columns for joins of the respective characteristics SID-table with the DIMTAB. As there is no BW tool for deleting secondary indexes of a DIMTAB, you do this via SE14 and delete the indexes on the database. After ZSAP_DIM_CLEANER did its job, you recreate the indexes again, either from SE14 or by activating the DIMTAB from SE11, which creates the missing indexes.