SQL implementation
Allocation rules and Assignment rules may use SQL implementation. When run, 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 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.
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 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.
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.
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.
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.
PEL statistical functions such as those that compute values for linear regression
PEL consolidation shares functions, PEL consolidation method functions, and PEL consolidation and currency filter functions
PEL logical functions such as Iif (PEL)
For more information about how PEL functions are supported, see SQL generation for PEL functions.
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
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.
See Also
Concepts
About rule implementations
MdxScript implementation
MdxQuery implementation
Allocation rules