Configure Excel Services data access by using embedded data connections

 

Applies to: SharePoint Server 2010

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:

• All steps in the Create a baseline environment for a BI test environment series of articles.

• All steps in the Configure Excel Services for a BI test environment article.

Excel Services in Microsoft SharePoint Server 2010 uses three methods of connecting to external data:

• You can use an unattended service account. For more information, see Configure the Excel Services unattended service account for a BI test environment.

• You can use an Office Data Connection (ODC) file. For more information, see Configure Excel Services data access by using external data connections in a BI test environment.

• You can embed an ODC file in a workbook. This article describes how to this.

To configure Excel Services data access to use embedded data connections, we will use the following process:

1. Configure a data access account

2. Configure Secure Store Service

3. Create and publish an Excel workbook that uses an embedded data connection

4. Verify that data updates in an Excel Services file

Configure a data access account

To configure Excel Services to use embedded data connections, we begin by configuring a data access account. We then grant this account Read access to data that is stored in SQL Server.

Note

We will be using the ContosoRetailDW dataset that we installed when we set up SQL Server. For more information, see Set up SQL Server for a BI test environment.

To create an Active Directory account for data access

1. Log into Contoso-DC using the Contoso\administrator account.

2. 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 and User logon name text boxes, type ExcelDataEmbed, and then click Next.

6. Type and confirm a password for the account.

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

8. Select the Password never expires check box.

9. Click Next, and then click Finish.

Now that the ExcelDataEmbed account is created, the next step is to grant that account read access to the required data. Use the following procedure to create a SQL Server logon and grant Read access to the database.

To create a SQL Server logon

1. Log in to Contoso-SQL 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. On the Connect to Server dialog box, select Database Engine as the Server type, type Contoso-SQL in the Server name text box, and then click Connect.

4. In Object Explorer, expand Security.

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

   The Login – New dialog box opens.

6. Next to the Login name text box, type Contoso\ExcelDataEmbed.

7. In the Login – New dialog box, in the Select a page pane, click User Mapping.

   A list of databases appears.

8. Select the Map check box for the ContosoRetailDW database, and then in the Database role membership for: ContosoRetailDW section, select the db_datareader check box.

9. Click OK to close the Login – New dialog box. Notice that the Contoso\ExcelDataEmbed data access account is now displayed in the Logins list.

Now that we have created a data access account, the next step is to configure the Secure Store Service application.

Configure the Secure Store Service application

To configure the Secure Store Service application, we begin by creating an Active Directory group. This group will be used to provide data access to Excel users who are using embedded data connections.

To create an Active Directory group for user data access

1. Log into Contoso-DC using the Contoso\Administrator account.

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

   The New Object – Group dialog box opens.

5. In the Group name box, type ExcelDataEmbedGroup, and then click OK.

   Notice that ExcelDataEmbedGroup now appears in the list of groups and users.

6. Double-click ExcelDataEmbedGroup to open it for editing.

7. Click the Members tab, and then click Add.

   The Select Users, Contacts, Computers, Services Accounts, or Groups dialog box opens.

8. In the Enter the object names to select box, type Contoso\susan.burk; Contoso\john.woods, and then click Check Names.

   The user accounts are verified and then appear as hyperlinks.

9. Click OK to close the Select Users, Contacts, Computers, Services Accounts, or Groups dialog box.

10. Click OK to close the ExcelDataEmbedGroup Properties dialog box.

Now that we have created our Active Directory accounts, the next step is to configure the Secure Store Service application. We begin by creating a target application.

To create a target application

1. Log into 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 Central Administration home page, under Application Management, click Manage service applications.

4. Click the Secure Store Service application.

5. On the ribbon, click New.

6. In the Target Application ID text box, type ExcelServicesEmbeddedData.

7. In the Display Name text box, type Excel Services Embedded Data.

8. In the Contact E-mail text box, type farmadmin@contoso.local.

   Note

   Although e-mail notifications are not configured in this environment, you must specify an email address in the Contact E-mail box.

9. In the Target Application Type drop-down list, select Group, and then click Next.

10. Keep the default credential fields, and then click Next.

11. On the Specify the membership settings page, in the Target Application Administrators box, type Contoso\FarmAdmin.

12. In the Members box, type Contoso\ExcelDataEmbedGroup, and then click OK. Do not leave the page yet.

13. Position the pointer on ExcelServicesEmbeddedData, click the down arrow that appears, and then click Set Credentials.

    The Set Credentials for Secure Store Target Application (Group) page opens.

14. In the Windows User Name box, type Contoso\ExcelDataEmbed.

15. Type and confirm the password for the Contoso\ExcelDataEmbed account, and then click OK.

16. Click OK.

Now that we have configured Excel Services data access to use embedded data connections, the next step is to create and publish an Excel workbook that uses an embedded connection. We will then use this workbook to verify that data updates correctly in Excel Services.

Create and publish an Excel workbook that uses an embedded data connection

In this section, we describe how to create a PivotTable report that uses data that is stored in SQL Server and publish the workbook to SharePoint Server. We will then refresh the data in the workbook that uses the embedded connection.

Creating a PivotTable report by using a SQL Server table

For this report, we use a table that is stored in SQL Server.

To create a PivotTable report by using external SQL Server data

1. Log into Contoso-Client using the Contoso\Susan.Burk account.

2. In Microsoft Excel 2010, on the Data tab, in the Get External Data group, click From Other Sources, and then click From SQL Server. The Data Connection Wizard opens.

3. In the Server name box, type Contoso-SQL, and then click Next.

4. Use the Select the database that contains the data that you want to select ContosoRetailDW.

5. Select the Connect to a specific table check box, and then select DimStore. Then, click Next.

6. In the Description box, type Contoso Stores, and then click Finish.

   The Import Data dialog box opens.

   Note

   If you are prompted to replace the ODC file that is located on your local computer, click Yes.

7. In the Select how you want to view this data in your workbook section, click PivotTable Report, and then click OK.

   An empty PivotTable report opens.

8. In the PivotTable Field List pane, drag StoreType to the Row Labels section.

9. Drag EmployeeCount to the Values section.

   The PivotTable report updates to display a count of stores that have employees.

10. In the Values section, click the down arrow for EmployeeCount, and then click Value Field Settings.

11. In the Summarize value field by section, click Sum, and then click OK.

    Notice that the PivotTable report updates to display the total number of employees for each kind of store.

    Do not close the workbook. Keep it open for the next procedure.

At this point, we have created and saved a PivotTable report in Excel. However, we have not yet embedded the external data connection information in the workbook. The next step is to configure and embed that connection.

To embed external data connection information in an Excel workbook

1. In Excel 2010, on the Data tab, in the Connections group, click Connections.

   The Workbook Connections dialog box opens.

2. Select the Contoso-sql ContosoRetailDW DimStore connection, and then click Properties.

   The Connection Properties dialog box opens.

3. Select the Definition tab.

4. In the Excel Services section, click Authentication Settings.

   The Excel Services Authentication Settings dialog box opens.

5. Select SSS, and in the SSS ID box, type ExcelServicesEmbeddedData.

6. Click OK to close the Excel Services Authentication Settings dialog box, and then click OK to close the Connection Properties dialog box.

7. If a warning appears that states that the link to the external odc file will be removed, click Yes.

8. In the Workbook Connections dialog box, click Close. Do not close the workbook yet.

At this point, we have created a workbook that contains an embedded data connection. The next step is to publish the workbook to SharePoint Server.

To publish the ContosoStores workbook

1. In Excel, click the File tab, and then click Save & Send.

2. Click Save to SharePoint.

3. Under Locations, click Browse for a location, and then click Save As.

   The Save As dialog box opens.

4. In the location box at the top of the window, type http://contoso-appsrv/sites/BICenter/Documents.

   Note

   It might take some time for the Save As dialog box to refresh and show the site content.

5. In the File name box, type ContosoStores.xlsx.

6. Select the Open with Excel in the browser check box, and then click Save.

   A browser window opens and displays the ContosoStores workbook.

At this point, we have created and published a workbook that uses an embedded data connection. The next and final step is to verify that the data updates correctly.

Verify that data updates in an Excel Services file

In this section, we verify that data in the Excel workbook updates by using the embedded connection correctly. We begin by viewing the ContosoStores workbook in a browser window. We modify a row in the SQL Server database that is used by the workbook, and then confirm that the workbook displays the updated data as expected.

On the Contoso-Client computer, the ContosoStores workbook is open in a browser window. Notice that the PivotTable report shows the number of employees for each kind of store.

• Catalog: 120 employees

• Online: 340 employees

• Reseller: 44 employees

• Store: 10535 employees

The next step is to modify a row of data in the table that is used by the ContosoStores workbook. We will change one of the Reseller stores to Store. This will change the number of employees for both the Reseller and Store groups.

To update data in the ContosoRetailDW database

1. Log into Contoso-SQL 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. On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.

4. In the toolbar, click New Query.

5. Type the following query to the query window:

   USE [ContosoRetailDW]
   GO
   UPDATE dbo.DimStore set dbo.DimStore.StoreType = 'Store' where dbo.DimStore.StoreDescription = 'Contoso Europe Reseller'
   GO
   

6. In the toolbar, click Execute.

   In the Messages pane, (1 row(s) affected) appears.

Now that we have changed one of the stores from Reseller to Store, the number of employees for those kinds of stores should have changed, too.

On the Contoso-Client computer, view the workbook. The PivotTable report should now show the following information:

• Catalog: 120 employees

• Online: 340 employees

• Reseller: 32 employees

• Store: 10547 employees

We see that after we changed that single row of data, Reseller went from 44 employees to 32, and Store went from 10535 employees to 10547 employees. The store that we changed has 12 employees. Therefore, this is correct. We have confirmed that data is refreshing as expected in the workbook.

See Also

Concepts

Configure Excel Services for a BI test environment
Configure PerformancePoint Services for a BI test environment
Configure Visio Services for a BI test environment