Form Performance Analysis

Introduction

If your Forms are taking too long to refresh, this article will walk you through the steps you can take to troubleshoot their performance. Your Form's performance will affect your Dashboard Apps and the users that will interact with the Apps, and therefore, it is important to optimize your Forms to minimize wait time for users. 

 

 

Check Data Partitions

First we will check the Data Partitions so we can determine whether the performance issues are linked to the Form itself or the underlying data.

 

Step 1. Identify which Form is not performant.

 

 

Step 2. Test the partitions in the Model that affect the Form. To do this, go to the Model and click on Data. Select the partition(s) that affects your Form (Rules, Calculated, Writeback, etc.). Click refresh. Check the bottom right of the Form for the load time. Do this for all the partitions.

 

 

If the Rule Partition is slow, refer to the Rule Performance Analysis troubleshooting article. If any other partition is slow, read the Performance Tuning Guide's partition section. 

 

Note: If the partition is slow, changes to the Form will likely not improve performance. The issue is with the size of the data in the partition. You can create an Archived Partition to store all the data that is not currently needed. Keep in mind, that for certain additions of SQL Server, you can only have up to 3 partitions including the Rule and Writeback partitions. Read Supported Features for SQL Standard for more information.

 

 

Form Native MDX

If all of the Data Partitions are quick to load, then the structure of the Form could be causing the performance issues. The structure of the columns, rows, and filters is determined by MDX. You can see the raw MDX of the Form in the Form Editor.

 

Step 1. Test the MDX of the Form. To do this, go to the Native MDX tab and copy all of the content in the Current tab.

 

 

Using SQL Server Management Studio connect to Microsoft Analysis Services and copy the code into an MDX query window. Run the query and check the length of time it took to run. To learn more about running MDX queries, read the MDX Training Basics series.  

 

 

 

Step 2. If running this query directly in Analysis Services does not cause performance issues, skip to the next section.

 

If the results still take a while to load, the query itself is the problem. To narrow down the issue, we'll need to start isolating the parts of the query that may be the culprit.

 

To start, remove the part of the code that is labeled PROPERTIES (within ON ROWS and ON COLUMNS). This is additional data that Kepion adds to your Form by default, but is usually not necessary for the query to function properly.

 

 

Step 3. Before running the edited query above (and every subsequent query), clear the SSAS Cache to get accurate run times. If you do not clear your cache, you may get false positives that the performance has been improved, when in reality, SSAS cached the original query.

 

Run the newly edited MDX query and check the run time.

 

 

 

Step 4. If this does not work, remove any calculated members in the SELECT and WITH statements. Make sure to clear the cache and then run the query again. MDX calculations are often the cause of performance-related issues, so it's important to run the query without them to see if the performance is affected.

 

 

 

 

Step 5. If the query time is still longer than desired, you will need to make changes either to the structure of your Form or the scope of your query. Adding more filters or increasing the specificity of your filters will nearly always increase the performance of your Form. If there are particularly complex hierarchy selections within your ROW or COLUMN definitions (such as aggregations, variable definitions, etc.), you can try changing these to potentially increase performance as well.

 

 

MDX Rules

If the Native MDX from the Form does not reveal any performance issues, navigate back into Kepion to the Model that contains this Form.

 

Step 1. In the Model, select the Rules node. In the Rules section, uncheck the Active box for each MDX script. Redeploy the application.

 

 

Check the Form to see if removing any of the scripts improved performance. Click the Green checkmark in the upper left-hand corner to see the run-time of the Form. If performance improved, reactivate each script one at a time to isolate which one is causing the issues.

 

 

 

Step 2. To investigate further, try commenting out sections of the script at a time to see if there's a particular part that's causing the issue.

 

 

Instead of going back into SQL Sever Management Studio to clear the cache in between changes, navigate to the Deployment node and select the Clear query cache button.

 

 

 

Dimension Member Formulas

If the MDX rules in the Model do not reveal any performance issues, the problems may be caused by calculated Dimension Members.

 

Step 1. In the member lists used in the Model, check the Calculations tab. If there are any Member Formulas defined, write down or save the definitions in a text editor or another program. Once you have recorded the calculation definitions somewhere else, delete all the formulas. Process the Dimension or redeploy the application.

 

 

Step 2. If this improves performance, start adding the Member Formulas back in one at a time to see which one is slowing down the Form. 

 

 

Form Formatting

If none of the previous steps improved performance, the issue may be with either the formatting or other features enabled on the Form.

 

Step 1. Create a copy of the Form and clear or remove all the formatting options, such as:

• Drop-downs
• Annotation (text) cells
• All properties
• All cell attributes
• All general formatting

The query time should be almost identical to the raw MDX in Microsoft Analysis Services from the first section.

 

 

Step 2. One-by-one, add the following options back into the Form, as necessary. If any of these options slow your Form down, stop and investigate it.

 

 

At this point, you will have recreated the original Form. If you have more questions on how to resolve the performance issues you've discovered, read the Performance Tuning Guide to find the options and settings that tend to have a greater impact on performance.

 

If you need further assistance, feel free to submit a ticket to our Support Team.