Create a trusted location for Power Pivot sites in Central Administration
Applies To: SQL Server 2016
Excel Services lets you specify which locations are valid repositories for workbooks that you open on a SharePoint server. These locations are called 'trusted locations', and you can use different configuration settings for each trusted location you create. For a deployment of Power Pivot for SharePoint, you might consider creating a trusted location for sites that contain Power Pivot workbooks so that you can apply the settings that work best for Power Pivot data access, while preserving default settings for the rest of the farm.
This topic contains the following sections:
You must be a farm or service administrator to designate a URL as a trusted location.
You must know the URL address of the SharePoint site that contains the Power Pivot Gallery or other library that stores your workbooks. To get the address, open the site that contains the library, right-click Power Pivot Gallery, select Properties, and then copy the first part of the Address (URL) that contains the server name and site path.
An initial installation of Excel Services specifies 'http://' as its trusted location, which means that workbooks from any site in the farm can be opened on the server. If you require tighter control over which locations are considered trustworthy, you can create new trusted locations that map to specific sites in your farm, and then vary the settings and permissions for each one.
Creating a new trusted location for sites that host Power Pivot workbooks is especially useful if you want to preserve default values for the rest of the farm, while applying different settings that work best for Power Pivot data access. For example, a trusted location that is optimized for Power Pivot workbooks could have a maximum workbook size of 50 MB, while the rest of the farm uses the default value of 10MB.
Creating a trusted location is recommended if you are using Power Pivot Gallery libraries to preview published workbooks and you encounter data refresh warnings instead of the preview image you expect. Power Pivot Gallery renders thumbnail images of reports and workbooks using data and presentation information within the document. If Warn on Data Refresh is enabled for a trusted location, Power Pivot Gallery might not have sufficient permissions to perform the refresh, causing an error to appear instead of the thumbnail image. Adding a site that contains Power Pivot Gallery as a new trusted location can eliminate this problem.
In Central Administration, in Application Management, click Manage service applications.
Click the Excel Services Service Application.
Click Trusted File Locations.
Click Add Trusted File Location.
Enter the URL of a site that contains a Power Pivot Gallery library.
In Location Type, select Microsoft SharePoint Foundation.
UNC and HTTP location types are not supported for Power Pivot data access.
Accept all of the default settings for properties in Session Management, Workbook Properties, and Calculation Behavior.
In Workbook Properties, set Maximum Workbook Size to 50. This aligns the upper limit for workbook file size to the upper limit for file uploads to the parent web application. If your workbooks are larger than 50 megabytes, you must further increase the file size limit. For more information, see Configure Maximum File Upload Size (Power Pivot for SharePoint).
In External Data, verify that Allow External Data is set to Trusted data connection libraries and embedded. This setting is required for Power Pivot data access in a workbook.
Also in External Data, for Warn on Refresh, clear the checkbox for Refresh warning enabled. Clearing the checkbox allows Power Pivot Gallery to bypass routine warning messages and show preview images of a workbook instead.