PerformancePoint Monitoring Data Source Connection Problems
Data Source Connection Failed!The inability to connect to a data source is all about permissions. Well, duh! Many people using PPS M&A (and BSM) for the first time pull their hair out trying to determine why data source connections keep failing. Most of the time they are looking at their own permission levels as the source of the problem "...but I'm an administrator on all these boxes!!"
A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.
That last sentence is important so it's worth repeating...
A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.
It does not matter if your personal administrative account "rules them all" within your IT infrastructure; if the identity account configured in the appropriate PPS application pool does not have permission to connect to and read data from any data source (OLAP, relational DB, an Excel file, a SharePoint list... anything) your connection will fail. Period.
By default PPS is not trying to make a connection as you, it makes the connection as the identity account configured within one of the Monitoring app pools.
NB: this post is all about the default data source connection behaviour of PPS M&A. Per User connections (where you are making the connection as yourself) can be configured, but that is another kettle of fish altogether. Per User connections take a bit of manual web.config work to configure, so you will certainly know if this has been set up (it's not just a simple checkbox that can be inadvertently ticked).
Application Pools
The Monitoring server configuration process creates and uses several Application Pools. For the purposes of this post we are only looking at two: PPSMonitoringWebService, PPSMonitoringPreview. Nonetheless we must also be wary of identity used by the SharePoint - 80 app pool too.When you initially configure your monitoring server you are given the chance to stipulate which account will be used as the identity in all app pools. So whatever you choose will be configured for all. The identity can be changed for each individual app pool as needed. Best practice here is to always use a specifically-created domain account.
PPSMonitoringWebService app pool identity account is used to connect to the Monitoring web service when you are working in Dashboard Designer (i.e. creating data sources, scorecards, KPIs etc.).
PPSMonitoringPreview app pool identity is used by the preview web site. This site allows designers to deploy and preview dashboards instead of having to deploy to SharePoint every time. If you deploy your dashboard to a preview site, any data source connections are made using the identity configured within this app pool.
SharePoint - 80 application pool identity is used to make connections once a dashboard has been deployed to SharePoint. Ensuring this app pool identity has appropriate access to the PPSMonitoring DB and data sources is essential. It is recommended to make the identity account used by this app pool to be the same as the PPS-created app pools. This just saves the hassles of aligning the permission set of two accounts rather than just one.
TIP: When trying to troubleshooting a connection/permission problem of any sort run a Profiler trace to see precisely who is making the connection attempt (and failing in the process). If you have different identities in the various app pools this will at least point you in the right direction as to where the permissions problem may be originating from.
