Reporting performance with navigational attributes

[Former Member]

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?

Thanks!

Chris

[Former Member · Accepted solution]

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:

http://help.sap.com/saphelp_nw04/helpdata/en/7d/eb683cc5e8ca68e10000000a114084/frameset.htm

Bye

Dinesh

[Former Member]

Hi Chris

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

Good luck

Kristian

[Former Member]

Thank you for your assistance. Are there any special considerations when adding a navigational attribute to an aggregate to be aware of? Are the attribute values actually stored in the aggregate then?

For example will this increase the time of "attribute change runs" (master data activation) or have other ramifications?

Thanks again,

Chris

[Former Member]

Chris,

Definitely there will be overhead on the "Attribute change run" job becasue of using the NAV attribute in the Aggregate. The nav attributes are stored in the aggregate, and realignment must happen when the master data changes.

Since MAT_PLANT has over 2 million values, you can also consider changing the Infocube design, by making 0MAT_PLANT as a Line item dimension...

Good Luck

Gova

[Former Member]

I created an aggregate with the navigational attribute and experienced a huge performance improvement. It went down from 15 minutes to about 30 seconds! The change run is running longer but not too bad.

Thanks again,

Chris

[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.