Power Pivot Configuration Tools
APPLIES TO: SQL Server Analysis Services Azure Analysis Services
Configure, repair, or remove a SQL Server 2017 Power Pivot for SharePoint with the Power Pivot Configuration Tools.
The SQL Server 2017 Setup Wizard does not install Power Pivot configuration tools for SharePoint. The configuration tools will be available for download in a future release of SQL Server 2017.
This topic describes the general use of the Power Pivot tools and the differences between them.
Applies to: SharePoint 2013
In this Topic:
Requirements for Using the Configuration Tools
You must be a farm administrator.
You must be a server administrator on the Analysis Services instance (SharePoint 2010 only).
You must be db_owner on the farm’s configuration database.
There are no TCP/IP port requirements for using the configuration tools and therefore you should not have to configure your firewall to accommodate the configuration tools. The configuration tool expects the web applications and shared services are available as part of the SharePoint platform. You may need to configure your firewall for the Analysis Services server. For more information, see Configure the Windows Firewall to Allow Analysis Services Access.
Two Versions of the Configuration Tool
The SQL Server 2017 Setup Wizard installs the Power Pivot Configuration Tool for SharePoint 2010 as well as a Power Pivot Configuration Tool for SharePoint 2013.
The tools can only be used with a SQL Server 2017 or SQL Server 2012 SP1 (11.0.3x) instance of Power Pivot for SharePoint. Do not use them with SQL Server 2008 R2 installations.
|Name||Supported version of SharePoint||Detailed Configuration|
|Power Pivot for SharePoint 2013 Configuration||SharePoint 2013||Configure or Repair Power Pivot for SharePoint 2013 (Power Pivot Configuration Tool)|
|Power Pivot Configuration Tool||SharePoint 2010 with SharePoint 2010||Configure or Repair Power Pivot for SharePoint 2010 (Power Pivot Configuration Tool)|
How the Two Configuration Tools are Different
The two versions of the Configuration tool are similar but there are differences in the configuration steps the two tools run. The differences are due to changes between SharePoint 2010 and SharePoint 2013 as well as the architecture differences between the SQL Server 2012 SP1 version of Power Pivot for SharePoint and the previous versions of Power Pivot for SharePoint.
The following table describes new and changed features in the Power Pivot for SharePoint 2013 Configuration tool. The table also describes features in the Power Pivot Configuration Tool that are not in the Power Pivot for SharePoint 2013 Configuration tool. The rows in the table are in the same order as the tabs in the configuration tools.
|Power Pivot for SharePoint 2013 Configuration||Power Pivot Configuration Tool|
|The main page has a new option for Power Pivot Server for Excel Services. The option supports the new architecture with Analysis Services running outside of the SharePoint farm. You configure Excel Services to use one or more Analysis Services servers that are running in SharePoint mode.
|The 2010 tool includes the page Register SQL Server Analysis Services ( Power Pivot) on Local Server to configure a local instance of Analysis Services. This page is not part of the 2013 tool because there is no local instance of Analysis Services.
|The page Create Power Pivot Service Application has an extra option of Upgrade Workbooks to enable data refresh. This option is not available in the 2013 tool.
|The 2013 tool has a new page Configure Power Pivot Servers. This page supports the new architecture of Analysis Services running outside the SharePoint farm. By default, the server name that was typed on the main page in the text box Power Pivot Server for Excel Services, is also be listed on the Configure Power Pivot Servers.
|The 2013 tool has a new page Register Power Pivot addin as Excel Services Usage Tracker. SharePoint 2010 Excel Services does not track usage data for Power Pivot.|
|The 2010 tool includes the page Add MSOLAP.5 as a trusted Provider to register MSOLAP so that Excel Services in SharePoint 2010 can load Power Pivot models. This page is not part of the 2013 tool. SharePoint 2013 Excel Services does not use the MSOLAP provider to load models.|
Overview of Using a Power Pivot Configuration Tool
When you start one of the Power Pivot Configuration Tools, the tool evaluates the existing installation to determine which operations are applicable. On a new installation, only the configuration task is available. After the server is configured, the remove task appears. If you started with a SQL Server 2008 R2 instance, upgrade will also be enabled in the list of available tasks.
If you are not familiar with Central Administration or Windows PowerShell, you can run the configuration tool as an alternative to complete a Power Pivot for SharePoint installation.
Additionally, the tool can detect whether the farm is configured or required features are missing. If the SharePoint program files are installed but the farm is not configured, the tool provides actions for configuring both the farm and the Power Pivot for SharePoint installation.
You can review the Script tab to learn and understand how to configure Power Pivot and SharePoint using Windows PowerShell. For more information, see the following:
The tool does not configure Reporting Services. If you are adding Reporting Services to your SharePoint environment, you need to install and configure Reporting Services separately. For more information, see the following:
Start One of the Power Pivot Configuration Tools
On the Start screen, type powerpivot
On the Start screen, type powerpivot or on the Start menu, click All Programs, click Microsoft SQL Server 2017, click Configuration Tools, and then click one of the following:
Power Pivot Configuration Tool.
Power Pivot for SharePoint 2013 Configuration.
Note: The tools are available only when Power Pivot for SharePoint is installed on the local server.
At startup, the configuration tools check the status of your installation and provide tasks that are valid for your installation.
Depending on the current state of your installation, one or more of the following tasks can be performed:
Click Configure or Repair Power Pivot for SharePoint to complete post-installation tasks, or to repair an installation.
Click Remove Features, Services, Applications and Solutions to remove features and solutions from the farm.
Click Upgrade Features, Services, Applications and Solutions to upgrade features and solutions that were installed using a previous version of Power Pivot for SharePoint.
For example, the image shows the startup page of the Power Pivot Configuration for SharePoint 2013 tool.
Each task is composed of individual actions that address some aspect of server configuration. For example, the configuration task includes actions for deploying solutions, creating a Power Pivot service application, activating features, and configuring data refresh. The list of actions will vary based on the current state of your installation. If an action is not needed, the tool excludes it from the task list.
When you click Run , the tool processes all actions in batch mode. Although each action appears as a separate item in the task list, all actions that are included in the task process together. Only actions that pass a validation check are processed. You might need to add or change some of the input values to pass the validation check.
Upgrade Power Pivot for SharePoint Describes the workflow that upgrades an existing installation that is already in a farm.
Uninstall Power Pivot for SharePoint Describes the workflow that removes Power Pivot for SharePoint services, solutions, and application pages from a farm.