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?
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: