Use Visio Services with complex data queries
Summary: You can use complex SQL queries, such as JOIN statements, to connect data to a Visio drawing with Visio Services by connecting your shapes to a SQL Server view. Visio Services itself cannot perform complex SQL queries. However, by using a SQL Server view, you can overcome this limitation.
You can connect to a SQL Server view from Microsoft Visio, link the data to the shapes in your diagram, and then publish a data-connected Web drawing to Microsoft SharePoint Server 2010 and render it with Visio Services. Using a SQL Server view provides a method of using complex queries to present data in a tabular format that is compatible with Visio Services.
Note
This scenario requires you to have Visio Services in Microsoft SharePoint Server 2010 deployed. For information about how to deploy Visio Services, see Plan for Visio Services (SharePoint Server 2010). Additionally, you must be using Microsoft Visio Professional 2010 or Microsoft Visio Premium 2010 to publish the Web drawing. This scenario also assumes that you are using Secure Store to store the credentials that are used for data refresh in Visio Services. For information about how to configure Secure Store, see Plan the Secure Store Service (SharePoint Server 2010) and Configure the Secure Store Service (SharePoint Server 2010).
There are two basic steps for using SQL Server views with Visio Services:
Create a SQL Server view: We recommend that you have a database administrator create a view that meets your needs.
Connect the view to a Visio drawing: In Microsoft Visio 2010, connect the data in the SQL Server view to the shapes in your diagram, and then publish it to a SharePoint document library.
Creating a SQL Server view
Creating a SQL Server view can be simple or complex, depending on your data. We recommend that you have your database administrator create the view that you need. You can follow these steps to begin to create a view in SQL Server Management Studio:
To create a SQL Server view
In Management Studio, connect to the Database Engine.
Expand the Databases node, and then expand the database where you want to create the view.
Right-click Views and then click New View.
In the Add Table dialog box, choose the tables, views, functions, and synonyms that you want to include in the view.
Click Close to continue designing the view.
When you have finished designing the view, click the Save button in the toolbar.
For complete information about how to create views in SQL Server, see Designing and Implementing Views and CREATE VIEW (Transact-SQL).
Video demonstration
This video gives an example of how to create a SQL Server view.
.jpg "Screenshot of video")
Running time: 4:03 |
Right-click the link, and then click Save Target As to download a copy. Clicking the link opens a .wmv file in the default video viewer for full-resolution viewing. |
.png "Play video")
.png "Download video")
For an optimal viewing experience, Using SQL Server views with Visio and Visio Services
In this scenario, we assume that you are using Secure Store to store the credentials for data refresh in Visio Services. You have the following options when you use Secure Store with Visio Services:
Unattended Service Account: Visio Services data refresh is achieved by using the unattended service account.
Specified Secure Store target application: Visio Services data refresh is achieved by using the Secure Store target application specified in the Office Data Connection (ODC) file associated with the Web drawing.
For both options, the data access account stored in Secure Store must have db_datareader permissions on the database where the SQL Server view resides.
Using the unattended service account
When you are using the unattended service account for data refresh, you can connect to the SQL Server view directly from Visio and then publish your diagram to SharePoint Server 2010 as a Web drawing whose data can be refreshed.
Important
You must have the unattended service account configured in Visio Services Global Settings. For information about configuring the unattended service account, see Use Visio Services with Secure Store.
Once the view has been created and you have access to it, you can connect to it from Visio. Use the following procedure to connect to the view.
To connect a Visio drawing to a SQL Server view
In Visio, open a diagram or create a new diagram.
On the Data tab, click Link Data to Shapes.
On the Data Selector page, select the Microsoft SQL Server database option, and then click Next.
In the Server name box, type the name of the instance of SQL Server to which you want to connect, choose the credentials that you want to use to log on, and then click Next.
On the Select Database and Table page, from the drop-down list, choose the database where you created the view.
Make sure the Connect to a specific table check box is selected, and choose the view that you created from the list.
Click Next.
Complete the wizard.
Once you have connected to the SQL Server view, you can drag the data rows onto the page to link data to your existing shapes, or add new shapes. When you have completed the diagram, you can save it to a SharePoint document library and render it with Visio Services. Use the following procedure to publish your Web drawing.
To publish a diagram to a SharePoint document library
In Visio, click File.
Click Save and Send.
Click Save to SharePoint.
Under File Types, choose Web Drawing, and then click Save As.
In the Save As dialog box, navigate to the SharePoint document library where you want to save the file, type a file name in the File name box, and then click Save.
Once the Web drawing has been saved to the SharePoint document library, you can view the drawing by clicking it directly or by configuring it to appear in a Visio Web Access Web Part. The drawing remains connected to the data in the SQL Server view, and the data refreshes based on the refresh settings that you have configured for Visio Services and for the Visio Web Access Web Part, if applicable.
Video demonstration
This video shows the steps involved in configuring data refresh by using the unattended service account.
.jpg "Screenshot of video")
Running time: 6:39 |
Right-click the link, and then click Save Target As to download a copy. Clicking the link opens a .wmv file in the default video viewer for full-resolution viewing. |
Using a Secure Store target application
You can use a specific Secure Store target application for data refresh in Visio Services. This allows you to limit data refresh capability to a select group of users or to reuse an existing target application if one has already been configured.
Important
You must have a Secure Store target application configured and the stored credentials must have read access to the database where the view resides. For information about configuring a target application for use with Visio Services, see Use Visio Services with Secure Store.
In order to use a specific Secure Store target application, you must specify the target application’s Application ID in an ODC file and connect to the ODC file from Visio. Because Visio cannot be used to create an ODC file, you must create the ODC file in Microsoft Excel. Use the following procedure to create the ODC file.
To create an ODC file
Open Excel.
On the Data tab, click From Other Sources, and then click From SQL Server.
On the Connect to Database Server page of the wizard, in the Server name box, type the instance of SQL Server where you created the view.
Select the authentication method that you want to use to connect to the database, and click Next.
On the Select Database and Table page, from the drop-down list, choose the database where you created the view.
Make sure the Connect to a specific table check box is selected, and choose the view that you created from the list.
Click Next.
On the Save Data Connection File and Finish page, click Authentication Settings.
On the Excel Services Authentication Settings dialog box, select the SSS option, and in the SSS ID text box, type the Application ID for the Secure Store target application that you want to use.
Note
You can choose the Windows Authentication option if you want to authenticate by using the credentials of the user that is viewing the Web drawing, but this option requires that you have configured Kerberos delegation.
Click OK.
On the Save Data Connection File and Finish page, click Browse.
In the File Save dialog box, navigate to the data connection library where you want to save the ODC file.
Type a file name, and then click Save.
On the Save Data Connection File and Finish page, click Finish.
On the Web File Properties dialog box, update the Title, Description, or Keywords if desired, and then click OK.
On the Import Data dialog box, click Cancel.
Exit Excel.
Note
There is no need to save the Excel workbook.
Once the ODC file has been saved, you can connect to it from Visio and use it to access the data in your SQL Server view. Use the following procedure to connect to your view by using the ODC file.
To connect a Visio drawing to a SQL Server view by using an ODC file
In Visio, open a diagram or create a new diagram.
On the Data tab, click Link Data to Shapes.
On the Data Selector page, select the Previously created connection option, and then click Next.
On the Select Data Connection page, click Browse.
On the Existing Connections dialog box, click Browse for More.
On the Data Selector dialog box, navigate to the data connection library where you saved the ODC file, select the ODC file, and then click Open.
On the Select Data Connection page, click Next.
Complete the wizard.
Once you have connected to the SQL Server view through the ODC file, you can drag the data rows onto the page to link data to your existing shapes, or add new shapes. When you have completed the diagram, you can save it to a SharePoint document library and render it with Visio Services. Use the following procedure to publish your Web drawing.
To publish a diagram to a SharePoint document library
In Visio, click File.
Click Save and Send.
Click Save to SharePoint.
In the File Types section, choose Web Drawing, and then click Save As.
In the Save As dialog box, navigate to the SharePoint document library where you want to save the file, type a file name in the File name box, and then click Save.
Once the Web drawing has been saved to the SharePoint document library, you can view the drawing by clicking it directly or by configuring it to appear in a Visio Web Access Web Part. The drawing remains connected to the data in the SQL Server view through the ODC file, and the data refreshes based on the refresh settings that you have configured for Visio Services and for the Visio Web Access Web Part, if applicable.
Video demonstration
This video shows the steps involved in configuring data refresh by using an ODC file.
.jpg "Screenshot of video")
Running time: 8:52 |
Right-click the link, and then click Save Target As to download a copy. Clicking the link opens a .wmv file in the default video viewer for full-resolution viewing. |
See Also
Concepts
Use Visio Services with SharePoint lists (SharePoint Server 2010)