Install PowerPivot for SharePoint 2010

Article
06/13/2017

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 2010, to create workbooks that contain analytical data, you must have PowerPivot for SharePoint to access that data in a server environment. This topic walks you through the basic installation process and it links you additional topics to help you configure PowerPivot.


Applies to: SharePoint 2010

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 SP2 must be installed. Without it, you cannot configure the farm to use SQL Server 2014 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. You will type the account and credentials on the Server Configuration page as part of the steps in this document.
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.
The PowerPivot for SharePoint instance cannot be part of a SQL Server Failover Cluster. Use high availability features of the SharePoint Product. For example, Excel Services manages load balancing of PowerPivot for SharePoint servers. For more information, see Manage Excel Services data model settings (SharePoint Server 2013) (https://technet.microsoft.com/library/jj219780.aspx).
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:
- 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.

Insert the installation media or open a folder that contains the setup files for SQL Server and then double-click setup.exe.
Click Installation in the left navigation pane.
Click New SQL Server stand-alone installation or add features to an existing installation.
On the Product Key page, specify the evaluation edition or enter a product key for a licensed copy of the enterprise edition.

Click Next.
Accept the Microsoft Software License Terms of agreement and We appreciate it if you also turn on the customer experience and error reporting. Click Next.
Update the setup files if you are prompted to do so.
On the Install 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.
On the Setup Role page, select SQL Server PowerPivot for SharePoint.
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.
Click Next.
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.
On the Feature Rules page, click Next. The page may be skipped.
On the Instance Configuration page, a read-only instance name of 'PowerPivot' is displayed for informational purposes. This instance name of POWERPIVOT 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.
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.
Click Next.
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 Add to add additional accounts. Click Next.
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.
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.
Click Next.
Click Next on each of the remaining pages until you get to the Ready to Install page.
Click Install.

Tip

If you need to trouble shoot the SQL Server installation, see View and Read SQL Server Setup Log Files.

Step 2: Configure the Server

Important

SharePoint 2010 SP2 must be installed before you can configure PowerPivot for SharePoint or a SharePoint farm that uses a SQL Server 2014 database server. If you have not yet installed the service pack, do so now before you begin configuring 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:

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 2014 version of MSOLAP is included in SQL Server Setup, therefore explicitly installing the SQL Server 2014 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.

Share via