Share via

  • Article

Install PowerPivot for SharePoint on an Existing SharePoint Server

You can add SQL Server PowerPivot for SharePoint to a computer that already has SharePoint Server 2010 installed to support query processing and data refresh for published Excel workbooks that contain PowerPivot data.

This topic contains the following sections:

Prerequisites

Step 1: Check for a prior installation of Analysis Services

Step 2: Install SQL Server PowerPivot for SharePoint

Step 3: Deploy the PowerPivot Solution Package

Step 4: Start Services on the Server

Step 5: Create a PowerPivot Service Application

Step 6: Configure Excel Services

Step 7: Configure Secure Store Service and Data Refresh

Step 8: Set Upper Limits on Disk Space Usage

Step 9: Increase Maximum Upload Size for SharePoint Web Applications and Excel Services

Step 10: Activate PowerPivot Feature Integration for Site Collections

Step 11: Verify Installation and Configuration

Troubleshoot Installation and Configuration Errors

Post-Installation Tasks

Note

You can use PowerShell script to automate post-installation configuration tasks. For more information, see PowerShell Configuration Script (PowerPivot for SharePoint).

Note

Use alternate instructions if you are using a clean server to deploy a dedicated PowerPivot for SharePoint server for small workgroups or departments. For more information about how to install PowerPivot for SharePoint on a computer that does not yet have an operational SharePoint server, see Install PowerPivot for SharePoint on a New SharePoint Server.

Prerequisites

You must have an operational single or multi-server farm of Microsoft SharePoint 2010 Enterprise edition. Excel Services must be enabled on your server or farm. The server must have been installed using the Server Farm installation option in SharePoint setup. A standalone SharePoint server that uses a built-in database is not supported.

You must have the Enterprise, Evaluation, Developer, or Data Center edition of SQL Server 2008 R2.

The computer must be joined to a domain.

Accounts that you specify for services must be domain user accounts. At a minimum, you will need one domain account for Analysis Services and another account for the PowerPivot service application. If you are configuring additional services (such as Excel Services), you should have separate accounts for each service you provision.

You must be a local administrator on the computer to run SQL Server Setup.

You must be a farm administrator to add PowerPivot for SharePoint to the farm. If the farm administrator account is not a member of the local Administrator security group, add it to the group so that you can install PowerPivot for SharePoint. You can remove it later after installation is finished.

You must have db_owner permissions on the SharePoint_Config database. The farm administrator who originally installed SharePoint will have these permissions automatically.

The SharePoint web application 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 1: 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.

Note

This problem is fixed in SQL Server 2008 R2 SP1, but unless you or someone in your organization has built a slipstream version of Setup that contains the fix, you can potentially run into this error if you installed server software in the order described.

Step 2: Install SQL Server PowerPivot for SharePoint

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.

    Setup Support Rules page in Install Wizard

    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.

    Installation Type page in Install Wizard

  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.

    Setup Role page with Existing Farm selected

  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.

    Server Configuration page in Install Wizard

  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 3: Deploy the PowerPivot Solution Package

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 4: 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. Start Excel Calculation Services.

  4. Start Secure Store Service.

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

Step 5: Create a PowerPivot Service Application

The next step is to create a PowerPivot service application.

Note

Errors that occur while trying to create the application indicate a problem with the installation. Try repairing your installation to see if it resolves the issue. For more information, see Repair (PowerPivot for SharePoint).

  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 6: Configure Excel Services

PowerPivot for SharePoint requires Excel Services to support PowerPivot data access in the farm. You can determine whether Excel Services is already enabled by confirming whether Excel Services Application appears in the list of service applications in Central Administration. If Excel Services is not listed, follow these steps to enable it now.

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

  2. In the Service Applications ribbon, in Create, click New.

  3. Select Excel Services Application.

  4. In Create New Excel Services Application, specify a name (for example, Excel Services Application).

  5. In Application Pool, select Create new application pool and give it a descriptive name (for example, Excel Services Application Pool).

  6. In Configurable, select a Windows domain user account for this application pool identity.

  7. Keep the default checkbox that adds the service application proxy to the default service connection list.

  8. Click OK.

  9. Click the Excel Services application you just created.

  10. Click Trusted File Locations and on this page, select your trusted location. (Typically, this is listed as http:// in the Address column.) To ensure that both Excel Services and PowerPivot service have access to the workbook, you must include SharePoint as an Excel Services trusted location. PowerPivot System Service cannot access workbooks that are stored outside of a SharePoint farm.

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

  12. In External Data, set Allow External data to Trusted data connection libraries and embedded. This setting is required for PowerPivot data access in a workbook.

  13. 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.

  14. Click OK.

Step 7: Configure Secure Store Service and Data Refresh

PowerPivot for SharePoint requires Secure Store Service to store credentials and the unattended execution account for data refresh. You can determine whether Secure Store Service is already enabled by confirming whether it appears in the list of service applications.

Important

If Secure Store Service is enabled, you should still verify that a master key has been generated for it. For instructions, see Part 2: Generate the Master Key in the following procedure.

If Secure Store Service is not listed, follow these steps to enable it now. By enabling Secure Store, workbook authors and document owners can access a broader range of data source connection options when scheduling data refresh for their published workbooks.

Part 1: Enable Secure Store Service

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

  2. In the Service Applications ribbon, in Create, click New.

  3. Select Secure Store Service.

  4. In the Create Secure Store Application page, enter a name for the application.

  5. In Database, specify the SQL Server instance that will host the database for this service application. The default value is the SQL Server Database Engine instance that hosts the farm configuration databases.

  6. In Database Name, enter the name of the service application database. The default value is Secure_Store_Service_DB_<guid>. The default 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.

  7. In Database Authentication, the default is Windows Authentication. If you choose SQL Authentication, refer to the SharePoint administrator guide for guidance on how to use the authentication type in your farm.

  8. In Application Pool, select Create new application pool. Specify a descriptive name that will help other server administrators identify how the application pool is used.

  9. Select a security account for the application pool. Specify a managed account to use a domain user account.

  10. Accept the remaining default values, and then click OK. The service application will appear alongside other managed services in the farm's service application list.

Part 2: Generate the Master Key

  1. Click the Secure Store Service application from the list.

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

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

  4. Click OK.

Part 3: Configure the Unattended PowerPivot Data Refresh Account

Creating an unattended data refresh account for PowerPivot data access is often required for external data access during data refresh. For example, if Kerberos is not enabled, you must create an unattended account that the PowerPivot service can use to connect to external data sources.

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 8: 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 9: Increase Maximum Upload Size for SharePoint Web Applications and Excel Services

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 10: 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 11: Verify Installation and Configuration

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 Tasks

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

Users will need SharePoint permissions before they can publish or view workbooks. Be sure to grant View permissions to users who need to need to view published workbooks and Contribute permissions to users who publish or manage workbooks. You must be a site collection administrator to grant permissions.

  1. In the site, click Site Actions.

  2. Click Site Permissions.

  3. Select the checkbox for the site collection Members group.

  4. On the ribbon, click Grant Permissions.

  5. Enter the Windows domain user or group accounts who should have permission to add or remove documents.

  6. Click OK.

  7. Select the checkbox for the site collection Visitors group.

  8. On the ribbon, click Grant Permissions.

  9. Enter the Windows domain user or group accounts who should have permission to view documents. As before, do not use e-mail addresses or distribution group if the application is configured for classic authentication.

  10. Click OK.

Install ADO.NET Data Services

ADO.NET Data Services is required for a data feed export of SharePoint lists. SharePoint 2010 does not include this component in the PrerequisiteInstaller program, so you must install it manually. For more information on how to install ADO.NET Data Services, see Install ADO.NET Data Services to support data feed exports of SharePoint lists.

Install Data Providers Used for Data Refresh

Server-side data refresh allows users to re-import updated data to their workbooks in unattended mode. In order for data refresh to succeed, the server must have the same data provider that was used to originally import the data. In addition, the user account under which data refresh runs often requires read permissions on the external data sources. Be sure to check the requirements for enabling and configuring data refresh to ensure a successful outcome. For more information, see Enable and Configure PowerPivot Data Refresh.

PowerPivot Gallery is a library that includes preview and presentation options for viewing PowerPivot workbooks on a SharePoint site. Using PowerPivot Gallery to publish and view PowerPivot workbooks is recommended for its preview capability. In addition, if you also deployed Reporting Services to the same SharePoint server, a PowerPivot Gallery provides ease of use in creating reports. You can launch Report Builder from within PowerPivot Gallery to base a new report on a published PowerPivot workbook. For more information about creating and using the library, see How to: Create and Customize PowerPivot Gallery and How to: Use PowerPivot Gallery.

Installing PowerPivot for Excel and building a PowerPivot workbook

After you have the server components installed in a farm, you can create your first Excel 2010 workbook that uses embedded PowerPivot data, and then publish it to a SharePoint library in a Web application. Before you can build Excel workbooks that include PowerPivot data, you must start with an installation of Excel 2010, followed by the PowerPivot for Excel add-in that extends Excel to support PowerPivot data import and enrichment. For instructions on how to install PowerPivot for Excel and create PowerPivot data, see Install PowerPivot for Excel (SQL Server Books Online) and Roadmap to Creating PowerPivot Workbooks in Excel.

Adding servers or applications over time

When you deploy the PowerPivot solution, feature integration is activated at the site collection level for all site collections in the web application. As you create new Web applications over time, you must deploy the powerpivotwebapp solution to each one. For instructions, see Deploy PowerPivot Solutions.

Depending on how you configure the PowerPivot service application, the PowerPivot System Service will be added to the default connection group, making it available to all web applications that use default connections. However, if you configured your Web applications to use custom service application connection lists, you will need to add the PowerPivot service application to each SharePoint web application for which you want to enable PowerPivot data processing. For more information, see Connect a PowerPivot Service Application to a SharePoint Web Application.

Over time, if you determine that additional data storage and processing capability is needed, you can add a second PowerPivot for SharePoint server instance to the farm. The installation process is almost identical to the steps you followed to add the first server, except for requirements in how you specify instance names and service account information. For instructions, see Add PowerPivot Servers to a SharePoint Farm.

Tuning configuration settings

A PowerPivot service application is created using default properties and values. You can modify configuration settings for individual service applications to change the methodology by which requests are allocated, set server timeouts, change the thresholds for query response report events, or specify how long usage data is retained. For more information about configuration in Central Administration or about using PowerPivot features in SharePoint Web applications, see Configuration (PowerPivot for SharePoint) and Operations (PowerPivot for SharePoint).