Skip to Content

SAP BW Indexing Scheme (ORACLE)


Indices are pointers which speed up the data retrieval process significantly. In its absence the performance can take a severe beating by doing a Full table scan.

The Schema<br />BW uses two fact tables per InfoCube – the “F-fact table” (request packages with ids > 0) and the “E-fact table” (consolidated request package with id=0). The corresponding table name prefixes are /BI0/F, /BIC/F, /BI0/E and /BIC/E. For APO cubes we have the names like /BI0/9AF, /BIC/9AF, /BI0/9AE or /BIC/9AE.

InfoCubes (fact tables, dimension tables) are fully indexed and usually do not require additional indices. Let’s examine the indexing scheme or various types of InfoCubes. Subsequently we will also look into the indexing for Dimension and master data tables (SID tables).

<u>Standard InfoCubes<br /><br /></u>Primary Index:It’s pretty interesting that both fact tables do not have a unique index defined over its primary key. In case of duplicates, the system takes care of it while compression process. Earlier we had the  primary index with a name like /BIC/F0 or which has been replaced (for F-fact tables) by a non-unique version (in E-fact tables). This new replacement is named /BIC/E<InfoCube>P. It’s a non-unique B-tree index (also known as composite Index) on all DIM Ids. This so-called P-Index is used for compression. This is not relevant for queries.~010, 020,030….


The secondary indexing scheme is similar for both types of tables (F and E).

Note 157918

provide details for checking the scheme. Each column that contains the foreign key of a dimension (having the prefix KEY_) is Bitmap indexed.

Exceptions to this are:

a) Line-item dimensions which are indexed via a normal non-unique B-tree.

<u> Note:</u>  B-tree indices for InfoCube dimensions (“high cardinality”) should be set only in special cases. The dimension should be very large (in comparison to the fact table) and the maintenance time for bitmap indices (deletion and re-build) should have significant impact. You should be aware of the fact that the B-tree index cannot be used for the star join and hence, reduces query performance.

b) Real-time InfoCubes (Real-time InfoCube) – Discussed later.

<u>APO-InfoCubes<br /><br /></u>Primary Index:APO cubes have a unique primary index for F-fact tables with names like BIC/9AFP because APO cannot cope with duplicates in InfoCubes.  E-fact tables have a non-unique primary index with names like /BIC/9AE<InfoCube>P. The latter index can also be unique without any harm.Secondary Indexes:Similar to standard InfoCubes. A23. <br />Note 363092, provides details in this direction.<br /><br /><u>Real-time InfoCubes (Transactional InfoCube in 3.x)<br /><br /></u>Primary Index:<br />Similar to standard InfoCubes.<u>Note</u>: SEM InfoCubes are real-time InfoCubes.<br /><br />Secondary Index:<br />E fact tables are indexed similar to standard InfoCubes. However, in the case of F fact tables, bitmap indexes are replaced by standard non-unique B-tree indexes for handling writing data into the underlying table. Bitmap indexes can cause deadlocks in case of concurrent write operations as they don’t support   row-level locking.<br /><br /><u>Indexing of  InfoCubes which are partitioned.<br /><br /></u>Primary Index:Similar to standard InfoCubes.<br /><br />Secondary Index: <div style=”border-right: medium none; padding-right: 0in; border-top: medium none; padding-left: 0in; padding-bottom: 1pt; border-left: medium none; padding-top: 0in; border-bottom: windowtext 1pt solid”>There is an additional index for F fact tables of partitioned InfoCubes, irrespective of whether the InfoCube is standard or Real-time. This is an additional bitmap index on the column of the F fact table that corresponds to the partitioning column of the E fact table. This index’s name is “900”, i.e. “/BIC/F…900″ on the database.


Indexing on Dimension & Master Data tables (SID Tables)

<u>Dimension Tables:<br /></u><br />Dimension tables are names as /BIC/D<InfoCube name>T/U/P (for Stnd time, Unit and packet dimensions) and /BIC/D<InfoCube name>1/2/3… for user dimensions. Dimension tables are usually B-tree indexed. <br /><br />Primary Index

Dimension tables have a Non-unique B-tree index on Dimension ID. This acts as a primary key


You must be Logged on to comment or reply to a post.
  • Nice job.  It's probably worth a mention, that the separate secondary indexes on each Master Data SID in the dimension is new with 7.0.  In 3.5, there were only two indexes on a dimension table, one on the Dim ID, and a another compound index made up of all the Master Data SIDs.  This master Data SIDs in teh compund index appeared in the order the characteristic appeared in the cube.
    So sometimes it made sense to add additional indexes on some of the Master Data SIDs.  That's no longer necessary since the indexes were added in 7.0, and it helps query performance.

    I never found anything in the 7.0 upgrade documentation or Notes that mentioned this change. But is is very, very important to note that this change effects new cubes.  In order to get all teh new indexes built on pre 7.0 cubes, it is necessary to activate all the cubes after the 7.0 upgrade.


    • Hi John,

      Yes I agree that it's important to know that this is a new enhancement in BI 7.0 and is important as far as indexing schema is concerned.
      Thanks for your valuable inputs.

      • Hi Debanshu,

        I just have one doubt if you could clear that one.
        When I go to the system for a fact table I find  ~0 index which is the primary index and also ~p index which also is a composite index consisting of all the fields exactly same as ~0 index.Could you please tell me the difference between these two indexes.