Configure Power Pivot and Deploy Solutions (SharePoint 2016)

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 2016 including Power Pivot Gallery, Schedule data refresh, Management Dashboard, and data providers. Run Power Pivot for SharePoint 2016 Configuration tool to complete the following:

• Deploy SharePoint solution files.

• Create a Power Pivot service application.

• For information on backend services and installing a SQL Server Analysis Services server in Power Pivot mode, see Install Analysis Services in Power Pivot Mode.

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

Run Power Pivot for SharePoint 2016 configuration

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, select All Programs, and then select Microsoft SQL Server 2017, select Configuration Tools, and then select Power Pivot For SharePoint 2016 Configuration. Tools is listed only when Power Pivot for SharePoint is installed on the local server.

2. Select Configure or Repair Power Pivot for SharePoint and then select 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. 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, select Configure Secure Store Service, and then clear the checkbox Include this action in the task list.

7. select 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 select Validate again.

8. Select 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, select Validate first.

Verify Power Pivot Configuration

Services:

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

2. Verify that SQL Server Power Pivot System Service is started.

Farm Feature:

1. In Central Administration, in System Settings, select 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.

   Select Settings, and then click Site Settings.

   Select 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, select Manage service applications.

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

   Select 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 select 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 Connections

   • Power Pivot Load Data Usage

   • Power Pivot Query Usage

   • Power Pivot Unload Data Usage

4. Verify Enable health data collection is selected.

5. Select 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.