Multi-tenancy and Project Server and PerformancePoint 2010

Update 21/01/2011: Update below regarding Excel Services and Multi-tenancy!

One of the exciting new features of SharePoint 2010 is multi-tenancy, if you’re working in hosted or shared environments then it is no less than a must-have feature.

Unfortunately in the real world of software nothing is perfect at RTM, and this would have to be one of those cases!

In short neither Project Server or PerformancePoint appear to support multi-tenancy in SharePoint 2010, it would seem that the feature has yet to be fully implemented for either service application, however the situation is not as bad as you might fear.

 

Project Server in a multi-tenanted environment

Firstly although Project Server does not accept any subscription related parameters when provisioning a new instance using PowerShell or the Central Admin, it does appear to work in a tenanted environment. Basically the fact that any provisioned PWA site is a site collection of its own means that once you have provisioned your PWA instance you can use the following PowerShell command to associate your PWA site collection with your tenant subscription:

Set-SpSite $PWASiteUrl -SiteSubscription $subscription

More good news is that once provisioned a PWA instance is able to communicate with other service applications belonging to the same subscription. Most importantly: Secure Store Service. Without that Excel Services wouldn’t work!

All is not good though, especially if you like to use the full feature set of the 2010 product, read on..

 

PerformancePoint in a multi-tenanted environment

This is where the news gets bad, it would appear that PerformancePoint in 2010 does not support multi-tenancy at all, it actually doesn’t appear to respect tenant subscriptions and so as a result you might end up with errors like the following when attempting to run Dashboard Designer or configuring the service application unattended account;

w3wp.exe (0x04E4)        0x0ED8        Secure Store Service        Secure Store        7557        Critical        The Secure Store Service application Secure Store Service Proxy is not accessible. The full exception text is: Access is denied.        adfdd2a3-b6e5-4d92-8c5e-5a44fd821969

w3wp.exe (0x04E4)        0x0ED8        Secure Store Service        Secure Store        d9ld        Unexpected        Unexpected exception from endpoint address

w3wp.exe (0x04E4)        0x0ED8        Secure Store Service        Secure Store        d9le        Unexpected        Logging unknown/unexpected client side exception: SecurityAccessDeniedException. This will cause this application server to be removed from the load balancer queue. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied

Some searching will lead you to a number of sources talking about the lack of a Master Key being created in the Secure Store which is most certainly the cause of the issue, unfortunately it appears that in the case where your Secure Store is created with tenancy enabled PerformancePoint is unable to see the Secure Store service application (and thus unable to retrieve the master key)!

Some deep reading led me to some TechNet articles detailing the requirements for PerformancePoint (sorry can’t find the link ATM), basically PPS requires that a Secure Store Service Application exists in the default proxy group, and as I’ve found that app also cannot be tenanted.

Fortunately this can also be worked around, maintaining a non-tenanted default Secure Store app dedicated to PPS does not as I see it introduce any security implications, in particular as you will have to provision a separate PPS service for each tenant in your farm (thus losing much of the benefits of multi-tenancy).

 

All I can say on this one is that I can’t wait for SP1 for PPS, maybe then we’ll even be able to name our databases and lose that BETA PowerShell command line syntax? :)

 

Update 21/01/2011:

See the following blog for information on how Excel Services impacts the above configurations!

Issues in PerformancePoint when using OLAP and SQL datasources

I came across this little annoying issue while creating some linked PPS dashboards where one scorecard item would filter another report by the selected row (in this case a ‘Program Name’ custom field). I was stumped to get the following message when selecting my program:

Error running data source query

After much head scratching I found the problem deep in the logs which for Google’s benefit I will include some server log snippets here:

Log Name: Application

Source: Microsoft-SharePoint Products-PerformancePoint Service

Description:

An exception occurred while running a report. The following details may help you to diagnose the problem:

Error Message: Error running data source query.

<br>

<br>

Contact the administrator for more details.

Dashboard Name:

Dashboard Item name:

Parameters: Programme A

Exception Message: Error running data source query.

And

PerformancePoint Services error code 10116.

<Data Name="string1">Error running data source query.

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (10, 19) Parser: The syntax for ‘A’ is incorrect.

The problem turned out to be a space in my custom field value name!

Basically the hint was in the Parameter and the syntax, it seems that PerformancePoint fails to send my parameter correctly and as a result it sends ‘Programme A’ as two separate invalid parameters ‘Programme’ and ‘A’, hence the error on “The syntax for ‘A’ is incorrect”.

This was quickly proved by updated the custom field lookup table values to remove any spaces, which after a cube rebuild resolved the error!

Definitely chalk that one down to a little defect hidden in the code somewhere!

Your First PerformancePoint Report

One of the biggest areas of advancement in Project Server 2010 is reporting, with Project now utilising; Excel Services, SQL Analysis Services, SQL Reporting Services and PerformancePoint the options are almost endless.

However most people I encounter look at that list and say; “What on earth is PerformancePoint?”, so lately I had another opportunity to flex my PPS skills and thought I might share my experience to give you a taste of how powerful yet simple to use this new thing can be.

 

Creating a Dynamic Resource Utilisation Graph using PerformancePoint

I’m going to keep this very simple, as I find PPS to be very often quite mind-boggling, so to give you a taste of it what I’ll describe here is how to create a simple equivalent to the old typical Data Analysis Resource Utilisation view.

So before you begin, make sure you have your Cube setup and built, the PerformancePoint service application setup and running in your SharePoint installation and some data to play with.

First step you need to get to the PerformancePoint Dashboard Designer, this is a nifty little web application (independent from the Office Web Apps) that is automatically installed and made available when you provision a PWA site.

To find it, open up the Business Intelligence site, and from there the quickest way to get to it is by hovering over any of the landing page images (Create Dashboards, etc) and selecting the ‘Start using PerformancePoint Services’ link.

image

You then have the icon to run Dashboard Designer:

image 

This should download and install the designer for you, and assuming that you have all the correct permissions (and that PWA is in your Internet Explorer trusted sites), you will end up with a mostly empty designer window looking something like the image below.

Now you’re ready to start creating those reports, first step setup your Data Connections by selecting the Create tab on the ribbon and selecting Data Source:

clip_image001

Select Analysis Services as the connection type, and populate the connection details and properties as required:

clip_image002

For Analysis Services datasources you should populate the Time tab to ensure that your Time dimensions are correct, something like this;

clip_image003

Finally when you are done, select save from above the ribbon to continue.

Note: This is something you will get used to with the Dashboard Designer, everything is automatically saved to the PWA BI site in their respective locations (dataconnections, reports or dashboards), with just one exception being the ‘Workspace’ which is effectively your configuration of Dashboard Designer (something I don’t usually bother saving).

Now lets move on and create our report, select PerformancePoint Content and then from the Create ribbon lets select ‘Analytic Chart’;

clip_image004

Then select your datasource just created and hit finish, then your report will open in the designer;

clip_image005

Give it a name and then lets start adding content:

  • First add some measures to the Series; Work and Actual Work.
  • Now add your Resource List dimension to the Bottom Axis.

It should look something like this;

clip_image006

If you want to expand members of a dimension select the chevron (Down arrow next to the X), and select the members in the dialog.

clip_image007

Here for dynamic dimensions like the Resource list you are better off right-clicking and selecting one of the Autoselect Members, such as All Children, like so;

clip_image008

Which now looks like:

clip_image009

Finally we need our Time dimension, add it to the Bottom Axis from the right list and use the chevron to select the desired time periods (I’m selecting months by name here, however you can use something called Named Set’s to do this dynamically for you – another blog article maybe). Finally I think it’s best to move the Resource List to the top of the series Series list and apply some filters to filter out the blanks, to give us something like:

clip_image010

Don’t forget the Edit tab on the Ribbon which has a number of settings that you’ll find handy getting your report right.

Almost there now..

Okay so now we’re ready to save and see this thing for real, so hit the save button, and lets minimise the designer and go back to our BI site in Internet Explorer.

If you open the default ‘PerformancePoint Content’ link, you should see your new report listed, select the drop-down to Display the report:

clip_image011

Final product:

clip_image012

Now this report is ready to add to any where in SharePoint using the PerformancePoint viewer web part, and the best thing is that all of the dynamic functions will be available to all users, so if someone wants to view this report in terms of Cost / Actual Cost, it’s just a few clicks away:

clip_image013

Or maybe you want to see the breakdown of a particular person’s activities using the Decomposition Tree?

clip_image014

I’d say this beats those old Data Analysis views!

That’s it for this how-to, hopefully this scratching of the surface has shown you some of the potential of PPS, keep experimenting and you’ll see very quickly how easy it is to replace those old Data Analysis views that are so 1990!

In the future I might come back and write a Part II to this one on creating your first KPI Scorecard in PPS, stay tuned..