Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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

SharePoint 2013

Published: December 18, 2012

Summary: Configure Visio Services data refresh in a test lab environment.

Applies to:  SharePoint Server 2013 Enterprise 

This article explains how to set up Visio Services 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 the data access account for Visio Services. This includes:

  • Configure a data access account

  • Configure a Secure Store target application

In this lab, we will also create a sample diagram with an external data connection in Visio 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.

You must also install Visio Professional 2013 on CLIENT1 if you have not already done so. You can download a trial version of Visio Professional 2013 from the TechNet Evaluation Center.

Create sample data

When configuring data access through Secure Store, you have to grant the account access to the data source to which your Visio diagram will be connected. For the purposes of this lab, we will create a SQL Server table. The table contains some simple data which we can use in a data-connected diagram which we will publish to a document library in the Business Intelligence Center.

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 ServerStatus;
    GO
    USE [ServerStatus]
    GO
    CREATE TABLE [dbo].[Status](
    [Server] [varchar](50) NULL,
    [Status] [bit] NULL
    ) ON [PRIMARY]
    GO
    INSERT INTO Status ([SERVER], [Status]) 
    VALUES ('Seattle', 'TRUE');
    GO
    INSERT INTO Status ([SERVER], [Status]) 
    VALUES ('New York', 'TRUE');
    GO
    INSERT INTO Status ([SERVER], [Status]) 
    VALUES ('London', 'FALSE');
    GO
    
  6. On the toolbar, click Execute.

With the sample table created, the next step is to create an Active Directory account for use as the data access account.

Configure a data access account

Secure Store maps users designated to have data access to an account that has data access. For this lab, we will create an Active Directory account and give it access to the ServerStatus database that we created in the previous section. Use the following procedure to create the account.

To create an Active Directory account for data access

  1. Log into 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 VisioDataAccess.

  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 has been 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 need to access from your Visio diagram. In this lab we will grant access to the sample SQL Server that we created earlier. Use the following procedure to create a SQL Server logon and grant that logon data reader access to the ServerStatus table.

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\VisioDataAccess.

  7. Under Select a page, click User Mapping.

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

  9. Click OK.

Now that the CORP\VisioDataAccess account has access to the ServerStatus database that we created, the next step is to configure Secure Store.

Configure Secure Store

Configuring Secure Store involves three steps:

  • Determine which users should have data access through Secure Store

  • Create a Secure Store target application that contains those users

  • Map the target application to the credentials that have data access

Generally, you will want to provide data access to multiple users. The easiest way to manage that is by creating an Active Directory group and then populating that group with the users to whom you want to grant data access.

Use the following procedure to create the Active Directory group.

To create an Active Directory group for user data access

  1. Log on to 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.

  5. In the Group name box type VisioDataAccessGroup.

  6. Click OK.

Next, we will populate the group with the users to whom we want to grant data access. In this case, we will use the Susan Burk and John Woods accounts. Use the following procedure to populate the VisioDataAccessGroup.

To populate the data access group

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

  2. On the Members tab, click Add.

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

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

  4. Click OK.

The next step is to create a Secure Store target application that contains the VisioDataAccessGroup group. Use the following procedure to create the target application.

To create a target application

  1. Log on to the APP1 virtual machine 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 box, type VisioServicesData.

  7. In the Display Name box, type Visio Services Data Access.

  8. In the Contact E-mail box, type User1@contoso.com.

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

    1. In the Target Application Administrators box, type CORP\User1.

    2. In the Members box, type CORP\VisioDataAccessGroup.

    3. Click OK.

Now that the target application has been created, we must associate it with the credentials that have data access (the CORP\VisioDataAccess account). 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 VisioServicesData, click the arrow that appears, and then click Set Credentials.

  2. In the Windows User Name box, type CORP\VisioDataAccess.

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

  4. Click OK.

The Secure Store configuration is now complete. In the next section, we will configure data access for the Visio user who will be creating data-connected diagrams.

Grant database access to the diagram author

When Visio Services renders a data-connected diagram, it can use Secure Store to refresh the data. However, Visio does not use Secure Store, but instead require the user to have direct database access to work with an ODC file or a data-connected diagram. Because of this, we must first grant Read access to the ServerStatus database to the user who will create the diagram.

In Test Lab Guide: Create a Business Intelligence Baseline Environment, we created an Active Directory group named VisioDiagramAuthors for granting database access to diagram authors. We must now grant db_datareader access to that group for the ServerStatus database.

Use the following procedure to grant database access to the CORP\VisioDiagramAuthors group.

To grant database access to a user

  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. Connect to the Database Engine.

  4. In SQL Server Management Studio, expand Security, and then expand Logins.

  5. Right-click the CORP\VisioDiagramAuthors logon, and then click Properties.

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

  7. Select the Map check box for the ServerStatus database.

  8. In the Database role membership for: ServerStatus list, select the db_datareader check box, and then click OK.

With configuration of database access for the CORP\VisioDiagramAuthors group complete, the next step is to create an Office Data Connection file.

Create a data-connected diagram by using an ODC file

In this section, we create a data-connected diagram using an external connection file, and then publish it to the document library in the Business Intelligence center. This helps us confirm that data refresh through Secure Store is working correctly.

Use the following procedure to create the diagram.

To create an ODC file and link data to shapes in Visio

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

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

  3. On the Choose a Template page, in the Template Categories section, click Network.

  4. Click Detailed Network Diagram, and then click Create.

  5. On the ribbon, click the Data tab, and then click Link Data to Shapes.

  6. On the Data Selector page, choose the Microsoft SQL Server database option, and then click Next.

  7. On the Connect to Database Server page, type SQL1, and then click Next.

  8. On the Select Database and Table page, select the ServerStatus database, and then click Next.

  9. On the Save Data Connection File and Finish page:

    1. Click Authentication Settings.

    2. On the Visio Services Authentication Settings dialog box, choose the Use a stored account option, type the application ID of the Secure Store target application that you created (VisioServicesData) in the Application ID text box, and click OK.

    3. Click Browse.

    4. Browse to a the data connection library in the Business Intelligence Center (http://WFE1/sites/BICenter/Data%20Connections).

      note Note:

      Visio Services does not require that ODC files be saved to a data connection library. However, for easiest administration, we recommend using data connection libraries to store all your data connection files.

    5. Type ServerStatusConnection.odc in the File name box, and then click Save.

    6. Click Finish.

  10. If the Web File Properties dialog box appears, click OK.

  11. On the Select Data Connection page, click Finish.

  12. On the blank drawing page:

    • Drag three Server icons from the Shapes area onto the page.

    • Drag each row from the data table in the External Data section to one of the shapes.

      Each shape should now have a data graphic connected to it indicating the server name and status (1 or 0).

You should now have a diagram similar to the following.

Image of servers with data callouts

The next step is to publish the diagram as a data-connected diagram and then confirm that data refresh is working correctly.

Use the following procedure to publish the drawing.

To publish a data-connected diagram

  1. When you are ready to save the drawing, click File, click Save, and then click Browse.

  2. On the Save As dialog box, type http://APP1/sites/BICenter in the location box at the top, and then press Enter.

    note Note:

    It may take some time for the dialog box to refresh and show the site content.

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

  4. In the File name box, type Server Status, and then click Save.

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

  6. When the drawing is displayed, if a Refresh Disabled warning is displayed, click Allow Refresh.

  7. Note the server statuses displayed:

    • Seattle – True (1)

    • New York – True (1)

    • London – False (0)

    Leave this browser window open for the data refresh test procedure that follows.

The rendered diagram shows the data currently in the ServerStatus database. To test the data refresh mechanism, we can modify the data in the Status table in SQL Server and then refresh the diagram to see the change.

Test data refresh

Use the following procedure to modify the data in SQL Server and then refresh the diagram.

note Note:

Remain logged on to CLIENT1 as Susan Burk while you do this procedure.

To test Visio Services data refresh

  1. Log into 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 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 [ServerStatus]
    GO
    UPDATE dbo.Status set dbo.Status.status = 1 where dbo.Status.server = 'London'
    GO
    
  6. In the toolbar, click Execute.

  7. Return to CLIENT1 where the Server Status diagram is displayed.

    Note that the London server still shows False (0).

  8. On the Visio Web Access toolbar, click Refresh.

    Note that the London server now shows True (1), reflecting the manual changes we made in SQL Server.

    note Note:

    The Visio Services cache settings affect how quickly the new results appear when you refresh the drawing. In this 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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.