PowerPivot and PerformancePoint Dashboards

PerformancePoint Services is a dashboard and scorecard development platform that is part of SharePoint Server 2010. Like PowerPivot, PerformancePoint can consume data from different data sources, including SSAS, Excel Services, SharePoint lists, and SQL Server relational database tables. PerformancePoint's more interesting analytic features, including interactive reports known as analytic grids and analytic charts, require a SQL Server Analysis Services database as their data source.

Installation and configuration of PerformancePoint Services is well beyond our scope. However, if you were able to create a development environment as described in Chapter 6, you have all the software required to install and configure PerformancePoint Services. The example to follow will assume a working PerformancePoint Services deployment. It further assumes the presence of an unattended service account, and that you have permissions to access the “PowerPivot” SSAS instance.

SQL Server Management Studio (SMSS) can be used to verify the ability of a domain account to access the PowerPivot for SharePoint database. While logged on as the candidate unattended service account user, open SSMS from the Start Programs menu, under Microsoft SQL Server 2008 R2 and then SQL Server Management Studio. This will create a window similar to Figure 8-29.

Figure 8-29. SQL Management Studio login

Ensure the Server type is set to Analysis Services. The Server name should be the name or IP address of your PowerPivot for SharePoint server followed by “PowerPivot”, just as the connection string was created earlier in the chapter for a connection to the PowerPivot for SharePoint database from a PowerPivot workbook. The final element of the connection dialog, the Authentication, should already be set to Windows Authentication, the only means of authenticating users for SSAS. Press the Connect button, and a window similar to Figure 8-30 should be generated.

Figure 8-30. Successful SSMS login

If a login error occurs, the candidate account may not have access to the PowerPivot for SharePoint database server. Verify the permissions on the PowerPivot instance. Otherwise, you should be able to expand the database listing in the left-hand window explorer to reveal all of the databases currently stored on the server.

After successfully verifying the ability of the unattended service account to access the PowerPivot for SharePoint database, the next step is to use the PerformancePoint Dashboard Designer to create a data connection. The exact address for opening Dashboard Designer in your environment will vary. However, if your SharePoint administrator utilizes the “Business Intelligence Center” site template to create your PerformancePoint site collection, you will be able to access the launch page for Dashboard Designer illustrated in Figure 8-31 from the new BI Center site. From your Business Intelligence Center site, choose any of the links labeled “Start using PerformancePoint Services”. The result will be the launch page illustrated in Figure 8-31. Clicking the Run Dashboard Designer button will launch the ClickOnce deployment of Dashboard Designer to your computer.

Figure 8-31. Run Dashboard Designer

After installation and launch, Dashboard Designer will resemble the window pictured in Figure 8-32. Dashboard Designer is intended to be a Microsoft Office–like environment for business users to create, publish, and manage dashboards and dashboard components. In order to illustrate basic use of PowerPivot for SharePoint as a data source, we need only to create a PerformancePoint data connection and then use that connection as the basis of a report.

Figure 8-32. Dashboard Designer

Creating the Data Connection

To configure the data connection from PerformancePoint Services to the PowerPivot for SharePoint database, first select the Data Connections library from the left-hand explorer. Then choose Data Source from the Create ribbon menu. Alternatively right-click the Data Connection library in the left-hand explorer window and choose New Data Source from the context menu. Either method will generate a window similar to Figure 8-33, in which the data connection configuration can be entered.

Figure 8-33. PowerPivot data connection

You can complete your connection configuration with the server name or IP address of the PowerPivot for SharePoint server, followed by “PowerPivot” to indicate a connection to the database instance dedicated for PowerPivot operations. After entering the server, the Database pulldown can be used to list all of the currently loaded databases in the PowerPivot instance. The Cube pulldown for PowerPivot solutions will in this release be Sandbox, though the pulldown will ensure the correct value. Ensure the Authentication section's Unattended Service Account radio button is selected. Finally, pressing the Test Data Source button will verify the connection can be made. Upon a successful connection test, I recommend changing the name of the data connection, which will default to “New Data Source” to something more useful like “PowerPivot Sales” or “PowerPivot ProductionOps.”

However, instead we will use the MSOLAP provider and a connection string in the form illustrated in Figure 8-33. Pressing the Test Data Source button will verify access to the PowerPivot data specified in the connection string.

If at this point an error occurs, repeat verification of the unattended service account for PerformancePoint and the ability of the unattended service account to access the “PowerPivot” instance of SSAS via SQL Management Studio. If the unattended service account has access to the “PowerPivot” instance, verify the PerformancePoint Services installation and that the PerformancePoint service is actually running on the SharePoint farm.

Creating the Analytic Chart

With the PerformancePoint data connection configured and verified, creation of the report to surface the PowerPivot data is a matter of drag and drop. To begin, from the Create ribbon menu, select the PerformancePoint Content library from the left-hand content explorer. Then choose Analytic Chart from the Create ribbon menu. Similar to the data connection creation, you may also right-click the PerformancePoint Contact library, choose New Report from the context menu, and then choose Analytic Chart from the Report Template selector. Either method will generate the PerformancePoint data connection selection dialog illustrated in Figure 8-34.

Figure 8-34. Selecting PerformancePoint data source

Choose the PowerPivot source created earlier and press the Finish button. This will launch the PerformancePoint report authoring environment illustrated in Figure 8-35. For convenience, the report has already been renamed to “Example Report” and the right-hand listing of the data source content has been expanded.

Figure 8-35. PerformancePoint report development environment

There are many variations and techniques possible via PerformancePoint analytic charts; however, the intent of this example is just to show the surfacing of information from PowerPivot by PerformancePoint. In order to chart both production cost and units, drag both of the measures from the right-hand explorer to the Series area in the bottom left portion of the report development environment. Drag the SortMonth from the right-hand explorer to the Bottom Axis area of the report designer. After adding the SortMonth, the report designer will create a preview of the chart, essentially a bar graph of both Production Cost and Production Units, for each of the 12 values of SortMonth.

Finally, drag the Year from the right-hand explorer to the Background area of the report designer. The purpose of the Background items requires a bit of explanation. Prior to your configuring a background selection, the analytic report reflects all data for all years in the PowerPivot for SharePoint database for the ProductionSummary.xlsx solution. Clicking the down arrow to the right of a background area element will produce a dialog to filter by that dimension, similar to what you see in Figure 8-36. As the dashboard is currently configured, only data for year 2010 is being reflected in the report. An additional benefit of adding a background element to an analytic chart is the automatic creation of a filter endpoint for connecting to a PerformancePoint dashboard filter, in order to interactively filter for values of Year.

Figure 8-36. Configuring background selection

Finishing the background selection, the PerformancePoint analytic chart should resemble Figure 8-37. The chart can be saved to the PerformancePoint content library by clicking the disk icon in the upper left-hand corner of Dashboard Designer, just to the right of the ribbon menu button.

Figure 8-37. Completed analytic chart

Deploying to SharePoint

After completing the analytic chart and saving it to the PerformancePoint content library, the next step to surface the PowerPivot data is to create a PerformancePoint dashboard. You can do that from Dashboard Designer, either from the Create ribbon menu or by right-clicking the PerformancePoint content library. Either method will result in a Dashboard Page Template selection similar to that in Figure 8-38. Select the 1 Zone template, as highlighted in the figure.

Figure 8-38. Dashboard page template selection

After selecting the page template, a dashboard design environment similar to Figure 8-39 will be presented. You can optionally rename the dashboard to something meaningful to replace the default “New Dashboard” name. In the single zone of the dashboard, drag and drop the desired analytic report from the right-hand explorer view. The results should be similar to Figure 8-39.

Figure 8-39. Selection of dashboard content

Finally, right-click the name of the dashboard in the left-hand explorer and choose Deploy to SharePoint from the context menu. Accept the defaults for the deployment dialog, and the PerformancePoint dashboard, with the sole content consisting of the PowerPivot-sourced analytic report, should open in Internet Explorer (or your default browser). Your results should be similar to Figure 8-40.

Figure 8-40. PerformancePoint dashboard complete