MdxScript implementation

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

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.

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.

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:

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.

See Also

Concepts

About rule implementations