The Database Reverse Engineering (DBRE) add-in is back … but only for Visio Pro for Office365 … and with less database types supported than before. So, the good news is tinged with a little disappointment. That said, there will be many users who will be glad to have the ability to document SQL Server and Oracle database schemas again. These are the only two database types supported, but then they are probably the most popular of the relational database types.
I must declare that I was an enthusiastic user of InfoModeler in the 90’s before the company was bought by Visio Corporation, and the technology became the database model diagram in Microsoft Visio. Of course, we originally had forward engineering too, and I often designed a prototype in Microsoft Access, sucked it into Visio, edited the schema in Visio (especially the indexes), and then forward engineered into SQL Server. I created schema documentation in Visio because it can be annotated and coloured, making discussion with other team members, and bosses, easier. This following screenshot is of a schema diagram of the sample AdventureWorksLT database that is available on SQL Azure, using the DBRE add-in.
The above schema diagram has been coloured and tables and groups grouped logically within containers, and the Database / Display Options have been changed to show the column data types.
Reverse Engineering a Database
I have detailed how to connect to a SQL Azure database with the DBRE add-in elsewhere (see https://blog.bvisual.net/2016/12/12/reverse-engineering-sql-server-dbs-with-visio-pro-dbre ), so I will discuss a few of its features here.
Firstly, the add-in can reverse engineer tables, views, stored procedures and functions.
However, only tables, views and relationships can be displayed on the page.
This example database schema is only small, and all the tables, views and relationships have been added to the page. It looks very similar to the schema diagram in SQL Server Management Studio, but it has the added advantage that it can reorganized and enhanced much more easily since it is within a first-class diagramming package.
Tables and views can be added multiple times, either on the same page or on multiple pages. Therefore, pages with only the tables (or views) for a particular focus can be created, thus simplifying the presentation, and increasing the comprehension, as in the first screen shot in this article.
The reverse engineered code can be viewed, and edited, but this is a little sterile because it cannot be extracted automatically.
It is useful for creating snapshots of a database at a point in time, and can be used to compare with later versions later. In fact, the Refresh command will automatically compare the model in the Visio document with the current database schema, and present the conflicts in the dialog, inviting the user to update the model.
Modeling a Database
The Database Model Diagram template does not have to be used with the reverse engineering wizard. It can be used to plan a schema using the master shapes rom the stencil, and creating relationships. It may be useful to review the Database Modeling Preferences because they have an impact when adding columns and relationships manually.
The Database Document Display options affect how each element is presented in the diagram. This affects all pages within the document, so it may be necessary to check the effect changes have on the arrangement on each page.
Of course, there is no ability to export the schema design anymore, so the model would need to be re-entered into a database. There are cases when that is satisfactory, because one person may be designing the schema conceptually, whilst another refines the design into a detailed schema.
The question must be asked though … perhaps the newer model-less database diagramming templates (Chen’s, Crow’s Foot, IDEF1X and UML Database Notation) are just as suitable? Script can be written to extract the tables, columns, and relationships from these diagram, whereas the Database Model Diagram shapes are controlled by the add-in code, and are very difficult to read in script.
This is an extremely welcome return of this add-in, and it is free… However, do not expect any extra capabilities over the old version, and do not expect any compatibility with the newer database notation templates. Microsoft have listened to the cries for its return because there are some users who have not upgraded from earlier versions of Visio. These users can now move to Visio Pro for Office365, which is where Microsoft wants everyone to be anyway.
In short, there cannot be any reason not to install this add-in if you do have Visio Pro for Office365. It can be downloaded from https://go.microsoft.com/fwlink/?linkid=835953.