Install PowerPivot for SharePoint in a Multi-Server SharePoint Farm

Prerequisites

You must be a local administrator to install SQL Server and SharePoint 2010.

The person who installs SharePoint must also configure the farm. To configure the farm, you must have a SQL Server login on the database server. The login must be assigned to the following roles: dbcreator, securityadmin and public.

The person who installs and configures PowerPivot for SharePoint must have db_owner permissions on the SharePoint_Config database. The farm administrator who originally installed SharePoint will have these permissions automatically.

You must have the Enterprise, Evaluation, Developer, or Data Center edition of SQL Server 2008 R2. If possible, run the Service Pack 1 (SP1) version of SQL Server 2008 R2 Setup.

You must have the enterprise or enterprise evaluation edition of SharePoint Server 2010.

The computer must be joined to a domain.

You must know which accounts you will use to run the Database Engine, the services in your farm, and Analysis Services in SharePoint integrated mode. Although you can change these accounts later, you will specify them for the first time during installation.

Service accounts that you specify during installation must be domain user accounts.

Before you begin installation, check your browser settings to verify you have an internet connection. The prerequisite installer opens an internet connection to download required software. You should make the following changes to ensure you get all of the required software:

• In Server Manager, temporarily disable Internet Explorer Enhanced Security Configuration to allow downloads to the server. For the purposes of downloading required software, you can turn off IE ESC for Administrators only.

• In Internet Explorer, you might also need to configure your browser to bypass a proxy server to allow localhost access to internet URLs.

  1. In Internet Explorer, on the Tools menu, click Internet Options.

  2. On the Connections tab, in the Local Area Network (LAN) settings area, click LAN Settings.

  3. In the Automatic configuration area, clear the Automatically detect settings check box.

  4. In the Proxy Server area, select the Use a proxy server for your LAN check box.

  5. Type the address of the proxy server in the Address box.

  6. Type the port number of the proxy server in the Port box.

  7. Select the Bypass proxy server for local addresses check box.

  8. Click OK to close the Local Area Network (LAN) Settings dialog box.

  9. Click OK to close the Internet Options dialog box.

Overview

A three-tier farm includes database, application, and web tiers. Adding PowerPivot to this topology requires that you run SQL Server 2008 R2 Setup to install PowerPivot for SharePoint on the application tier. PowerPivot program files are added to the web tier as a post-installation task when you deploy the PowerPivot web application solution. PowerPivot data files and application data is stored in content and service application databases in the farm. Although there are deployment steps, there is no separate installation step on either the web tier or data tier that you need to perform.

This topic assumes your farm topology is similar to the one described in the article Multiple servers for a three-tier farm. If you already have a farm that is operational, skip ahead to Install PowerPivot for SharePoint.

Step 1: Install a database server

If you are just getting started with your topology, begin by installing a SQL Server Database Engine. These instructions result in a database server that can be accessed by the SharePoint servers in your farm.

1. On the computer that you are using for the database server, run SQL Server Setup to install SQL Server Database Engine (see How to: Install SQL Server 2008 R2 (Setup)).

   When selecting features to install, choose the following:

   • Database Engine Services

   • Client Tools Connectivity

   • Management Tools - Complete (Basic will be included automatically)

2. After the Database Engine is installed, enable TCP/IP for remote connections and restart the service:

   1. Start SQL Server Configuration Manager.

   2. Open SQL Server Network Configuration.

   3. Select Protocols for MSSQLSERVER.

   4. Right-click TCP/IP and select Enable.

   5. Click SQL Server Services.

   6. Right-click SQL Server (MSSQLSERVER), and click Restart.

3. Enable inbound access to the database server through Windows Firewall. This allows the SharePoint servers in the farm to connect to the SharePoint databases. For more information, see Configuring the Windows Firewall to Allow SQL Server Access.

   1. In Administrative Tools, click Windows Firewall with Advanced Security.

   2. Click Inbound Rules.

   3. Click New Rule.

   4. In Rule Type, click Custom.

   5. Click Next.

   6. In Program, in the Services section, click Customize.

   7. Click Apply to this service.

   8. Select SQL Server (MSSQLSERVER) if you installed SQL Server as the default instance, and then click OK.

   9. Click Next.

   10. In Protocol and Ports, accept the default settings and click Next.

   11. In Scope, accept the default settings and click Next.

   12. In Action, accept the default settings and click Next.

   13. In Profile, clear the checkboxes for Private and Public, and then click Next.

   14. In Name, type a descriptive name for the inbound rule (for example, SQL Server).

   15. Click Finish.

4. Create a corresponding outbound rule for the same service instance.

   1. In Administrative Tools, click Windows Firewall with Advanced Security.

   2. Click Outbound Rules.

   3. Click New Rule.

   4. In Rule Type, click Custom.

   5. Click Next.

   6. In Program, in the Services section, click Customize.

   7. Click Apply to this service.

   8. Select SQL Server (MSSQLSERVER) if you installed SQL Server as the default instance, and then click OK.

   9. Click Next.

   10. In Protocol and Ports, accept the default settings and click Next.

   11. In Scope, accept the default settings and click Next.

   12. In Action, select Allow the connection and click Next.

   13. In Profile, clear the checkboxes for Private and Public, and then click Next.

   14. In Name, type a descriptive name for the outbound rule (for example, SQL Server).

   15. Click Finish.

Step 2: Install and configure a multi-server SharePoint 2010 farm

On the computers you are using as SharePoint servers, run the SharePoint Prerequisite Installer program on each, followed by SharePoint Server Setup. Refer to the instructions in SharePoint 2010 documentation to install and configure a SharePoint 2010 farm that includes two web servers and an application server:

Multiple servers for a three-tier farm (SharePoint Server 2010)

When asked to specify a database server, specify the database server you installed earlier.

In the procedures that follow, it is assumed that you configured the farm using the instructions provided for three-tier farm setup. The farm should include the following elements:

• Excel Services, Search Service, and Secure Store Service

• A web application and site collection

• Usage and health data collection

• Diagnostic logging

All SharePoint web applications for which you want to enable PowerPivot data access must be configured to use classic-mode authentication. For more information, see the “Verify the Web application uses Classic mode authentication” section in Deploy PowerPivot Solutions.

Step 3: Check for a prior installation of Analysis Services

If your computer has a previous installation of SQL Server 2008 R2 Analysis Services or PowerPivot for SharePoint, you might run into a known Setup bug where invalid .NET assembly cache status information about the Microsoft.AnalysisServices.SharePoint.Integration.dll file causes SQL Server Setup to fail with the following error: “Could not load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’”.

The error is most likely to occur under these conditions: a SQL Server 2008 R2 installation of Analysis Services, followed by SharePoint Server 2010, followed by a PowerPivot for SharePoint.

The error does not always occur on a system having this configuration, but it happens often enough to warrant precautionary steps to avoid running into it in the first place. Follow these steps to avoid the error.

1. Check the Programs folder for an instance SQL Server 2008 R2 Analysis Services. If you find an existing installation or evidence of a prior installation, perform the remaining steps. Otherwise, go directly to Step 2: Install PowerPivot for SharePoint.

2. Using Notepad, with administrative rights, edit setup100.exe.config to add the following XML statements.

   First, right-click Notepad and choose Run as Administrator.

   Open setup100.exe.config from this location: %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64

   Add <disableCachingBindingFailures> to the file, and then save the file. This entry instructs Setup to ignore cached binding failures so that a new instance of the object can be installed even if it failed earlier.

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
     <runtime>
       <disableCachingBindingFailures enabled="1" />
     </runtime>
   </configuration>
   

3. Continue on to Step 2: Install SQL Server PowerPivot for SharePoint.

For more information about this error and its resolution, see Installation failure: “Could not load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’ and Error message when you install SQL Server 2008 R2 before you run the PreRequisiteInstaller.exe tool.

Step 4: Install PowerPivot for SharePoint on an application server

Run SQL Server 2008 R2 Setup to add PowerPivot for SharePoint to a SharePoint farm. If the farm consists of multiple SharePoint servers, you must run SQL Server 2008 R2 Setup on an application server that is already joined to the farm.

1. On computer that already has an installation of SharePoint 2010, insert the installation media or open a folder that contains the setup files for SQL Server 2008 R2.

2. Run SQL Server Setup with administrator permissions. Right-click Setup.exe and select Run as Administrator.

3. Click Installation on the navigation pane to the left.

4. Click New installation or add features to an existing installation.

5. In Setup Support Rules, click OK if no problems were detected. Otherwise, follow the instructions on the page to correct the problems and then restart setup.

6. In Setup Support Files, click Install. This step adds the SQL Server Setup program files to your computer and identifies any problems that might occur when the support files are installed.

   

   Note

   If Windows Firewall is enabled, you will be warned to open ports to enable remote access. This warning is generally not applicable to PowerPivot installations. Connections to PowerPivot services and data files are made using the SharePoint ports that are already open for SharePoint service-to-service communication. You only need to open ports if you are installing other SQL Server features that require data access from a remote computer. Examples include using Analysis Services multidimensional databases as an external data source for a PowerPivot workbook, using client applications such as SQL Server Management Studio to browse a PowerPivot workbook that is loaded on a remote SharePoint server, or setting up a Database Engine as a database server for a multi-server SharePoint farm. For more information about how to open ports used by these servers, see Configuring the Windows Firewall to Allow SQL Server Access.

7. Click Next to install the support files.

8. In Installation Type, select New installation or add features. This page only appears if you already have an existing instance installed. Do not select Add features to an existing instance of SQL Server 2008 R2. PowerPivot for SharePoint must be installed as a new instance. Click Next.

   

9. In Product Key, enter a product key for a licensed copy of the Enterprise, Developer, or Data Center edition, or specify the Evaluation edition. Click Next.

10. Accept the Microsoft Software License Terms of agreement, and then click Next.

11. In Setup Role, select SQL Server PowerPivot for SharePoint.

    Also on the Setup Role page, in Add PowerPivot for SharePoint to:, select Existing Farm. Click Next.

    

12. In Feature Selection, view a read-only list of the features that will be installed. This list is displayed for informational purposes. You cannot add or remove items that are preselected for this role. Click Next.

13. In Installation Rules, click Next.

14. In Instance Configuration, a read-only instance name of 'PowerPivot' is displayed for informational purposes. This instance name is required and cannot be modified. However, you can enter a unique Instance ID to specify a descriptive directory name and registry keys. Click Next.

15. In Disk Space Requirements, verify you have sufficient disk capacity to install the feature, and then click Next.

16. In Server Configuration, specify a domain user account for SQL Server Analysis Services. Do not use a built-in system account, such as Local System or Network Service. Specifying a domain user account is required for installing Analysis Services in a SharePoint farm. For more information about account recommendations, see Plan a PowerPivot Deployment in a SharePoint Farm. Click Next.

    

17. In Analysis Services Configuration, click Add Current User.

    In the same page, add the Windows user account of any person who requires administrative permissions on the Analysis Services service instance. Any user who wants to connect to an Analysis Services service instance in SQL Server Management Studio to troubleshoot database connection problems or get version information must have system administrator permissions on the server. Add the user account of any person who might need to troubleshoot or administer the server now.

    Click Next.

18. Click Next on each of the remaining pages until you get to the Ready to Install page.

19. Click Install.

Step 5: Deploy PowerPivot for SharePoint to web front end servers

Deploying a PowerPivot solution to SharePoint web application is required for server configuration.

1. Click the Start button, select All Programs, select Microsoft SharePoint Products 2010, and then select SharePoint 2010 Central Administration.

2. In SharePoint 2010 Central Administration, in System Settings, click Manage farm solutions.

   You should see two separate solution packages: powerpivotfarm.wsp and powerpivotwebapp.wsp. The first solution (powerpivotfarm.wsp) is deployed when you install the first PowerPivot for SharePoint instance and never needs to be deployed again. The second solution (powerpivotwebapp.wsp) is deployed for Central Administration, but you must deploy this solution manually for each SharePoint web application that will support PowerPivot data access.

3. Click powerpivotwebapp.wsp.

4. Click Deploy Solution.

5. In Deploy To?, select the SharePoint web application to which you want to add PowerPivot feature support.

   Important

   Be sure that the web application is configured for classic-mode authentication. For instructions on how to verify authentication mode, see Deploy PowerPivot Solutions.

6. Click OK.

7. Repeat for other SharePoint web applications that will also support PowerPivot data access.

Step 6: Start Services on the Server

A PowerPivot for SharePoint deployment requires that your farm include the following services: Excel Calculation Services, Secure Store Service, and Claims to Windows token service.

Both Excel Services and PowerPivot for SharePoint use the Claims to Windows Token Service to establish connections to external data sources using the Windows identity of the current SharePoint user. If the service is not already started, you must start it now to enable Excel Calculation Services to forward authenticated requests to the PowerPivot System Service.

1. In Central Administration, in System Settings, click Manage services on server.

2. Start the Claims to Windows Token Service.

3. Verify Excel Calculation Services is started.

4. Verify Secure Store Service is started.

5. Verify that both SQL Server Analysis Services and SQL Server PowerPivot System Service are started.

Step 7: Create a PowerPivot Service Application

The next step is to create a PowerPivot service application.

1. In Central Administration, in Application Management, click Manage service applications.

2. In the Service Applications ribbon, click New.

3. Select SQL Server PowerPivot Service Application. If it does not appear in the list, PowerPivot for SharePoint is not installed or the solution is not deployed.

4. In the Create New PowerPivot Service Application page, enter a name for the application. The default is PowerPivotServiceApplication<number>. If you are creating multiple PowerPivot service applications, a descriptive name will help other administrators understand how the application is used.

5. In Application Pool, create a new application pool and select a security account for it. A domain user account is required.

6. In Database Server, choose a database server on which to create the service application database. The default value is the SQL Server Database Engine instance that hosts the farm configuration databases.

7. In Database Name, the default value is PowerPivotServiceApplication1_<guid>. The default database name corresponds to the default name of the service application. If you entered a unique service application name, follow a similar naming convention for your database name so that you can manage them together.

8. In Database Authentication, the default is Windows Authentication. If you choose SQL Authentication, refer to the SharePoint administrator guide for best practices on how to use this authentication type in a SharePoint deployment.

9. Select the checkbox for Add the proxy for this PowerPivot service application to the default proxy group. This adds the service application connection to the default service connection group. You must have at least one PowerPivot service application in the default connection group.

   If a PowerPivot service application is already listed in the default connection group, do not add a second service application to that group. Adding two service applications of the same type in the default connection group is not a supported configuration. For more information about how to use additional service applications in a connection group, see Connect a PowerPivot Service Application to a SharePoint Web Application.

10. Click OK. The service will appear alongside other managed services in the farm's service application list.

Step 8: Configure Excel Services

In this step, you modify Excel Services configuration to support large file uploads and allow preview image generation in PowerPivot Gallery.

Alternatively, if you want to preserve the Excel Services default settings, you can skip this step for now. Later, after you have created sites on your SharePoint server, you can specify a new trusted location for those sites that support PowerPivot data access, and then configure those sites for large file uploads and preview image generation. For more information, see Create a trusted location for PowerPivot sites.

1. In Central Administration, in Application Management, click Manage service applications.

2. Select Excel Services Application.

3. Click Trusted File Locations and on this page, select your trusted location. (Typically, this is listed as http:// in the Address column.)

4. In the Workbook Properties area, set Maximum Workbook Size to 50.

5. In External Data, verify that Trusted data connection libraries and embedded is selected. This setting is required for PowerPivot data access in a workbook.

6. Clear the Warn on Data Refresh checkbox to allow preview images of individual worksheets in PowerPivot Gallery. If you choose to keep the warning and workbook settings specify refresh on open, you might get a single preview image of the warning instead of the pages in your workbook.

7. Click OK.

Step 9: Configure Secure Store Service and Create the PowerPivot Unattended Data Refresh Account

PowerPivot for SharePoint requires Secure Store Service to store credentials and the unattended execution account for data refresh. Secure Store Service is already enabled for your farm, but further configuration is required to make it available. At a minimum, you must generate a master key. Additionally, you should also create target applications for the PowerPivot unattended data refresh account and any other credentials that need to be stored for subsequent use in data refresh.

1. In Central Administration, in Application Management, click Manage service applications.

2. Select Secure Store Service.

3. In the Service Applications ribbon, in Key Management, click Generate New Key.

4. Enter and then confirm a pass phrase. The pass phrase will be used to add additional secure store shared service applications.

5. Click OK.

For additional instructions on how to enable Secure Store Service and prepare a server for PowerPivot data refresh, see Enable and Configure PowerPivot Data Refresh.

For instructions about how to create the unattended account later or configure other stored credentials used in data refresh, see Configure and Use the PowerPivot Unattended Data Refresh Account and Configure and Use Stored Credentials for PowerPivot Data Refresh.

Step 10: Configure alternate access mapping for Web front-end servers

To ensure that requests for PowerPivot data access or data refresh are handled by each web front-end server, you must map the different URLs of each server to the same web application. To learn more about alternate access mappings, see Configuring Alternate Access Mapping in SharePoint 2010 on the MSSharePointTips web site.

1. In Central Administration, in Application Management, click Configure alternate access mappings.

2. Click Add Internal URLs.

3. In Alternate Access Mapping Collection, select the web application for which you are configuring alternate access. On a default installation, the web application is SharePoint – 80.

4. In URL protocol, host and port, specify the URL of the first web front-end server and then click Save.

5. Click Add Internal URLs again. The same web application should still be selected.

6. Type the URL of the second web front-end server, and then click Save.

Step 11: Set Upper Limits on Disk Space Usage

New in SP1 is the ability to set a maximum limit on how much disk space is used for PowerPivot data files that are cached to disk. The default is to use all available disk space. If you are using SP1, follow these instructions to limit disk space consumption: Configure Disk Space Usage (PowerPivot for SharePoint).

Step 12: Increase File Maximum Upload Size for SharePoint Web Applications

Because PowerPivot workbooks can be large, you might want to increase the maximum file size. There are two file size settings to configure: Maximum Upload Size for the web application, and Maximum Workbook Size in Excel Services. The maximum file size should be set to the same value in both applications. For instructions, see Configure Maximum File Upload Size (PowerPivot for SharePoint).

Step 13: Activate PowerPivot Feature Integration for Site Collections

Feature activation at the site collection level makes application pages and templates available to your sites, including configuration pages for scheduled data refresh and application pages for PowerPivot Gallery and Data Feed libraries.

1. On a SharePoint site, click Site Actions.

   By default, SharePoint web applications are accessed through port 80. This means that you can often access a SharePoint site by entering http://<computer name> to open the root site collection.

2. Click Site Settings.

3. In Site Collection Administration, click Site collection features.

4. Scroll down the page until you find PowerPivot Integration Site Collection Feature.

5. Click Activate.

6. Repeat for additional site collections by opening each site and clicking Site Actions.

For more information, see Activate PowerPivot Feature Integration for Site Collections.

Step 14: Verify integration and server availability

PowerPivot query processing in the farm occurs when a user or application opens an Excel workbook that contains PowerPivot data. At a minimum, you can check pages on SharePoint sites to verify that PowerPivot features are available. However, to fully verify an installation, you must have a PowerPivot workbook that you can publish to SharePoint and access from a library. For testing purposes, you can publish a sample workbook that already contains PowerPivot data and use it to confirm that SharePoint integration is correctly configured.

To verify PowerPivot integration with a SharePoint site, do the following:

1. In a browser, open the Web application you created. If you used default values, you can specify http://<your computer name> in the URL address.

2. Verify that PowerPivot data access and processing features are available in the application. You can do this by verifying the presence of PowerPivot-provided library templates:

   1. On Site Actions, click More Options….

   2. In Libraries, you should see Data Feed Library and PowerPivot Gallery. These library templates are provided by the PowerPivot feature and will be visible in the Libraries list if the feature is integrated correctly.

To verify PowerPivot data access on the server, do the following:

1. Upload a PowerPivot workbook to PowerPivot Gallery or any SharePoint library. For more information about how to access sample files, see Roadmap to Creating PowerPivot Workbooks in Excel.

2. Click on the document to open it from the library.

3. Click on a slicer or filter the data to start a PowerPivot query. The server will load PowerPivot data in the background and return the results. In the next step, you will connect to the server to verify the data is loaded and cached.

4. Start SQL Server Management Studio from the Microsoft SQL Server 2008 R2 program group in the Start menu. If this tool is not installed on your server, you can skip to the last step to confirm the presence of cached files.

5. In Server Type, select Analysis Services.

6. In Server Name, enter <server-name>\powerpivot, where <server-name> is the name of the computer that has the PowerPivot for SharePoint installation.

7. Click Connect.

8. In Object Explorer, click Databases to view the list of PowerPivot data files that are loaded.

9. On the computer file system, check the following folder to determine whether files are cached to disk. The presence of cached files is further verification that your deployment is operational. To view the file cache, go to the \Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup folder.

Troubleshoot Installation and Configuration Errors

If you get errors instead of the pages and features you expect, do the following:

• Review release notes for both SharePoint 2010 and SQL Server 2008 R2 to get workarounds for known installation problems. Release notes are provided with the installation media or on the Microsoft site from which you downloaded the software.

• Review the Troubleshooting PowerPivot for SharePoint Installation Problems page on the Microsoft TechNet WIKI.

• Refer to SharePoint deployment articles on the Microsoft web site. The articles include steps for configuring settings in Internet Explorer. For more information, see Deploy a single server with SQL Server.

Post-Installation Steps

After you verify the installation, finish service configuration by creating a PowerPivot Gallery or tuning individual configuration settings. To make full use of the server components you just installed, you can download PowerPivot for Excel to create and then publish your first PowerPivot workbook.

Grant SharePoint Permissions to Workbook Users

Install ADO.NET Data Services 3.5 SP1

Install Data Providers Used in Data Refresh and Check User Permissions

Create a PowerPivot Gallery

Installing PowerPivot for Excel and building a PowerPivot workbook

Adding servers or applications over time

Tuning configuration settings

See Also

Tasks

Concepts