A brief overview of PerformancePoint Planning business rules

Over the course of the past year I’ve had a number of conversations on what type of business rule to choose for a PerformancePoint model.  The answer is always - ‘it depends’.  Choosing the right type of business rule depends on the the context of your business requirements. For example, technical accuracy of a rule (i.e. Sales Volume * Product Price) 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 follows the described behavior.

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.

Definition: All rules implemented here are stored as MDX script in the AS cube. These rules run every 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.

Procedural – 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. If the rule is a MDX defined rule, 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.  If the rule is a SQL defined rule, 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.

For each rule sets, you can create the following types of 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 us 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.

Those a few that come to mind.  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.

Kevin