In this chapter, we will discover how PerformancePoint Services allows us to build integrated business intelligence solutions that bring these capabilities together into powerful interactive dashboards.
http://msftdbprodsamples.codeplex.com/
http://technet.microsoft.com/en-us/library/ms143219.aspx
Though PerformancePoint Services is a new feature in SharePoint Server 2010, it is far from being a new product. Like many Microsoft products, including SharePoint itself, PerformancePoint has a long, and sometimes inglorious, history.
In the early years of this century, the “scorecard” became a popular business metaphor for bringing together related pieces of information from throughout the enterprise to provide decision makers with a high-level view of the business. Special-purpose scorecards like the “Balanced Scorecard” became popular but there were no good tools available to deliver them. After a few attempts at getting Excel and web-based templates deployed, Microsoft published the Office Business Scorecard Manager 2005.
In 2007, Microsoft purchased ProClarity Corporation, thereby acquiring one of the premiere business intelligence software companies. Microsoft immediately began integrating features of ProClarity's rich server- and client-based data analysis tools into its products.
When MS PerformancePoint Server was released in 2007, it contained two major modules: monitoring & analysis (M&A) and planning. The M&A component combined the features of the Business Scorecard Manager product with new capabilities brought in from ProClarity and saw good adoption. Use of the planning module, however, was sparse and in 2009, Microsoft dropped it due to lack of market interest. What remained of PerformancePoint was then rolled into the enterprise license of Microsoft Office SharePoint Server (MOSS) 2007. In fact, at that point, the MS PerformancePoint Server 2007 product could be loaded onto a MOSS server farm (with Enterprise Client Access Licenses, or CALs) without additional licensing.
Now, with the release of SharePoint Server 2010, PerformancePoint has become a fully integrated service within the SharePoint environment. You get all of SharePoint's administration and content management tools along with the analytic abilities of PerformancePoint.
PerformancePoint Services is implemented using the new service application framework introduced in SharePoint Server 2010. In the same way that Excel Services and Visio Services run as separate service processes, so does PerformancePoint.
Figure 17–1 shows the primary components that cooperate to provide the business intelligence experience PerformancePoint Services makes possible.
The PerformancePoint end-user experience is designed to be very simple. A PerformancePoint dashboard is deployed as nothing more than a folder containing one or more web pages within a SharePoint library. These pages contain all of the user interface elements necessary to perform complex business analysis on the underlying business data. This user interface is designed to require very little specialized training. With some experience, most users find the scorecards, charts, and reports very easy to interpret and manipulate. All of this content is delivered via a web browser, of course, but there are also many opportunities to take data offline into office applications such as Excel for deeper ad hoc analysis.
In order to deliver this sophisticated user interface, the KPIs, scorecards, dashboards, and other BI components must first be defined and deployed to SharePoint. The tool for accomplishing this is the Dashboard Designer, a Windows application for defining BI components and deploying them to SharePoint. This tool does not need to be separately downloaded and installed. It is automatically installed as a “one-click” application when needed. See “Authoring and Publishing PerformancePoint Solutions” later in this chapter for a tour of this tool. Note that this is a development tool and is not designed for use by most end users.
At the application layer, PerformancePoint conforms to the typical design of a SharePoint service. It is made up of a service process and a proxy component. The proxy component provides code running within the IIS application pool process with access to the functionality of the PerformancePoint service application. The service runs as an independent process in the operating system and is often deployed on a separate tier of application servers that sits between the web front-end server and the database servers, as shown in Figure 17–1.
Other application-level services commonly employed as part of a PerformancePoint solution include the Secure Store Service (SSS) and SQL Server Reporting Services (SSRS). SSS provides a location for logon credentials to be securely stored within the SharePoint environment. For more details on the purpose and configuration of the Secure Store Service, see Chapter 5. While not part of SharePoint Server, the SSRS component of SQL Server is frequently leveraged in conjunction with PerformancePoint solutions due to the ease with which it allows complex reports to be created and delivered.
On the database layer of the architecture, there are several data sources you'll have to become familiar with.
As you are probably aware, SharePoint stores its web site contents in content databases. These databases contain the site collections, sites, lists, libraries, and pages that make up the sites served by SharePoint. In the context of PerformancePoint, most of the business intelligence objects, including the dashboards themselves, are also stored in lists and libraries within SharePoint's content databases.
Like the other service applications in SharePoint Server 2010, PerformancePoint Services needs to store data that does not fit well into the usual format of lists and libraries in SharePoint. This data is stored in a separate database that's created when a new instance of the PerformancePoint service application is created. The tables in this database, like all SharePoint databases, should never be manipulated directly but only through PerformancePoint Services. These tables contain various parameters used by PPS, as well as dashboard annotations and comments entered by users.
Because of the types of analysis normally performed with PerformancePoint Services, perhaps the most common data source for Key Performance Indicators (KPIs), scorecards, and dashboards is SQL Server Analysis Services (SSAS). The cubes stored in SSAS contain the raw business information that PPS will “slice and dice” to perform the analysis required by the user. For a full description of using SSAS cubes to store and manipulate multidimensional data, see Chapter 1.
While SQL Analysis Services is the most common source of information for PPS dashboards, it is far from the only option. Any data store from which you can read data is a potential data source for PerformancePoint. Additional data sources supported out of the box include SQL Server relational tables, Excel spreadsheets (either file-based or via Excel Services), and SharePoint lists.
With a little more effort, you can expand the available data sources to include any ODBC-compliant relational database and even data accessed via custom code written and deployed by the user's organization. For details on creating custom data source providers for PerformancePoint Services, see msdn.microsoft.com/en-us/library/bb8317514.aspx
. To access other non-Microsoft databases, you can use either Business Connectivity Services (see Chapter 4) to expose the data as a SharePoint list or a SQL linked server to expose the data as a SQL Server table (msdn.microsoft.com/en-us/library/ms188279.aspx).
Much of the security for a PerformancePoint solution is handled by the SharePoint Foundation component. The dashboards exposed by PerformancePoint Services are stored as ASPX pages in a folder within a SharePoint document library. The permissions associated with the libraries, folders, and dashboard pages will control who is allowed to access which pages.
However, the page itself is not usually what is most important. It is the data that we want to protect. Security can become problematic when we have to access data sources outside of SharePoint. PerformancePoint Services provides three authentication methods for accessing backend data sources: per-user identity, an unattended service account (USA), and a custom connection string for SQL Server Analysis Services.
Per-user identity allows a PPS dashboard to impersonate the user's credentials when accessing backend data. This is a very secure way to access data because it provides a second check before allowing the user to access not just the dashboard, but the data underlying the dashboard. However, this form of authentication requires Kerberos delegation to be in place between the PerformancePoint server and the data source, so it's not always possible to use this type of authentication. See “Planning Considerations for Services that Access External Data Sources” at technet.microsoft.com/en-us/library/cc5170988.aspx#ConsiderationsForAccessingExternalData
.
The most commonly used form of authentication with PerformancePoint services is the unattended service account. This is an account that is configured in the Secure Store Service and used to access a backend data source. The data source will see only the service account's credentials, so it will not be able to filter the data it returns based on the identity of the user accessing the dashboard. The service account must be given access to all necessary data within all data sources in order for PerformancePoint to function properly. It is a best practice to use a service account with the least permissions that will allow it to access the needed data.
The last option, called “Custom Data,” uses the unattended account but also includes the user's login name on the connection string. This option works only with SQL Server Analysis Services 20017 or later. In SSAS, this is known as “Dynamic Security.” The idea is to allow the SSAS server to filter the query results when full Kerberos delegation is not possible. The user's login can be used in MDX queries and SSAS role assignments to limit the data returned by the cube.
The most important thing to note when choosing among these authentication options is that the choice can now be made for each data source that is configured. In PerformancePoint Server 2007, it was necessary to configure a single authentication mode for the entire server application. It was not possible to configure one data source using per-user identity and another to use an unattended service account. In SharePoint 2010, if multiple types of authentication are required, you don't need to configure multiple PerformancePoint service application instances. Creating separate data sources is sufficient. However, the unattended account is configured for a PPS application instance so all data sources running against the USA in that instance will use the same account. See “Setting up PerformancePoint Services” and “Creating a Data Source” later in this chapter for details.
A PerformancePoint solution is built by creating a set of business intelligence components that work together to control how business data is aggregated and displayed. This section will introduce the concepts behind these components and how they are deployed to a PerformancePoint service environment. For a step-by-step guide to creating a real-world PPS solution, see “Authoring and Publishing PerformancePoint Solutions” later in this chapter.
The components that go into a PerformancePoint solution are described at a conceptual level in this section. Later we'll describe how these components are represented and stored in SharePoint.
A dashboard is a set of web pages displayed by SharePoint to allow the user to view and analyze data. Figure 17–2 shows a sample dashboard.
A dashboard page consists of several parts. Typically, a page contains a navigation area at the top of the page, as shown in Figure 17–3. In this case, the pages of the dashboard, “Sales Summary” and “by Sales Territory,” are presented as links in the header of the page. Clicking on an active link takes the user to a new page in the dashboard while preserving the filters used on the current page. This allows multiple pages to act as a single dashboard.
The rest of the page is separated into zones, similar to the web part zones used in SharePoint pages. The difference is that dashboard zones can be added, modified, and removed from a dashboard page after it is created. These zones allow dashboard components, including filters, scorecards, and reports, to be stacked and connected in the same way web parts can be manipulated on a web part page. Figure 17–4 highlights the zones and direction (vertical vs. horizontal) of the default dashboard page layout.
The BI components that make up the page are assembled, arranged, and connected using the Dashboard Designer. When the dashboard is deployed to SharePoint, the various object definitions control the page's behavior. A common pattern, as shown in Figure 17–4, is to place visible filters in the header zone of the page and then to fill the other zones with scorecards and reports as needed.
The connections created between components allow them to act together. For example, when the user changes the Date filter on the sample dashboard, each of the scorecards and reports on the page are updated to reflect data only from those periods. When a row is selected in the scorecard shown on the left, the data on the reports in the right column are filtered to match. We will be creating this dashboard and its connections later in this chapter.
A Key Performance Indicator is a definition of business-relevant measurements (or “metrics”) used to display easy-to-understand conditions as shown in Figure 17–5.
KPIs are defined in PerformancePoint as a set of metrics that are either “actual” or “Target” metrics. Actual metrics are the values that are calculated from the underlying business data. In the example in Figure 17–5, “Total Sales” is an actual metric. Target metrics, in contrast, define a desired goal for the actual metric, based on some condition or formula specified as part of the KPIs definition. The “GP% vs. no Discount” column is a target metric in this example. Target metrics can be displayed in several ways but include three basic components: the indicator and a value before and/or after the indicator. The meaning of these values can be defined wherever the KPI is displayed. In this case, the highlighted line shows an actual value of “-12.72%”, a yellow triangle indicator, and a target value of “13.17%”.
The definition of the KPI specifies where the actual and target values come from as well as what type of indicator to show. PerformancePoint comes with a large set of indicators to choose from, as Figure 17–6 shows. Through Dashboard Designer you can also create your own custom indicators using custom images.
The first components to be created in a PerformancePoint solution are data sources. These define the locations and parameters to use when accessing the data that will be used by the dashboard. PerformancePoint supports two major categories of data sources: tabular and multidimensional.
Tabular data sources are those that provide PPS with a relational table of data to work with. The most commonly used data sources of this type are SQL Server tables. Other options include cells retrieved from Excel spreadsheets, either through Excel Services or directly from a file, or the items in a SharePoint list. It is also possible to use SharePoint's Business Connectivity Services (BCS) or SQL linked servers to pass data from other RDBMS or line-of-business applications into a PerformancePoint dashboard.
Multidimensional data sources are the most common type used in PerformancePoint solutions. They use SQL Server Analysis Services (SSAS) cubes to furnish and process data. Analysis Services is the only currently supported OLAP data source.
Filters are components that let users select parts of the data set to examine while excluding the rest. For example, you can use a filter to examine data only for certain time periods, as Figure 17–7 shows.
When creating a filter, there are two primary considerations: the type of filter and the display method to be used.
The types of filters available are:
msdn.microsoft.com/en-us/library/ms11717594.aspx
.Once the type of filter has been selected, it can be displayed in three different ways.
When a filter is placed on a dashboard, it is not just a user interface control that controls the data displayed on that page. The selections made are written to PerformancePoint's database for later use. If the user returns to that page days later, that selection will still exist. The number of days the selection is retained can be configured in SharePoint Central Administration. See “Setting up PerformancePoint Services” later in this chapter. Because the filter is part of the dashboard, not just the page, that same filter selection will also be transferred to any other page that is part of the same dashboard. Therefore, if a selection is made on one page and then the user navigates to another page on the same dashboard, all of the filter selections made on the previous page apply to the new page as well.
A PerformancePoint scorecard, shown in Figure 17–8, is used to display a set of key performance indicator metrics. You can configure the KPIs to display differently depending on what you need the scorecard to reflect. For example, the designer may choose to use a background color for a KPI cell instead of displaying the indicator image.
In addition to the KPI metrics, scorecards generally also include one or more sets of dimension attributes that are used to drill down or roll up the KPIs.
A report in PerformancePoint refers to a component that displays business data other than KPIs. Reports are authored separately and then connected to the filters and scorecards on a dashboard to filter the data returned in the report. Our sample dashboard contains two of the most common reports used in PerformancePoint server: the analytic chart and the analytic graph (Figure 17–9).
PerformancePoint generates some reports entirely within itself but some reports are created in cooperation with other technologies. Here are the report templates supported:
Each of these reports is rendered on the dashboard page using a web part that manages the connection with other parts of the dashboard.
Now that we're familiar with the basic concepts of a business intelligence solution, let's take a look at how these pieces are put together in a SharePoint site.
A content type in SharePoint defines all of the metadata about how a particular type of list item or library document will be handled by SharePoint. This includes a list of the fields associated with the object and any custom actions it may support. In the case of PerformancePoint, the following content types are defined to enable its functionality.
With the exception of data sources, all of these content types define items in a SharePoint list. Data sources are stored as documents in a library because they are stored as Office or Universal Data Connection (UDC) files or as PerformancePoint data source files. Bear in mind that these items represent the definition of the object, not an end-user-viewable object. These items can be edited using the Dashboard Designer but don't display any content on their own. Only when a dashboard is “deployed” is it compiled into a set of ASPX pages that can be viewed by users.
A major advantage of PerformancePoint Services over PerformancePoint Server 2007 is the use of content types. With content types, all of the functionality of SharePoint is now available for use with PerformancePoint artifacts. This includes participating in workflows, using information rights management policies, and including PPS objects in any list or library where the PPS features and content types are active. You are no longer constrained to using a special site definition with a predefined structure for your PPS solutions.
PerformancePoint defines list and library templates designed to store PerformancePoint artifacts.
List Templates:
Library Templates:
Dashboard pages are created as ordinary web part pages. The various components of a page are created as connected web parts. The web parts used by PerformancePoint are:
PerformancePoint defines a sample site template to help designers begin using the service (Figure 17–10). This template is nothing more than an ordinary site with some prepopulated content to introduce the user to PerformancePoint features. The content is contained in a Content Editor Web Part and can be deleted when no longer needed. You can also add the PPS lists, libraries, and content types to any existing site instead of using the template. All that's required is for the PPS features to be activated.
In addition to this home page, the site template contains an instance of each of the lists and libraries described in “List and Library Templates” above. This makes the site a good location for one or more complete solutions to be stored. Additional content can be added to the site as needed.
PerformancePoint Services is part of the Enterprise Client Access License (ECAL) for SharePoint Server 2010. In order to use any such features, your SharePoint site collection must have the SharePoint Server Enterprise Site Collection Features feature activated under the Site Collection Features.
The PerformancePoint functionality is enabled using two additional features. The first is the PerformancePoint Services Site Collection Features feature. This feature can also be found under Site Collection Features. The second feature is activated at the site level and is named PerformancePoint Services Site Features. See “Deploy the Business Intelligence Center” later in this chapter for step-by-step instructions for enabling these features.
Like the other services we have examined in this book, PerformancePoint Services are configured using Central Administration or PowerShell commands (cmdlets). Let's take a look at the settings for PerformancePoint Services, along with the procedures for setting up a PPS instance.
The default, wizard-based installation of SharePoint Server 2010 includes an instance of the PerformancePoint Services application, so creating a new instance is not normally necessary. If you do need to create one, however, this can be easily accomplished through SharePoint Central Administration.
Figure 17–11. The Manage Service Applications page
Figure 17–12. New PerformancePoint Service Application dialog
After a few moments, the Manage Service Applications page will reappear with the new PPS application listed. It is now time to configure the application. If you are working with the default PPS instance, you should review the default settings, paying particular attention to the unattended service account, which should have the minimum privileges. See “Security PerformancePoint Solutions” earlier in this chapter to review why the USA is important.
To begin configuring the service application, click on the name of the service (PPS Application in our case) to be taken to the Manage PerformancePoint Services page as shown in Figure 17–13.
On the Manage PerformancePoint Service page, there are various settings to configure, which we'll discuss now. While most of the settings can be left to their default values, some need to be configured in order to address specific requirements.
The PerformancePoint Server Application Settings page (Figure 17–14) contains a variety of settings that control the performance and behavior of PPS. These settings apply only to sites associated with this instance of PerformancePoint Services.
The purpose of this section is to configure the unattended service account. Recall that this is the Active Directory domain account that will be used when accessing data sources without using the user's own identity (Kerberos). The credentials for this account are stored in the Secure Store Service. See “Securing PerformancePoint Solutions” earlier in this chapter.
This section of the page has three entry boxes. The first textbox is used to identify the SSS application to be used for the account's credentials. The second and third boxes allow for the entry of the user name and password of the account, respectively. Remember that this account should have minimal permissions but provide access to all necessary data.
Each dashboard user can be given permission to add comments to the cells in a PPS scorecard. These comments are then available to other users when they view that scorecard. The comment functionality also uses the term annotation. Technically, a scorecard cell can have one annotation and each annotation can have multiple comments.
This section of the page contains a checkbox that can be used to enable the annotation feature within the PPS instance. There is also a setting that limits the number of annotations that can exist on a single scorecard. The default is 1,000 annotations per scorecard.
There is also a Delete Comments by Date… button that displays the dialog shown in Figure 17–15. This dialog can be used to start a background job that will clean up comments older than a given date, which can use useful if it becomes necessary to reclaim space in the SQL database associated with this PPS instance. The name of this database is based on the name of the PPS application and a unique GUID, like “PPS Application_d17430dcfa17de40817a84dcb1b0c39f78e”.
The cache section of the page contains a single entry that sets how long (in seconds) the images associated with KPIs should remain in memory before the memory is reclaimed. The default is 10 seconds, which should allow the image to be reused several times during the rendering of a single page. If your site makes heavy use of only a few unique indicators, it may make sense to extend this value to keep from frequently reloading them from disk.
This section allows the administrator to set a standard timeout on a data source, preventing pages from becoming unresponsive when a data source becomes slow or unavailable. The default is 300 seconds, which should be sufficient in most cases. If data access is consistently over 5 minutes, either the data source is not responding properly or the queries being used to access the data should be reconsidered. When data access for a dashboard page takes a very long time, two major problems result.
First, system resources are consumed processing and returning large amounts of data that will, most likely, never be used. The thread, table, memory, and I/O locking and contention created by executing very large queries can quickly drain system performance for all users.
Second, users are not going to wait for several minutes each time they interact with the dashboard. A user viewing a dashboard is trying to avoid information overload, so loading 1,000 or more records defeats this purpose. Long wait times degrade the user experience and lead to dashboards that aren't used.
Filters are used on dashboards to set how the data is “sliced.” PerformancePoint dashboards have the ability to remember the last filter values used by each user. This is valuable when moving from one dashboard page to another or when returning to a dashboard on a future visit.
To avoid storing this data forever, this configuration section allows the administrator to set a time out (in days) for how long to retain unused filter values.
The maximum number of members that can be loaded into a filter tree (see Figure 17–16) is configured here as well. Each time you use a filter, you are selecting a set of values to apply with the filter. For example, if you select the year 2010 at the Months level of the date hierarchy, you are selecting 12 values that must be stored for the filter. If you are selecting at the Day level, there are 365 values that must be stored. Each of these values is called a member of the filter. This setting prevents very large selection lists from slowing down the server for all users.
This setting is similar to the Filters section except that it applies to the selection of measures on a dashboard.
The Show Details feature allows an end-user to drill into a value that is derived from Analysis Services. The functionality of the drill-through is controlled by Analysis Services but it is rendered by PerformancePoint Services (see Figure 17–17). These settings limit the performance impact that this feature can have on the server hosting the PPS application.
The “Initial retrieval limit” limits the number of rows that can be retrieved on the first page of the details report. The default of 1,000 is generally sufficient without creating too great a load on the server. The “Maximum retrieval limit” is used to prevent excessively large datasets from being returned on subsequent pages of the report. You can either choose a fixed number of rows or leave control of this setting with Analysis Services. To reduce page load times and unnecessary server traffic, consider reducing this value to one in line with the expected use of the report.
The Decomposition Tree feature is a very powerful analytical tool, with an interface designed to be simple and intuitive to an untrained user (see Figure 17–18). The Decomposition Tree leverages the dimensions already built into the solution to drive the analysis. It allows users to do complex analysis without involving a developer.
However, because of the large number of members that a dimension might contain, it can become a performance drain on the system.
Each item listed in a vertical column of the decomposition tree is counted against this limit. Note that this limit applies to only one column of items as highlighted in Figure 17–18. The total number of items in all columns may well exceed this value. This limit should not be set higher than is needed for the users to perform the analysis required.
PerformancePoint Services stores its metadata in SharePoint lists and libraries. These locations must be listed as “trusted” before PPS will consider them valid locations from which to read its objects. By default, all locations within SharePoint are automatically considered trusted, as Figure 17–19 shows.
Note that just because the location is trusted by PerformancePoint, it is not necessarily accessible by any particular user. The SharePoint permissions on those items still control access to them. The fact that they are in a trusted location only allows PPS to use them if the user has access to them.
If there's a need to restrict the locations from which a PPS application can load data sources, switch this setting to “Only specific locations” and click Apply. This will enable a new set of options for adding specific locations, as shown in Figure 17–20.
To add a location with this dialog box:
6. Click on Add Trusted Data Source Location. The dialog shown in Figure 17–21 will be displayed.
7. Enter the URL of a SharePoint-based site or document library. Only SharePoint locations can be used.
8. Click on the validation button to the right of the URL textbox. If the location is a valid one, the other controls will be enabled.
9. Select the Location Type option to use and enter a description for the trusted location.
10. Click OK to create the trusted location.
Trusted Content Locations are configured in the same way as trusted data source locations with the following exception. PPS content items created by the Dashboard Designer application, such as KPIs, Scorecard, Reports, and so on are stored in SharePoint lists instead of in document libraries. Therefore, when selecting trusted locations, the options are Site, Site Collection, or List.
As mentioned in the introduction to this chapter, PerformancePoint was previously a stand-alone server product called Microsoft PerformancePoint Server 2007. The Monitoring module of that product supported many of the same types of objects (including scorecards and dashboards) that PerformancePoint Services now supports. The last option on the Manage PerformancePoint Services page is used to import objects from a PPS 2007 monitoring database into the SharePoint lists and libraries to be used with PerformancePoint Services. A wizard helps with the upgrade process.
The first page of the import wizard (Figure 17–22) contains general information about the import process. Before using the import wizard, you'll want to become familiar with both the PPS 2007 and SharePoint 2010 environments, including security and source and destination locations. Microsoft provides guidance specifically for planning this process, which you'll find at http://technet.microsoft.com/en-us/library/ee74817117.aspx
.
After you click Next on the introduction page, you'll see the page shown in Figure 17–23. This page lets you identify the security mode used by the original PerformancePoint Server 2007 installation. Select the correct mode and click Next.
In step 2 of the wizard (Figure 17–24), you enter the database credentials to be used to connect to the PPS 2007 content database. This is the database where the dashboards were stored in the previous installation.
The credentials you enter can use either SQL or Windows authentication but they must have access to the PPS 2007 content to be migrated. Click Next when ready.
Step 3 (Figure 17–25) identifies the name of the content database to be accessed. Select the database and click Next.
In the remaining steps, you select the destination list and library that will receive the migrated content items. Once the migration starts, a bar will display showing its progress. When the migration is complete, be sure to review the objects and security permissions to resolve any inconsistencies between the old environment and the new. Remember, some PPS 2007 report types are not supported in SharePoint Server 2010, so it may be necessary to rework and redeploy some of your dashboards.
For a complete discussion of the options for upgrading PerformancePoint 2007 content to SharePoint 2010, take a look at the MSDN blog entry at blogs.msdn.com/b/performancepoint/archive/2010/02/25/upgrading-performancepoint-server-2007-to-pps-2010.aspx
.
The commands we'll discuss next are the same ones you used in “Setting up PerformancePoint Services,” but now we'll use PowerShell commands. As you can see in Tables 17–1, 17–2, 17–3, and 17–4, there are four basic groups of commands: New operations, Get operations, Set operations and Remove (clear) operations.
Tip To obtain additional details and examples for each of these PowerShell commands, use the following commands from the PowerShell command line.
Get-Help <PS Cmdlet>
for details and
Get-Help <PS Cmdlet> -examples
for samples
In this section, we will create all of the PerformancePoint content objects necessary to implement a typical business intelligence dashboard using data from the AdventureWorks sample database. Our solution will include the KPIs, scorecards, filters, reports, and dashboards necessary to provide the user with a rich data analysis environment. We will deploy this solution to SharePoint and explore the resulting user experience.
In the following section, “Advanced Report Types,” we will then expand on this solution by adding reports to the dashboard based on Excel Services and Strategy Map report types.
Note This tutorial will make extensive use of the AdventureWorks SSAS solution. You can download the Adventure Works database from http://msftdbprodsamples.codeplex.com/
. Take a look at Chapter 1 for details about deploying the solution as an SSAS database.
PROBLEM CASE
We will create a work area for our solution by deploying the PerformancePoint Business Intelligence Center site template. First we must verify that the necessary features are activated at the site-collection level. You will need site collection administrator rights in order to enable features and create the site.
Figure 17–26. Activating SharePoint and PerformancePoint site collection features
The site created has all of the lists and libraries needed to deploy a complete PerformancePoint solution. It also contains informational content describing the site's purpose, which you can delete when you no longer need it.
All of the objects we create for our solution will be stored in a Dashboard Designer Workspace file, which is stored on your local desktop with a DDWX file extension. This file acts as an offline store for these objects until you are ready to publish them to SharePoint. The workspace file is similar to a Visual Studio solution file in that it allows you to organize, edit, and manage all of the components of your solution in one place.
Because the Dashboard Designer is a one-click Windows application, there is no executable to download and install on the desktop. The easiest way to install it is simply to use it and then save a file. We will do this as part of the next section.
Now let's create a data source from which to retrieve business data for our dashboard.
This will launch the Dashboard Designer. If this is the first time you've used it, you'll see messages and progress bars indicating that it's installing. The Web browser may ask you to authorize the installation. Finally, the designer will be shown with an empty workspace and the Select a Data Source Template dialog displayed, as shown in Figure 17–29.
Take a moment to find your way around the Dashboard Designer application—you'll spend a lot of time here! At the top is a ribbon menu system that works like any Office 2010 application. The options that appear in the ribbon will change as the context of the central window changes.
On the left side of the window is the Workspace Browser, which is a tree listing the contents of the workspace. Currently, this list shows one workspace file called Untitled Workspace, one SharePoint list called Data Connections, and one item within that list—a data source currently named New Data Source.
The editor for the item selected in the left-hand list is displayed in the center window where changes can be made. This window will have a set of tabs across the top based on the type of object being edited. The Properties tab is common to all objects and is used to name and organize objects within the workspace.
To the right of the center window are panels that list the objects within the workspace that are related to the current item.
Figure 17–31. Selecting a time dimension
Note Depending on when you downloaded the AdventureWorks sample databases, the actual dates available in your cube may be different from those shown. The sample database used here has data from FY 2002 to FY 2005.
At first glance, it may seem strange to enter all of this information about the time dimension in the editor you're using to create a data source. The reason for doing this is that it allows the use of Time Intelligence filters. These very powerful tools will help when it comes time to present data to the user. See “Creating Filters” later in this chapter for details. Now that the data source is set up, let's go back and finish setting up the workspace itself.
If you go back to the Data Connections library in your BI Center site, you'll see that there's one data source there. Why is it called New Data Source? The answer is that we haven't yet published the data source we created to SharePoint. We've only saved it to the workspace file.
Hint: You can tell that an item hasn't been published because there is a pencil superimposed on its icon in the Workspace Browser.
Now that we have set up our data source and started our workspace file, let's get ready to retrieve data for analysis.
Our dashboard will contain one set of KPIs. We will create a set of actual and target metrics that will allow the marketing department to track the effectiveness of their promotions in terms of their effect on Gross Profit Margin.
Our workspace is currently connected to the Data Connections library, but that area can only be used to store data sources. To create the KPI, we need to connect our workspace to the PerformancePoint Content list in the BI Center site.
Note The use of display folders within the Dashboard Designer is entirely optional. Their purpose is only to help keep the solution organized. They have no effect on anything other than how items are displayed in the Workspace Browser. Workspace folders do not get reflected in the folder structure of the content list in PerformancePoint or anywhere else outside of Dashboard Designer. Typically, they are used to separate items by type (KPIs, Filters, Dashboards, etc.) or functionally by the part of the solution with which they are associated. Folders can be nested as needed by separating folder names with a backslash () character. For the rest of this chapter, items will be placed into such folders but the directions won't mention it each time from here on out.
The thresholds used in the last target created may not seem to make sense at first. The default banding pattern used by PerformancePoint is “Band by normalized value of Actual/Target”. In this mode, a calculation is performed on each actual and target value for the KPI. A percentage is assigned based on the distance the actual and target are from the “worst value” set in step 3 of the scoring pattern wizard. By default, the “worst value” is set to 0. The worst value is somewhat arbitrary since it is quite possible for actual values to be below it. The percentages calculated in that case are simply negative.
In our case, 0% indicates that the gross profit margin for the selected promotion is exactly the same as for the No Discount promotion. If it's lower, the percentage value is negative. If it's better, the value is positive. Adventure Works management has decided that our profitability goal for these promotions is to be within 20 percent of the non-discounted value (green indicator). A value worse than 100 percent below the standard indicates a serious problem, so the indicator turns red. Anywhere between -20 percent and -100 percent, the indicator will show a slight problem or a yellow indicator.
On our dashboard, we want to display a list of the promotions we've run and the values of our KPIs for each promotion. This is called a scorecard (see Figure 17–52), which we will create as a separate component in our workspace (inserted using text markup feature).
The empty scorecard shown in Figure 17–52 has two main areas. The center window is where you design the scorecard; you will be able to arrange and format items in this window. To the right are panels that provide the elements to go on the scorecard. First, we identify the KPIs to be shown, then we identify the rows and grouping to use in calculating the KPIs.
Figure 17–54. Adding the KPI to the scorecard
Figure 17–57. Selecting members
Figure 17–59. The scorecard with added elements
At this point, the scorecard will automatically be processed. It should look something like the one in Figure 17–61. The rows show the promotion hierarchy, including rolling up to parent levels. The columns show our KPIs as they are currently configured. Notice that rows are appearing for promotions even though there were no sales for those promotions.
Our dashboard will contain two filters. The first filter will control the date range for the figures displayed. The second will allow us to select from among our sales territories.
For the “Enter time formula” step, we will define the options the user will have for selecting time periods. The formulas are entered on the left and the name displayed to the user is on the right. The formulas are designed to output a list of members of the cube's time dimension as specified in the data source. For a good overview of these formulas, go to blogs.msdn.com/b/performancepoint/archive/2010/01/21/time-intelligence-formula-quick-reference.aspx
.
Now we'll create a filter for selecting from the Sales Territory dimension. This filter will display a tree for the territory hierarchy and allow us to select one territory or higher-level region.
Figure 17–70. Selecting Sales Territory members
Figure 17–71. Confirming the members
KPIs and scorecards allow us to compare business data against goals. Once we have identified the problem areas in the enterprise, we need to be able to understand the conditions that are causing problems. A “report” in PerformancePoint is a generic term referring to a window of information that has been sorted out based on the filters and scorecard selections made in the dashboard.
The two most common types of reports in PerformancePoint are Analytic Charts and Analytic Grids. We will create one of each for our dashboard.
Figure 17–75. The empty Margin by Sales Channel report
The empty grid report window is divided into several areas. The draft view of the report appears in the center window. Beneath the center window are panels labeled Rows, Columns and Background that will be used to specify the contents of the report. In the report designer, “background” refers to the ways in which the data in the report can be filtered.
To the right is a Details tree from which we'll select items to add to the report by dragging them onto one of the panels at the bottom of the window.
Now that we have defined the rows and columns for the report (Figure 17–76) it will automatically display a default view. In our dashboard, we want to see the product categories by default. We also want to be able to filter this report by date, territory, and promotion.
Figure 17–77. Selecting members for the report's default view
Now let's create an Analytic Chart report. This report will compare the gross profit margin across product categories.
At this point, the chart report looks almost exactly like the grid report. The difference is that the panels across the bottom are Series, Bottom Axis, and Background (see Figure 17–79).
The Information Bar is a useful feature on reports. When this is turned on it shows the user exactly how the data in the report is being filtered. In the designer, the bar shows “No background selections exist” because there are no filters applied until the report is embedded into a dashboard (Figure 17–81).
We have created all of the components for our dashboard. Now we'll put it all together and make it work as an integrated package.
Figure 17–82. The default page template
This will create an empty dashboard page with three zones (Figure 17–83). At the top of the center window is a list of the pages in the dashboard. Beneath the pages is the Dashboard Content panel. This is where the selected dashboard page will be assembled from the components we've created. At the right, the Details panel contains the components that can be used.
The components are now on the page (Figure 17–84), but they are not integrated with one another. By dragging fields from one component to another, data is passed to synchronize or filter data throughout, or between, dashboard pages. The behavior we want is as follows:
Now we will create the connections to create this behavior
Figure 17–87. Connecting to the Promotion field
We are now ready to deploy our dashboard and start analyzing data. Remember that the dashboard we created in the previous section is only the definition for a dashboard. That definition is stored in our PerformancePoint Content list. When we deploy the dashboard, the definition is converted into a folder of ASPX pages that implement our dashboard.
Note that PerformancePoint uses Silverlight controls to render some of the UI controls. Now would be a good time to download and install the latest version of Silverlight from www.microsoft.com/silverlight
.
Figure 17–90. Putting the dashboard in the Dashboards library
Figure 17–91. The Sales Summary page
Figure 17–92. Text in the information window
Figure 17–93. Values from the previous session are remembered.
Figure 17–94. Click a row on the scorecard to modify the reports
Figure 17–95. Exporting data to Excel
Figure 17–96. Decomposing a cell
Now let's extend our sample dashboard to include data from two external sources: Excel and Visio. The data will be integrated using the Excel Services and Strategy Map report types, respectively.
PROBLEM CASE
Microsoft Excel is one of the most widely used packages for doing numerical analysis. As a file-based desktop application, however, it is limited in its ability to reach a large audience and integrate with other solutions. As we saw in Chapter 5, SharePoint Server 2010 incorporates Excel Services to address these issues by creating a server environment for storing, processing, and delivering Excel content. With PerformancePoint Services, we have the opportunity to leverage Excel's analytical abilities and familiar user interface to integrate rich reports into our PPS solutions.
In this section, we will create a simple Excel Services spreadsheet and deploy it to SharePoint. Then, we will integrate the elements of that spreadsheet into our existing dashboard as a pair of new reports on a new page. The new page will allow us to view a grid and a chart with a breakdown of our sales figures by Sales Territory.
Figure 17–97. Selecting the database server to connect to
Figure 17–98. Entering a name for the data connection file
Figure 17–101. The pivot chart
Figure 17–103. Entering the chart name
Figure 17–104. Entering the name of the pivot table
Figure 17–105. Selecting a style for the data bars
Figure 17–106. Setting up conditional formatting for the Date filter
The Excel spreadsheet is now ready to be published to SharePoint. For simplicity, we will store it in the Dashboards library in our BI Center site (though it could be stored in any location trusted by Excel Services). First, we'll create a copy of the data connection we're using.
Figure 17–107. Copying the data connection
Figure 17–108. Naming the data connection file
Before we publish the spreadsheet to SharePoint, we need to identify the objects and parameters to expose to Excel Services. The objects we'll use are the SalesChart and SalesTable objects. We will declare the Date and Promotions cells as parameters, which PerformancePoint will use to pass in dashboard filter selections.
Figure 17–111. Adding parameters
Figure 17–112. Saving to SharePoint
Now that we have a spreadsheet published to SharePoint, we will add the PivotTable and PivotChart as reports in a new page on our dashboard.
We now have the new filter and reports created for our new page. Next we'll create the new dashboard page and deploy the dashboard.
The dashboard page should now resemble the image in Figure 17–116..
Notice that all of the formatting from Excel is displayed on the dashboard, including the pie chart and data bars (Figure 17–117). Now any changes made to the Excel spreadsheet will automatically be reflected in the dashboard. This allows users who are more comfortable with Excel to author reports that can be integrated with the rest of a PerformancePoint solution.
Microsoft Visio is a powerful desktop visualization tool. As we saw in Chapter 2, the Visio Services component in SharePoint Server 2010 allows Visio diagrams to be brought to life with real data. With PerformancePoint Services, we can take that integration one step further. A Visio diagram can be created that visually represents the KPIs on a scorecard. Those KPIs can then be connected to the Visio diagram to create a graphical representation of the status of the enterprise. In PerformancePoint, this type of report is called a strategy map.
In this section, we will create a simple Visio diagram that we'll deploy to PerformancePoint as a Strategy Map report. The term “strategy map” refers to a particular type of diagram often used in conjunction with the “balanced scorecard” methodology. While this feature in PerformancePoint is ideally suited for implementing that type of map, any Visio diagram can be used as long as it contains only simple shapes that don't involve sets or groupings within Visio.
Note Readers who are not familiar with Visio or Visio Services should review Chapter 2 before proceeding with the rest of this section.
Figure 17–118. A simple Visio diagram
Figure 17–119. Creating a strategy map report using the Promotion Scorecard
Figure 17–121. Connecting the Reseller Promotions shape to the scorecard
Try selecting different values for the Date and Sales Territory filters (Figure 17–126). Both the scorecard and the map are updated simultaneously. There are several features of the map to note:
There is an important limitation when using strategy maps that can be handled with a little additional planning. In our example, if we select a different date range and sales territory, we may see rows on the scorecard that don't appear on the map, or we may see rows on the scorecard disappear due to a lack of data. These shapes still appear on the map, but they don't show a color. When we connect shapes to the scorecard, that connection is static. We can't change that association as needed to fit how the scorecard is filtered. We also can't add shapes as new rows appear in the scorecard.
There are different strategies for handling such situations. One way is to include every possible shape on the map. You could also limit the filtering of the page to prevent unwanted rows from appearing in the scorecard. Regardless, the shapes on the map and how they are associated are fixed at runtime.
Strategy maps are a versatile way to add visualization to your solution. They can be used to provide a user-friendly means of interpreting your key performance indicators.
In this chapter, we have explored PerformancePoint Service as outlined in Figure 17–127, including: