Rational Guide to Monitoring and Analyzing now available

The Rational Press team have just informed us that The Rational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007 is now available.

Multiple Filter Links on Strategy Map Reports

If you've been wondering why you have been unable to pass more than one dashboard filter link to a Strategy Map report wonder no longer. This is a bug that will hopefully be fixed soon; MS are aware of it. I discovered it while writing the Dashboard chapter of the book.

Scenario

You have a dashboard consisting of a scorecard and a strategy map report. The  strategy map is linked to the scorecard KPI items. You wish to filter both the scorecard and strategy map on your dashboard using two global filters: Fiscal Year and Country. Creating links for both filters on the Page endpoint on the scorecard is no problem. Doing the same on the strategy map is not.

In the RTM build a strategy map dashboard item exposes two endpoints: Filters and Page. Strategy map filter links should all be applied to the Page endpoint. The dashboard UI will allow you to connect filter links to the Filters endpoint but the resultant dashboard will simply not apply the filter - this endpoint is a no-op. The Filters endpoint was deprecated but somehow made it into the RTM build.

The Filters endpoint no-op is a red herring that distracts you from the real problem: after you have connected one filter link to the Page endpoint it is no longer available to be used by a second filter link.

 

We should be able to hook up multiple filter links to the Page endpoint. At this point you can hook up either the Fiscal Year or the Country filter link, but not both. This is not acceptable.

Workaround

The only thing that is stopping you from applying more than one filter link to the Page endpoint is the value of the AcceptsMultipleFilterLinks attribute on the Page EndPoint element within the workspace. It should be True but the designer UI sets it to False. We need to get into the XML of the workspace and change it manually.

1. Save & close your workspace (and take a backup, just in case)
2. "Open with" the workspace .bswx file in Notepad or any other text / XML editor
3. Search for "StrategyMap" in order to find the correct report view node
   
4. Scroll through the <CustomData> element numbers and letters that represent the strategy map object itself until you get to the <EndPoints> node underneath
5. Change the value of the AcceptsMultipleFilterLinks for the Page endpoint from false to true
    
6. Save & close the file and reopen in Dashboard Designer. You can now apply as many filter links to the strategy map Page endpoint as you like

PerformancePoint Aust / NZ Launch Dates

PerformancePoint PM Bruno Aziza just posted the dates and registration links to the Australian & NZ PPS launch events which I have copied and pasted here for convenience.

Melbourne - November 28 - Register here, Agenda here

Sydney - November 29 - Register here, Agenda here

Auckland - December 4 - Register here, Agenda here

 

I'll be presenting the M&A session at both the Melbourne and Sydney events. If you're going to be at either event please come up and say hi.

PerformancePoint Monitoring Data Source Connection Problems

Now that the PPS user groups are live on TechNet I have already seen several posts about data source connection issues. This was similar to posts in the BSM newsgroup on the same topic. I have been meaning to post about this for a while. Now is as good a time as any.

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.

UPDATE (04 April 2008): Thanks to Phil Austin who wrote this post on using IIS 5.1 in Win XP. I did not cover this situation adequately in the original version of this post. Because there are no application pools in this version of IIS you cannot determine which account is being used as the identity via the IIS manager. However a quick profiler trace as recommended above will expose the ASPNET as the account that is trying to connect. Ensure it has appropriate access to the data source and you're right to go.

Identity Permissions

The screenshot below is the most common error seen by many people using Dashboard Designer to create a data source.

[I'm simply repeating the text from the screenshot so that search engines will pick it up and make this post more find-able]

"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"

This problem is easy to troubleshoot if you know where to look. First place not to look is the mirror; the problem here is not your own personal permission set.

Here are two simple troubleshooting steps:

1. Determine what account is configured for the app pool identity. If you have performed a "Next > Next > Next" install this will probably be Network Service. This account is fine for testing when all the bits you need are on the one machine (ServerA). If, however, your SSAS server, where your cubes are housed exists on a separate machine (ServerB) the Network Service account from ServerA will not be able to make a connection to it. As mentioned above, best practice here is to use a purpose-built domain account. Get into IIS and look at the Identity tab on the app pool properties to confirm precisely which account is being used.

2. Impersonate the identity account and try to connect to the data source in question. Leave Dashboard Designer alone for a while. Forget about it. Either log in as, or Run As... using the app pool identity account and try to access the data you're looking for using another application. For example, if you're having problems with a cube try creating a PivotTable in Excel or browse the cube in SSMS. The most important thing here is do not test the connection as you, do it as the identity account - this is what PPS is trying to do. If you can't connect to the data source when impersonating the identity account then PPS doesn't have much of a chance either.

Web Service Connection

Data source connections are made using app pool identities, we know that now. However, in order to determine the app pool identity you first need to connect to the Monitoring web service. If you are seeing the message below then your first problem is web service related, it's not data source (yet).

[Once more repeating the text for search engines]

"Unable to connect to the specified server. Make sure the address is correct"

This is an error that you may see in Dashboard Designer. It means you cannot connect to the Monitoring web service configured in the Server tab of Dashboard Designer options, specifically the Server URL property.

This error is far less common than that of data sources but is still worth mentioning. Often, this is an app pool identity permissions problem, but sometimes it's a result of the web service URL settings within a specific workspace.

App Pool Identity Permissions (again)

If you originally had Network Service (or something else) set up as an app pool identity account and subsequently changed it to a domain account you may have problems connecting to the Monitoring web service. If you change the app pool account to something else manually be sure to grant the new account membership to the BPMDeveloper role in the PPSMonitoring system database.

Workspace URL Setting

Workspace files contain the URL of the Monitoring server they were built against / published to. for example if you have just opened up one of the sample workspaces that ship with PPS M&A you may find that you cannot connect to your perfectly-configured Monitoring web service any more. The sample MS workspaces were all built and tested using a monitoring server called ab004-23. The web service address within these workspaces is set to http://ab004-23:40000/WebService/PmService.asmx. Unless you have a server with the same name in your domain (with a PPS Monitoring Server on it) you will not be able to connect. All that you need to do here is change the server name to that of your monitoring server:  http://SERVERNAME:40000/WebService/PmService.asmx. This is all documented in the ReadMe that ships with the samples but it's worth mentioning here again.

It is also worth noting that if you open an MS sample workspace and then close the workspace, (keeping Dashboard Designer open) the Server URL and Server name properties will continue to point to the monitoring server on ab004-23 (or the most recent workspace file). This is not a problem and is common (by design behaviour) to all workspaces, not just the MS samples.

You have three simple choices:

1. manually change the URLs in the Options > Server tab
2. Open up a workspace you have already worked on that is configured to point towards your monitoring server. The URLs will be automatically populated
3. Close down the Dashboard Designer application and fire it up again. Dashboard Designer is a ClickOnce application. Once initially installed, each time it is subsequently launched it will repopulate the Server URL and Server name text boxes with the PPS Monitoring web service URL of the Monitoring Central site it was last launched from.

In Conclusion

So, the next time you are having PPS data source connection problems, don't immediately think the problem lies within your own personal permission set. Instead think of your PPS application pool identity account saying, as Seinfeld's George Costanza would, "It's not you, it's me."