Install PowerPivot for SharePoint

PowerPivot for SharePoint is a collection of middle-tier and backend services that provide PowerPivot data access in a SharePoint 2010 farm. If your organization uses the client application, PowerPivot for Excel, to create workbooks that contain analytical data, you must have PowerPivot for SharePoint to access that data in a server environment.

This topic contains the following sections:

Prerequisites

Step 1: Install PowerPivot for SharePoint

Step 2: Configure the Server

Step 3: Install Analysis Services OLE DB providers on Excel Services Application Servers

Step 4: Verify the installation

For instructions on how to install PowerPivot and Reporting Services on the same server, see Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint.

Prerequisites

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

SharePoint Server 2010 enterprise edition is required for PowerPivot for SharePoint. You can also use the evaluation enterprise edition.

SharePoint Server 2010 SP1 must be installed. Without it, you cannot configure the farm to use SQL Server 2012 features.

The computer must be joined to a domain.

You must have a domain user account to provision Analysis Services. In a PowerPivot for SharePoint installation, the Analysis Services service account must be a domain user account so that you can manage it from Central Administration.

The PowerPivot instance name must be available. You cannot have an existing PowerPivot named instance on the computer on which you are installing PowerPivot for SharePoint.

If you are installing PowerPivot for SharePoint on an existing farm, you must have one or more SharePoint web applications that are configured for classic mode authentication. PowerPivot data access will only work if the web application supports classic mode authentication. For more information about classic mode requirements, see PowerPivot Authentication and Authorization.

Review the following additional topics to understand system and version requirements:

• Hardware and Software Requirements (PowerPivot for SharePoint and Reporting Services in SharePoint Mode)

• Guidance for Using SQL Server BI Features in a SharePoint 2010 Farm

Step 1: Install PowerPivot for SharePoint

In this step, you run SQL Server Setup to install PowerPivot for SharePoint. In a subsequent step, you will configure the server as a post-installation task.

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

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

3. Click New SQL Server stand-alone installation or add features to an existing installation.

4. On the Setup Support Rules page, review any warnings and click OK.

5. On the Product Key page, specify the evaluation edition or enter a product key for a licensed copy of the enterprise edition.

   Click Next.

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

7. Update the setup files if you are prompted to do so.

8. On the Setup Support Rules page, setup identifies any problems that might prevent it from installing. Review the list to determine whether Setup detected potential problems on the system.

   

   Note

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

   Click Next. Wait while SQL Server Setup program files are installed on the server.

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

10. Optionally, you can add an instance of the Database Engine to your installation. You might do this if you are setting up a new farm and need a database server to run the farm’s configuration and content databases. If you add the Database Engine, it will be installed as a PowerPivot named instance. Whenever you need to specify a connection to this instance (for example, in the farm configuration wizard if you are using that wizard to configure the farm), enter the database name in this format: <servername>\PowerPivot.

    

11. Click Next.

12. On the Feature Selection page, 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.

13. On the Installation Rules page, click Next.

14. On the Instance Configuration page, 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. On the Disk Space Requirements page, verify you have sufficient disk capacity to install the feature and then click Next.

16. On the Server Configuration page, type desired account information.

    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.

    

    If you added the SQL Server Database Engine and SQL Server Agent, you can configure the services to run under domain user accounts or under the default virtual account.

    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.

17. Click Next.

18. If you are installing the Database Engine, the Database Engine Configuration page appears. In Database Engine Configuration, click Add Current User to grant your user account administrator permissions on the Database Engine instance. Click Next.

19. On the Analysis Services Configuration page, click Add Current User to grant your user account administrative permissions. You will need administrative permission to configure the server after Setup is finished.

20. On the same page, add the Windows user account of any person who also requires administrative permissions. 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.

21. Click Next.

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

23. Click Install.

Step 2: Configure the Server

Installation is not complete until the server is configured. In this release, server configuration is always performed as post-installation task, using one of the following approaches: PowerPivot Configuration Tool, Central Administration, or PowerShell. To continue, choose one of the following approaches:

• Configure or Repair PowerPivot for SharePoint 2010 (PowerPivot Configuration Tool)

• PowerPivot Configuration in Central Administration

• PowerPivot Configuration using Windows PowerShell

Connecting to the Database Engine Instance. When you installed PowerPivot for SharePoint, SQL Server Setup gave you the option of adding an instance of the Database Engine to your installation. You might have added a Database Engine instance to your installation if you are setting up a new farm and need a database server to run the farm’s configuration and content databases. If you added the Database Engine, it was installed as a PowerPivot named instance. Whenever you need to specify a connection to this instance (for example, in the farm configuration wizard if you are using that wizard to configure the farm), remember to enter the database name in this format: <servername>\PowerPivot.

Step 3: Install Analysis Services OLE DB providers on Excel Services Application Servers

Additional installation steps are required if you run Excel Calculation Services and PowerPivot on separate application servers. On the application servers running Excel Calculation Services, install the appropriate version of the Analysis Services OLE DB (MSOLAP) provider.

• The SQL Server 2012 version of MSOLAP is included in SQL Server Setup, therefore explicitly installing the 2012 version of MSOLAP is only required if your application server is not a PowerPivot application server.

  Note

  The Excel Calculation Services application server also needs an instance of the file Microsoft.AnalysisServices.Xmla.dll in the global assembly. To install the .dll on the application server, install SQL Server Management Studio. Select the “Management Tools – Complete” on the Feature Selection page of the SQL Server Setup wizard.

• If you want the application server to support older PowerPivot workbooks, you need to install the SQL Server 2008 R2 version of MSOLAP.

For more information about installing the provider, including verification steps, see Install the Analysis Services OLE DB Provider on SharePoint Servers

Step 4: Verify the Installation

In this last step, you will verify that both SharePoint 2010 and PowerPivot for SharePoint are fully functional. For instructions, see Verify a PowerPivot for SharePoint Installation.

See Also

Tasks

Deployment Checklist: Scale-out by adding PowerPivot Servers to a farm

Deployment Checklist: Multi-Server Installation of PowerPivot for SharePoint

Concepts

PowerPivot for SharePoint Installation (SharePoint 2010)

Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint