Our tech team has noticed a pattern of SAP BPC performance issues with certain SAP BPC 10 and SAP BPC 10.1 systems on Microsoft. Dimension indexes are becoming fragmented, which seems to relate to dimension processing. The addition of even a single member to a dimension, with the same processing, fragments the first index (clustered) in the list. In turn, Analysis Services is unable to read from the dimension table efficiently, which in turn kills retrieval of data (reports / schedules) performance.
When the index is fragmented, it causes a “Table Scan” requiring the lookup of records one by one until it locates the queried record. If the index is working correctly, it can be looked up in milliseconds. If you process a dimension manually or automatically, be sure to check the “DIM” table index and rebuild as necessary.
The clustered index, the first one in the list, is the main concern with this issue. It can be rebuilt manually at any time without negatively affecting anything and can significantly improve performance! This can be an issue even with relatively few records in the table. Database administrators might claim that with 1000 records in the table it wouldn’t be an issue. On the contrary, it is an issue because no data is held in Analysis Services, it reads everything from SQL.
As an example, a recent Column5 customer came to us with a performance struggle – it was taking them 30 minutes to run a report. Column5’s tech team rebuilt a single clustered index on their account dimension, and the same report runtime decreased to 24 seconds.
To rebuild the index, you’ll need to do the following:
This process usually takes less than 20 seconds but can significantly improve performance. This should be done if the index is at least 17% fragmented.
If you still have performance concerns having followed the above guidance or other technical issues, please contact Column5. In addition to immediate technical support help, our technical services team offers a wide range of performance tuning and technical environment review options.