Deployment Checklist: Multi-Server Installation of Power Pivot for SharePoint 2010
This checklist guides you through the steps for adding Power Pivot for SharePoint to a three-tier SharePoint 2010 farm that you build from the ground up. A three-tier farm includes database, application, and web tiers. Adding Power Pivot to this topology requires that you run SQL Server Setup to install Power Pivot for SharePoint on the application tier. Power Pivot program files are added to the web tier, but only as a post-installation task when you the deploy web application solution. Although there are deployment steps, there is no separate installation step on either the web tier or data tier that you need to perform. The only installation step that you need to perform is installing Power Pivot for SharePoint on the application servers.
Applies to: SharePoint 2010
In this topic:
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.
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.
In Internet Explorer, on the Tools menu, click Internet Options.
On the Connections tab, in the Local Area Network (LAN) settings area, click LAN Settings.
In the Automatic configuration area, clear the Automatically detect settings check box.
In the Proxy Server area, select the Use a proxy server for your LAN check box.
Type the address of the proxy server in the Address box.
Type the port number of the proxy server in the Port box.
Select the Bypass proxy server for local addresses check box.
Click OK to close the Local Area Network (LAN) Settings dialog box.
Click OK to close the Internet Options dialog box.
This topic assumes your farm topology is based on 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 Power Pivot for SharePoint.
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.
On the computer that you are using for the database server, run SQL Server Setup to install SQL Server Database Engine (see Install SQL Server 2015 from the Installation Wizard (Setup)).
When selecting features to install, choose the following:
Database Engine Services
Client Tools Connectivity
Management Tools - Complete (Basic will be included automatically)
After the Database Engine is installed, enable TCP/IP for remote connections and restart the service:
Start SQL Server Configuration Manager.
Open SQL Server Network Configuration.
Select Protocols for MSSQLSERVER.
Right-click TCP/IP and select Enable.
Click SQL Server Services.
Right-click SQL Server (MSSQLSERVER), and click Restart.
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 Configure the Windows Firewall to Allow SQL Server Access.
In Windows Control Panel, in Administrative Tools, click Windows Firewall with Advanced Security.
Click Inbound Rules.
Click New Rule.
In Rule Type, click Custom.
In Program, in the Services section, click Customize.
Click Apply to this service.
Select SQL Server (MSSQLSERVER) if you installed SQL Server as the default instance, and then click OK.
In Protocol and Ports, accept the default settings and click Next.
In Scope, accept the default settings and click Next.
In Action, accept the default settings and click Next.
In Profile, clear the checkboxes for Private and Public, and then click Next.
In Name, type a descriptive name for the inbound rule (for example, SQL Server).
On the computers you are using as SharePoint servers, run the SharePoint Prerequisite Installer program on each, followed by SharePoint Server Setup.
Use the following instructions in SharePoint 2010 documentation to install and configure a SharePoint 2010 farm that includes two web servers and an application server:
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 services and features:
Excel Services, Search Service, and Secure Store Service
A web application and site collection
Usage and health data collection
Run SQL Server Setup to add Power Pivot for SharePoint to a SharePoint farm. If the farm consists of multiple SharePoint servers, you must run SQL Server Setup on an application server that is already joined to the farm.
Always install Power Pivot for SharePoint on an application server. Although the web front-end servers will also run Power Pivot for SharePoint server components, components that run on the web front-end are installed during the Power Pivot for SharePoint configuration step, when you deploy solutions in the farm. For more information about setup, see Install Power Pivot for SharePoint 2010.
If your deployment topology calls for two Power Pivot for SharePoint instances, run SQL Server Setup on each application server. You can only have one instance of Power Pivot for SharePoint on a single computer. If you require multiple instances, you must use additional servers. For more information about adding multiple Power Pivot for SharePoint servers to the same farm, see Deployment Checklist: Scale-out by adding Power Pivot Servers to a SharePoint 2010 farm.
A farm topology that includes a web-front end or application server running the following applications, without an installation of Power Pivot for SharePoint on the same computer, will require additional software to support Power Pivot data access and features:
Excel Services or PerformancePoint Services
Central Administration running as a standalone application on its own server
Both Excel Services and PerformancePoint Services require a newer version of the Analysis Services OLE DB provider to support Power Pivot data access. If you run either application on a computer that does not have the most recent OLE DB provider, you must install the provider manually. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers
Similarly, a computer that has just Central Administration, without Power Pivot for SharePoint on the same computer, will require the ADOMD.NET client library. This library is used by the Power Pivot Management Dashboard to access internal data it uses to populate the dashboard. For more information, see Install ADOMD.NET on Web Front-End Servers Running Central Administration.
Use the Power Pivot Configuration Tool to configure Power Pivot for SharePoint. The tool will scan the farm’s existing configuration and provide options for installing or activating the SharePoint features that are needed by Power Pivot for SharePoint. During this step, the Claims to Windows Token Service will be started. Additionally, if other required SharePoint features are not yet enabled, the configuration tool will add them to the list and include actions for enabling the feature.
For more information, see Configure or Repair Power Pivot for SharePoint 2010 (Power Pivot Configuration Tool).
To ensure that requests for Power Pivot 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.
In Central Administration, in Application Management, click Configure alternate access mappings.
In Alternate Access Mapping Collection, select your web application.
Click Add Internal URL.
Specify the URL of the first web front-end server.
Repeat the previous steps to add the URL of the second web front-end server.
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 Power Pivot Gallery and Data Feed libraries.
The Power Pivot Configuration Tool will activate feature integration for the site collection that you specify. You can run the tool multiple times to select additional site collections. Alternatively, site administrators can configure feature activation from within SharePoint. For more information, see Activate Power Pivot Feature Integration for Site Collections in Central Administration.
Power Pivot query processing in the farm occurs when a user or application opens an Excel workbook that contains Power Pivot data. At a minimum, you can check pages on SharePoint sites to verify that Power Pivot features are available. However, to fully verify an installation, you must have a Power Pivot workbook that you can publish to SharePoint and access from a library. For testing purposes, you can publish a sample workbook that already contains Power Pivot data and use it to confirm that SharePoint integration is correctly configured.
To verify Power Pivot integration with a SharePoint site, do the following:
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.
Verify that Power Pivot data access and processing features are available in the application. You can do this by verifying the presence of Power Pivot-provided library templates:
On Site Actions, click More Options...
In Libraries, you should see Data Feed Library and Power Pivot Gallery. These library templates are provided by the Power Pivot feature and will be visible in the Libraries list if the feature is integrated correctly.
To verify Power Pivot data access on the server, do the following:
Download the Picnic data sample that accompanies a Reporting Services tutorial. You will use the sample workbook in this download to verify Power Pivot data access. Extract the files.
Upload a Power Pivot workbook to Power Pivot Gallery or any SharePoint library.
Click the document to open it from the library.
Click a slicer or filter the data to start a Power Pivot query. The server will load Power Pivot 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.
Start SQL Server Management Studio from the Microsoft SQL Server 2016 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.
In Server Type, select Analysis Services.
In Server Name, enter <server-name>\powerpivot, where <server-name> is the name of the computer that has the Power Pivot for SharePoint installation.
In Object Explorer, click Databases to view the list of Power Pivot data files that are loaded.
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.
After you verify the installation, finish service configuration by creating a Power Pivot Gallery or tuning individual configuration settings. To make full use of the server components you just installed, you can download Power Pivot for Excel to create and then publish your first Power Pivot workbook.
Set Upper Limits on Disk Space Usage
You can set a maximum limit on how much disk space is used for Power Pivot data files that are cached to disk. The default is to use all available disk space. For instructions on how to limit disk space usage, see Configure Disk Space Usage (Power Pivot for SharePoint).
Increase File Maximum Upload Size for SharePoint Web Applications
Because Power Pivot workbooks can be large, you might want to increase the maximum file upload 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 (Power Pivot for SharePoint).
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.
In the site, click Site Actions.
Click Site Permissions.
Select the checkbox for the site collection Members group.
On the ribbon, click Grant Permissions.
Enter the Windows domain user or group accounts who should have permission to add or remove documents.
Select the checkbox for the site collection Visitors group.
On the ribbon, click Grant Permissions.
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.
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 PowerPivot Data Refresh with SharePoint 2010.
Create a Power Pivot Gallery
Power Pivot Gallery is a library that includes preview and presentation options for viewing Power Pivot workbooks on a SharePoint site. Using Power Pivot Gallery to publish and view Power Pivot workbooks is recommended for its preview capability. In addition, if you also deployed Reporting Services to the same SharePoint server, a Power Pivot Gallery provides ease of use in creating reports. You can launch Report Builder from within Power Pivot Gallery to base a new report on a published Power Pivot workbook. For more information about creating and using the library, see Create and Customize Power Pivot Gallery and Use Power Pivot Gallery.
Tune Configuration Settings
A Power Pivot 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 Power Pivot features in SharePoint Web applications, see Power Pivot Server Administration and Configuration in Central Administration.