Existing SharePoint 2010 Installation
Working with an existing installation is by far the simplest from a solution developer point of view. Coaxing, coercing, or otherwise convincing your SharePoint Administrator to execute the steps described in the following subsections will enable the PowerPivot for SharePoint features and allow publishing of PowerPivot solutions to an existing SharePoint server.
Note The steps to install PowerPivot into an existing SharePoint installation are already outlined in great detail in Microsoft's MSDN Library at http://msdn.microsoft.com/en-us/library/ee210616(SQL.105).aspx. I have listed the high-level tasks here as a guide to the process.
There are a number of pre-conditions for adding PowerPivot to an existing SharePoint server. First, the SharePoint server must have been installed using the Server Farm setup installation, as the built-in database installed during the stand-alone option is not supported for PowerPivot. The installing user must be a local administrator for the computer to which PowerPivot will be added. Additionally, the user must also be a farm administrator in order to add PowerPivot for SharePoint to the farm. From a SQL Server edition perspective, you must have licensing for Enterprise, Evaluation, or the Data Center editions of SQL 2008 R2.
Finally, the target computer should be joined to a domain. You will need a minimum of two domain accounts, one each for Analysis Services and the PowerPivot service application. For development installations, I go with domainAnalysisService and domainPowerPivotService, just to add consistency.
Step One: Install SQL Server PowerPivot for SharePoint
The first task is to add PowerPivot for SharePoint using the SQL Server 2008 R2 installation media. Start the SQL Server 2008 R2 setup.exe program, selecting the new installation option. The key point in this installation is in the Setup Role dialog, illustrated in Figure 6-1. You must choose the SQL Server PowerPivot for SharePoint option, as illustrated in the figure.
Figure 6-1. Setup Role selection
Step Two: Deploy the PowerPivot Solution Package
With the software installed on the farm, the next task is to deploy the PowerPivot solutions (powerpivotwebapp.wsp) to the farm via SharePoint 2010 Central Administration. The installer deploys this solution to Central Administration, but your SharePoint Administrator must manually deploy this solution to each web application that will use PowerPivot for SharePoint features.
The PowerPivot Management Dashboard is the part of Central Administration that monitors usage, calculation, refresh, and storage of PowerPivot solutions. The dashboard is actually written in PowerPivot, making deployment of the solution package vital for Central Administration. That's why the solution package is automatically deployed by the installer. However, additional web applications hosted on the SharePoint farm will have PowerPivot available only after the solution (powerpivotwebapp.wsp) is deployed to each.
Step Three: Start Other Required Services
PowerPivot for SharePoint was designed, out of the box, to leverage existing SharePoint services. For example, Excel Calculation Services supplies workbook calculations for PowerPivot for SharePoint workbooks. You'll need to start that service. In addition to Excel Calculation Services, you will need to start the Claims to Windows Token Service (enabling external data connections with SharePoint user's Windows credentials) and the Secure Store Service.
Step Four: PowerPivot Service Application
Using SharePoint 2010 Central Administration, you should create a new service application, based on the SQL Server PowerPivot Service Application type. From a future management best practice, consider the naming convention of your service application and database, both of which will be added in this process, to ensure the ability to tie a PowerPivot database to a PowerPivot service application.
Step Five: Enable Excel Services
In Step Three, Excel Calculation Services was started. In this task, you will create a new application of type Excel Services Application. Additionally, the trusted file locations (from where Excel Services can access workbooks) and External Data settings will be established at the service application level. Of particular note here is the impact of the Maximum Workbook Size (the Microsoft procedure recommends 50, while I use 512 routinely). This is one of the key settings governing workbook complexity. Additionally, it governs the maximum file size that can be uploaded to PowerPivot for SharePoint (along with the Web Application Maximum Upload Size in Step Eight).
Step Six: Enable the Secure Store Service
In order to allow workbook authors to use external data sources, a means of securely storing the credentials for those sources must be deployed. The Secure Store Service supplies this encrypted storage layer for external data credentials. Using SharePoint 2010 Central Administration, the farm administrator will create a new service application, based on the SQL Server PowerPivot Service Application type. From a future management best practice, consider the naming convention of your service application and database, both of which will be added in this process, to ensure the ability to tie a PowerPivot database to a PowerPivot service application.
Step Seven: Enable Usage Data Collection
One of the appealing enterprise architecture features in PowerPivot for SharePoint is the management dashboard. Enabling usage data collection allows the management dashboard to accurately reflect the frequency of calculation, query, and usage of the PowerPivot for SharePoint solutions on this farm.
Step Eight: Maximum File Upload Size
Even with compression from the in-memory SSAS instance, PowerPivot workbooks can be large. SharePoint governs the maximum file upload size in two separate settings. As a best practice, I encourage you to keep these values the same. The first place this value is set is the Excel Service Maximum Workbook Size (from Step Five). The second related value is the Maximum File Upload size for the SharePoint web application.
Step Nine: Activate PowerPivot at the Site Collection Level
At this point, PowerPivot for SharePoint is installed and configured on your SharePoint farm, and activated as a feature for a SharePoint web application. However, before a user can actually publish a PowerPivot workbook to SharePoint, the features must be activated for at least one site collection. At this point, your administrator may want to activate online as well for the PowerPivot feature.
Step Ten: Verify the Configuration Changes Work
Verifying that PowerPivot for SharePoint is as easy as opening the web application into which PowerPivot for SharePoint was installed and using the Site Actions menu to ensure there is a PowerPivot Gallery available. Additionally, uploading one of your PowerPivot solutions from an earlier chapter into a PowerPivot Gallery document library should allow a preview similar to Figure 6-2.
Figure 6-2. PowerPivot Gallery