Configure Power Pivot and Deploy Solutions (SharePoint 2013)

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

This topics describes the deployment and configuration of middle-tier enhancements to the Power Pivot features in SharePoint Server 2013 including Power Pivot Gallery, Schedule data refresh, Management Dashboard, and data providers. Run Power Pivot for SharePoint 2013 Configuration tool to complete the following:

• Deploy SharePoint solution files.

• Create a Power Pivot service application.

• Configure an Excel Services Application to use an SQL Server Analysis Services server in SharePoint mode. For information on backend services and installing a SQL Server Analysis Services server in SharePoint mode, see Install Analysis Services in Power Pivot Mode.

For information on installing the Power Pivot for SharePoint 2013 Configuration tool, see Install or Uninstall the Power Pivot for SharePoint Add-in (SharePoint 2013)

Run Power Pivot for SharePoint 2013 configuration

Note: The SQL Server 2017 setup wizard installs two different configuration tools for Microsoft SQL Server 2016 Power Pivot for SharePoint. They each support a different version of SharePoint.

Note: To complete the following steps, you must be a farm administrator. If you see an error message similar to the following:

• "The user is not a farm administrator. Please address the validation failures and try again."

Either login as the account that installed SharePoint or configure the setup account as the primary administrator of the SharePoint Central Administration Site.

1. On the Start menu, click All Programs, and then click Microsoft SQL Server 2017, click Configuration Tools, and then click Power Pivot For SharePoint 2013 Configuration. Toold is listed only when Power Pivot for SharePoint is installed on the local server.

2. Click Configure or Repair Power Pivot for SharePoint and then click OK.

3. The tool runs validation to verify the current state of Power Pivot and what steps are required to complete configuration. Expand the window to full size. You should see a button bar at the bottom of the window that includes Validate, Run, and Exit commands.

4. On the Parameters tab:

   1. Default Account UserName: Enter a domain user account for the default account. This account will be used to provision services, including the Power Pivot service application pool. Do not specify a built-in account such as Network Service or Local System. The tool blocks configurations that specify built-in accounts.

   2. Database Server: You can use SQL Server Database engine that is supported for the SharePoint farm.

   3. Passphrase: Enter a passphrase. If you are creating a new SharePoint farm, the passphrase is used whenever you add a server or application to the SharePoint farm. If the farm already exists, enter the passphrase that allows you to add a server application to the farm.

   4. Power Pivot Server for Excel Services: Type the name of an SQL Server Analysis Services SharePoint mode server. In a single-server deployment, it is the same as the database server. [ServerName]\powerpivot

   5. Click Create Site Collection in the left window. Note Site URL so you can reference it in later steps. If the SharePoint server is not already configured, then the configuration wizard defaults the web application, and site collection URLs to the root of http://[ServerName]. To modify the defaults review the following pages in the left window: Create Default Web application and Deploy Web Application Solution

5. Optionally, review the remaining input values used to complete each action. Click each action in the left window to see and review the details of the action.

6. Optionally, remove any actions that you do not want to process at this time. For example, if you want to configure Secure Store Service later, click Configure Secure Store Service, and then clear the checkbox Include this action in the task list.

7. Click Validate to check whether the tool has sufficient information to process the actions in the list. If you see validation errors, click the warnings in the left pane to see details of the validation error. Correct any validation errors and then click Validate again.

8. Click Run to process all of the actions in the task list. Note that Run becomes available after you validate the actions. If Run is not enabled, click Validate first.

Verify Power Pivot Configuration

Services:

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

2. Verify that SQL Server Analysis Services and SQL Server Power Pivot System Service are started.

Farm Feature:

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

2. Verify that Power Pivot Integration Feature is Active.

Site Collection Feature:

1. Browse to your site URL that was created by the Configuration tool.

   Click Settings, and then click Site Settings.

   Click Site Collection Features.

2. Verify that Power Pivot Feature Integration for Site Collections is Active.

Power Pivot Service Application:

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

2. Verify the service application status is started. The default name is Default Power Pivot Service Application.

   Click the name of the services application to open the Power Pivot Management Dashboard for the service application opens. On first use, the dashboard takes several minutes to load.

For more information, see Verify a Power Pivot for SharePoint Installation.

Troubleshoot Issues

To assist in troubleshooting issues, it is a good idea to verify the diagnostic logging is enabled.

1. In SharePoint Central Administration, click Monitoring and then click Configure usage and health data collection.

2. Verify Enable usage data collection is selected.

3. Verify the following events are selected:

   • Definition of usage fields for Education telemetry

   • Power Pivot Connects

   • Power Pivot Load Data Usage

   • Power Pivot Query Usage

   • Power Pivot Unload Data Usage

4. Verify Enable health data collection is selected.

5. Click OK.

For more information on trouble shooting data refresh, see Troubleshooting Power Pivot Data Refresh (https://social.technet.microsoft.com/wiki/contents/articles/3870.powerpivot-troubleshooting-data-refresh.aspx).

For more information on the configuration tool, see Power Pivot Configuration Tools.