Creating a Schema from Visio External Data record sets

Visio introduced External Data record sets in 2007, and I have made great use of them ever since. Visio 2013 has deprecated reverse engineering of databases to produce a schema, which was a function that I loved to use because it allowed me to provide clients with up to date, annotated, enhanced schema diagrams. Microsoft have, however, provided some nice database schema templates in Visio 2013 Professional, so I thought I would provide a way to use the External Data record sets to produce schema diagrams.

I have created a new custom stencil, based upon the IDEF1X Database Notation template in Visio 2013 Professional, and have added some macros to provide the guided diagramming. I say guided because page position of tables and relationships are left to you, but the macros will create table definitions from External Data record sets.

All of the three database notation templates contain an Entity and an Attribute master. In fact, they also have a Primary Key Attribute master too, but this is identical to the Attribute master, apart from a setting to say it is the primary key. This can be done from the Attribute shape anyway, so I will dispense with the Primary Key Attribute master.

image

My new stencil, called Shape Data Model, contains modified copies of the Entity and Attribute masters, called Data Entity and Data Attribute. I chose to start with the IDEF1X stencil because the Attribute master has the ability of optionally displaying the data type. I then added Shape Data rows to both the Data Entity and Data Attribute shapes because I wanted to be able to copy record set and column settings from an External Data record set to the shapes.

There are some nuances in the column settings of a record set. For example, if the column is a String data type, then the contents could be optionally set as Hyperlink, which will cause an actual hyperlink to be created on the linked shape. If a column is a Number data type, then the units to display can be set; similarly, Currency types can be set. Even Calendar types can be set if the column is a Date or Time data type. I added these nuances into the page user-defined cells and Attribute Shape Data rows, and made their visibility dependent upon the relevant data type.

My intention was to add sufficient cells to the Data Entity and Data Attribute shapes so that a meaningful standard Visio report definition could be created. The good thing about using the existing Link Data to Shapes to create External Data record sets is that a known, homogenous table interface is available for any data source that Visio can link to.

In the following example, I have used the OrgData Excel file that is supplied with Visio, with two extra columns to create hyperlinks for Call and Email.

image

The Data Entity shape has Shape Data for the source of the selected External Data record set.

Each Data Attribute shape stores the column settings, where applicable.

image

A record set can have multiple primary keys, and these are shown automatically. In fact, I enhanced the Primary Key, Foreign Key, Optional and Required options to use Shape Data rows, in addition to the right-mouse menu items in the original Attribute shape. This is so multiple rows can be set in one action using the Shape Data window, rather than having to open the menu for each Shape Attribute individually.

Any invisible columns are shown with a strike through the name, and hyperlink strings are shown with an underline.

The sort order is calculated from the position in the container Data Entity list shape, and the count of Data Attribute shapes is shown on the Data Entity header.

I have also included two Visio report definitions. The first one lists all of the entities in the current page.

image

The second report lists all of the attributes for each entity.

image

 

There is just one public macro in the Shape Data Model stencil, DropOrUpdateDataEntity.

image

It will create a Data Entity shape for the active record set in the External Data window, in the centre of your Visio document view.

Alternatively, you can use the shapes to design your Shape Data rows….

Download files from https://1drv.ms/u/s!AqkzN8kb1lAz5DwwsrC6xBwLIaPh

Place the macro-enabled stencil into My Shapes folder …

Addendum:

This morning, I realised that I should use my little macro on the SharePoint List that I created for my last post – https://blog.bvisual.net/2014/03/03/mapping-sharepoint-list-columns-to-visio-shape-data-types/

This shows off the all of the different data types quite well:

image

The window on the right is from my forthcoming SharePoint Explorer add-in for Visio, which allows for column selection, unlike the current Data Selector…

12 Responses to “Creating a Schema from Visio External Data record sets”

  1. al edlund Says:

    you continue to demonstrate your mastery of all things visio. Great job.
    al

  2. Jon Says:

    David, Al pointed this out to me, and i think it’s exactly what I need, however I don’t have Visio 2013, and it seems the templates don’t load to V 2010. Suggestions? Upgrade?

  3. Jon Says:

    vrd are only 2014, but these are reports yes? actually I don’t need that, I have the data already in that fformat, and that the format I wish to import.

  4. rjp Says:

    David, first off, thank you for posting this. This seems to be the only article on the net showing how we can extend Visio 2013 to get back some of what we lost in 2010. I was going through the files to better understand what you’ve done as I want to create one for another DB I use which has different field types. Is there any way you can share that macro password so I can get a glimpse of what you did to get the drag/drop to work?

  5. Syed Ali Says:

    David, The link to download the stencil is not working. Can you please fix.
    Thanks,

  6. Reverse Engineering SQL Server DBs with Visio Pro DBRE | bVisual - for people interested in Microsoft Visio Says:

    […] looked at using the new Crow’s Foot Diagram template in an earlier article ( see Creating a Schema from Visio External Data record sets), and I have nearly finished making this into a VSTO […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: