E Table Index deletion and generation Solving compression issues

In my previous Blog – SAP BW Indexing Scheme (ORACLE) (SAP BW Indexing Scheme (ORACLE)), I threw some light on the Indexing Schema adopted by SAP for BW objects under oracle database. That was information and information put to use is Wisdom…so here’s the wisdom gained through that knowledge. It helped considerably in solving lot of issues I have faced and I hope it will do the same for you as well. In a Production or test environment, issue with compression of a cube is a very common problem. The most frequently occurring issue is that the Compression job on a cube gets stuck and runs for hours together inspite of data being less. Eventually the compression job has to be killed and repeated which again consumes valuable time. *Best Practice: Lessons learnt from Experience**_._**

**_Cause_*: In most cases, the compression job spends a lot of time deleting and rearranging the indices on the cube tables. The indices on the F fact table are dropped and E table gets populated and its indices are re-arranged accordingly. The DROP_INDEX is performed on F fact table and INSERT on the E fact table. *Resolution:

*While struggling through the options to fix the compression issue, we came across 2 specialized Function Modules which are designed to delete the indices on only E fact table.2 – *RSDU_INFOCUBE_INDEXES_REPAIR – *If you want to rebuild the indexes use this function (same parameter as above).

We tested these FMs in our case and it worked wonders. The compression job which was running for more than 3 hrs and was never ending, completed in just 20 min after we dropped the E table Indices.* Here are the steps, one needs to perform: (Cube YTEST has been taken as an example) *1 – Please confirm that secondary indices are there on F fact table for the cube.
.

2 – Then confirm if Secondary indices are there on E fact table of the cube.

3 – Also confirm if the P index, which is exclusively used for compression, is there on E fact table of the cube. The P Index is a consolidated index on all the dimension keys and is exclusively used for compression. It’s not used for reporting.

4 – Once all the above steps are performed successfully, run the FM – +RSDU_INFOCUBE_INDEXES_DROP+ by giving the E table as parameter.The FM will execute successfully within few seconds.

5 – After that check the indices on E table of Cube . They should now be dropped.

6- Check the P index on the E fact table. The P index is not dropped as it’s mandatory for compression.

7- Also cross-check the secondary indices on F fact table. Those should be intact.

8 – After that execute the compression. It will complete in very less time. In my case it just took 20 minutes compared to 3 hrs earlier.

9 – After that rebuild the secondary indices for E fact table of the Cube with FM +RSDU_INFOCUBE_INDEXES_REPAIR+

10 – And finally check the indices on E fact table. Those should now be successfully generated.

2 Comments

You must be Logged on to comment or reply to a post.

Michael Hill September 24, 2010 at 11:13 am

If the ~P index has degenerated then the SE37 pgm won’t fix it. I had to use SE14. Let me know if you think that is not advisable. Mike

like (0)
Bernd Boecker December 8, 2010 at 12:53 am

On Oracle Bitmap Indexes are used building the Star transformation when selecting cube’s data.
All Bitmaps of the used selection keys are merged to do y quick lookup into the underlying F- and E- tables.

They are a compact index type for READING millions of rows , but not for WRITING.

Good practice is to drop the Indexes before loading into cubes (i.e. process chain “DROP INDEX”). Be aware that if you load small deltas this may not be necessary. With full loads
it surer is a killer if you don’t drop them in advance.

On E-tables the normal B-Tree P-Index is kept for updating the compressed table. This is no Problem because this index lookup locks only the row that is updated with index information.

Later rebuild the bitmaps (Oracle will do this very fast in parallel).

like (0)