• Article

Install PowerPivot for SharePoint on a New SharePoint Server

This topic explains how to set up a new PowerPivot server on a clean Windows Server 2008 computer. It guides you through steps for installing both SharePoint 2010 and PowerPivot for SharePoint. When you finish, you should have an operational server that is available for immediate use.

Note

These instructions are intended for workgroup or small departmental server deployments, or for those who want to install PowerPivot for SharePoint in the fewest possible steps. You should use alternate instructions if you already have SharePoint installed. For more information, see Install PowerPivot for SharePoint on an Existing SharePoint Server.

This topic contains the following sections:

Prerequisites

Overview

Step 1: Install SharePoint Server 2010 (Unconfigured)

Step 2: Install and Configure SQL Server Components

Step 3: Verify Installation

Troubleshoot Installation

Post-installation steps

Prerequisites

Verify your computer meets the hardware and software requirements for SharePoint 2010 and PowerPivot for SharePoint:

You must have installation media for the Enterprise, Evaluation, Developer, or Data Center editions of SQL Server 2008 R2.

You must have installation media for SharePoint 2010 Enterprise edition.

Port 80 must be available. If port 80 is used by another application, you must install PowerPivot for SharePoint using the Existing Farm option. For more information, see Install PowerPivot for SharePoint on an Existing SharePoint Server.

The computer must be joined to a domain.

You must have a domain user account to provision the farm. You can reconfigure services to run under different accounts later.

You must have one or more domain user accounts to provision SQL Server services. You will configure both Analysis Services and the Database Engine in this installation.

You must be a local administrator to install SQL Server and SharePoint 2010. Refer to SharePoint 2010 product documentation Deploy a single server with SQL Server to learn more about account and permission requirements.

Overview

SQL Server Setup includes a New Server installation option for installing PowerPivot for SharePoint so that it is immediately available when Setup is finished.

To use this option, you must start with a clean server that has SharePoint 2010 installed but not yet configured. Step 1 in this topic guides you through the steps for installing SharePoint in an un-configured state. Next, in step 2, you will run SQL Server Setup and select the New Server option to do the following:

  • Install and configure SQL Server Database Engine instance as the database server for SharePoint.

  • Configure the SharePoint server that you installed in step 1 and enable features that are required for PowerPivot for SharePoint.

  • Install and configure PowerPivot for SharePoint.

When you use the New Server installation option, all server products use default values. For a description of which features and properties are configured, see Default Configuration for PowerPivot for SharePoint.

Step 1: Install SharePoint Server 2010 (Unconfigured)

In this step, you install SharePoint prerequisites and run server setup.

If you have installed a SharePoint Server before, you know that it is common to configure the server immediately after you install it. However, because your objective is to use the New Server installation option in SQL Server 2008 R2 Setup, you must skip the SharePoint server configuration in step 1 to allow SQL Server Setup to run the configuration program for you in step 2. Allowing SQL Server Setup to configure SharePoint lets you use a Database Engine instance from the SQL Server 2008 R2 release as your database server. More importantly, it allows SQL Server Setup to configure SharePoint to use settings that are optimal for PowerPivot query processing and server management.

  1. Insert the installation media or open a folder that contains the setup files for SharePoint 2010.

  2. Run the Prerequisite Installer to add required operating system roles, features, and other software required for your installation.

  3. Run SharePoint Server Setup to install the server software.

  4. Accept the Microsoft Software License Terms of agreement, and then click Continue.

  5. Click Server Farm. You must select this option. Standalone deployments are not supported. By selecting the Server Farm option, you add the Claims infrastructure that supports administrative and data connections.

    Install Type dialog box in SharePoint Setup

  6. In the Server Type page, select Complete. By selecting this option, you add all of the SharePoint features required for both application server and web front end roles on the same server.

    Server Type dialog box in SharePoint Setup

  7. Click Install Now.

    After Setup is finished, you will be prompted to run the SharePoint Products and Technologies Configuration Wizard.

    Important

    Do not configure the server at this time. If you run the configuration wizard now, you will get an error because the database server is not yet installed. Installing the database server, followed by server configuration, is performed in a subsequent step.

  8. Clear the checkbox next to Run the SharePoint Products and Technologies Configuration Wizard, and click Close.

    Install page prompting for more configuration

Step 2: Install and Configure SQL Server Components

In this step, you run SQL Server 2008 R2 Setup. Setup both installs and configures PowerPivot for SharePoint and a Database Engine instance. This instance will be used as the database server for the SharePoint server.

Setup also configures the SharePoint server software that you installed in step 1. To do this, SQL Server Setup runs the SharePoint Products and Technologies Configuration program (PSConfig.exe) in the background to create the configuration database and set up PowerPivot features. SharePoint will be initialized with values that you specify in the SQL Server Installation Wizard.

  1. Insert the installation media or open a folder that contains the setup files for SQL Server 2008 R2.

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

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

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

  8. In the Setup Support Files page, click Install.

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

    Note

    You can ignore the warning for opening ports in Windows Firewall. You only need to open ports if outside applications will be connecting to the either the Database Engine, a native mode Analysis Services instance, or Reporting Services through remote connections. For more information, see Configuring the Windows Firewall to Allow SQL Server Access.

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

  11. In the same page, in Add PowerPivot for SharePoint to:, select New Server.

    Feature role page in Setup wizard

  12. Click Next.

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

  14. In Installation Rules, click Next.

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

  16. In New SharePoint Farm Configuration, enter a domain user account for the server farm account. This account will be used to both run the Central Administration service and to access the configuration database. It will also be used to provision essential services, including the PowerPivot service application pool. Do not specify a built-in account such as Network Service or Local System. Setup will block installation for built-in accounts.

  17. Specify a pass phrase that other farm administrators will provide when adding a new application server or Web front end server to the farm. Follow the same rules and conventions as you would to create any strong password.

  18. Specify a port number to connect to the Central Administration web application or use the randomly generated number that is provided. Setup checks that the number is available before offering it as an option.

  19. Click Next.

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

  21. In Server Configuration, enter account information for SQL Server Agent, SQL Server Database Engine, and SQL Server Analysis Services.

    For SQL Server Database Engine and SQL Server Agent, you can configure the services to run under domain user accounts or under a built-in account such as NT AUTHORITY\SYSTEM.

    For SQL Server Analysis Services, you must specify a domain user account. Do not specify a built-in account. Domain accounts are required for managing the Analysis Services service account as a managed account in SharePoint Central administration.

    Never use your own domain user account to provision any service. Doing so grants the server the same permissions that you have to the resources in your network. If the server is compromised by a malicious user, that user will be logged in under your domain credentials, with the ability to download or use the same data and applications that you do.

    For more information about account recommendations, see Plan a PowerPivot Deployment in a SharePoint Farm.

  22. Click Next.

  23. In Database Engine Configuration, click Add Current User to grant your user account administrator permissions on the Database Engine instance. Setup will use these permissions to create the configuration database for the farm. Click Next.

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

  25. In the same page, add the Windows user account of any person who will administer the Analysis Services instance. For example, any user who wants to connect to the 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.

  26. Click Next.

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

  28. Click Install.

After Setup is finished, you will be a farm administrator in the SharePoint farm, a system administrator for the Database Engine and Analysis Services, and a site collection administrator for the default SharePoint Web application. To improve security, consider delegating administrative tasks to other users. The farm administrator account in particular should not be a member of the local Administrators user group. For instructions on how to change administrators for your deployment or delegate administration to others, see SharePoint product documentation on the SharePoint 2010 Tech Center site.

Step 3: Verify Installation

A PowerPivot for SharePoint instance that you install in a SharePoint farm is administered through SharePoint Central Administration. At a minimum, you can check pages in Central Administration and on SharePoint sites to verify that PowerPivot server components and 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 Central Administration, do the following:

  1. On the Start menu, click All Programs, open Microsoft SharePoint 2010 Products, and click SharePoint 2010 Central Administration.

  2. Enter your user name and password, and then click OK.

    Optionally, you can modify browser settings to avoid having to enter a user name and password each time you open Central Administration. To add Central Administration as a trusted site, do the following.

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

    2. On the Security tab, in the Select a zone to view or change security settings section, click Trusted Sites, and then click Sites.

    3. Clear the Require server verification (https:) for all sites in this zone checkbox.

    4. In Add this Web site to the zone, type the URL to your site, and then click Add.

    5. Click Close, and then click OK.

      Note

      SharePoint installation documentation includes additional instructions for working around proxy server errors and for disabling Internet Explorer Enhanced Security Configuration so that you can download and install updates. For more information, see the Perform additional tasks section in Deploy a single server with SQL Server on the Microsoft web site.

  3. In Central Administration, in System Settings, click Manage farm features.

  4. Verify that PowerPivot Integration Feature is Active.

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

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

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

  8. Click Default PowerPivot Service Application to open PowerPivot Management Dashboard for this application. On first use, the dashboard takes several minutes to load.

    Alternatively, click the empty space next to Default PowerPivot Service Application to select the row, and click Properties to view the configuration settings for this service application. You can modify both configuration settings and application properties to change your server configuration. For more information about these settings, see Create and Configure a PowerPivot Service Application.

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. This verifies that the Analysis Services server is available.

  8. In Object Explorer, you can 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

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 complete server installation, there are several post-installation tasks you should perform to improve the security or usability of the server.

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 3.5 SP1

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 in Data Refresh and Check User Permissions

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.

Change Application Pool and Service Identities in SharePoint

SQL Server Setup provisions farm features, applications, and services to run under a single account. This simplifies installation, but it does not result in a deployment that meets the security requirements of a SharePoint farm. To create a more robust deployment, change the application pools and service identities to run under different accounts after setup is complete.

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

  2. Select, but do not click on, Default PowerPivot Service Application. You can select it by clicking the empty space next to the link text.

  3. In the Service Applications ribbon, click Properties.

  4. Select Create a new application pool.

  5. Enter a name for the application pool, such as Service Application Pool - PowerPivotService.

  6. Choose an existing configured account or register a new managed account to set the identity of the application pool.

  7. Click OK.

For more information about how to change application pools and service identities for PowerPivot server components, see Change Service Accounts and Passwords (PowerPivot for SharePoint).

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

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