Configure Excel Services data access by using external data connections in a BI test environment

Applies to: SharePoint Server 2010, Excel Services

Excel Services in Microsoft SharePoint Server 2010 provides three methods of using Secure Store to refresh the external data source in a workbook:

• The unattended service account

• A data connection embedded in the workbook

• A data connection through an Office Data Connection (ODC) file

This lab covers the Office Data Connection file option. For more information about how to use an embedded connection, see Configure Excel Services data access by using embedded data connections. For more information about how to use the unattended service account, see Configure the Excel Services unattended service account for a BI test environment.

In this lab we create a PivotTable report on Microsoft Excel 2010 that uses an external data connection to Microsoft SQL Server 2008 R2. Next, we explain how to save this PivotTable report on Microsoft SharePoint Server 2010, and then how to confirm that data refresh is working correctly.

Configure a data access account

When you configure data access through Secure Store, you must grant the account access to the data source to which your Excel Services workbook will be connected. For the purposes of this lab, we will continue to use the Contoso database sample data which we can use in a data-connected workbook that we will publish to a SharePoint document library.

To create an Active Directory account for data access

1. Log on to Contoso-DC as Contoso\Administrator.

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 boxes, type ExcelODCAccess.

6. Click Next.

7. Type and confirm 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, and then click Finish.

Once the account is created, the next step is to grant that account Read access to the required data. In a production scenario, you would grant access to whatever data source that you have to access from your Excel Services workbook. In this lab we will grant access to the ContosoRetailDW database. Use the following procedure to create a SQL Server logon and grant that logon Data Reader access to the database.

To create a SQL Server logon

1. Log on to Contoso-SQL as Contoso\SQLAdmin.

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 box, and then click Connect.

4. In Object Explorer, expand Security.

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

6. In the Login name box, type Contoso\ExcelODCAccess.

7. Under Select a page, click User Mapping.

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

9. Click OK.

Configure Secure Store

Earlier in this lab series, we configured Secure Store Service in the Configure Secure Store Service for a BI test environment article. Now we must create a target application for Excel Services with a series of procedures. These procedures explain how to create an Active Directory group in the virtual network, how to create the target application in Secure Store, and finally how to assign credentials to this target application.

In the Secure Store target application, we will map a specific group of users to the Contoso\ExcelODCAccess account that we created in the previous section. Although these users will never have direct access to this account, Excel Services will use this account on their behalf to refresh data-connected workbooks.

To avoid the complexity of administering individual users, we will create an Active Directory group to contain them.

To create an Active Directory group for user data access

1. Log on to Contoso-DC as Contoso\Administrator.

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.

5. In the Group name box, type ExcelODCAccessGroup.

6. Click OK.

After the group account is created in Active Directory, we must now populate the group with user accounts to which we want to give data access through Secure Store. The following procedure explains how to do this.

To populate the data access group

1. In Active Directory Users and Computers, in the users list, double-click the ExcelODCAccessGroup group.

2. On the Members tab, click Add.

3. On the Select Users dialog box, type Contoso\susan.burk; Contoso\john.woods, and the click OK.

4. The Members tab should now show Susan Burk and John Woods as members of the group.

5. Click OK.

Now that you have created Active Directory accounts for data access, you can create a target application for Excel Services to use with an Office Data Connection (ODC). Use the following procedure to create and configure the target application.

To create a target application

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 Application Management section, click Manage service applications.

4. Click the Secure Store Service service application.

5. On the ribbon, click New.

6. In the Target Application ID box, type ExcelServicesODC.

7. In the Display Name box, type ExcelServicesODC.

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

   Note

   Although e-mail notifications are not configured in this environment, Contact E-mail is a required field.

9. In the Target Application Type drop-down list, select Group.

10. Click Next.

11. Leave the default credential fields, and then click Next.

12. On the Specify the membership settings page:

    • In the Target Application Administrators box, type Contoso\FarmAdmin.

    • In the Members box, type Contoso\ExcelODCAccessGroup.

    • Click OK.

Once the target application has been created, the next step is to associate the data access account that we created (Contoso\ExcelODCAccess) with the target application. Use the following procedure to set the credentials for the target application.

To set the credentials for the target application

1. On the Secure Store Service Application page, in the Target Application ID column, point to ExcelServicesODC, click the arrow that appears, and then click Set Credentials.

2. In the Windows User Name box, type Contoso\ExcelODCAccess.

3. Type and confirm the password for the Contoso\ExcelODCAccess account.

4. Click OK.

Create and publish an ODC file

Now that Secure Store has been configured, the next step is to create the ODC file and publish it to a trusted data connection library. Use the following procedure to create an ODC file that has a connection to the ContosoRetailDW database.

To create and publish an ODC file

1. Log on to the Contoso-Client virtual machine by using the Contoso\Susan.Burk account.

2. Click Start, click All Programs, click Microsoft Office, and then click Microsoft Excel 2010.

3. On the Data tab, click From Other Sources, and then click From SQL Server.

4. On the Connect to Database Server page of the Data Connection Wizard, type Contoso-SQL in the Server name box, and then click Next.

5. On the Select Database and Table page, select ContosoRetailDW from the drop-down list.

6. In the list of tables, select DimProduct, and then click Next.

7. Click Finish.

   Note

   If you are prompted to overwrite the ODC file on the local disk, click Yes.

8. On the Import Data dialog box, select the PivotTable Report option, and then click OK.

9. On the Data tab, click Connections.

10. On the Workbook Connections dialog box, click Properties.

11. On the Connection Properties dialog box, on the Definition tab, click Authentication Settings.

12. On the Excel Services Authentication Settings dialog box, select the SSS option, type ExcelServicesODC in the SSS ID box, and then click OK.

13. On the Connection Properties dialog box, click Export Connection File.

14. On the File Save dialog box, in the URL box, type http://Contoso-AppSrv/sites/BICenter, and then press Enter.

    Note

    It may take several moments for the list to refresh with content from the BI Center.

15. In the list of All Site Content, double-click Data Connections.

16. In the File name box, type DimProductTable, and then click Save.

17. On the Data Connections dialog box, select Office Data Connection File from the Content Type drop-down list, and then click OK.

18. On the Connection Properties dialog box, click OK, and then, on the Workbook Connections dialog box, click Close.

19. Exit Microsoft Excel.

    Note

    You do not have to save the workbook.

Remain logged on to Contoso-Client as Susan Burk for the next procedure.

We have now successfully published an ODC file that contains a data connection to the DimProduct table of the ContosoRetailDW database and uses the ExcelServicesODC target application in Secure Store for authentication.

Testing data refresh

In this section we will test what we previously configured by publishing a data-connected Excel workbook that uses the DimProductTable.odc file that we published earlier for data connectivity. Use the following procedure to create and publish a pivot table.

To create and publish a pivot table

1. On Contoso-Client, click Start, click All Programs, click Microsoft Office, and then click Microsoft Excel 2010.

2. On the Data tab, click Existing Connections.

3. On the Existing Connections dialog box, click Browse for More.

4. On the Select Data Source dialog box, in the URL box, type http://Contoso-AppSrv/sites/BICenter, and then press Enter.

   Note

   It may take several moments for the list to refresh with content from the BI Center.

5. In the list of All Site Content, double-click Data Connections.

6. On the list of Data Connections, select DimProductTable, and then click Open.

7. On the Import Data dialog box, select the PivotTable Report option, and then click OK.

8. In the Pivot Table Field List:

   1. Drag Manufacturer to the Row Labels box.

   2. Drag ProductName to the Values box.

9. Click File, click Save & Send, click Save to SharePoint, click Browse for a location, and then click Save As.

10. On the Save As dialog box, in the URL box, type http://Contoso-AppSrv/sites/BICenter, and then press Enter.

    Note

    It may take several moments for the list to refresh with content from the BI Center.

11. In the All Site Content list, double-click Documents.

12. In the File name box, type ProductsPerVendor.xlsx.

13. Confirm that the Open with Excel in the browser check box is selected, and then click Save.

The workbook will render in the browser. Leave the browser open for the following procedures.

To confirm that data refresh is works by using the ODC file and Secure Store, we will follow these steps:

• Refresh the data currently displayed in the browser on Contoso-Client

• Change a value in the ContosoRetailDW database

• Refresh the data displayed in the browser on Contoso-Client again and confirm that the new information is displayed

First, we will refresh the existing data. Use the following procedure to refresh the data in the browser.

To refresh a data-connected workbook by using Excel Services

1. In the browser, on the Data drop-down list, click Refresh All Connections.

   Note

   If a warning message appears, click OK.

If everything is configured correctly, the data will refresh without error, although we will not see any changes in values at this point. The next step is to change a value in the ContosoRetailDW database and confirm that the changes appear in the workbook.

To update data in the ContosoRetailDW database

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. 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. Copy the following query to the query window:

   USE [ContosoRetailDW]
   GO
   UPDATE dbo.DimProduct set dbo.DimProduct.Manufacturer = 'Contoso, Ltd' where dbo.DimProduct.Manufacturer = 'Northwind Traders'
   GO
   

6. In the toolbar, click Execute.

   Note

   After the query runs, you should see (47 row(s) affected) in the Messages window.

Return to the Contoso-Client virtual machine, where ProductPerVendor.xlsx is displayed in the browser.

Note the currently displayed number of products provided by Northwind Traders is 47, and the number provided by Contoso, Ltd is 710 in the spreadsheet. We changed all the products from Northwind Traders to Contoso, Ltd in the DimManufacturer table, so the new value should appear when we refresh the workbook. Use the following procedure to refresh the workbook.

To refresh a data-connected workbook by using Excel Services

1. In the browser, on the Data drop-down list, click Refresh All Connections.

   Note

   If a warning message appears, click OK.

Notice that Northwind Traders has now disappeared from the report and the number of products provided by Contoso, Ltd has changed from 710 to 757, confirming that data refresh through the ODC file and Secure Store is working.