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 …
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…
al edlund says
you continue to demonstrate your mastery of all things visio. Great job.
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?
I have saved the stencil in 2010 format … it might work : http://1drv.ms/1unySZI
Thank you david, sort of..the feild name boxes have different curved lines on their right side. But it basically works. What i’d really like is to be able to import the style of spreadsheet you have in your data entities attributes report. This is the type of data I have, list form of attributes and their types and many entities within one sheet.
You should be able to use the vrd files that are in the original download, or follow this http://1drv.ms/1unOXyx
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.
VRD (Visio Report Definitions) have not changed for many versions of Visio ….
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?
I have sent you an email.
Syed Ali says
David, The link to download the stencil is not working. Can you please fix.
I have updated the link 🙂
David, link is not working…can you please post the link again.
Dave S says
Hi David many thanks for the tutorial. I’m looking to understand the macro is there any chance of sharing the PW. My objective is to automate the creating of an entity diagram from excel data with the relationships (stored in excel) being created at the same time. Do you know if this is possible to automate?
Did you ever release your “SharePoint Explorer add-in for Visio”? I am looking for an automated way to generated ERDs from SharePoint lists containing relational data. I believe this should be possible using SharePoint REST API calls to retrieve list structure data such as column names and types as well as details about Lookup columns pointing to other lists…
David Parker says
I haven’t released it, and since then there has been improvements to CSOM, JSOM and the Rest API. I would love to have the time to update and release ….
David, nevermind…was able to download the Stencil…it has 4 shapes
Thanks a lot
Great idea! It’s not easy to find tools to generate diagrams from such simple data like Excel sheet headers.