Your Forms' performance has a major impact on your Dashboard Apps and their users. If your Forms take a long time to refresh, users will lose precious time. This article will explain how to troubleshoot their performance your Forms to ensure optimal performance.
Check Data Partitions
We will first check the Data Partitions, so we can determine whether the performance issues are linked to the Form itself or the underlying data.
1. Identify which Form(s) is not performant.
2. Go to Modeler and find the Model that contains the Form.
3. Click on Data and double-click the Partition(s) affecting your Form.
4. Click Refresh.
5. Check the bottom of the table for the load time.
(Optional) 6. Repeat steps 3-5 for all the Partitions potentially slowing down your Form.
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 three 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 load quickly, then the Form's structure could be causing the performance issues. The structure of the columns, rows, and filters is determined by MDX, so you need to take the Form's raw MDX from the Form Editor.
Tip: To learn more about running MDX queries, read the MDX Training Basics series.
1. In the Modeler, open the underperforming Form.
2. Click the Edit icon and go to Advanced > Native MDX.
3. Copy the code in the right pane.
4. Open SQL Server Management Studio, select Analysis Services for the Server type, and click Connect.
5. Select New Query.
6. Paste the MDX code into the query window and select Execute.
7. Verify the runtime at the bottom-right of the SSMS window.
Note: If running this query directly in Analysis Services does not cause performance issues, skip to the next section.
Form Native MDX Extended
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.
Attention: Clear the SSAS Cache each time you edit the code in order to get accurate runtimes. 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.
1. 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 proper functionality.
2. Click Execute to run the newly edited MDX query and check the runtime.
3. If this does not work, remove any calculated members in the SELECT and WITH statements. MDX calculations are often the cause of performance-related issues, so it's important to run the query without them.
4. 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 boost your Form's performance. If there are particularly complex hierarchy selections within your ROW or COLUMN definitions (e.g., aggregations, variable definitions, etc.), try changing them to potentially improve performance.
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.
1. In the target Model, select the Rules node.
2. Uncheck the Active box for each MDX script.
3. Redeploy the Application.
4. Open the Form to see if removing any of the scripts has improved performance.
5. Click the checkmark in the upper right-hand corner of Kepion to see the runtime of the Form.
6. If performance has improved, reactivate each script one at a time to isolate which one is causing the issues.
7. To investigate further, try commenting out parts of the script to see if a particular section is causing the issue.
Tip: Instead of going back to SSMS, clear the cache in between changes by going to the Deployment node and selecting Clear query cache.
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.
1. Go to the Member Lists used in the Model and select the Calculate tab. If there are any Member Formulas defined, write down or save the definitions in a text editor or another program.
2. Select the box next to Member Label to select all of the formulas.
3. Click Remove to delete all the formulas.
4. Process the Dimension or redeploy the application.
5. If this improves performance, add 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 as improved performance, the issue may be with either the formatting or other features enabled on the Form.
1. Create a copy of the Form and clear or remove all the formatting options.
For example:
- 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.
2. Add the options back into the Form, as necessary. If any of these options slow down your Form, 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.