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.
The index should be taken care of during the dimension build process. If your system is performing differently, you may be affected by this bug and should open a ticket with SAP.
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:
- Open SQL Management Studio and connect to your database
- Drill into your database and find your “DIM” dimension tables (eg. DIMEntity” for the account dimension)
- Drill into the table on the left-hand side, and right click the “Index” folder
- Choose “Rebuild All”
- You can either click "OK" to rebuild the indexes or "Cancel"
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.
Related Articles:
Author Bio:
Charles Dagaev, an EPM technology expert, began his SAP BPC career in 2001 with OutlookSoft, the original creator of SAP BPC, and worked at SAP until 2008 when he came over to Column5. He is certified as both an Application and Technical Consultant for the software, having performed over 1000 technical implementations of the BOBJ Analytics software products across every different business vertical worldwide over the past 18 years. He currently runs the Technical Services group at Column5 with a team of SAP Analytics (EPM/EIM/BI/HANA/NW/BW/MS) resources, all of whom have been the Subject Matter Experts (SME) in the numerous areas of the aforementioned software including NetWeaver, HANA, and Microsoft technology stacks.
