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. This article explains to application modelers how to conduct rule performance analysis by checking underperforming rules and reconfiguring them for optimal performance.
Data rules
1. Identify the underperforming data rule.
Action and view rules have different performance and optimization possibilities. Ensure 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 to query the values directly from the partition, rather than having to refresh the entire view to get your data.
5. If you are testing an action rule, you can add parameters to limit the scope of data. Instead of pulling all the data from 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 many columns of data, you perform them on 2. However, remember to add the column(s) back in at the end of the rule.
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. Select 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 breaking down and analyzing rules.
MDX rules
For more information on how to troubleshoot MDX rules, read the MDX Performance section in article's Form Performance Analysis section. In addition, please refer to the Performance Tuning Guide for information on how to optimize the performance of your application.