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.

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.

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.

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.

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

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

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 – http://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:

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…