Designing and Building Secondary Indexes on DataStore Objects

 This post describes best practices for building appropriate secondary indexes on DataStore Objects.

General guidelines for placing secondary indexes on DataStore Objects:

1. The presence of secondary indexes on an DataStore Object can greatly improve query performance, but it may slow down load performance.
2. If your application does not have routine, frequent reads or reports on a DataS­tore Object, do not build secondary indexes on it.

 

Designing Secondary Indexes:

Apply these general guidelines when determining the number and structure of secondary indexes on DataStore Objects:

1. Do not design a secondary index that duplicates the primary key of the DSO, or any high ­order subset thereof. For example, if the primary key consists of Customer Number + Invoice Date + Document Number + Line Item Number, do not create a secondary index consisting of the first key field (Customer Number), or the first two key fields (Customer Number + Invoice Date), or the first three key fields, etc.
2. Do not construct any secondary index as a subset of the high ­order fields in another secondary index.
3. Construct a secondary index for the characteristics used most frequently in query restrictions.
4. Use a single characteristic in a secondary index if queries frequently restrict on that characteristic independently of others.
5. Use a combination of multiple characteristics in the same secondary index if queries frequently restrict on that combination. For example, Fiscal Period + Version.
6. After building indexes to support query restrictions, you may also see further performance improvement by building secondary indexes on the characteristics most frequently used on rows in the initial drill­down state of your queries.
7. When building a secondary index composed of multiple characteristics, use the following guidelines to determine the sequence of characteristics in the index:
   • If one or more of the index characteristics also appears in query filters independently of the other characteristics, place these independent characteristics in the first (high order) positions of the index definition. For example, if you frequently restrict queries on Profit Center and Version, but you also restrict queries on Version only sometimes, then place Version first in the index definition.
   • If the guideline above does not apply, then sequence characteristics in your index definition according to their cardinality (the number of unique characteristic values they have. Place the characteristics with the highest cardinal­ity first. For example, suppose you wish to build an index on Employee Number, Customer Number, and Version. Suppose further that you anticipate 100,000 unique values of Customer Number, 8,000 unique values of Employee Number, and 10 unique values of Version. You should therefore compose your secondary index as Customer Number + Employee Number + Version.

 

 Building Secondary Indexes:

You build secondary indexes on DataStore Objects using the DSO editor (the same tool you use to create and change DataStore Objects definitions.) To create a secondary index on an DataStore Object:

1. Go in to the DataStore Object editor in the BW Administrator Workbench. (Right click on a DataStore Object in the InfoProvider tree and choose “Change.”)
2. Right-­click on the “Indexes” folder and choose Create.
3. On the pop­up window that appears, do not check the unique check box unless this index uniquely identifies a single record. Click OK or press Enter.
4. Drag and drop characteristics from the Key fields and Data Fields folders onto the folder representing your new secondary index.
5. As necessary, drag and drop characteristics within the secondary index folder to sequence them according to the guidelines above.
6. Save and activate the DataStore Object.

Notes:

If the DataStore Object already contains data, activation may take a few minutes while the system builds the new index.

Secondary indexes get transported with the DataStore Object to Test and Production. You should build and test secondary indexes in the Development environment first, then transport them up the landscape.

 

In practice, you should rarely use this option, since the primary key uniquely identifies a row in the DSO. Circumstances may arise, however, where some combination of characteristics outside the primary key also identifies a unique record. In those instances, you should set this check box on.

 

As a personal note of caution, I have seen secondary indexes causing lot of issues in the DSO activation step. For Example, a delivery DSO was having secondary indexes on it. This DSO sometimes took very long time to activate a single request. Again this may depend on your local system settings and version and even nature of the data sometimes.

Courtesy: SAP and Project Help Documents