Time Intelligence Quirk in PPS Monitoring
Posted by Alan on January 19, 2009
Recently, a coworker of mine (we will call him Richard) nearly frustrated himself to death due to an “undocumented feature” within PPS Monitoring. Here are the details:
We had been working on a fairly straight forward PPS Monitoring project for a client. The main emphasis was the creation of a balanced scorecard tied to a corporate strategy map. This client, as most clients, does not have perfect data. Some of the data for their KPIs (i.e., customer satisfaction, corporate awareness, etc.) was not stored in any system, and other more formal data (i.e., revenue growth, etc.) was found only in the SQL Server tables of their ERP (Dynamics NAV) system. To handle the first issue we decided to utilize SharePoint lists to track this data and for the second we decided to just pull straight from the SQL tables. These choices made sense as they would work just fine, carried no additional cost to the client, could be implemented quickly and were easy to maintain. So far, so good.
Now for this scorecard Richard decided that it would be valuable to implement a page filter where the client could look at scorecard values and the strategy map over various time horizions. Within this filter, one of the choices was to be a Year-to-Date (YTD) calculation.
So we did all our design work and then used the PPS Web Parts to pull the scorecard and strategy map into SharePoint. Now it was time to test our filter. Everything worked beautifully — beautifully that is until we got to testing the YTD filter. When we chose that one, instead of getting a correct YTD calculation returned, all we got back were blank rows.
Well after much troubleshooting, research and a phone call with a Microsoft PPS Product Manager, we were able to confirmed that there is a technical issue with how PPS Monitoring deals with time intelligence when the values are coming from a tabular data source such as SQL Server or SharePoint lists. Because of this, if you have a list of values that are broken down monthly (i.e., Jan = $100, Feb = $200, Mar = $150, etc.) if you try to use the time intelligence feature to calculate a year-to-date number it just doesn’t work. No values are returned at all when the calculation runs.
Just to be clear, this is only a problem with tabular data sources and the system works correctly if your data is coming from an analysis cube. We also got word back from Microsoft that this issue should be addressed with release 2.0.