Best practices for writing rules
The following list provides guidelines to help you avoid performance pitfalls.
Explicitly scope all dimensions.
By default, if a dimension is not explicitly scoped, the rule compiler sets the scope to include all leaf-level members. This can adversely affect performance when the rule runs.
For example, for some models, the PEL compiler would interpret the SCOPE statement
SCOPE(Scenario.AllMembers)as follows:SCOPE(Account.AllMembers.Members, BusinessProcess.AllMembers.Members, Scenario.AllMembers.Members, Entity.AllMembers.Members Time.AllMembers.Members);Limit the number of cells in a cube that can be acted on by a rule set.
Do not write rules that affect too many cells in the cube. If you find that a rule runs slowly, consider dividing the functionality of the rule among several smaller rules, and perhaps grouping them in a rule set.
Whenever possible, write the rule to be as general as possible.
For information about how to use conditional directives to enable your rule to work with different models or making the same calculations apply to different dimension expressions, see Making a rule more general with #if - #else.
For information about how to use parameters to obtain user input at run-time, see Using variables and parameters.
Know the results of a rule before executing it.
For example, rules written with SQL or MDX implementation can modify or delete any data in the model cubes or SQL Server-based tables.
Similarly, rules written as Pass-through statements (PEL) modify data in the SQL Server based tables. Pass-through rules are actually sending embedded rules written in native MDX or native SQL.
Note
You cannot create or run native SQL and MDX rules without the approval of the PerformancePoint Global Administrator and the SQL Database Administrator.
Limit the scope of any single expression to no more than one hierarchy of a dimension.
An expression that operates on more than one hierarchy will cause a compiler error. For example, the following expression causes an error because two different hierarchies (Calendar and Fiscal) are applied to the Time dimension in the same expression.
[Time].[Calendar].[January 2006] – [Time].[Fiscal].[December 2005]If you reference a dimension more than one time in a rule with MdxScript or MdxQuery implementation, make sure that you use the same member set (hierarchy). Otherwise, the rule will fail.
Make sure that references to the Time dimension specify the same member set as defined in the model, such as All Members, Daily, Monthly, Quarterly, or Yearly.
When you troubleshoot a rule, view its generated MDX or SQL code by using the Debug Rule option. For information about how to use the option, see Debugging a rule.