Configure Excel Services for a BI test environment

  • Article

 

Applies to: SharePoint Server 2010, Excel Services

Important

This article is part of the Configuring a BI infrastructure: Hands-on labs series. To complete the steps in this article, you must first have completed:

Excel Services in Microsoft SharePoint Server 2010 is a service application that lets users share and view Microsoft Excel workbooks. The service also enables administrators to specify permission settings in Microsoft SharePoint Server 2010 to control what users can see or do with each workbook.

Configuring Excel Services consists of three steps:

  • Deploying Excel Services on the SharePoint Server 2010 farm

  • Configuring trusted file locations and trusted data connection libraries

  • Configuring data access for authors of Excel workbooks

Deploy Excel Services

Deploying Excel Services consists of the following steps:

  • Creating an Active Directory account for the application pool under which the Excel Services service application will run

  • Registering that account as a managed account in SharePoint Server 2010

  • Starting the Excel Calculation Services service

  • Creating an Excel Services service application

The first step in deploying Excel Services is to create an account in the Active Directory directory service to run the application pool for the Excel Services service application. Use the following procedure to create the account.

To create an account for the application pool

  1. Log on to Contoso-DC using the Contoso\administrator account.

  2. On the Contoso-DC virtual machine, click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the contoso.local node.

  4. Right-click Users, click New, and then click User.

  5. In the Full name box and the User logon name box, type ExcelAppPool.

  6. Click Next.

  7. In the Password and Confirm password boxes, type a password for the account.

  8. Clear the User must change password at next logon check box.

  9. Select the Password never expires check box.

  10. Click Next.

  11. Click Finish.

  12. Log off Contoso-DC.

Once you have created the account, it must be registered as a managed account in SharePoint Server 2010. Doing so makes the account available to service applications when you create them. Use the following procedure to register the managed account.

To register a managed account

  1. Log in to Contoso-AppSrv using the Contoso\FarmAdmin account.

  2. Click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.

  3. On the SharePoint Central Administration Web site home page, in the left navigation, click Security.

  4. On the Security page, under General Security, click Configure managed accounts.

  5. On the Managed Accounts page, click Register Managed Account.

  6. In the User name box, type Contoso\ExcelAppPool.

  7. In the Password box, type the password for the Contoso\ExcelAppPool account.

  8. Click OK.

By default, the application pool account does not have the required access to the SharePoint content database. Access to this database is required for Excel Services. Use the following procedure to grant the required database access to the application pool account.

To grant content database access to the managed account

  1. On Contoso-AppSrv, click Start, click All Programs, click Microsoft SharePoint 2010 Products, right-click SharePoint 2010 Management Shell, and then click Run as Administrator.

  2. At the Windows PowerShell Command Prompt, type the following (press Enter after each line):

    $w = Get-SPWebApplication -identity http://contoso-appsrv
$w.GrantAccessToProcessIdentity("Contoso\ExcelAppPool")

Once you have granted database access to the application pool account, you must start the Excel Calculation Services. This is the SharePoint Server 2010 service that performs all processing and activities associated with Excel Services.

You must start this service on at least one application server. In a production environment, you would choose a server or servers on which to run the service, depending on your capacity requirements.

Use the following procedure to start the Excel Calculation Services service.

To start the Excel Calculation Services service

  1. On the Central Administration home page, in the System Settings section, click Manage services on server.

  2. Above the Service list, click the Server drop-down, and then click Change Server.

    Note that the application server, Contoso-AppSrv, is listed together with Contoso-SQL. In a farm with multiple application servers, you would use this dialog box to select the server where you want to run Secure Store Service, and then start the service as shown later in this article.

  3. Click CONTOSO-APPSRV.

  4. In the Service list, click Start next to Excel Calculation Services.

Once the service is started, you must create an Excel Services service application. Use the following procedure to create the service application.

To create an Excel Services service application

  1. On the Central Administration home page, under Application Management, click Manage service applications.

  2. On the Manage Service Applications page, click New, and then click Excel Services Application.

  3. In the Name section, type Excel Services in the text box.

  4. Select the Create new application pool option and type ExcelServicesAppPool in the text box.

  5. Select the Configurable option, and from the drop-down list, select CONTOSO\excelapppool.

  6. Click OK.

Configure trusted file locations and trusted data connection libraries

In Configure a Business Intelligence Center for a test environment, we created a Business Intelligence Center which includes a document library and a data connection library. In order for data access to function, Excel Services must be configured to trust these libraries.

Use the following procedure to create a trusted file location for the document library in the Business Intelligence Center.

To create a trusted file location

  1. On the Central Administration home page, under Application Management, click Manage services applications.

  2. In the list of service applications, click Excel Services.

  3. Click Trusted File Locations.

  4. On the Trusted File Locations page, click Add Trusted File Location.

  5. In the Location section:

    1. Type http://Contoso-AppSrv/sites/BICenter/Documents in the Address text box.

    2. Select the Children trusted check box.

  6. In the External Data section:

    1. Under Allow External Data, select the Trusted data connection libraries and embedded option.

    2. In the Automatic refresh and Manual refresh text boxes, type 0.

      Note

      In a production environment, you would not typically set these values to 0. (Setting the cache values to 0 can cause additional resource utilization on the server.) However, in this series of labs, we are testing data refresh for data-connected workbooks, and setting the cache settings to 0 enables us to see data refresh results immediately.

  7. Click OK.

By creating a trusted file location, we can now publish data-connected workbooks with embedded data connections. However, for Excel Services to correctly render data-connected workbooks with external data connections, we must create a trusted data connection library.

To create a trusted data connection library

  1. On the Central Administration home page, in the Application Management section, click Manage service applications.

  2. In the list of service applications, click Excel Services.

  3. Click Trusted Data Connection Libraries.

  4. On the Trusted Data Connection Libraries page, click Add Trusted Data Connection Library.

  5. In the Address box, typehttp://Contoso-AppSrv/sites/BICenter/Data%20Connections%20for%20PerformancePoint.

  6. Click OK.

When you have completed all of the previous procedures, Excel Services configuration is complete. The next step is to configure data access for the users who will create data-connected Excel workbooks.

Configure Excel client data access

In the following Excel Services labs, we will be configuring data refresh for Excel workbooks by using Secure Store Service. However, you should know that there are differences in the way that Excel Services and the Excel client application authenticate with data sources.

Although Excel Services can use Secure Store Service to authenticate with a data source when rendering a data-connected workbook, Microsoft Excel does not use Secure Store for data authentication. Excel requires that workbook authors have direct database access in order to access external data from the workbook.

In this case, we will create an Active Directory group to contain our workbook authors, and then create a SQL Server logon for that Active Directory group. We will then grant db_datareader access for this group to the ContosoRetailDW database which we will use in successive Excel Services labs.

To create an Active Directory group

  1. Log on to Contoso-DC by using the Contoso\administrator account.

  2. On the Contoso-DC virtual machine, click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the contoso.local node.

  4. Right-click Users, click New, and then click Group.

  5. In the Group Name box, type ExcelWorkbookAuthors, and then click OK.

Next, we need to add users to the group. In this case, we will add the user Susan Burk to the group because Susan Burk has Contribute access to the Business Intelligence Center where we will publish our data-connected workbooks in successive Excel Services labs.

Note

Although we are only adding a single user to the group in this lab, in a production scenario you would add all the workbook authors to the group. (Using a group provides easier administration than adding individual logons for each user.) Depending on how your data is organized in your production environment, you may want to use multiple groups if different sets of users need access to different databases.

To populate the ExcelWorkbookAuthors Active Directory group

  1. In Active Directory Users and Computers, double-click the ExcelWorkbookAuthors group.

  2. On the Members tab, click Add.

  3. In the Enter object names to select box, type Contoso\Susan.Burk, and then click OK.

  4. Verify that Susan Burk appears in the Members list, and then click OK.

Now that the Susan Burk account has been added to the ExcelWorkbookAuthors group, the next step is to create a SQL Server logon for that group and grant that logon Read access to the ContosoRetailDW database.

To create a SQL Server logon

  1. Log on to Contoso-SQL by using the Contoso\SQLAdmin account.

  2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

  3. Connect to the Database Engine.

  4. Expand the Security node.

  5. Right-click Logins, and then click New Login.

  6. On the Login – New page, click Search.

  7. On the Select User or Group dialog box, click Object Types.

  8. On the Object Types dialog box, select the Groups check box, and then click OK.

  9. On the Select User or Group dialog box, in the Enter the object name to select box, type Contoso\ExcelWorkbookAuthors, and then click OK.

  10. In the pane on the left side, click User Mapping.

  11. Under Users mapped to this login, select the Map check box for the ContosoRetailDW database.

  12. Under Database role membership for: ContosoRetailDW, select the db_datareader check box.

  13. Click OK.

When you have completed all of the previous procedures, Excel Services will be operational and ready to use. The next step is to configure security access to your data sources. The following labs cover how to configure security access by using Secure Store Service: