Best practices for writing rules in Planning Business Modeler

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


as follows:

  • 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.

  • 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.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!