Skip to Content
avatar image
Former Member

Reporting performance with navigational attributes

Does anyone have any suggestions for improving the reporting performance using navigational attributes?

We have a navigational attribute set on a field in 0MAT_PLANT (similar to MRP Controller) in InfoCube 0IC_C03 - the 0MAT_PLANT master data table has over 2 million rows. It is taking 3+ minutes to locate 12,000 records right now in the table using an attribute and then finally execute the 0IC_C03 query. I notice it is very slow even running master data queries straight off of 0MAT_PLANT with this attribute, but combining that with the 0IC_C03 query it is even slower and sometimes times out after 600 seconds.

I've recreated the 0IC_C03 indices and statistics but this didn't help. We cannot add this nav attribute to the 0IC_C03 InfoCube structure because the master data is constantly changing.

Should we add an index to the 0MAT_PLANT master data table on this attribute? Or does an index need to be added to a DIMID/SID table somewhere? Any suggestions anyone has?



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 26, 2006 at 07:26 PM

    Hi Chris,

    I am not sure about having an index on the master data table. Perhaps you can build an aggregate on the cube using the navigational attribute to speed up the performance.

    Check this link on aggregates:



    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 26, 2006 at 07:59 PM

    Hi Chris

    It is possible that an index on the /BI0/XMAT_PLANT table could help. Please see note 402469.

    Good luck


    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      The aggregate was a good suggestion. You just have to evaluate the impact of the Change Run time vs the improved query time and consider how often this query runs each day. It may still be useful to add an index on the Nav Attr table to help queries that can't use the aggregate.

      Indices can also be added to a dimension table to help with large dimensions that have multiple characterisics. If you get an Explain Plan showing how the query is executed and the estimated cost of the different table accesses, you can review it with your DBA and see if additional indices might help more. You can not add the indices thru Admin Workbench, they would need to be added using SE14 which usually a BW developer doesn't have access to.