Delete/Create InfoCube Index Practices

[sap_cohort]

Hi, We have a significant Process Chain that we run 3 times daily. 2 times occur during the day when users are actively reporting. Before each chain run we drop the indexes and then rebuild them at the end of the chain. This has worked fine, but I am concerned about reporting performance during the 45 minutes the chain is running.

I'm curious what practices people use or what the recommended best practice should be.

1. Drop all InfoCube Indexes at beginning of chain and rebuild 45 minutes later at end of chain?

2. Drop and rebuild each infocube index as it's being loaded?

3. Don't Drop and rebuild the InfoCube Indexes during the day when users are active.

I'm thinking this route because we are just loading deltas and it shouldn't take much more time to load with the infocube indexes in place.

4. Any other ideas?

Thanks for any opinions.

[former_member213233 · Accepted solution]

Hi,

In our scenario we Drop and rebuild Index for each infocube as it's being loaded. As by doing so we are dropping the index for very less time compare to do same in begining and end of the PC.

Dev

[Former Member · Accepted solution]

Hi -

I can say the second option!

Drop the index before loading of the cube and then rebuild the same after its completion,..( No need to make it at the end of the chain )

It holds good for all the infocubes,..

Regards,

Vishwa.

[sap_cohort · Accepted solution]

OK, I agree Drop Individual Indexes;Load Data; Rebuild...

Anyone else from the camp to just not drop the indexes if we are just doing deltas a couple of times a day?

[Former Member · Accepted solution]

Hi,

Dropping indexes before loading to the cube is the best option as the load make take somemore time if indexes are not dropped....

That too mainly sometimes u may get ABAP short dumps MSG_TYPE_X....if the dropping is not done...

Now it depends, on how u handle the situation.....

--

rgds,

Edited by: Krishna Rao on May 21, 2009 6:26 PM

[sap_cohort]

Question Answered.

My understanding is mostly the best option is to drop and recreate after cube load. This is not always the best option depending on your loading schedule and other factors.

[Former Member]

Chiming in a little late on this one, but I would concur with Stefan. We do NOT drop indexes during our intraday loads. The cubes we load mid day have 10's of millions of rwos and are queried heavily. If I yanked out the indexes, two things happen - query performance goes from great to terrible, and overall system performance gets killed.

As Stefan says, it depends on how many rows get updated, how heavily the cube(s) in question are queried, how large the cubes are, and overall system capacity.

[sap_cohort]

Thanks Everyone. I think the multiple points of view in this discussion raises the bar of knowledge on this topic for everyone...

Thanks!

[Former Member]

Hi,

I would say it depends, option 2 is not necessarily the best option in all cases. Depending on your database system, the way you load (parallel yes/no) and the distribution of the records between E and the F table (compression), option 3 can be also very useful. At my current client we mainly use 3 as the database (MaxDB) is not very good in re-building indexes.

From what I understood from you, you should at least try option 3 as I suspect that there are only a few records being added to the cube (delta) compared with the total number of records. However, if you are on Oracle you have to ensure that you don't load in parallel into the cube as you the might experience these ORA...60 locks.

Hope it helps.

Stefan

[Former Member]

This message was moderated.