SQL SERVER – 2005 – White Paper – Integrating Visio 2007 and Microsoft SQL Server 2005

This article focuses on integration techniques specific to Microsoft Office Visio 2007 and Microsoft SQL Server 2005. Using Visio 2007, you can connect Visio shapes to data that was generated outside Visio. A large amount of data can be captured in a SQL Analysis Services database. Being able to analyze that data in a visual way enhances the value of the data. In the following example, sales data stored in an Analysis Services cube is used to generate a Visio PivotDiagram so that the data can be explored and graphically enhanced.

View Integrating Visio 2007 and Microsoft SQL Server 2005

Why Link Visio 2007 Diagrams to SQL Server Data

Most of us think of Visio as a drawing tool for network maps and flow charts. The idea in this paper is different. When shapes are linked to data, the diagram stops being a static picture and starts showing numbers from your database. That makes a report easier to read for people who never open Management Studio.

A PivotDiagram works like a tree. It starts with one shape for the total and lets you break it down by category, for example sales by region and then by product. Each level is built from the data source, so the numbers come from the cube, not from someone typing them into a box. When the data changes, you refresh the diagram instead of drawing it again.

If you try this with your own data, a few tips help:

• Start with a small cube or a small table. A diagram with hundreds of shapes is hard to read.
• Create a read-only login for the connection. A reporting tool does not need write access to your server.
• Refresh the data before you share the diagram, so nobody makes decisions on old numbers.
• Keep the diagram focused on one question, such as where sales dropped this quarter.

The paper was written for SQL Server 2005 and Office 2007, so the screens and menus in newer versions look different. The idea still holds: put the data where people already look, in a picture they understand. Check which data sources your own Visio version can connect to before you plan the report.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.