SQL, MDX, and Data Rules power the calculations and movement of data in your Application. Improperly configured Rules have the potential to slow down your Application's performance. In this article, we'll explain how to check underperforming Data and SQL Rules and reconfigure them for optimal performance.
Data Rules
1. Identify the underperforming Data Rule.
Action and View Rules have different performance and optimization possibilities. Make sure you have chosen the rule type best suited for the task and performance.
Here are some considerations when deciding between Action and View Rules:
| Description | Action | View |
|---|---|---|
|
My calculated results need to be used in other calculations. |
X | |
|
I want the results to update on demand. |
X | |
|
I want to query the results fast. Changing the data is less important. |
X | |
|
My calculations are short and simple. |
X | X |
|
My calculations are converting values like currency exchange. |
X | X |
|
The calculated results need to be referenced in only one partition. |
X | X |
|
I need to change my data more than I need to query the results of my data. |
X |
Tip: To learn more about rule types, go to Modeler Tutorial Session 12a.
2. Test each step within the Rule to find the underperforming one.
To test performance: select the step, refresh the view, and then check the duration in the bottom-left corner of the screen.
3. If possible, reduce the number of steps. For example, rather than joining two tables together and then performing a calculation in the following step, it's likely possible to define the calculation directly into the Join transformation.
4. Linking Rules can sometimes slow performance. This is because linked Components function like Views, dynamically generating linked data. If possible, see if the data can be stored, or actualized, in a partition. Doing this allows you query the values directly from the partition, rather than having to refresh the entire view to get your data.
5. If your Data Rule is an Action rule, you can add parameters to limit the scope of data. Instead of pulling all the data in the fact table, you can use Parameters to specify a particular Scenario, Month, or other Dimension Member. Multiple Parameters can scope the data down even more!
Tip: For more information on parameters, go to the Modeler Tutorial Session 12c.
6. After adding parameters, you can often remove extraneous columns. Data Rules perform better when less data is shifting around. As a general rule, if your rule has a column scoped to a single parameter or Dimension Member, you can remove that column without affecting the calculation.
The calculation may perform better as each step has less data to carry over (e.g., instead of performing the transformations on 5 columns of data, you perform them on 2). However, make sure to add the column(s) back in at the end of the Rule by selecting them from the right.
Or in the Insert or Publish step, you can specify a variable or parameter value for the column source:
SQL Rules
1. Copy the text of the underperforming SQL Rule.
2. Open and log in to SQL Server Management Studio (SSMS).
3. Right-click the target Application database and select New Query.
4. Paste the text from the above SQL rule.
5. Select Include Actual Execution Plan 
and Results to Text 
.
6. Click Execute to run the script.
7. Check each tab for the results of the run.
8. In the Execution plan tab, see if any step of the process takes too much of the memory (Query cost).
9. In the Client Statics tab, check the time that each trial takes to run. If any trial takes too long or the average is too high, consider ways to reconfigure the query.
If possible, try converting the query to a Data Rule. Doing so can improve the efficiency of Rule break down and analysis.
MDX Rules
For more information on how to troubleshoot MDX rules, read the MDX Performance article's Form Performance Analysis section. In addition, please refer to the Performance Tuning Guide for information on how to optimize performance of your Application.