For Want of a Nail…

So I spent about two hours this morning swearing and doing my best not to put my fist through the screen of my monitor.  Why you ask?  Well next week we are doing a presentation to a client that is interested in PerformancePoint.  So I am doing some mock-ups that are relevant to their business model. 

So to that end, I had created some dimensions, added them to a brand new model, deployed it and authored an input form in the Excel Add-In.  Pretty straight forward stuff.   I go back into the Modeler, create a simple cycle and then back into Excel to test it.  The assignment shows up fine, but for the life of me I can’t get my data entry grid to turn yellow!!!

So back into the Modeler.  I check my security and confirmed that I had remembered to grant the roll access to the model.   I check that the cycle has referenced the correct time period as my input form as well as the scenario I was expecting to use.  Everything was fine.  So I go and synchronize my dimensions again and I deploy the model again.  No luck.

So as my frustration builds, I create a brand new input form and all that goes with it.  No luck.

Now I am really frustrated to the point where I create a brand new model and then create a brand new input form.  No luck.   And the worse part is that it is still too early in the day to begin drinking heavily.

So I take a break from it and jump on this fancy thing we call the interweb and start doing some searches and checking some of the PPS blogs I read.  Luckily I see this new post by Peter Eb. on his blog about hiding filters to clean up your input form and while it is not the issue that I was facing it got me thinking in a different direction.

As it turns out I had a dimension in the model that I was originally planning on using, but when I created the input form I decided I did not need it.  When I recreated the model I had just blindly copied what I had in my first model, including the dimension I was not going to use.  And if you have a dimension in your model, it has to be on your input form (even if the member is set to none and it is hidden) or you are never, ever going to be able to do data entry.

If you are wondering what the title of this post has to do with the content then read the following proverb:

For Want of a Nail
For want of a nail the shoe was lost.
For want of a shoe the horse was lost.
For want of a horse the rider was lost.
For want of a rider the battle was lost.
For want of a battle the kingdom was lost.
And all for the want of a horseshoe nail.

It just goes to show you how something as small as having an unused dimension in a model can break everything else.   When working with PPS modeling, pay attention to detail.

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.

PerformancePoint Back-End Rules vs. Excel Formulas

So we have had some discussion here about the best time to use back-end MDX or SQL rules created in the Modeler vs. Excel formulas that are created within the Excel input form.   Some scenarios are simple and some are not so clean cut.  

For instance, it makes sense to use a back-end rule when you have to move data between multiple models or when you want to seed values into an input form for users to see when they open the form (i.e., last year’s actuals in this year’s budget input form).      

However, some situations are more vague.   How about when you want to multiply the value found in one cell by the value in another and have the results appear automatically inside a third cell?   Either a back-end rule or an Excel formula could work here, so which do you use? 

For me it all comes down to the level of control you need.   The advantage of a back-end rule is that the end-user cannot overwrite it and the calculation is carried out exactly like the author wants.   On the other hand, the Excel formula is easier to create and has the advantage of helping guide the user to the final outcome but gives them the flexibility to go outside the ”rules” if necessary.  

So which is best?  Ultimately it comes down to “it depends”.   I see use for both when deploying PerformancePoint.   

Interesting Take on Global Filters

Peter Eb. has written a neat article on his blog that shows a twist on using global filters.   Check it out.