Enable Excel Services For Power Pivot Purpose

PowerPivot for SharePoint requires Excel Services to support PowerPivot data access in the farm. You can determine whether Excel Services is already enabled by confirming whether Excel Services Application appears in the list of service applications in Central Administration. If Excel Services is not listed, follow these steps to enable it now.

1. In Central Administration, in Application Management, click Manage service applications.
2. In the Service Applications ribbon, in Create, click New.
3. Select Excel Services Application.
4. In Create New Excel Services Application, specify a name (for example, Excel Services Application).
5. In Application Pool, select Create new application pool and give it a descriptive name (for example, Excel Services Application Pool).
6. In Configurable, select a Windows domain user account for this application pool identity.
7. Keep the default checkbox that adds the service application proxy to the default service connection list.
8. Click OK.
9. Click the Excel Services application you just created.
10. Click Trusted File Locations and on this page, select your trusted location. (Typically, this is listed as http:// in the Address column.) To ensure that both Excel Services and PowerPivot service have access to the workbook, you must include SharePoint as an Excel Services trusted location. PowerPivot System Service cannot access workbooks that are stored outside of a SharePoint farm.
11. In the Workbook Properties area, set Maximum Workbook Size to 50.
12. In External Data, set Allow External data to Trusted data connection libraries and embedded. This setting is required for PowerPivot data access in a workbook.
13. Clear the Warn on Data Refresh checkbox to allow preview images of individual worksheets in PowerPivot Gallery. If you choose to keep the warning and workbook settings specify refresh on open, you might get a single preview image of the warning instead of the pages in your workbook.
14. Click OK.

Enable Secure Store Service and Configure Data Refresh

---

PowerPivot for SharePoint requires Secure Store Service to store credentials and the unattended execution account for data refresh. You can determine whether Secure Store Service is already enabled by confirming whether it appears in the list of service applications.

Important
If Secure Store Service is enabled, you should still verify that a master key has been generated for it. For instructions, see Part 2: Generate the Master Key in the following procedure.

If Secure Store Service is not listed, follow these steps to enable it now. By enabling Secure Store, workbook authors and document owners can access a broader range of data source connection options when scheduling data refresh for their published workbooks.

Part 1: Enable Secure Store Service

1. In Central Administration, in Application Management, click Manage service applications.
2. In the Service Applications ribbon, in Create, click New.
3. Select Secure Store Service.
4. In the Create Secure Store Application page, enter a name for the application.
5. In Database, specify the SQL Server instance that will host the database for this service application. The default value is the SQL Server Database Engine instance that hosts the farm configuration databases.
6. In Database Name, enter the name of the service application database. The default value is Secure_Store_Service_DB_. The default name corresponds to the default name of the service application. If you entered a unique service application name, follow a similar naming convention for your database name so that you can manage them together.
7. In Database Authentication, the default is Windows Authentication. If you choose SQL Authentication, refer to the SharePoint administrator guide for guidance on how to use the authentication type in your farm.
8. In Application Pool, select Create new application pool. Specify a descriptive name that will help other server administrators identify how the application pool is used.
9. Select a security account for the application pool. Specify a managed account to use a domain user account.
10. Accept the remaining default values, and then click OK. The service application will appear alongside other managed services in the farm's service application list.

Part 2: Generate the Master Key

1. Click the Secure Store Service application from the list.
2. In the Service Applications ribbon, click Manage.
3. In Key Management, click Generate New Key.
4. Enter and then confirm a pass phrase. The pass phrase will be used to add additional secure store shared service applications.
5. Click OK.

Part 3: Configure the Unattended PowerPivot Data Refresh Account

Creating an unattended data refresh account for PowerPivot data access is often required for external data access during data refresh. For example, if Kerberos is not enabled, you must create an unattended account that the PowerPivot service can use to connect to external data sources.
For instructions about how to modify the unattended account later or configure other stored credentials used in data refresh, see How to: Configure the PowerPivot Unattended Data Refresh Account and How to: Configure Stored Credentials for PowerPivot Data Refresh.