Export (0) Print
Expand All

Test Lab Guide: Configure Excel Services data refresh by using an external connection

SharePoint 2013
 

Applies to: SharePoint Server 2013 Enterprise

Topic Last Modified: 2013-12-18

Summary: Configure data refresh in Excel Services by using an external data connection.

This article explains how to set up data refresh using Excel Services in SharePoint Server 2013 in a test lab that is based on the Configure SharePoint Server 2013 Preview in a Three-Tier Farm test lab guide.

ImportantImportant:
This scenario applies only to Microsoft TechNet Test Lab Guides.

In this article:

This test lab guide explains how to configure data refresh in Excel Services using an external data connection. This includes:

  • Configuring sample data and a data access account

  • Configuring a Secure Store target application to contain the data access credentials

  • Creating an Office Data Connection (ODC) file that contains the connection information.

In this lab, we will also create a sample PivotTable report in Excel and publish it to the Business Intelligence Center to confirm that data refresh is working.

This test lab guide assumes that you have completed all the steps in the following test lab guides:

When you configure the data access account, you have to grant the account access to the data source to which your Excel workbook will be connected. For the purposes of this lab, we will create a SQL Server table. The table contains some simple data that we can use in a data-connected workbook which we will publish to the SharePoint document library in the Business Intelligence Center.

NoteNote:
If you have previously followed the steps in Test Lab Guide: Configure Excel Services data refresh by using an embedded connection or Test Lab Guide: Configure the Excel Services unattended service account, this database may already exist. In this case, there is no need to recreate the database.

Use the following procedure to create the SQL Server table.

To create a table with sample data
  1. Log on to SQL1 by using the CORP\User1 account.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, 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. On the toolbar, click New Query.

  5. Copy the following query to the query window:

    USE [master]
    GO
    CREATE DATABASE CorporateAccounts;
    GO
    USE [CorporateAccounts]
    GO
    CREATE TABLE [dbo].[Accounts](
    [Region] [varchar] (50) NULL,
    [Sales] [money] NULL,
    [OpEx] [money] NULL,
    [AssetValue] [money] NULL
    ) ON [PRIMARY]
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('North', 23486.56, 16874.53, 64789.34);
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('South', 19864.35, 11265.84, 55349.37);
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('East', 31824.65, 22849.67, 87318.27);
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('West', 27691.26, 17628.43, 84287.67);
    GO
    
    
  6. On the toolbar, click Execute.

With the sample database and table created, the next step is to create an Active Directory account for use as the 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.

To create an Active Directory account for data access
  1. Log on to DC1 as CORP\Administrator.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the corp.contoso.com 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 CorporateAccounts database. Use the following procedure to create a SQL Server logon and grant that logon db_datareader access to the database.

To create a SQL Server logon
  1. Log on to SQL1 as CORP\User1.

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

  3. On the Connect to Server dialog box, select Database Engine as the Server type, type SQL1 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 CORP\ExcelODCAccess.

  7. Under Select a page, click User Mapping.

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

  9. Click OK.

Earlier in this lab series, we configured Secure Store in the Test Lab Guide: Configure Secure Store 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, 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 CORP\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 DC1 as CORP\Administrator.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the corp.contoso.com 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 CORP\susan.burk; CORP\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 APP1 using the CORP\User1 account.

  2. Click Start, click All Programs, click Microsoft SharePoint 2013 Products, and then click SharePoint 2013 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 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 User1@contoso.com.

    NoteNote:
    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 CORP\User1.

    • In the Members box, type CORP\ExcelODCAccessGroup.

    • Click OK.

Once the target application has been created, the next step is to associate the data access account that we created (CORP\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 CORP\ExcelODCAccess.

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

  4. Click OK.

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 CorporateAccounts database.

To create and publish an ODC file
  1. Log on to the CLIENT1 virtual machine by using the CORP\Susan.Burk account.

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

  3. If a template selection page is displayed, choose Blank workbook.

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

  5. On the Connect to Database Server page of the Data Connection Wizard, type SQL1 in the Server name box, and then click Next.

  6. On the Select Database and Table page, select CorporateAccounts from the drop-down list.

  7. In the list of tables, select Accounts, and then click Next.

  8. Click Finish.

    NoteNote:
    If you are prompted to overwrite the ODC file on the local disk, click Yes.
  9. On the Import Data dialog box, select the Only Create Connection option, and then click OK.

  10. On the Data tab, click Connections.

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

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

  13. On the Excel Services Authentication Settings dialog box, select the Use a stored account option, type ExcelServicesODC in the Application ID box, and then click OK.

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

  15. On the File Save dialog box, in the URL box, type http://WFE1/sites/BICenter, and then press Enter.

    NoteNote:
    It may take several moments for the list to refresh with content from the BI Center.
  16. In the list of All Site Content, double-click Data Connections.

  17. In the File name box, type AccountsTable, and then click Save.

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

  19. When the export has completed, on the Connection Properties dialog box, click OK, and then, on the Workbook Connections dialog box, click Close.

  20. Exit Excel.

    NoteNote:
    You do not have to save the workbook.

Remain logged on to CLIENT1 as Susan Burk for the next procedure.

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

In this section we will test what we previously configured by publishing a data-connected Excel workbook that uses the AccountsTable.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 CLIENT1, click Start, click All Programs, click Office, and then click Excel 2013.

  2. If a template selection page is displayed, choose Blank workbook.

  3. On the Data tab, click Existing Connections.

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

  5. On the Select Data Source dialog box, in the URL box, type http://WFE1/sites/BICenter, and then press Enter.

    NoteNote:
    It may take several moments for the list to refresh with content from the BI Center.
  6. In the list of All Site Content, double-click Data Connections.

  7. On the list of Data Connections, select AccountsTable.odc in the Name column, and then click Open.

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

  9. In the pane on the right side, in the Pivot Table Field List:

    1. Select the AssetValue check box.

    2. Select the OpEx check box.

    3. Select the Region check box.

    4. Select the Sales check box.

  10. Click File, click Save, and then click Browse.

  11. On the Save As dialog box, in the URL box, type http://WFE1/sites/BICenter, and then press Enter.

    NoteNote:
    It may take several moments for the file list to refresh.
  12. In the Document Libraries list, double-click Documents.

  13. In the File name box, type CorporateAccountsExternal.xlsx.

  14. Click Save.

  15. When the save has completed, navigate to the document libaray in the Business Intelligence Center (http://wfe1/sites/BICenter/Documents) and click on the CorporateAccountsExternal.xlsx file to render it in the browser using Excel Services.

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 CLIENT1

  • Change a value in the CorporateAccounts database

  • Refresh the data displayed in the browser on CLIENT1 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.

    NoteNote:
    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 CorporateAccounts database and confirm that the changes appear in the workbook.

To update data in the CorporateAccounts database
  1. Log on to SQL1 by using the CORP\User1 account.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, 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 [CorporateAccounts]
    GO
    UPDATE dbo.Accounts set dbo.Accounts.Sales = 25000.00 where dbo.Accounts.Region = 'North'
    GO
    
    
  6. In the toolbar, click Execute.

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

Return to the CLIENT1 virtual machine, where CorporateAccountsExternal.xlsx is displayed in the browser.

Note the currently displayed number for Sales in the North region is 23486.56. We changed that to 25000 in the Accounts table, so the new value should appear when we refresh the workbook. Use the following procedure to refresh the workbook.

NoteNote:
If you see a warning that the page has expired, click OK and restart the procedure.
To refresh a data-connected workbook by using Excel Services
  1. In the browser, on the Data drop-down list, click Refresh All Connections.

    NoteNote:
    If a warning message appears, click OK.

Notice that the Sales amount for the North region is now $25,000, confirming that data refresh through the ODC file and Secure Store is working.

NoteNote:
The Excel Services cache settings affect how quickly the new results appear when you refresh the workbook. In the Test Lab Guide: Configure Excel Services lab, we set the cache settings to zero (0), allowing immediate refresh. In a production environment, refresh will likely take longer depending on how you configure the cache.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft