Step-by-Step Guide to Reverse Engineering a Database in Visio 2000

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Visimation, Inc.
Microsoft Certified Partner

November 2001

Applies to:
   Microsoft Visio Enterprise 2000
   Microsoft Visio Professional 2000

Summary: This article presents step-by-step guidelines for performing the reverse engineering process and creating your Visio diagram. (13 printed pages)

Contents

Introduction
Major Steps
Step 1. Prepare the Data Source
Step 2. Open Appropriate Solution in Visio 2000 Enterprise or Professional
Step 3. Setup the Appropriate Visio Driver
Step 4. Run the Wizard for Reverse Engineering
Step 5. Save Your Visio Drawing
Step 6. Create Your Diagram(s)

Introduction

Microsoft®Visio® 2000 Enterprise Edition and Professional Edition both include solutions for reverse engineering a database schema into Entity Relationship (ER) diagrams. In addition, Visio Enterprise includes the ability to reverse engineer into an Object Role Modeling (ORM) model as well as the ability to generate databases from ER or ORM diagrams.

The reverse engineering technology is geared primarily towards Relational Database Management Systems (DBMS) using Open Database Connectivity (ODBC), but can also reverse engineer through OLE DB.

This document presents a step-by-step walkthrough of the reverse engineering process.

Major Steps

Listed below are the major steps required to reverse engineer a database schema into Visio:

  • Prepare the data source
  • Open the appropriate solution in Visio 2000 Enterprise Edition or Professional Edition
  • Setup the appropriate Visio driver
  • Run the Wizard for reverse engineering
  • Save your Visio drawing
  • Create the diagram

Step 1. Prepare the Data Source

In most cases, the Visio database solution connects to the database solely through ODBC. With certain Database Management Systems (DBMS), the solution speaks directly to the DBMS client software. In Visio Enterprise, you can also connect through OLE DB. In any of these situations it is necessary for you to ensure that the database is accessible from your system and that you have the necessary credentials to connect to the data source. This may involve making sure that DBMS client software is installed on your system as well as communicating with your database administrator regarding permissions or other database specifics.

Setting up an ODBC data source can be accomplished using the ODBC Data Source Administrator. To open the ODBC Data Source Administrator in Microsoft Windows® 2000, go to Control Panel and select Administrative Tools. In the Administrative Tools, double-click on Data Sources (ODBC).

This takes you to where you can view existing data sources defined in ODBC. The Administrator screen also allows you to create new data sources, check ODBC driver versions, run ODBC traces, and other ODBC management tasks.

Figure 1. ODBC Data Source Administrator

To add a data source, select the appropriate tab, User DSN or System DSN, and then click Add. This takes you to a screen for selecting the ODBC driver to use for the data source. If you do not see a suitable driver in the list, then you may need to install ODBC drivers from either Microsoft or a third party vendor.

Figure 2. Create New Data Source

After you select the driver and click on the Finish button, you are taken to the specific settings for the ODBC driver. The screens that follow vary by driver and you may wish to consult help from the driver vendor for any questions encountered setting the options for the driver.

**Note   **The Visio database solution supports User DSNs and System DSNs. It does not support connections using File DSNs.

Troubleshooting Tip

If you are getting ODBC errors when reverse engineering a database into Visio, then it is likely that the data source should be checked. Many of the ODBC drivers allow you to test the connection to the data source. This can be done from the ODBC Data Source Administrator by selecting the data source and then clicking on the Configure button. The specific screens that follow vary by driver.

Another way to test the ODBC data source connection is by using Microsoft® Access. You can create a new .MDB and then use the Table Link feature to point out to the data source.

A good troubleshooting step is to create a new data source as part of your troubleshooting. Also note the driver version and manufacturer as you may wish to see if later drivers are available or if a driver from a different company gives better results.

If the troubleshooting steps above do not provide enough information, you may wish to turn on the ODBC tracing from the ODBC Data Source Administrator. Be sure to turn it back off when you are done troubleshooting.

Step 2. Open Appropriate Solution in Visio 2000 Enterprise or Professional

The instructions for opening up the database solution vary between Visio Professional and Enterprise. For both, start by opening up Microsoft Visio. Although you can navigate to the proper templates from the initial Welcome to Microsoft Visio screen, these instructions assume you are starting from the File menu. You can cancel from the Welcome screen to access the File menu.

Regardless of which product you are working with, the Database menu item indicates that the Database solution is opened and running. You may also see one or more anchored windows displaying.

Figure 3. Database Menu Item

Starting the Database Solution in Visio Professional

In Visio Professional, you open the Database solution from the File menu by choosing New, then pointing to Database, and clicking Database Model Diagram.

Figure 4. Starting the Database Solution in Visio Professional (Click for larger image)

Starting the Database Solution in Visio Enterprise

With Visio Enterprise, when you open the Database solution you have the choice of diagramming with ER or ORM. Which Visio template you open determines the methodology. In addition, Visio Enterprise adds another element to this choice—project files.

ER Diagram

To reverse engineer to an ER diagram with the Visio Enterprise Database solution you may select either Database Model Diagram or ER Source Model from the Database menu under File and New. Either option takes you to a new Visio drawing from which you can reverse engineer a database. The decision as to which option you choose depends on whether you will make use of the project features and whether you will be forward engineering a database from the diagram. You may wish to select the Database Model Diagram if you wish to explore the Database solution functionality. More information about projects and source models can be found in the Visio Help files (on the Help menu, click Microsoft Visio Help).

Figure 5. Starting the Database Solution in Visio Enterprise, ER Template Options (Click for larger image)

ORM Diagram

To start an ORM diagram, on the File menu, point to New, point to Database, and then click ORM Source Model.

**Note   **The ORM Source Model in Visio Enterprise supports forward and reverse engineering. The ORM Diagram found in the Visio Professional edition allows drawing of ORM diagrams only and does not support forward and reverse engineering. In addition, there is no conversion utility available to migrate ORM Diagrams created in Visio Professional into ORM Source Models in Visio Enterprise.

Figure 6. Starting the Database Solution in Visio Enterprise, ORM Template (Click for larger image)

Step 3. Setup the Appropriate Visio Driver

For the Visio database solution to reverse engineer the structure (as well as forward engineer), it needs to supplement the information returned from the ODBC drivers. Each DBMS implements varying degrees of the SQL standards and the information returned through the ODBC drivers for a DBMS may not be complete enough to uncover all the details of the structure. These differences are even greater when dealing with forward engineering.

This is where the Visio "Driver" comes into play. It operates between the Database solution and ODBC to enhance structure information. The typical communication path between the Database solution and the DBMS is shown in Figure 7.

Figure 7. Typical communication path for the database solution

Selecting the Visio Driver

To set the Visio Driver, on the Database menu, point to Options, and then click Drivers.

Figure 8. Selecting the Visio Driver (Click for larger image)

This takes you to a screen where you can choose from the installed Visio Drivers. Select the driver that matches your DBMS. If your DBMS is not listed, you may choose to use the ODBC Generic Driver. Also, if using Visio Enterprise you may choose to go through OLE DB with the Generic OLE DB Provider.

**Note   **Using the Generic ODBC driver may not give complete information for reverse or forward engineering.

**Note   **If you can use either a specific Visio Driver and ODBC or OLE DB with your DBMS, you will get better results reverse engineering using the specific Visio Driver and ODBC.

Figure 9. Visio Driver Selection

After selecting your Visio Driver, click Set as Default, and then you need to set some options for the driver. While the Visio Driver is selected, click on the Setup button to go to the options for the driver. It is within these screen tabs that you tell the Visio Driver information specific to the DBMS—for such things as the DBMS version and mapping of portable (conceptual) to physical data types. The information contained in the Preferred Settings tab will vary by the DBMS. The Preferred Settings tab for both Microsoft Access and Microsoft SQL Server are shown in Figure 10.

Figure 10. Visio driver settings (Click here for larger image)

Step 4. Run the Wizard for Reverse Engineering

Note   ** The sample database used in the following screens is the NeuCom database (NeuCom database.mdb) included with Microsoft Visio 2000 Professional and Enterprise Editions. The samples are not loaded as part of the default installation. If you wish to install the samples for Visio then you should go to Control Panel and choose Add/Remove Programs**, select Microsoft Visio 2000 then click on the Change button. Depending on how it was installed it may require that you have the Visio CD. This takes you to the Visio Installation manager where you can choose to Add/Remove components. Select the Sample Drawings to be added.

Now that you have the data source setup and the Visio Driver configured, the next step is to run the Reverse Engineer Wizard.

To launch the Reverse Engineer Wizard, select Reverse Engineer from the Database menu.

Figure 11. Launching the Reverse Engineer Wizard

The first screen of the Wizard allows you to select the data source to reverse engineer and the Visio Driver to use. It also allows you to detour from the Wizard for setting up the data source, which we covered in Step 1, as well as setting up the Visio Driver, which we covered in Step 3. With these steps already done, all that is required is to select your data source from the list and choose Next.

Figure 12. Selecting your data source in the Reverse Engineer Wizard (Click for larger image)

**Note:   **To create a connection, you can use the Wizard to specify an installed Visio driver and the ODBC data source. However, for best results, set your default driver to the target database you want to reverse engineer before you run the wizard.

After clicking Next you may be presented with a logon screen for the data source. Enter your logon credentials and click OK.

Figure 13. Data Source Logon Screen

The following screen of the Reverse Engineer Wizard allows you to choose what types of objects you want reverse engineered from the database. If an item is grayed out, then the DBMS does not support that type of object. Make your selections then click Next.

Figure 14. Selecting objects to reverse engineer (Click for larger image)

Next you are presented with a listing of all the tables and views in the database. Tables are designated with a capital T and views with a capital V. Select the tables and views you wish to have reverse engineered then click Next.

Figure 15. Selecting tables and views to reverse engineer (Click for larger image)

The final screen of the wizard presents a summary of what tables and catalog information will be reverse engineered. If the items are correct then click Finish to have the database solution collect the requested information.

Figure 16. Reverse Engineer Wizard summary screen (Click for larger image)

After you click Finish, the Wizard disappears and leaves you back at your Visio page.

If you are working with an ER diagram, at least two anchor windows should be showing in your drawing window, Tables and Output. The Output window shows the results of the reverse engineering on the Import/Export tab and the Tables window lists all of the tables and views reverse engineered.

**Tip   **To show the tables and views in physical order or alphabetical order you can right-click on the Tables window and toggle the Sort option.

Figure 17. Output window showing results of reverse engineer (both ER and ORM) (Click for larger image)

Figure 18. Tables window listing tables and views reverse engineered for ER diagram

If you reverse engineered into an ORM drawing with Visio Enterprise, the Output window shows as well as the Business Rules window. The Business Rules window shows the facts derived from the database structure.

Figure 19. Business rules window shows facts in the ORM model (Click for larger image)

Step 5. Save Your Visio Drawing

After you get to this point in the reverse engineer process, it is recommended that you save your Visio drawing. Although the drawing page does not show any information on it yet, all of the reverse engineered information has been stored as model information with the Visio drawing.

Step 6. Create Your Diagram(s)

The final step in reverse engineering is getting the graphical representation of the database. The process for this varies for ER diagrams versus ORM diagrams and is covered separately. For both, it is important to recognize that the data on the drawing page is a representation of the model data that is already stored in the Visio drawing.

ER Diagrams

Now that you have all of the information from the database structure stored as a model in the Visio drawing you can select the portions of the database you wish to diagram and easily add these to the page. Dragging the tables and views from the Tables window onto the drawing page is the way to do this. You can select individual tables and drag them onto the page or multi-select and drag a group of tables onto the page. As you drag tables onto the page the relationships between the tables are drawn automatically.

One feature available that makes diagramming your database very easy and efficient is to drag a table that you care about onto the page; then right-click on the table and chose Show Related Tables. This draws all the tables and relationships on the drawing associated with the original table. This feature also makes it simple to diagram databases where you do not necessarily know the relationships before hand.

Figure 20. Choosing Show Related Tables for easy ER diagramming

ORM Source Model Diagrams

With ORM diagrams, the idea is similar to drawing with ER diagrams. You simply drag out the items you wish shown in the drawing onto the drawing page. For this you can drag entire Facts from the Business Rules window or you can open up a window containing the Objects and drag those onto the page. The Object Types window can be opened from the View menu in the Database menu. The Objects have similar right-click shortcut menu features to the ER Show Related Tables. For Objects, you can right-click and choose Show Relationships from the shortcut menu to draw all the Facts that the Object takes part in.

About Visimation

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