MdxQuery implementation in Planning Business Modeler

Typically, MdxQuery rule implementation is used for Assignment Rules or Allocation Rules. PerformancePoint Planning Business Modeler translates rules that have MdxQuery implementation into an MDX query with calculation. PerformancePoint Planning Server retrieves requested fact data, performs any specified calculations, and then stores the calculated values in the underlying fact table.

Rules that have MdxQuery implementation are run only when Planning Business Modeler specifically invokes the rule during model data reprocessing, or when a user specifically runs a rule. If another rule causes data changes before the time that you run your rule, the results from running your rule might not include those data changes. In addition, data changes that are caused by a rule that runs after your rule has executed might overwrite the calculations performed by your rule.

In addition, because rules that have MdxQuery implementation retrieve data from the model cube, the results of MdxQuery rules account for any MdxScript calculations that are performed on the model cube.

In this topic

When to consider MdxQuery implementation

Performance characteristics


When to consider MdxQuery implementation

Consider using rules that have MdxQuery implementation when

  • Your rule performs calculations on data in the cube, such as aggregation

  • Your rule performs dimension hierarchy traversal.

  • Your rule requires complex business logic. Rules that have MdxQuery implementation are written in Microsoft Office PerformancePoint Expression Language (PEL). PEL supports many features that resemble, or are equivalent to, standard MDX language.

  • Your rule requires MdxQuery implementation, such as cross model allocation.

Top of Page

Performance characteristics

MdxQuery rules in Planning Server can fully use the rich features of standard MDX language in operating on the underlying cube. However, MdxQuery implementation in Planning Server inherits not only these features, but also inherits the constraints of OLAP architecture.

A rule with MdxQuery implementation operates in two phases:

  • Query

  • Writing result data to the fact table

Typically, a rule with MdxQuery implementation writes data to the fact table at a much slower rate than a similar rule with SQL implementation.

Several other factors can constrain query performance. The following situations could cause slow performance in the query phase.

  • Sparse cube data

  • Aggregation overhead that is introduced by the dimension hierarchy design

  • Interaction between existing MDX scripts that are installed in the cube

Top of Page


When you run several rules that have MdxQuery implementation, Planning Business Modeler runs the rules in the order that the rules are listed. To view the results of calculation, you must reprocess the model data.

In addition, Planning Business Modeler writes calculated results to the fact table. Therefore, virtual cells that are not represented directly by table values may not be included in the scope of the destination for writeback. This includes virtual cells such as cells whose coordinates contain the NONE member.

If a rule that has MdxQuery implementation is executing when cube reprocessing initiates, Planning Server might cancel rule execution. The following list shows the ways that cube reprocessing can be initiated.

  • By user command in the Planning Business Modeler user interface

  • By Planning Server as part of automatic, periodic reprocessing

  • By Planning Server when a rule executes with the Reprocess after execution rule property set to TRUE.

If Planning Server cancels the rule, it displays an error message indicating that the MDX query against the cube is being canceled. If this occurs, you must run the rule again.

When Planning Business Modeler executes a rule with MDXQuery implementation, Planning Server does update or insert any records that fail validation.

For example, consider the situation in which a rule attempts to write to leaf level and non-leaf level members of a dimension, and non-leaf writeback is not allowed for the given dimension. In this case, when the rule is executed, the records that correspond to non-leaf members will result in validation errors, and Planning Server will not write any data.

Note: Rules with SQL implementations behave differently in similar situations.

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!