Create Index on Process Chain

[Former Member]

Hi There

I am loading data from R/3 to ODS to Cube in the background.In Process Chain,They have done like first loaded to ods and activated then they have been loaded from ODS to Icube and deleted indexes and created indexes then compressed.

How it could work and what is the use delete and generate index variant in the PC's?

Thanks in advance.

Regards,

Chandu.

[sachin_kulshrestha2]

Hi,

The user of index on infoprovider is to improve the performance when bex query is executed.

Deleting of index is done to align the new data in the cube. After the load is finished creation of index is done.

Hope this helps!!!

[Former Member]

The Delete Index preocess type will drop the indexes on the F fact table of the InfoCube(s) specified in the variant.

The sequence would be Delete Index, Load Data, Create Index.

Maintaining the bitmap indexes on the F Fact while loading will slow the load process down. The more dimensions defined on the cube, the greater the impact.

Bitmap indexes were never really designed with DB Updates in mind, so they are not very efficiently maintianed by the DB. In most data warehouse environments, not just BW, they are deleted, fact tables updated, then indexes are recreated. Oracle 10g is supposed to handle updating bitmap indexes better - we'll see about that.

Also, you can have load failures due to locks placed on the bitmapped indexes (ORA-00600 errors)

<b>Creating the indexes after the loading is critical as query performance will suffer greatly without them.</b>

Now, it is certainly possible to run your loads without dropping and recreating the indexes - you might do this if you have loads running during the day (during reporting hours). You probably would NOT want to drop the indexes in this case, as anyone running quries then would probably have their queries run much longer.

If your volume of daily records loaded is small compared to the total of records in the F fact table, and it takes a lot of time to rebuild the indexes each day, you might opt to not delete the indexes.

[Former Member]

Hi Chandu,

The chain looks correct. Though you may have the delete indexes step before the cube load and also Compression step before the create indexes step.

The indexes are meant faster retrieval of data from the cube. Since the indexes are created for the data existing in the cube they need to deleted and recreated each time the cube is updated with new data.

Bye

Dinesh