InfoCube Performance

Database Indexes

As the number of data records in the InfoCube increases, performance can be impaired for both the load and the query. This is due to the increasing system load for index maintenance. The indexes that are created in the fact table for each dimension allow you to easily find and select the data. When initially loading data into the InfoCube, you should not create the indexes at the same time as constructing the InfoCube, rather only afterwards.

The indexes displayed are the secondary indexes of the F and E fact tables for the InfoCube. The primary indexes and those defined by the user are not displayed. The aggregates area deals with the corresponding indexes of the fact table for all aggregates of an InfoCube.

Checking Indexes

Using the Check Indexes button, you can check whether indexes already exist and whether these existing indexes are of the correct type (bitmap indexes).

Yellow status display: There are indexes of the wrong type

Red status display: No indexes exist, or one or more indexes are faulty

You can also list missing indexes using transaction DB02, Missing Indexes pushbutton. If a lot of indexes are missing, it can be useful to run the ABAP reports SAP_UPDATE_DBDIFF and SAP_INFOCUBE_INDEXES_REPAIR.

Deleting Indexes

For delta uploads with a large quantity of data (more than a million records), you should not align the database indexes of the InfoCube with every roll up, rather delete the indexes first, and then completely reconstruct them after rolling up. Parallel loading is only possible when the indexes are deleted.

With Delete DB indexes (immediately), the indexes of the F and E fact tables are deleted. With Delete DB indexes (batch), the indexes of the F fact table are deleted.

Repairing Indexes

Using this function, you can create missing indexes or regenerate deleted indexes. Faulty indexes are corrected.

Building Indexes

You can delete indexes before each data load and then rebuild them again afterwards. You can also set this automatic index build for delta uploads.

With Build DB indexes (batch), the indexes of the F and E fact tables are rebuilt.

Aggregate Table Indexes

Just as for the indexes of the fact table, you can maintain the indexes of the aggregation tables. Maintaining the indexes of the aggregation tables affects all the aggregation tables of the InfoCube.

Database Statistics

The database statistics are used by the system to optimize the query performance. You should keep the database statistics up-to-date for this reason.

It is recommended that you always update the statistics if you have loaded more than a million new records into the InfoCube since the last update.

You can automatically recalculate the database statistics after each load or after each delta upload.

Using the pushbutton Check Statistics, you can check the InfoCube to see if statistics exist. If no statistics exist yet, the status display changes to red. Using Recalculate Statistics, you can add the missing InfoCube statistics in the background.

You can determine the percentage of InfoCube data that is used to create the statistics. The percentage is set to 10% by default. The larger the InfoCube, the smaller the percentage that you choose should be, since the demand on the system for creating the statistics increases as the size grows.