Each rule in PerformancePoint Planning Business Modeler has three specific attributes that govern how the rule is managed and processed:
- Rule set type; for example, the procedural rule set.
- Rule type; for example, procedural rules or definition rules.
- Rule implementation; for example, Multidimensional Expressions (MDX) or PerformancePoint Expression Language (PEL).
Choosing the right type of business rule depends on the the context of your business requirements. For example, technical accuracy of a rule can be done through any rule type - but choosing the right rule type requires you to consider how the resulting data is processed and stored in the system and how that impacts or aligns with your business requirements. I have described the behavior of PPS rule sets below. Any rules that are added in these rule sets follow the described behavior.
| Rule Set Type | Best Used For | Execution |
| Procedural rules set All rules implemented here are saved as a stored procedure in the application database and are executed by the Business Modeler client, scheduled to run as a job by a the Modeler client, or launched as a Job in the Excel Client by a user with Modeler security permissions. Permitted rule type: ▪ Allocation rules ▪ Assignment rules ▪ Model Variable rules | Calculations that you want to perform as needed, or for calculations that you want to run in a scheduled job. For example: ▪ Allocating resources. ▪ Performing user-defined calculations. ▪ Assigning values to model properties. | -Run directly from the Business Rules workspace. - Include in a scheduled job. |
| Procedural rules set - MDX Query Implementation, this rule will query the cube and then write the results to the model fact table in the application database. The cube will not updated with the result until the next reprocess. | Use when a rule will write to the Fact Table and effects a small number of cells (<500,000) | |
| Procedural rules set – Native MDX Query Implementation | Use when a rule will write to the Fact Table and effects a small number of cells (<500,000)> | |
| Procedural rules set - SQL Implementation, this rule will query, calculate and store the result on and in the Application database. Again, a reprocessing of the cube needs to happen before the results are in the cube. | Use when a rule will write to the Fact Table, effects a large number of cells (≥500,000) and does not require the use of CASE or IF statements. | |
| Procedural rules set – Native SQL Implementation | Use when a rule will write to the Fact Table, effects a large number of cells (≥500,000) and requires the use of CASE or IF statements. | |
| Automatic All rules implemented here are stored as MDX script in the AS cube. The values are persisted in the fact table each time the rule is run. These rules run when an internal dirty flag is set and the system understands data has changed and rules need to be rerun. You must reprocess the cube for the results to commit to the AS cube. Permitted rule type: - Assignment rules | User-defined assignment rules that you want Planning Business Modeler to run automatically each time the data in a model is reprocessed (an incremental deployment). It can be a good substitute for a definition rule. For example, you might use an automatic rule to calculate a specialized aggregation, or to make sure that a forecast is updated with any input before additional jobs are run. | - Run directly from the Business Rules workspace. - Include in a scheduled job. - Automatically runs when the model reprocesses data. |
| Definition Business Modeler runs definition rules every time you deploy the model. All rules implemented here are stored as MDX script in the AS cube. These rules run when you deploy the model or when the scope of a change impacts the scope of a definition rule. Calculated values are never stored in the fact table and are only accessible in the cube. Permitted rule type: - Definition rules | Use when a rule will always be true and you do not need to see the data in a fact table. Values that you want to automatically recalculate in the cube. For example, Planning Business Modeler runs standard aggregations as definition rules, so that the model cube always has the most recent values. | Automatically runs every time that the model is deployed and every time that a cell in the rule scope is queried. |
| Consolidation Use for rules that calculate eliminations for a financial model with shares calculation. | Use when you want to run eliminations especially with percent control or percent ownership. | Include in a consolidation job. |
| Currency Use to perform currency conversions. | When you need to access different type of currency exchange rate (e.g. average, historical, ending) | Include in a currency translation job. It also get triggered by consolidation job automatically. |
| Intercompany reconciliations IC reconciliation is included with two types of standard models: - The financial model with shares calculations - The financial model without shares calculations | Use to eliminate transactions between sibling entities. IC reconciliation adjusts for differences in the values that are recorded by the buyer entity and the seller entity for intercompany transactions. If, after adjustments for currency differences, the recorded values do not match, IC reconciliation creates balancing entries. | Include in an intercompany reconciliation job. |
| Outbound Outbound rule sets make it possible for you publish this semantically rich data to external data stores called Data Destinations that are specified by a data administrator. | In this manner, your Planning Server data is available to an extended business user community, and can be used by applications other than Planning Server tools such as Planning Business Modeler and PerformancePoint Add-in for Excel. For example, at the end of the budget cycle, a customer might want to use new fact data in an application such as SAP. Alternatively, a customer might want to create a data catalog of results from Planning Business Modeler. | Include in a data export job. |
| Combination: Definition Rule & Assignment Rule | Use when you want to see the results immediately and also want to store the results in the Fact Table. | |
For each rule sets, you can create the following types of rules:
| Rule Set | Rule Type |
| Automatic | Assignment |
| Definition | Definition |
| Procedural | Assignment |
Rules Guidance
- Too many rules may hurt performance.
- Definition rules with a lot of math and/or complex formulas such as IIF statements can hurt performance. Definition rules are more suited for simple arithmetic.
- Make sure rules do not divide by zero.
- Properly scope business rules.
Each rule type inherits the behavior of the rule set it is defined in. Based on the description above, there are some design considerations to take in while choosing the correct rule for your requirements. Here are few that come to my mind.
1. If you want to calculate a ratio which does not need to be persisted and may only used for display in a form, you may want to choose a definition rule. Be aware, that ratio will not be stored in the SQL table and so you can not use it for outbound rules or ETL processes that use SQL. Also, any change to a cell within the scope of the MDX generated query will cause this rule to rerun. Sometime, this becomes a performance problem depending on the number and complexity of definition rules.
2. If you choose a procedural rule, the results are processed on the server and will not show up immediately in a excel matrix. Why? Remember, the excel client queries the cube and you need a reprocess of the cube before a procedural rules results are shown. I usually set the reprocess interval in the Admin console to 1 second for demo scenarios. That way I can demo procedural rules without waiting a few minutes for the servers to sync. Of course, this is not advised for production systems unless. You should do load testing on the servers and determine the best interval for your application.
3. Automatic rules run all the time! This is a great benefit for most scenarios but if you have an automatic rule that is very complex, takes a long time to run and/or is CPU intensive, your performance will suffer. You may want to consider a schedule procedural rule.
To sum it up – take into consideration all angles of how rules work when choosing the rule type or combination of rule types to meet your technical and business needs.
