Alan Whitehouse's Ramblings

Continuing to work until my heavy investment in lottery tickets finally pays off….

  • Categories

  • Archives

  • Deep Thought

    Historically speaking, all true change in the world has come thanks to leaders emerging, them taking charge and giving the masses someone to rally around. Can an intentionally "leaderless" movement survive or will it just slowly fade away?

  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 18 other followers

  • Me!

  • Advertisements
  • Disclaimer

    The views expressed in this blog, while intelligent and always right, are strictly my own, and do not necessarily reflect the views of anyone else with which I am in any way affiliated. And don't forget, I own the rights to all information on this blog (except for the stuff I stole from other people).
  • Current Rant


    You want change, then get involved. Vote, run for office, go to shareholder meetings and contact advertisers or investors. Sitting around banging drums, singing kumbaya, smoking weed and having a camp out under the stars is not going to get you the change you want.

  • Admin Stuff

PerformancePoint PEL Business Rule Creation Walk Through

Posted by Alan on April 23, 2008

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:

Sales Forecast Model Summary 

Here are details on the Account Dimension:

 Account Dimension

Here are details on the Customer Dimension:

 Customer Dimension

Here are details on the Scenario Dimension:

 Scenario Dimension

Here are details on the Time Dimension:

 Time Dimension

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

Sales Forecast Input Form

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:

Sales Revenue Calculation Rule

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”:

 Variable Cost Calculation Rule

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:

Sales Forecast Input Form with Comments

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


9 Responses to “PerformancePoint PEL Business Rule Creation Walk Through”

  1. Hi Alan,

    Nice post. Would it be worth adjusting the “write” security settings on the Sales Revenue and Variable Costs account dimension members so that users are not provided with a yellow input cell? This way they won’t even get the chance to try and calculate the value for themselves.


  2. Alan said


    Hadn’t thought it through that far (the comments were the easy thing to show and I think the concept is useful elsewhere as well) but your suggestion would definately be the proper way to implement this type of deployment it in a production envrionment. I can see where people wouldn’t bother to open the comments and then get frustrated because their number keeps getting changed and they don’t know why.

    Thanks for the great addition to the concept.


  3. Ashish Manekar said

    Thanks for such a nice Guideance ……

    How to apply business rules for the Application where end-user put value into ExcelSheet1…and …recieves(can see changes) on Excel sheet 2

  4. Alan said


    If the matrix on ExcelSheet1 and the matrix on ExcelSheet2 are tied to the same model, once data is entered into ExcelSheet1 after they hit “calculate” those figures will appear in ExcelSheet2.

    If the each matrix goes against a different model, then you will have to write a backend rule that moves data from one model to the other.

    Hope this helps.

  5. Ashish said

    Thanks Alan,
    I will try this soultion…thanks for your help,i will be use Push or pull allocation rules to send data from one model to other.Do you think that will work ?

  6. zang said

    Good way to explain ……..thank u guys

  7. Random T. said

    I noticed that this is not the first time at all that you write about the topic. Why have you decided to write about it again?

  8. wordpress business themes,best wordpress business themes,business themes wordpress…

    […]PerformancePoint PEL Business Rule Creation Walk Through « Alan Whitehouse’s Ramblings[…]…

  9. Pewekar said

    Is this possible in Sharepoint 2010 & 2013 ?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: