PerformancePoint PEL Business Rule Creation Walk Through

When writing my prior post about PerformancePoint Back-End Rules vs. Excel Formulas I got to thinking about all the frustration we have had here about getting our heads around the rules engine within PPS.  And the more I thought about it the more I realized that one of the biggest problems out there is that there are just not enough examples of user created rules being posted for other people to use as guides.

So what I thought I would do is create a basic model with a few dimensions and create a back-end rule that will multiple some values automatically.   I think this type of rule will be very useful to use when designing models as well as being very common.  Get comfortable, this is a long post.

Here is the scenario.  You have a sales forecast.  Within that forecast you want the end-user (the one doing data entry) to enter in the number of units they think they will sell to a customer over the course of the year.  You also are allowing them to enter in the price they will be selling those units for as well as the cost of each unit.  Now I realize that in real life you probably would not have sales enter in the costs per unit but I didn’t want  to complicate the scenario.  Finally, you don’t trust the sales staff to be able to properly multiple the units to be sold by the price and cost per unit to get a total sales revenue figure and total variable cost figure.  So to ensure the calculation is correct you want to implement a rule that does it for them, and since an Excel formula can be overwritten you decide a back-end rule created in PEL is the way to go.   

So to this end, I created a Sales Forecast Model that utilizes 4 dimensions (Account, Customer, Scenario, Time).  Just so what i so it is very clear as to what I was working with I have provided screen shots of my setups below.

Here are the details from the Model Summary page:

 

Here are details on the Account Dimension:

 

Here are details on the Customer Dimension:

 

Here are details on the Scenario Dimension:

 

Here are details on the Time Dimension:

 

Here is a copy of the Sales Forecast Input Form from the Excel Add-In:

So looking above, what my rules are going to do are 1) multiply “Units Sold” by “Price Per Unit” and populate the “Sales Revenue” line and 2) multiple “Units Sold” by “Cost Per Unit” and populate the “Variable Costs” line.  Net Income will be calculated automatically by the built-in PPS functionality. 

I am not going to get into if these rules should utilize the “Automatic Rule Set” or the “Procedural Rule Set” or a “Definition Rule Set” as that is a whole other topic.  For good reference on that, go get yourself a copy of The Rational Guide to Planning With Microsoft Office PerformancePoint Server 2007.  I chose to create my rule under the “Definition Rule Set”.   

My first rule is entitled “SalesRevenueCalculation”.  The key here is to remember that a rule has 2 parts to it — “scope” and “this”.  The “scope” is what you want to effect and the “this” is what you want to do to it.  The syntax of my rule is shown in the screen shot below:

So here is my best attempt to translate this rule into plain English.   The “scope” portion of the rules says the following (remember my translation does not necessarily correspond to the order the items appear on the screen):

• For any month within the year 2008 (my input form and assignment only referenced 2008 and the months January to December)
• For any customer that I select (remember my model only had 3 customers in the dimension)
• When I am working with the “Forecast” scenario
• Modify the row “Sales Revenue” according to my “this” statement

Next we have the “this” component which states:

• Make “Sales Revenue” equal to “Units Sold” multiplied by “Price Per Unit”

What is cool about the way the scope works is that I could have had this rule execute only for particular months or only for a single particular customer.  Basically you can get very granular as to when a rule comes into play.

The example above shows the whole rule expression.  I like to use the Scope Editor which is the button with all the lines on it on the very right at the top of the screen.  I find it easier to create Scopes with it.  Below is a screen shot of the “VariableCostCalculationRule”:

 

Finally, since the rule will overwrite what the user puts in the “Sales Revenue” and “Variable Costs” fields, it might be nice to let them know that this is being calculated automatically and that any value they put in the field will be replaced.  To that end, I added a traditional Excel comment as shown below:

There you go.  I hope my example makes sense and helps some of you out when working with rules within PerformancePoint.