Thursday, 3 April 2008

PerformancePoint Monitoring Data Source Connection Problems - Solution for XP

Long title for a post! Over the last day or two I've been getting to grips with PerformancePoint 2007 , in particular the Monitoring Server product - including KPI's, scorecards, dashboards and so on. Be warned - there are some prerequisites including ASP.NET AJAX extensions which aren't mentioned on the PerformancePoint website.

Long story short, I came up against the problem detailed in full by Nick Barclay here. If you're running Vista or Windows 2003 you should read that post for the solution. However was I using XP.

Briefly, the problem was that creating a Data Source in the Dashboard Designer results in the following message:

"The PerformancePoint Server could not connect to the specified data source. Verify that either the current user or application pool user has Read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct"

The reason for the message is that PerformancePoint is written in ASP.NET and as such, under IIS 6 it uses several Application Pools. Its the account that these app pools run under that is used to connect to a Data Source. Unfortunately XP uses IIS 5.1 so there's no app pools and therefore Nick's solution doesn't work. However there is a solution that I stumbled across.

On IIS 5 ASP.NET websites run under the ASPNET account. However, changing the priviliges (ie adding to the Administrators group) is bad practice and more fundamentally won't work. The solution is to give the ASPNET account priviliges on the target Data Source. This is easy enough for SQL Server - simply add the ASPNET account as a user and give it access to the relevant database. The Analysis Services solution is slightly more involved.

Basically you need to add a Role to the Analysis Services cube and add the ASPNET account as a member. Here's some screenshots showing the process in SSMS.

Connect to your cube and navigate to the 'Role' folder and right-click on it. Choose 'New Role...':

On the Create Role dialogue click the 'Full Control (Administrator)' box, and give it an appropriate name (for the purposes of this example we'll leave it as 'Role'):

Click the 'Membership' property in the right-hand pane. Then click the 'Add...' button:

Type ASPNET as the object name and click 'Check Names' - it should underline it:

Click OK, and OK again on the 'Create Role' dialogue. Fire up the Dashboard Designer and you should find the Data Source connects fine.


Rajeswari said...

Thank you so much Phil.

I have the set up in XP and I exactly followed the steps provided by you and configured the new role.
When I fired the dashboard designer. I still got an error message "The database connection failed. Please contact an administrator"

Can you please help ?

Thanks in advance!!

Tavis said...

Indeed, this was exactly my problem.

Rajeswari...I think you get the same error if you can't connect to the pp monitoring server. (which is VERY deceptive)

Click on the "windows globe" button in the top left corner --> options(bottom right) --> server tab. There's a connect button in there, click connect to see if you're getting to the PPSmonitoringwebservice.asmx (or whever it's called...i dont have it in front of me, but it's the webservice file).

If that's not connecting, you gotta get that working first.