MdxScript implementation in Planning Business Modeler

You can use MdxScript implementation only for definition rules. Typically, definition rules calculate values for cells in the cube that are frequently queried, or are Read-only. In addition, definition rules are used when the values in cells change often.

MdxScript implementation is often a good choice for variance calculations. For example, a common variance rule might calculate the difference between actual values and budgeted values. If the actual values frequently change, the values that are used in calculation should also be frequently updated to guarantee accurate results.

Another example of a common MdxScript rule implementation is a ratio calculation. Report clients access such information frequently. However, because ratio values might change in real time, you must calculate the value at the time it is needed. Otherwise, the system might store a result that does not reflect the real-time changes.

When a rule with MdxScript implementation is deployed, PerformancePoint Planning Business Modeler translates the rule into MdxScript, and installs the translated script into the underlying cube. Rules with MdxScript implementation do not affect underlying fact tables, and no results are persisted in the model. Every time that you query a cell set in the rule scope, PerformancePoint Planning Server performs the calculations of the translated MDX script and caches the results that are shown in the cube.

The SQL Server Analysis Services engine invokes MdxScript scripts in the cube as needed.

In this topic

When to consider MdxScript implementation

Performance characteristics

Limitations

Comments

When to consider MdxScript implementation

Consider using MdxScript implementation when you are working with cells that have one or more of the following characteristics:

  • Cell values are volatile, such as values that are re-calculated in real time.

  • Cell values are frequently queried.

  • Cell values can be Read-only.

Top of Page

Performance characteristics

The performance of rules that are written with MdxScript implementation depends on how the calculated values are used by other rules and queries.

For example, if your model contains multiple rules with MdxScript implementation, and those rules have many inter-rule dependencies, decrease in performance might be severe.

In addition, performance is strongly affected by the scope that is defined by the rule. For example, if a rule scope involves all members from multiple dimensions, performance might be very slow.

Top of Page

Limitations

MdxScript calculated values are not included in SQL queries and fact table queries such as queries from rules with SQL implementation. By implication, values that are calculated from rules that are implemented in MdxScript are not included in any part of the following:

Top of Page

Comments

The underlying server that is running SQL Server Analysis Services automatically determines the order in which rules with MdxScript implementation are calculated, based on dependency of intermediate results. As a result, if rules with different implementations affect the same cell, the MdxScript rule will be calculated last. In this scenario, the results that are calculated from the MdxScript rule will override the calculations from the other rules.

Definition rules with MdxScript implementation should explicitly specify the measure group to which the script applies, if any. Because the PEL compiler does not insert the default measure when one is not specified, failure to specify the measure may cause unexpected results, such as recursive overwrites.

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!

×