Integrating the Visio Database Modeling Solution with Other Modeling Tools

Article
02/20/2014

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Published: September 1, 2001

Technical Article

Visimation, Inc.

Microsoft Certified Partner

Applies to:

Microsoft Visio Professional 2002

Microsoft Visio Professional 2000

Microsoft Visio Enterprise 2000

Summary: This document presents the integration possibilities through sharing model data between Visio and other tools.

For the latest information, please see https://www.microsoft.com/technet/prodtechnol/visio/default.mspx and https://office.microsoft.com/home/office.aspx?assetid=FX01085798.

About this article

Microsoft® Visio® Professional 2002, Visio Professional 2000, and Visio Enterprise 2000 include a robust database modeling solution. Integration of the database modeling solution in Visio with other database modeling tools may be helpful in leveraging existing tools and practices within your organization or aid in migration to the Microsoft Visio modeling tools. This document presents the integration possibilities through sharing model data between Visio and other tools such as Computer Associates Erwin, VisioModeler (formerly InfoModeler), Visio 5.0 Enterprise Database Solution, Visio 5.0 Professional Database Diagrams, and to any number of tools via the Microsoft Repository using the open Microsoft Database Model Type Information Model (DBM TIM) or a commonly supported Database Management System (DBMS).

Introduction

The Database Solution found in Microsoft Visio 2000 Professional and Enterprise Editions as well as Microsoft Visio 2002 has the ability to integrate with other modeling tools. For Visio 2000, integration of the Database Solution comes in the form of transferring model data by import into Visio, export from Visio, or sharing model data in the Microsoft Repository or another commonly supported database management system (DBMS). The Microsoft Visio 2002 Database Solution includes similar sharing opportunities to Visio 2000 with the exception of the Microsoft Repository support.

The features available for integration with other tools vary primarily by product. Although Enterprise and Professional versions of Visio 2000 have the same code base for the Database Modeling solution, the feature set enabled and the installed files vary. Visio Professional carries a subset of the features and supporting files of the Enterprise edition. If you are working with Visio 2000, you have more opportunity for sharing model data using Enterprise than Professional.

The options on the Import menu (Import/Export in Enterprise) for the Database Solution are covered in this paper. The Visio 2000 Professional and Enterprise versions of this menu are shown in Figure 1. Some of the options are more of a migration path rather than for long-term integration. Since these options allow for leveraging of existing resources, they are also discussed in this paper.

Figure 1: Visio 2000 import and export menu comparison

We will also look at several powerful integration opportunities not shown in the Import/Export menu. These are not technically importing or exporting, but rather a type of integration via sharing the model through independent tool interaction on a shared data source. We will cover sharing models via the Microsoft Repository using the Database Information Model, and via creation and reverse engineering from common DBMSs.

Microsoft Visio 2000 Enterprise Edition integration opportunities:

Import/Export Erwin .ERX files
Import VisioModeler (formerly Infomodeler) .IMD, .IML, and .IMO files
Import Visio 5.0 Professional ER or Crow's Foot diagrams
Import Visio 5.0 Enterprise Database Model diagrams
Share models via the Microsoft Repository using the Database Information Model
Share models via creation and reverse engineering from common DBMS

Microsoft Visio 2000 Professional Edition integration opportunities:

Import Erwin .ERX files
Import VisioModeler (formerly Infomodeler) .IMD files
Reverse engineer schema from common DBMS after creation by another tool

Microsoft Visio 2002 Professional model sharing opportunities:

Import Erwin .ERX files
Import VisioModeler (formerly Infomodeler) .IMD files
Import Visio 5.0 Enterprise, Visio 2000 Enterprise and Professional Database Model diagrams
Reverse engineer schema from common DBMS after creation by another tool

Microsoft Visio 2002 Professional closely follows the same feature set split as Visio 2000 for the Database Modeling solution. The "Enterprise" level features will not be offered directly under a Visio Enterprise version of 2002, but rather from Visual Studio Enterprise Architect Edition.

Visio Professional is for customers who:

Need to automatically diagram existing database schema
Need to automatically create UML diagrams from existing Visual Studio projects
Need to create software and database models via drag and drop
Need to create application infrastructure diagrams using software and network shapes
Need interoperability for those who need to view or edit models created in Visual Studio.net but don't need full modeling functionality

Visual Studio.net is for customers who:

Need full-fledged database and software modeling functionality
Forward engineer changes to your database schema
Generate code skeletons from UML class diagrams
Database and UML reporting
Semantic error checking
Business rules-based database design
Need to easily exchange database schema and software models with other Visio users

Figure 2 compares the Visio database solution integration opportunities by Visio product and version.

Figure 2: Microsoft Visio database solution integration opportunities by product and version

The remainder of this article takes you through the steps to perform each of the integration opportunities mentioned in the introduction. This article's organization is based on the diagram shown in Figure 2**.** To find a specific section for a combination of product and version, go to the version section and then find what you want to do (for example, Import ERWin ERX files).

Visio 2000 Database Solution

Both Visio Professional 2000 and Visio Enterprise 2000 include the ability to import ERwin .ERX files. The versions supported for import include 2.6, 3.0, and 3.52.

Import Computer Associates ERwin .ERX file

The import brings in ERwin model elements including entities, attributes, relationships, categories, and text blocks. Data types may be converted to either portable data types for the specific DBMS defined in the .ERX file or to a default data type. For the conversion of data types to the portable data types for the DBMS, Visio must have an equivalent Visio Driver for the specific DBMS.

Importing an .ERX file does not bring in certain ERwin features. For example, ERwin displays, stored procedures, and custom trigger code are not imported.

To perform the import

Open a new Visio Database Model Diagram or ER Source Model (Enterprise Only).
Select Import (Import/Export in Enterprise) from the Database menu.
On the Import menu, select Import ERwin ERX model.
Browse to the file by clicking on the Browse button or enter a full name and path directly into the form.
Click OK to perform the import.

Figure 3: Importing ERwin .ERX files into Visio 2000 Professional or Enterprise
After the import is complete, the tables brought in are shown in the Tables anchored window. Drag and drop the tables you want in your diagram onto the page.

Export Computer Associates ERwin .ERX file

Besides importing ERwin files, with Enterprise 2000 you may also export to the ERwin .ERX file format. The versions supported for export include 2.6, 3.0, and 3.5.

To perform the .ERX export

Open the Visio Database Model Diagram or ER Source Model that you wish to export.
Select Import/Export from the Database menu.
On the Import/Export menu, select Export ERwin ERX model.
Choose a file name for the .ERX file.
Select the version of .ERX file you wish to create.
Click OK to perform the export.

Figure 4: Exporting from Visio 2000 Enterprise to ERwin .ERX format
The results of the export are presented in the Output anchored window on the Import/Export tab. Issues discovered on export are noted; along with what action the Visio Database Solution did with regard to the issue.

Figure 5: Visio 2000 Enterprise export to ERX format results

Import VisioModeler (formerly InfoModeler) files

The import of VisioModeler files is a migration path enabling previous users of InfoModeler and VisioModeler to leverage their existing models and move them forward into the Visio Database Solution. The Visio Database Solution was developed from the robust InfoModeler technology. There are three types of files that can be imported into Visio 2000 Enterprise including .IMD, .IML, and .IMO files.

Each of the three file types must be imported into different diagrams in Visio 2000 that correspond to the kind of information in the VisioModeler file: .IMD files are imported into a Database Model Diagram, .IML files import into an ER Source Model, and .IMO files can be imported into an ORM Source Model. Because Visio 2000 Professional does not include ER Source Model or ORM Source Model diagrams, only .IMD files can be imported into Visio 2000 Professional.

Import supports the following versions of VisioModeler (Infomodeler) files: 1.5, 2.0, 3.0 or later

To perform the import

Open a new Visio Database Model Diagram, ER Source Model, or ORM Source Model depending on the type of VisioModeler file you wish to import.
Select Import (Import/Export in Enterprise) from the Database menu.
On the Import menu, select one of the following, as appropriate:
- Import VisioModeler IMD model
- Import VisioModeler IML model
- Import VisioModeler IMO model
Browse to the file by clicking on the Browse button or enter a full name and path directly into the form.
Click OK to perform the import.

Import Visio 5.0 Professional ER or Crow's Foot diagrams

Visio Professional 5.0 contains a database solution for creating ER and Crow's Foot diagrams. Moving these diagrams into the full-featured Database Modeling solution in Visio 2000 allows users to leverage and expand upon work created in Visio Professional 5.0. This import is available both in Visio 2000 Professional and Enterprise.

To perform the import

Open a new Visio Database Model Diagram or ER Source Model.
Select Import (Import/Export in Enterprise) from the Database menu.
On the Import menu, select Import Visio Database Diagram.
Browse to the file by clicking on the Browse button or enter a full name and path directly into the form.
Click OK to perform the import.

Import Visio 5.0 Enterprise Database Model Diagrams

Database Model diagrams created in Visio Enterprise 5.0 may be opened in both Microsoft Visio 2000 Professional and Enterprise editions. A version 5.0 file is automatically converted to the 2000 format when the file is opened in Visio 2000. To save in the 2000 format, be sure to save the file. In addition to the file format itself being converted, Database Model diagrams may need to have shapes migrated to newer versions of the shapes. This also can be done automatically when the file is opened. If there are shapes that need to be migrated, you are presented with a dialog asking whether you wish to create a backup copy of the diagram before migration. When the migration is complete, a log of the file changes can be viewed.

Visio Enterprise 2000 includes the ability to reverse engineer from and generate to the Microsoft Repository using the Microsoft Database Model Type Information Model (DBM TIM). This allows for the use of this open standard for the exchange of model information that is independent of the physical database implementation. This method of exchange is likely to give better results for ongoing integration opportunities where each tool operates independently on the common repository with the schema information.

Using the Microsoft Repository may be an excellent option for integrating with other tools if:

The other modeling tools support the Microsoft Repository and Microsoft Database Model Type Information Model (DBM TIM) for both forward and reverse engineering.
There is no specific import/export route into the Visio Database Solution from the tools.
There is an ongoing integration where both tools are used to operate on the same model data through time.

Note: Of the Microsoft Visio products discussed in this paper, the only product that supports the Microsoft Repository functionality is Microsoft Visio 2000 Enterprise Edition. No edition of Microsoft Visio 2002 supports the Microsoft Repository for the Database Solution.

The operations used with this integration are reverse engineering from, and forward engineering to, the Microsoft Repository. The current schema is maintained within the Microsoft Repository. Each tool reverse engineers the schema from the Repository then updates the schema in the Repository after making changes to the model data.

Figure 6: Sharing models using the Microsoft Repository

The initial model data may be placed into the Microsoft Repository from Visio or another modeling tool. In Visio, use the Generate Wizard, from the Database menu, for the initial population of the repository. During the initial population, the Generate Wizard allows for the creation of a blank Jet-based repository and adding of the Microsoft Database Model Type Information Model (DBM TIM).

After the initial population of the schema in the Microsoft Repository, updates of the schema from the Visio Database Solution are handled through the Update Database Wizard. Selecting Update from the Database menu launches the Update Database Wizard. The Visio drawing may also have the model refreshed against the current state of the schema by using the Refresh Model Wizard. Selecting Refresh from the Model sub-menu on the Database menu opens the Refresh Model Wizard.

The Visio Database Solution includes three-way compare technology that allows for checking the current model in a drawing against an image of the model state when the database was last updated from the drawing as well as the current schema in the database. The differences are shown and you are presented with the ability to resolve the conflicts when using the Model Refresh or the Update Database Wizard. If you know that resolving the conflict requires a change to the database, you should use the Update Database Wizard, which can update the model or the database.

Note: The scope used in the three-way compare is limited to those tables reverse engineered from the database or present in the model during an update of the database. For example, if a table is added to the schema by another tool, the three-way compare does not indicate the added table as a conflict to resolve. This design enables you to focus on small parts of a database without being confronted with unnecessary clutter from changes in the database not directly relevant to your area of concern. Although this is a benefit for large projects where multiple people manage different parts of the schema, it may lead to confusion when managing the schema from multiple tools. It is recommended that a new reverse engineer be performed to include new schema elements into your Visio Database Model.

Reverse engineering from the Microsoft Repository with Visio Enterprise assumes that a repository file exists and there is a schema already stored in the repository. Forward engineering allows creation of a repository file as well as the addition of the Microsoft Database Model Type Information Model (DBM TIM) and schema. Instructions for interacting between the Visio Database Solution and the Microsoft Repository are included below.

To reverse engineer from the Microsoft Repository using the Reverse Engineer Wizard

Open a new Microsoft Visio Database Model Diagram, ER Source Model or ORM Source Model.
Launch the Reverse Engineer Wizard by choosing Reverse Engineer from the Database menu.
Choose Microsoft DBM Repository from the Installed Visio Drivers drop-down list then click Next.
Connect to the Microsoft Repository either by selecting the file, or DSN options and logon credentials. Then click OK.
Select the particular schema to extract from the repository then click OK.
Choose the object types to reverse engineer then click Next.
The following screens allow sub-selection of the object types reverse engineered such as the tables and views and stored procedures. Click Next on each screen after making your selections until reaching the summary screen.
On the Reverse Engineer Wizard summary screen, click Finish.
The tables and views can be dropped onto the drawing from the Tables Window if working in the Database Model Diagram or ER Source Model. Facts maybe dropped out onto the page if working in an ORM Source Model.

Using the Database Update Wizard

Open your Database Model Diagram containing the model data to update the schema.
Launch the Database Update Wizard by selecting Update from the Database menu.
Ensure that the option Update the database is selected.
Select the option Detect changes in the database if you wish to invoke three-way compare, and then click Next.
Choose Microsoft DBM Repository from the Installed Visio Drivers drop-down list, and then click Next.
Connect to the Microsoft Repository either by selecting the file or DSN options and logon credentials, and then click OK.
View the list of conflicts and determine which ones to change in the database, which to change in the model, and those you do not wish to have changed. Each conflict can be clicked on and the action to take determined by the radio buttons in the Resolution frame. Once the conflicts are marked for resolution, click the Next button.
The following two screens provide summary information as to what will change in the model (the Database Model Diagram), and what will be changed in the database. Click Next on each screen if the information is correct.
The wizard then performs a physical validation of the changes to ensure that there are no errors. Click Next to continue if physical validation is complete without errors.
The wizard then performs the changes and presents you with the option of viewing the DDL if you specified in the wizard to generate DDL.

Visio Professional supports reverse engineering but not forward engineering. For Visio Professional, you can go from another tool into a commonly supported DBMS and then into Visio Professional. This may offer a good migration path from other tools and existing models into Visio Professional.

Visio Enterprise 2000 includes the ability to reverse engineer from and generate to many DBMSs.

Working with multiple applications in an on-going integration situation (many trips to and from each application for the same model) is best handled going through a common middle ground, a defined standard, preferably one in which each application has as part of it's core competency. For the Visio Database Solution, and probably for many relational database modeling tools, this core competency is reverse and/or forward engineering to a data source for some specific DBMSs. Find the commonly supported DBMSs and you have likely candidates for sharing the model.

Once you find a commonly supported DBMS, you can generate a test or exchange the database that you use for the shared schema. To find which DBMSs are supported for the Visio Database Solution, open a new Database Model Diagram and select Drivers from the Options sub-menu on the Database Menu. The list of Installed Visio Drivers shows the DBMSs supported. Two generic drivers are listed: the ODBC Generic Driver and the Generic OLE DB Provider. The remaining drivers in the list are specific to a DBMS and will give better results than the generic drivers.

Figure 7: Visio 2000 Supported DBMSs

Note: Select the intermediate DBMS with care. It is important to ensure that the features you are concerned with are handled by the DBMS. For example, if you were concerned with triggers and stored procedures, then Microsoft Access would not be the best choice of an intermediate DBMS because Access does not support triggers and stored procedures.

Logically, this sharing is similar to the option discussed in the section Share models via the Microsoft Repository using the Database Information Model. The difference is really what DBMS you are using for the storing the schema.

Figure 8: Sharing models using a commonly supported DBMS

Similar to sharing models in the Microsoft Repository, the actions performed from each tool involve reverse engineering from the DBMS and forward generation (updates) from the tool back to the DBMS. The current schema is maintained in the DBMS. From within Visio Enterprise, the wizards used to accomplish these tasks are the Generate Wizard (for initial population of the schema if it is coming from Visio), the Update Database Wizard, and the Reverse Engineer Wizard.

Visio Professional 2002 Database Solution

Like Visio 2000 Professional, Microsoft Visio 2002 Professional includes the ability to import ERwin .ERX files. The versions supported for import include 2.6, 3.0, and 3.52.

Import Computer Associates ERwin .ERX File

The import brings in ERwin model elements including entities, attributes, relationships, categories and also text blocks. Data types may be converted to either portable data types for the specific DBMS defined in the .ERX file or to a default data type. For the conversion of data types to the portable data types for the DBMS, Visio must have an equivalent Visio Driver for the specific DBMS.

Importing an .ERX file does not bring in certain ERwin features. ERwin displays, stored procedures, and custom trigger code are not imported.

To perform the import

Open a new Visio Database Model Diagram or ER Source Model (Enterprise Only).
Select Import from the Database menu.
On the Import menu, select Import ERwin ERX model.
Browse to the file by clicking on the Browse button or enter a full name and path directly into the form.
Click the OK button to perform the import.

Figure 9: Importing ERwin .ERX files into Visio 2002 Professional

After the import is complete, the tables brought in will be shown in the Tables and Views window. Drag and drop the tables you want in your diagram onto the page.

Import VisioModeler (formerly InfoModeler) Files

The import of VisioModeler files provides a migration path enabling previous users of InfoModeler and VisioModeler to leverage their existing models and move them forward into the Visio Database Solution. The current Visio Database Solution was developed from the robust InfoModeler technology.

There are three types of VisioModeler files including .IMD, .IML, and .IMO files. Based on the type of information in the files and the corresponding file types in Visio 2002 Professional, only .IMD files may be imported. The other VisioModeler files correspond to ER Source Models and ORM Source Models that are found in Visio 2000 Enterprise Edition and can be imported into a 2002 version using additional Database Modeling functionality to be packaged with other Microsoft product offerings such as Visual Studio.

Import supports the following versions of VisioModeler (Infomodeler) files: 1.5, 2.0, 3.0 or later.

To perform the import

Open a new Microsoft Visio Database Model Diagram.
Select Import from the Database menu.
On the Import menu, select Import VisioModeler IMD model.
Browse to the file by clicking on the browse button or enter a full name and path directly into the form.
Click the OK button to perform the import.

Figure 10: Importing VisioModeler .IMD files into Visio 2002 Professional

Import Visio 5.0 Enterprise, Visio 2000 Enterprise and Professional Database Model Diagrams

Database Model diagrams created in Visio Enterprise 5.0, Microsoft Visio 2000 Professional and Enterprise editions may be opened in Visio 2002 Professional. You may be prompted regarding enabling macros for Microsoft Visual Basic for Applications (VBA). Since the VBA project associated with a Database Model Diagram should be empty, it is not necessary to enable the macros.

When a database drawing from a previous version of the Visio Database Solution is opened, the shapes in the drawing need to be updated to a later version. You are prompted when older shapes are found and asked whether you wish to save a backup copy of the file as well as create a report of all changes made during the update.

Like Visio 200 Professional, Visio 2002 Professional includes reverse engineering capabilities but does not include forward engineering capabilities. Given this, it is possible to use the reverse engineering capability as a powerful migration tool for moving existing models from other tools into Visio 2002 Professional. The key to this migration is finding a commonly supported DBMS. Once one is identified, the model data can be forward engineered into the intermediate DBMS from another modeling tool and then reverse engineered into Visio 2002 Professional.

Note: 'Enterprise' level functionality such as forward engineering capabilities are to be available with other Microsoft Product offerings such as Visual Studio.

To find the Visio 2002 supported DBMSs, open a new Database Model Diagram and select Drivers from the Options sub-menu on the Database Menu. The list under Default driver for Visio gives the DBMSs supported. Two generic drivers listed are the ODBC Generic Driver and the Generic OLE DB Provider. The remaining drivers in the list are DBMS specific and provide better results than the generic drivers.

Figure 11: Visio 2000 Supported DBMSs

About Visimation

Visimation is a Microsoft Certified Partner who specializes in Microsoft Visio consulting, custom development, training, and services.

https://www.visimation.com/