<body>Definition:
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 157918provide 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
3 Comments
