Performance of Navigation Attributes in Queries and Value Help 

From a system performance point of view, you should model an object on a characteristic rather than on a navigation attribute, because:

·        In the enhanced star schema of an InfoCube, navigation attributes lie one join further out than characteristics. This means that a query with a navigation attribute has to run an additional join (compared with a query with the same object as a characteristic) in order to arrive at the values. This is also true for DataStore objects.

·        For the same reason, in some situations, restrictions for particular values in the navigation attribute (values that have been defined in the query) are not taken into account by the database optimizer when it creates run schedules. This can result in inefficient run schedules, particularly if the restrictions are very selective. In most cases, you can solve this problem by indexing the navigation attribute in the corresponding master data tables (see below.)

·        If a navigation attribute is used in an aggregate, the aggregate has to be adjusted using a change run as soon as new values are loaded for the navigation attribute (when master data for the characteristic belonging to the navigation attribute is loaded). This change run is usually one of the processes critical to the system performance of a productive BI system. This is why avoiding using navigation attributes, or not using navigation attributes in aggregates, you can improve the performance of this process. On the other hand, not using navigation attributes in aggregates can lead to poor query response times. The data modeler needs to find the right balance.

Additional Indexing

It is sometimes appropriate to manually create additional indexes for master data tables, to improve system performance for queries with navigation attributes. A typical scenario would be if there were performance problems during the selection of characteristic values, for example:

·        In BEx queries containing navigation attributes, where the corresponding master data table is large (more than 20,000 entries), there is usually a restriction placed on the navigation attributes.

·        In the input help for this type of navigation attribute.

Example

You want to improve the performance of navigation attribute A in characteristic C. You have restricted navigation attribute A to certain values. If A is time-independent, you need to refer to the X table of C (/BI0/XC or /BIC/XC). If A is time-dependent, you need to refer to the Y table of C (/BI0/YC or /BIC/YC). This table contains a column S__A (A = Navigation attribute). Using the ABAP dictionary, for example, you need to create an additional database index for this column: SAP Easy Access  →  Tools  →  ABAP Workbench  →  Development  → Dictionary.

You must verify whether the index that you have created has actually improved performance. If there is no perceivable improvement, you must delete the index, as maintaining defunct indexes can lead to poor system performance when data is loaded (in this case master data) and has an impact on the change run.