PerformancePoint Services in SharePoint 2010

Editor’s Note: This article is an excerpt from Sahil Malik’s Microsoft SharePoint 2010: Building Solutions for SharePoint 2010 (APress, 2010) and is printed with the publisher’s permission.

There used to be a product called Microsoft Office Business Scorecard Manager. It had some compelling monitoring and analytic capabilities. As a successor to that product, Microsoft released a product called Microsoft Office PerformancePoint Server in November 2007.

PerformancePoint Server 2007 included monitoring and analytic features, such as dashboards, scorecards, Key Performance Indicators (KPIs), reports, filters, and strategy maps, which are delivered via a monitoring server. There were primarily two client user interfaces to the monitoring server, namely the Dashboard Designer and various SharePoint Web Parts. The Dashboard Designer was a thick client application downloaded from the monitoring server, which allowed power users to do the following:

• Create data source connections
• Create views that use those data connections
• Assemble the views in a dashboard
• Deploy the dashboard to Microsoft Office SharePoint Server 2007 or Windows SharePoint Services (WSS)

All of this information was stored to a SQL Server 2005 database that was managed directly through the monitoring server.

Once a dashboard had been published to the monitoring system database, it could then be deployed to Microsoft Office SharePoint Server 2007 or Windows SharePoint Services. Therefore, in that sense, PerformancePoint Server was a product that worked in parallel with SharePoint. Yet another portion of the PerformancePoint Server was the planning center operation. PerformancePoint Planning Server supported a variety of management processes, which included the ability to define, modify, and maintain logical business models integrated with business rules, workflows, and enterprise data. Finally, there was the management report, which was a component designed for financial reporting.

That is all history! Microsoft Office PerformancePoint Server was discontinued in April 2009. Before you feel sad or shed any tears for the loss, the product was instead reincarnated as PerformancePoint Services for SharePoint 2010. It is available as part of non-free versions of SharePoint 2010. It is envisioned to be quite disruptive to the marketplace that it is entering because it is a highly reengineered and well thought out product.

What is PerformancePoint Services for SharePoint 2010? PerformancePoint Services for SharePoint 2010 is the part of SharePoint that allows you to create rich, context-driven dashboards that aggregate data and content to provide a complete view of how your business is performing at all levels. In other words, it is the easiest way to create and publish business intelligence dashboards in SharePoint 2010. At the heart of PerformancePoint Services is the Dashboard Designer. The Dashboard Designer is a thick client that you can launch directly from the browser, and it allows you to create KPIs, scorecards, analytic charts and grids, reports, filters, and dashboards.

Compared to PerformancePoint Server 2007, there are many enhancements in PerformancePoint Services for SharePoint 2010. Some of these enhancements include the following:

• Enterprise-level scalability: Built upon the new services infrastructure in SharePoint 2010, PerformancePoint Services has the ability to scale out more than PerformancePoint Server 2007.
• SharePoint repository: There is no longer a separate monitoring server database. All objects that are created are now stored in the content database. This has numerous advantages centered on security, administration, backup and restore, and even the end-user experience.
• All PerformancePoint features are now SharePoint features: There is a business intelligence repository available as a site definition or you have the ability to create new sites based upon other site definitions and enable certain features to make use of PerformancePoint features in any site collection.
• PerformancePoint filters can now be connected with standard SharePoint Web Parts, because they build upon the standard WSS Web Parts connection framework.
• Integration with SharePoint also makes it possible for PerformancePoint to work with every other SharePoint feature such as search, indexing, workflows, Excel Services, and Visio Services.
• There are some significant improvements in the various SharePoint Web Parts, chart types, and the Dashboard Designer.

With this theory, let's start with the process of administrating, configuring, and using PerformancePoint Services for SharePoint 2010. Configuring PerformancePoint Services is split into two halves: one that you need to do in Central Administration and one that you need to do in the site collections you intend to use with PerformancePoint Services.

PerformancePoint Central Administration Settings

If you used the Farm Configuration Wizard to configure your SharePoint installation, chances are PerformancePoint Services is ready to go for you. Let's look at the specific configuration necessary to use PerformancePoint Services on any particular farm. Visit Central Administration for your farm. Under Central Administration click on Manage service applications. PerformancePoint Services is yet another shared services application within SharePoint. Look for PerformancePoint service application within Central Administration; if one isn't here, choose to create one in Central Administration using the page at /_admin/ServiceApplications.aspx. If you click on the properties for the PerformancePoint service application, you should see a screen similar to the one shown in Figure 1.

The check box that you see next to the name of the PerformancePoint service application is telling SharePoint that all new web applications by default will use this particular instance of the PerformancePoint service application. Therefore, for any web site to use a different instance you would have to explicitly go into the web site settings and allow a certain web site to use a different application instance. This is an important consideration from a planning and scalability point of view.

There is yet another very important service available on SharePoint called the Secure Store Service. The Secure Store Service is the evolution of what used to be single sign-on in SharePoint 2007. Single sign-on in SharePoint 2007, and the Secure Store Service, provides a secure mechanism to store various credentials for various application IDs. Specifically, if the PerformancePoint service application was not associated with a Secure Store Service, you won’t be able to set an unattended service account for PerformancePoint Services to connect to data sources. As a result, the only mechanism you can use to connect to data sources is where the identity of the logged in user is used to connect to the back-end data source. This means that Kerberos must be running properly on your network. For practical reasons though, you need to configure Kerberos on your network anyway, so this is not such a big deal.

One other thing I should mention here is that PerformancePoint Services is claims aware. However, in today's world, a lot of back-end data sources are not claims aware. As a result, even though the identity being passed within the PerformancePoint infrastructure is a claims-based identity, the identity required to talk to back-end data sources is usually a Kerberos identity.

Next, choose to manage the PerformancePoint service application. You will then see a screen with four options, as shown in Web Figure 1.

Starting from the bottom, the Import PerformancePoint server 2007 content option, as the name suggests, allows you to import content from previous versions of PerformancePoint to PerformancePoint Services for SharePoint 2010.

Trusted content locations and trusted data source locations work in a manner very similar to Excel Services. By default, all SharePoint locations are trusted. This is a setting similar to Excel Services.

The setting at the top, PerformancePoint service applications settings, is where all of the other settings go. At the top of this page is a section for Secure Store Service and the unattended service account. In Central Administration, under Manage service applications, if you use the Farm Configuration Wizard to set up your farm, you should see an instance of the Secure Store Service already created for you. If it hasn’t been created for you, go ahead and create one.

Back in the PerformancePoint service application settings page, provide the name of the Secure Store Service application name and provide an unattended service account. The unattended service account is what will be used to authenticate with back-end data sources. Thus, you want to ensure that this account is not a highly privileged account and is different from your farm account or any application pool accounts. Not doing so may inadvertently give access to data sources that you didn't plan on giving access to. Also, before you're able to specify the unattended service account, you will first have to visit your Secure Store Service application settings page and ensure that you generate a new key first. This generated key requires you to specify a password.

The one final setting, which you do not have to perform but you should know about, is that under Manage web applications, select your port 80 web application and choose Service Connections from the ribbon. This should open a dialog box that lets you associate various service application instances with existing web applications, as shown in Figure 2.

PerformancePoint Site Collection Settings

Let me start with a bit of good news first: If you have installed SharePoint Enterprise Edition, you do not need to do any more farm-level or web application–level configurations. The various Web Parts, web services, and the Dashboard Designer are already there for you. All you need to do now is activate the appropriate features on the sites and site collections to start making use of PerformancePoint Services.

Right out of the box there is a site definition called the Business Intelligence Center provided for you, which makes use of all the necessary features. All the features that make up that site definition can also be individually activated in other site collections so that you can use PerformancePoint Services anywhere you wish. This gives you immense flexibility.

I will demonstrate the use and configuration of PerformancePoint Services in a blank site collection. Start by creating a blank site collection at the root level in your port 80 web application. Configuring your site collection is a matter of activating a few features. You need to activate the following site collection features in this order:

1. SharePoint Server Enterprise Site Collection Features
2. SharePoint Server Publishing Infrastructure
3. PerformancePoint Services Site Collection Features

Then, under Site Features (not Site Collection), activate the PerformancePoint Services Site Features feature.

Note that SharePoint Server Publishing Infrastructure is a prerequisite for PerformancePoint Services site collection features. This is because the dashboard publishing uses the SharePoint Server Publishing Infrastructure.

Once these features are activated, activate the PerformancePoint Services Site Features at the site level.

Using PerformancePoint

PerformancePoint Server 2007 used a separate database to store all of its necessary information. That has changed in PerformancePoint Services for SharePoint 2010, because all the necessary information is now stored inside of lists and document libraries; lists that contain dashboards, definitions, reports, scorecards, filters, KPIs, and indicators; as well as document libraries that contain exported dashboards and data sources.

Now that you have properly configured PerformancePoint both in Central Administration and in the site collection, you have available all the list definitions and the necessary content types to get started.

1. Create a new list based on the PerformancePoint Content list definition and call it PerformancePoint Content.
2. Create a new document library called Dashboards based on the Dashboards Library list definition.
3. Create another document library called Data Connections based on the Data Connections for PerformancePoint list definition.

In the PerformancePoint Content list, you should see all the necessary content types, as shown in Figure 3.

One thing is clear: the driving forces behind PerformancePoint Services are content types. Therefore, anything that applies to content types, reusability, queryability, structure, and information management policies can be used with PerformancePoint content.

Click on any of the content types and that will launch a click once application, which is your Dashboard Designer. Once the Dashboard Designer is launched, you can then work entirely in the Dashboard Designer to create various artifacts. Start by saving your dashboard as MyDashBoard.ddwx on your local disk before you add any new items in the workspace. At this point, your workspace in the dashboard Designer should look Figure 4.

Before you start creating artifacts in PerformancePoint, first set up the database that you will use. PerformancePoint Services is very versatile and can work with various back-end sources such as Excel Services, SQL Server Analysis Services, a regular SQL Server table, or even SQL Server Reporting Services. I will demonstrate the usage of PerformancePoint Services with SQL Server Analysis Services.

1. To begin, set up the AdventureWorks sample databases downloaded from msftdbprodsamples.codeplex.com/releases/view/24854.
2. Once you have downloaded and installed the AdventureWorks sample databases, open the C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\enterprise\Adventure Works.sln project in the Business Intelligence Development Studio of SQL Server 2008.
3. After opening the solution, in the Solution Explorer double-click the Adventure Works.ds data source. Click the Edit button just below the Connection String box. Supply your SQL Server (database engine) server and instance name (if it's a named instance). Click the Test Connection Button. If the test succeeds, click OK to save the changes.
4. Right-click the solution in Solution Explorer and choose Properties. On the Adventure Works DW 2008 SE Property Pages dialog box, choose the tree item for Deployment. Change the Target, Server property to your SQL Server Analysis Services server name and instance name (if it is not a default instance).
5. In the Solution Explorer, right-click the solution (Solution 'Adventure Works') and click Deploy.

Once the project is deployed, right-click the data connections document library in your Dashboard Designer and choose to create a new data source. Create a new SQL Server Analysis Services–based project, using the Adventure Works DW 2008R2 database and the Adventure Works cube, as Figure 5 shows. Choose to save this data connection as "AdventureWorks."

Next, right-click the PerformancePoint Content list and choose to add a new report. When prompted, choose to add a new analytic chart. Choose the new AdventureWorks data connection you have just created as the data source for this analytic chart. In the new report, drag and drop product categories in the series and in the bottom axes drag and drop geography. This can be seen in Figure 6.

Save this report as AdventureWorks Products. Once this report is saved in your list, note that it is saved as a certain content type. I'd like to point out here that if you were to add that content type to any other list, you would be able to save your reports in any list in SharePoint you wished. This really gives you the maximum flexibility you need.

Back on your SharePoint site on the default page, put the page in edit mode and drop the PerformancePoint report Web Part. Then, point this Web Part to this newly created report that you've just uploaded in your SharePoint site. You should note that the report is running in full interactivity on the SharePoint site. You should see a big blue bar called All geographies. Go ahead and click on the big blue bar, and the report should update, giving you the various products available. This can be seen in Web Figure 2.

Why is it that my bike sales are so high? I'm not asking this, but I know my business users will. When you see the bright red bar, right-click it and choose decomposition. This brings up a Decomposition Tree for the underlying data source, and it would immediately tell you that you are selling a lot of road bikes (shown in Web Figure 3).

Why are you selling so many road bikes? As an analyst now, you can click on it and easily choose the dimension you wish to expand upon (see Web Figure 4).

Let’s expand on Geography, which reveals that you are selling a lot of bikes in California. Expanding further by city reveals that you are selling a lot of bikes in Carson, and especially in the months of May, November, August, and February. This is a pattern that repeats every year. This pattern can be seen in Figure 7.

This clearly tells you that the Carson market is pivotal to your company's success! And, you were able to determine this without bothering a developer.

Learn more from "SharePoint Performance Optimization with SQL Server," October 2012.