Export (0) Print
Expand All

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

SharePoint 2013

Published: December 18, 2012

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

Applies to:  SharePoint Server 2013 Enterprise 

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.

Important Important:

This scenario applies only to Microsoft TechNet Test Lab Guides.

In this article:

Scenario overview

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

  • Configuring sample data and a data access account

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

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.

Before you begin

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

You must have completed all of the steps in the above-listed test lab guides before beginning the procedures in this test lab guide.

Create sample data

For the purposes of this lab, we will create a SQL Server database and 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.

note Note:

If you have previously followed the steps in Test Lab Guide: Configure the Excel Services unattended service account or Test Lab Guide: Configure Excel Services data refresh by using an external connection, 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 database and table.

To create a database and 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.

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.

To create an Active Directory account for data access

  1. Log into DC1 using the CORP\administrator account.

  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 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 SQL1 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 as the Server type, type SQL1 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 CORP\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 CorporateAccounts database, and then in the Database role membership for: CorporateAccounts section, select the db_datareader check box.

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

The data access account now has access to the CorporateAccounts database, but we must also grant access to the CORP\ExcelWorkbookAuthors group in order for users to have database access from Excel. Use the following procedure to grant database access to the CORP\ExcelWorkbookAuthors group.

note Note:

If you created the database when you did a different test lab guide, the CORP\ExcelWorkbookAuthors group may already have database access.

To configure database access for Excel workbook authors

  1. In Management Studio, under Logins, double-click the CORP\ExcelWorkbookAuthors login.

  2. Under Select a page, click User Mapping.

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

  4. Click OK.

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

Configure Secure Store

To configure Secure Store, 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 DC1 using the CORP\Administrator account.

  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.

    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 CORP\susan.burk; CORP\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 Secure Store. We begin by creating a target application.

To create a Secure Store target application

  1. Log into 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 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 User1@contoso.com.

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

  12. In the Members box, type CORP\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 CORP\ExcelDataEmbed.

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

Now that we have configured the target application, 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 a PivotTable report by using a SQL Server table

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.

For this report, we use the Accounts table in the CorporateAccounts database that we created.

To create a PivotTable report by using external SQL Server data

  1. Log into CLIENT1 using the CORP\Susan.Burk account.

  2. In Excel 2013, 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 SQL1, and then click Next.

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

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

  6. In the Description box, type Corporate Accounts, and then click Finish.

    The Import Data dialog box opens.

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

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

At this point, we have created 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 2013, on the Data tab, in the Connections group, click Connections.

    The Workbook Connections dialog box opens.

  2. Select the SQL1 CorporateAccounts Accounts 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 the Use a stored account option, and then, in the Application 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 a workbook to a SharePoint document library

  1. In the Excel workbook, click File, click Save, and then click Browse.

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

    note Note:

    It may take several moments for the file list to refresh.

  3. In the Document Libraries list, double-click Documents.

  4. In the File name box, type CorporateAccountsEmbedded.xlsx.

  5. Click Save.

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

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.

Test data refresh

To confirm that data refresh works with the embedded connection account, 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.

    note Note:

    If a warning message appears, click OK.

If the embedded connection is configured correctly, the data will refresh without error, although we will not see any changes in data 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.OpEx = 18000.00 where dbo.Accounts.Region = 'North'
    GO
    
    
  6. In the toolbar, click Execute.

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

Note the currently displayed value for OpEx for the North region in the spreadsheet. We updated this row to a value of $18,000.00 in the Accounts table, so the new value should appear when we refresh the workbook. Use the following procedure to refresh the workbook.

note Note:

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.

    note Note:

    If a warning message appears, click OK.

The new value of 18000.00 should now appear for OpEx for the North region, confirming that data refresh through the embedded connection and Secure Store is working.

note Note:

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