Verify a Power Pivot for SharePoint Installation

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

A Power Pivot for SharePoint instance that you install in a SharePoint farm is administered through SharePoint Central Administration. At a minimum, you can check pages in Central Administration and on SharePoint sites to verify that Power Pivot server components and 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.

Verify Central Administration Integration

To verify Power Pivot integration with Central Administration, do the following:

1. On the Start menu, click All Programs, open Microsoft SharePoint 2016 Products, or Microsoft SharePoint 2013 Products, and click SharePoint 2016 Central Administration, or SharePoint 2013 Central Administration.

2. Enter your user name and password, and then click OK.

   Optionally, you can modify browser settings to avoid having to enter a user name and password each time you open Central Administration. To add Central Administration as a trusted site, do the following.

   1. In Internet Explorer, on the Tools menu, click Internet options.

   2. On the Security tab, in the Select a zone to view or change security settings section, click Trusted Sites, and then click Sites.

   3. Clear the Require server verification (https:) for all sites in this zone checkbox.

   4. In Add this Web site to the zone, type the URL to your site, and then click Add.

   5. Click Close, and then click OK.

      Note

      SharePoint installation documentation includes additional instructions for working around proxy server errors and for disabling Internet Explorer Enhanced Security Configuration so that you can download and install updates. For more information, see the Perform additional tasks section in Deploy a single server with SQL Server on the Microsoft web site.

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

4. Verify that Power Pivot Integration Feature is Active.

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

6. Verify that SQL Server Power Pivot System Service are started.

   In a multiple server SharePoint Farm, you may need to change the server you are viewing to validate that all of the servers you deployed Power Pivot to are running.

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

8. Click Default Power Pivot Service Application to open Power Pivot Management Dashboard for this application. On first use, the dashboard takes several minutes to load.

   Alternatively, click the empty space next to Default Power Pivot Service Application to select the row, and click Properties to view the configuration settings for this service application. You can modify both configuration settings and application properties to change your server configuration. For more information about these settings, see Create and Configure a Power Pivot Service Application in Central Administration.

Verify Integration at the Site Level

To verify Power Pivot integration with a SharePoint site, do the following:

1. 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.

2. 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:

   1. Select Site Contents.

   2. In the app list, 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.

Verify Data Access on the Server

To verify Power Pivot data access on the server, do the following:

1. 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.

2. Upload the Excel workbook (.xlsx) to Shared Documents. The workbook contains embedded Power Pivot data.

3. Click on the document to open it from the library.

4. Click on a slicer or filter at the top of the workbook. Month, color, and type are slicers in this workbook. Clicking a slicer starts a Power Pivot query and proves that your server is operational. The server will load Power Pivot data in the background and return the results.

5. Go back to the library. Select the down arrow to the right of the workbook, and then click Launch Power View. This step confirms that the Power View feature in Reporting Services is operational. If you did not install Reporting Services, skip this step.

   In the next step, you will connect to the server in Management Studio to verify the data is loaded and cached.

6. Start SQL Server Management Studio from the Microsoft SQL Server 2017 program group in the Start menu. If this tool is not installed on your server, you can skip ahead to the last step to confirm the presence of cached files.

7. In Server Type, select Analysis Services.

8. In Server Name, enter <server-name>\powerpivot, where <server-name> is the name of the computer that has the Power Pivot for SharePoint installation.

9. Click Connect. This verifies that the Analysis Services server is available.

10. In Object Explorer, you can click Databases to view the list of Power Pivot data files that are loaded.

11. 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 <drive>:\Program Files\Microsoft SQL Server\nnn\MSAS13.POWERPIVOT\OLAP\Backup\Sandboxes\Default Power Pivot Service Application folder. Each cached database is stored in its own folder, using a GUID-based naming convention to ensure a unique name.