Beware of Rules with Circular References

So we found out something fun the other day.    We had a model that was up and running.   Everything was working fine, we were creating input sheets and submitting data.  Life was good.   Then stuff stopped working.    We created a new input form and tried to save it and got errors.   We tried to browse the cube in SQL Management Studio and it froze everything.  We tried to browse the data in the cube with Excel and it hung Excel.  We got error messages in Event Viewer.  We opened the model in the Planning Business Modeler (which we still could) and checked it out and it all looked fine so we deleted the cubes and redeployed and then found that this process just would not finish. 

So we did the logical thing and started to freak out.  In the back of our minds was the total number of late nights and weekends it would take to get us back to where we were if we had to do a complete rebuild from scratch.   Lesser men would have cried.

Then we calmed down and started to backtrack and look at what changes were made between when things last worked and when they stopped working.   It turns out that three new rules had been created and deployed and then things stopped working.   So we opened up PBM, deleted the rules, went into Analysis Services and deleted the cube and then redeployed — it all worked again!  

So what had happened?  Well one of the business rules that was written referenced itself.  In simplest terms, we had a formula like “A = A + B”.  So every time “A” changed, then it would take that value and add “B” to it which would mean that the system saw that “A” had changed so it had to add “B” again.  This would go on forever and ever.   The funny thing is that we did validate the rules using that function in the PBM and later found out that the validation routine only checks if your syntax is correct, not it your formula makes sense or not.    I am guessing that my cube was actually processing just fine, but it was literally going to take forever to finish.

I hope that the PPS development team will add the ability to check for circular references when rules validate in v2 of PPS.  Until then — watch yourselves.

Ramifications of Changing the Name and Label of a Dimension Member

Almost every component of PerformancePoint Planning (Applications, Model Sites, Dimensions, Cycles, Jobs, etc.) allows you to change the value in the “Name” field but not the “Label” field.  At a database level, “Label” is your unique identifier.  Now I personally have always thought that the PPS team got this backwards and that “Name” should be the unique ID and “Label” should be the changeable field, but they never bothered to ask me, so I guess we just have to go with it….

Now when it comes to Dimension Members they are treated a little differently.  You can change the value in either the “Name” or “Label” fields even after you have created and deployed your Model, created an Input Form, begun a Cycle and started submitting data. 

At a first glance, this seems to be a great thing.  Being able to modify the value in the “Label” field gives you ultimate flexibility to modify your structure as your organization changes over the course of time .  When you make a change, after you re-deploy your model when you open up your forms or reports, the new names are displayed.

However, there is an ugly downside to this flexibility.  If on your forms/reports you have included a filter, that filter will cease to work if one of the values in the filter has had its “Label” changed — basically the filter is now looking for something that does not exist anymore.  In addition, any custom created MDX rules that you have created will also cease to function.  Both of these will have to be re-worked in order for your Model to function properly. 

Ultimatley remember this  – a change to a ”Label” done in the PerformancePoint Planning Business Modeler does not roll-down to forms or rules, so be very careful and think it through before you ever change a Dimension Member’s Label.

Skill Sets Required to Successfully Implement PPS

Recently I did a skills inventory to see where we might need to add additional staff or build new skills in existing staff to keep up with our PPS pipeline and the expected deployments we see happening over the next 6 to 12 months.  I took into account the 3 components of PPS – Monitoring, Analytics and Planning.  What it comes down to is the fact that you need many different skill sets and no single person is going to have them all (or if they do, you probably can’t afford to hire them).  So what are the skills and skill sets I believe need in place to successfully implement a major PPS project?  Here they are:

• Business Consulting Skills:  Remember that a majority of your interactions will be on the business side of the house and not the IT side (probably 80/20).   Being able to effectively interact with CxO’s and department managers is going to be critical.   You can’t go in spouting off terms like SSIS, MDX and PEL and expect to get any results.
• Real Life Understanding of Business:  You have to be able to understand what drives business.   It is easy to discuss the theoretical value of a balanced scorecard, but in reality most companies have a lot of other fish to fry before they get to that point.    
• Understanding of the Client’s Business/Industry:   Whereas you don’t have to be an expert you have to be able to understand what they do, why they do it and the envrionment they do it in.   For instance, if they are a not-for-profit and all the end users are volunteers and they have high turn-over; , you may have to simplify the input forms and other screens to the point of reducing the quality of your data.  However the cost of not doing such may make dramatically increase the costs of managing the solution and push it outside of their budget.
• Accounting/Finance Knowledge:  For a monitoring or analytics project you can probably fake this as long as the client can point you in the right direction to the data and tell you what they want to see.  However, if you are going to do Planning, then a knowledge of accounting/finance critical.  If you can’t read/intrepret/understand an income statement or balance sheet, don’t undertand the ramifications of partial ownership which changes throughout the year and don’t know a debit from a hole in the ground, then you are in big, big trouble.
• SQL Server 2005 (2008):  PPS sits on top of SQL Server and therefore you are going to eventually have to mess around with it.  Either from an installation point of view, or maintenance or reporting.
• Other Data Sources:  In a perfect world, when it comes to Monitoring, Analytics or getting actuals into Planning, every host data source would be SQL Server based.  However, we don’t live in that perfect world.  The larger the client the more likely they will have multiple data sources and systems including Oracle, DB2 and databases you have never heard of before.   You will have to get at these somehow in order to complete your solution. 
• SQL Server Analysis Services:  M&A loves to consume cube data.  Planning stores the data in cubes.  You will be working with cubes eventually if you want to work with PPS.
• SQL Server Integration Services:  See my comment above about working with other data sources.  To get data into a data warehouse and cubes for Monitoring and/or Analytics or loading actuals in for Planning, you will be using SSIS.  
• SQL Reporting Services:  The Planning operational reports utilize SRS and as you are doing Monitoring or wanting to distribute Planning data the obvious choice will be SQL Reporting Services.
• SQL Scripting (T-SQL):  See my comments above about SQL Server and SSIS and you will realize why this is on my list.
• MDX and by extension PEL:  More advanced requirements will require more advanced solutions.  Whether it be doin some unique filtering and slicing and dicing within Monitoring or creating unique business rules within Planning you will need to know MDX and within Planning that extends to knowing PEL.  You won’t get though any real project without knowing them.
• Excel (including VBA and advanced Excel funtionality):  Part of your Planning solution is going to involve using some of the advanced functions of Excel.  Whether it be the cool data visualization components or the need to tweek the interface using VBA you are going to need to know more than basic Excel.
• SharePoint (WSS/MOSS):  Monitoring uses SharePoint to display your scorecards, dashboards, KPI’s and all the rest.  To do this successfuly and have it look good and to deal with things like security you will need to know SharePoint.
• Proclarity:  The base Monitoring and Analytic components lack the ability to create things like pie charts, heat maps or decomposition trees.  To get that functionality you will also have to implement PAS and Proclarity Desktop.
• Visio 2007 and Virtual Earth:  Anyone can knock out a simple chart or graph.  You want to make your data sing, then look at using Visio or Virtual Earth as part of your presentation layer.  By the way, Virtual Earth will end up requiring .NET development skills as well.

I may be forgetting something in my list, but I hope not because it is long enough as it is.   In short, to successfully do PPS, there are a ton of skill sets that are required.  Granted, not every project will require every skill set, but eventually you will need them all.

A Shout Out to Peter Eb.

Just wanted to publically say thanks to Peter Eb. for some advice he gave me in regards to PerformancePoint Planning and an issue I was having in regards to cascading values in rows/columns.   It is really going to allow us to provide our client with a better solution that will make the M&A portions of this project later this year go much much easier.   I think he is going to write it up on his blog.  If he decides not to, then I will as it is a solution I think everyone can use.