SQL implementation in Planning Business Modeler (PerformancePoint Server)

Allocation rules and Assignment rules may use SQL implementation. When run, PerformancePoint Planning Business Modeler translates rules with this implementation into SQL stored procedures that operate on the underlying fact tables.

Rules with SQL implementation run only when specifically invoked. Rules can be invoked by the PerformancePoint Planning Server system during model data reprocessing, or by user command such as Run from Planning Business Modeler, or Run Job from PerformancePoint Add-in for Excel.

Note:  Rules with SQL implementation run only when they are invoked. Thus, data changes that occur after a rule has been run will not be included in the results stored by the rule. In addition, data changes that occur after a rule has been run might overwrite the values calculated by the rule.

In this topic

When to consider SQL implementation

Performance characteristics

Limitations

Comments

When to consider SQL implementation

The following list describes rules for which you should consider SQL implementation.

  • Your rule uses a SCOPE expression that generates a very large MemberSet.

  • Your rule causes many cell values to be written to the fact table.

    For example, a typical use of a rule with SQL implementation is a forecast. In this scenario, the rule might copy all existing fact data, increase all values by 10 percent, and then replace the original facts with the increase values.

  • Your rule uses only the supported subset of Microsoft Office PerformancePoint Expression Language (PEL) functions.

  • Your rule does not use aggregated values, or it performs simple or no aggregation in its calculations.

    For example, if your cube contains sales fact data, the SQL Server Analysis Services server preprocesses aggregated data and caches the data in the cube. However, Planning Business Modeler translates a rule with SQL implementation into an SQL stored procedure. The stored procedure cannot retrieve values from the cube, but instead must recompute all the aggregated values.

Top of Page

Performance characteristics

In general, rules with SQL implementation run faster than rules with other implementations. Because the rule becomes an SQL stored procedure, the rule requires fewer queries and less movement of data. For large data sets, this can be an important performance difference.

Top of Page

Limitations

  • Rules with SQL implementation operate on the underlying fact tables. Thus, their results do not account for any MdxScript calculations that might be performed on the model cube.

  • To use rules with SQL implementation in a model, the Account dimension must incorporate a parent-child hierarchy. If the Account dimension has an All Members hierarchy or a Member Set View hierarchy, rules that have SQL implementation are not supported. For more information about how to select a hierarchy for a dimension, see Dimensions in Planning Business Modeler.

  • The PEL compiler only supports a subset of the PEL language functions for rules with SQL implementation. Additional constraints also apply to rules that use relative dimension member functions and parameters. The PEL compiler detects these limitations during rule validation, and displays explanations.

    The following list shows some of the categories of functions that are not fully supported for rules with SQL implementation.

  • Planning Business Modeler includes limited support for constant expressions and assumption models for rules with SQL implementation.

  • Planning Business Modeler includes limited support for main model sparsity.

    Addition and subtraction operations are supported only for expressions with the same sparsity level. Expressions with mixed sparsity levels are supported for multiplication and division operations. The sparsity level of the resultant expression will be the same as the sparsity level of the sparsest expression.

    The following table shows support for different types of sparsity.

Type of sparsity

Code example

Description

Constant sparsity

this = (MODEL(assumption_model_name), dimension_member) ) + 1

Not supported

Constant sparsity

this = (MODEL(assumption_model_name), dimension_member)) * 1

Supported

Assumption model sparsity

this = (MODEL(assumption_model_name1)) + (MODEL(assumption_model_name2))

Not supported

Assumption model sparsity

this = (MODEL(<iterm>(assumption_model_name)))

Supported

Main model sparsity

this = (product.h.lag(1)) - 1.5

Not supported

Main model sparsity

this = (product.h.lag(1))*1.5

Supported

Top of Page

Comments

  • Before Planning Server runs a rule with SQL implementation, it retrieves all cell values that are defined in the SCOPE expression. Calculations are performed on the retrieved values. Results are written to the fact table.

  • Within a single rule set, Planning Server runs rules with SQL implementation in the order that they are listed.

  • From the Business Rules workspace, you can deploy the rule, validate the rule, or run the rule. In addition, by using the Debug option, you can view the actual text of the translation Planning Business Modeler creates for your rule. For more information, see Debugging a rule.

  • When executed, rules that have SQL implementation update or insert only those records that pass validation. During execution, Planning Server discards records that fail validation for rules that have SQL implementation.

    For example, consider the situation in which a rule contains instructions that write to leaf level and non-leaf level members of a dimension, but non-leaf writeback is not allowed for the given dimension. In this case, when executed, the rule writes records that correspond to leaf level members only. Records that correspond to no-leaf members result in validation errors. During the write-back operation, Planning Server discards the records with validation errors.

    Note that rules that have MDXQuery implementation 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!

×