General “tips” for investigating and improving performance in the PPS Add-In for Excel (in no particular order…)
Based on the steps we take when investigating performance issues with the PPS Add-In for Excel, I thought it may be helpful to post some of the things we check for. In no particular order, here is what we walk through when trying to improve performance for a form or report in the PPS Add-In for Excel.
1. Compare the results of running queries in the PPS Add-In for Excel vs. directly against Analysis Services
a. Use the “View MDX” option from the PPS menu/ribbon to copy the MDX for a matrix. Use SQL Management Studio or other tool to run this MDX directly against Analysis Services*. If the time is similar then investigate optimizing or changing the way calculations are defined in the model. *Note: You will need to adjust the MDX query to return results on 2 axis to run it in SQL Management Studio by moving the ‘on pages’ portion of the query to the where clause or other approach to making the query result render-able by SQL Management Studio.
2. (Excel 2007 Only) Set the matrix style to none
a. The matrix styles feature adds additional processing time to format the matrix. Setting the matrix style to none will speed up performance by removing this step.
3. 3. Check if the time the add-in is spending “detecting writeable regions” is a bottle neck. If so separate the writeable areas and read only areas in a matrix into separate matrixes.
a. If a matrix used in an assignment has “Allow Data Entry” set to true, then the Add-In will do additional processing to determine what cells are writeable and highlight them. If setting the “Allow Data Entry” property to false speeds up performance significantly you may consider separating the potentially writeable areas of a matrix and the read-only areas of a matrix into separate matrixes and setting the “Allow Data Entry” property to false on the read only matrix.
4. 4. performance “gets slower” over time while entering data in an assignment it may be because the “What-If Change-List” is growing large and thus taking more time to calculate.
a. Using the "View change list" option you can view the current contents of the assignment change-list. If it appears your performance problem is occurring as this list grows, consider doing the following:
i. Break the data entry matrix in to multiple smaller matrixes. The “What-If” query will only apply over the matrixes where changes are being applied, so data unaffected by the what-if changes will return faster when in separate queries.
ii. Consider instructing users to do use the “Submit Draft” option to submit changes to the server that the user is done changing, use the “Clear Changes” option to clear the change-list, and then re-download the assignment cache. This could help keep the change-list size smaller and lower the amount of time for recalculations
5. 5. In general, the first time a query is run in a report or assignment it is usually is slower. Additionally if it is the first query executed against the Analysis Services server it will also be slower. Check and see if performance improves on subsequent refreshes.
6. Check the size of the local cube being downloaded in an assignment where the "Automatically cache assignments" option is turned on.
a. When an assignment is cached locally a local cube is created to store data and perform recalculations. If this cube is large the client machine may have difficulty downloading and querying against it.
b. To check if this is an issue clear the offline cache and disable the automatic caching feature to see if “online” performance is significantly better. If it is investigate making the size of the local cube that will be created by:
i. Reducing the read permission scope for users
ii. Disabling the “offline” functionality for the model in BizModeler which will disable the automatic caching feature for all assignments that use the model.
iii. Using the “manually define sub-cube” option to define a more specific sub-cube to download and cache locally.
7. Try to use calculated members over Design-Time formulas whenever possible
a. Rendering and processing of design time formulas by the add-in is a common performance bottle neck.
No comments:
Post a Comment