Most of my database model are written with Visio. I don’t want to start a digression whether Visio is good or not to build a simple data model: Visio is enogh for my modeling needs and customers love its colours and the ability to open the model with Office when I need to discuss it with them. When I have finished modeling, I generate the database and everything works fine.
Nevertheless, Microsoft seems not to like the forward engineer capabilities of Visio. The last release that supports forward engineering is the Enterprise Architect version of Visio 2003, which requires Visual Studio 2005 to be installed on the box. Since I am really tired to install old releases of Visio just to perform forward engineer (moreover, the 2003 release does not support the new data types) I decided it was time to follow the standard approach to Microsoft products: “if Microsoft does not help, do not ask, do it by yourself”.
Thus, I wrote an Office add-in that performs forward engineer of a Visio database model to SQL Server. It does not support any other database driver and has some big limitations, since the library that should let programmers have access to the underlying Visio data model is non documented and full of uninplemented interfaces. Thus, I needed to collect information over the web, searching for people who tried the same before me. Nevertheless, for a standard data model it works fine and saves me to the need to install old software on new computers. :)
It has two basic functions, available through a new ribbon:
- Validate Data Model simply asks the model to validate. I have not found a way to give a feedback whether the model has been correctly validated or not, it is up to you to check if any error appears in the output window… nevertheless, better than nothing.
- Forward engineer opens a very simple dialog box from which you can choose the database name, some options and then generate the SQL script. I personally love the option to open the script directly after generation, so that SSMS pops up and I can create the database.
The add-in is still in beta, it needs Office 2010 and has been written with Visual Studio 2010, .NET 4.0. If you are interested in testing it you can download the first beta here.
The nice part of the story is that, without any previous knowledge of Office programming and no knowledge at all of the Visio internals, it took me roughly one day from the idea and some hints found on the web to the working add-in and it has been a nice arena for me to try VS 2010… I wonder why Microsoft still refuses to add this feature to Visio, they have much better programmers than me, after all I am a BI guy.
Comments welcome, of course. :)