Toad Data Modeler 6.4 - User Guide

Compare Models, Generate Change Report

Toad Data Modeler allows you to view differences between two models and for physical models you can generate change reports in HTML, RTF and PDF formats. Change reports are not available in logical models.

You can compare your models and generate the change report via the Model Actions.

How to Compare Models and Generate Change Reports

• Click on Compare and Generate Report | Run in Model Menu or Run Compare and Generate Report Action in Model Actions.

• Select another model for comparison. It can be either a model opened in Toad Data Modeler or a model loaded from Model File.

• Check Resolve Application Variables to resolve variables during generation including variables in names
• The next dialog presents you with a couple of setting related to Model Comparison. When you're done editing the options, click on Next.

• Now you have to choose what types of objects will be included in the comparison. If you wish to select objects in more detail, click on Detailed Settings.

• You are now presented with Compare Tree dialog displaying all differences between the two given models. Now you have the option to generate a report by clicking on Report button.

• Check the Review screen which shows you the final statistics and close Model Compare by clicking the Finish button.

• Select Format of the generated report.
• There are two options available during the entire process of generating a report. Hide Log hides the bottom part of the dialogs which otherwise displays information related to the generation. Checking Close after Execution closes the dialog window after you're done creating your report.

Generating HTML Reports

• After you select HTML from previous dialog and click on Next, you have the option to change Report Caption and Language of the report.

• You are able to define your own path where the generated report will be saved, if you need to.

• In this dialog there are several options for customizing the look of the report.

• Now is the time for you to decide what kind of information should the generated report contain. If you want to get a complete report, check all boxes. For easier management use buttons located on the top.

• The final dialog where you can set a few last options.
• Click on Execute to generate the report. A dialog window will inform you when the process is done.

Generating RTF and PDF Reports

• On this dialog you are able to change the report caption and its language.

• You are able to define your own path where the generated report will be saved, if you need to.

• A few options regarding the report look are available here. Also note the option to insert text or image watermark.

• Now is the time for you to decide what kind of information should the generated report contain. If you want to get a complete report, check all boxes. For easier management use buttons located on the top.

• A few final options are available here. For example, you can decide between generating Summary only, Details only, or both.
• Click on Execute to generate the report. A dialog window will inform you when the process is done.

What to Report Dialog Buttons

Compare Tree Overview

Option	Description
	Generate a Change Report.
	Expand All.
	Collapse All.
	Go to next object in the tree.
	Go to previous object in the tree.
	Display options for objects.
	Display options for properties.
Source Model	The source model to which the second model is compared to.
Model to Compare	The model which is compared to the source model.

Compare Tree Icons

Difference between Source model and Compared model.

Right-click an item to see the following options:

Model Update

Toad Data Modeler allows you to update models to synchronize changes between your database and your model.

Example: You loaded the database structure of your database to Toad Data Modeler (Reverse Engineering). - Model A was created. Then you made some changes in your database and now you want to update the Model A. For this purpose, you can use the Model Update feature.

Model Update includes operations such as Connections, Model Comparison and Model Merge

To update your model

• Open the model that you want to update.

• Click the button on the Menu Toolbar or Select Model | Update Model from Database to open the Model Update Wizard

• Select Connection from the list of stored Connections. If you don't already have one, see How to Set Up A New Connection.

• Select the objects you want to update in your model. You can use the Filter and Select All/Deselect All/Invert Selection buttons for easier object management. Also note that you can Export or Import your selection to or from a file. After you're done selecting, click on Execute button.

• Before Toad Data Modeler compares your model with the database, you need to go through some settings related to comparison.

• Here you can select what types of objects should Toad Data Modeler compare between given model and the database. If shown settings aren't detailed enough for you, click on the Detailed Settings button. This will allow you to choose not only from all groups of objects, but from the objects themselves. To update everything in your model, check every checkbox or select Compare All.

• After you're done with selecting the objects, proceed to the next dialog.

• You are now able to see the Compare Tree. It displays all differences between your current model and the database. The Model Update changes the model on the right (your model) so it matches the model on the left (the database). Check the changes you would like to execute and proceed to the next dialog.

• The last dialog shows you the review of the Model Comparison. An option to Merge Model is available in case you don't want to only update your model. This option creates a new model which will appear in your Application View after finishing the update. Click on Finish and after a short while your Model will be updated.

Compare Tree Overview

Option	Description
	Import selection from a saved file.
	Save selection to a file.
	Runs the verification process. The verification might return a warning . Details are available in Verification Log.
	Checks all items.
	Unchecks all items.
	Checks all items that exist in the Source model, but not in the Target model (CREATE).
	Checks all items that does not exist in the Source model, but exist in the Target model (DROP).
	Checks all items that exist in both models, but are different (ALTER).
	Opens the Wildcard Dialog where you can define settings for bulk selection/deselection of the Action box of the items listed on page Select Items.
	Refresh Necessitated Items Explanation: Some objects are related together (e.g. entity and domain, entity and relationship). Let's say you uncheck a Domain in Select Object Types dialog. However you keep an Attribute of the Domain type checked for conversion. In the next screen the Domain will be selected for conversion (and highlighted in gray), even if you don't want it to. This is because of its relationship with the Attribute, which cannot exist without the Domain. Now, if you uncheck the Attribute, the Domain will still be checked for conversion. This is where you use this button. It runs through all checked objects and removes the Domain highlighted in gray since the Attribute is no longer checked. That means the Domain is no longer necessary, since it has no relationships with currently checked objects and you unchecked it in Select Object Types dialog.
	Expand All.
	Collapse All.
	Go to next object in the tree.
	Go to previous object in the tree.
	Display options for objects.
	Display options for properties.Source
Source Model	The source model (often the database).
Model to Merge	The model which is going to be updated.
Operation	Check this checkbox toupdate the item. Uncheck this checkbox to not update the item.

Compare Tree Icons

Difference between Source model and the updated model.

This object exists in Source model but does not exist in the updated model.

This object is missing in Source model but exists in the updated model.

Right-click an item to see the following options:

Option	Description
Check All Children	Checks the Operation checkbox of all children items.
Uncheck All Children	Unchecks the Operation checkbox of all children items.
Check All Children to Add	Checks the Operation checkbox of children items that exist in Source model but not in the Target model ( items) to generate the CREATE statement.
Check All Children to Remove	Checks the Operation checkbox of children items that are missing Source model and exist in Target model ( items) to generate the DROP statement.
Check All Modified Children	Checks the Operation checkbox of children items where properties differ (items) to generate the ALTER statement.
Uncheck All Children to Add	Unchecks the Operation checkbox of children items that exist in Source model but not in the Target model ( items) to not generate the CREATE statement.
Uncheck All Children to Remove	Unchecks the Operation checkbox of children items that are missing Source model and exist in Target model ( items) to not generate the DROP statement.
Uncheck All Modified Children	Unchecks the Operation checkbox of children items where properties differ (items) to not generate Change Script for this change.
Wildcard Filter	Opens the Wildcard Dialog where you can define settings for bulk selection/deselection of the Operation box of the items listed on page Select Items.
Expand All Children	Expands all sub-items of the selected item.
Collapse All Children	Collapses all sub-items of the selected item.

Synchronization

Toad Data Modelerhelps you with synchronization tasks and allows you to:

• Generate SQL scripts representing changes made to your model
• Transfer changes made in your database to your existing physical models
• Keep your model and your database synchronized and up-to-date during the development process

Database and Model Synchronization

Situation A - Generation of Change Scripts

You have made changes in your physical ER diagram in Toad Data Modeler and now you need to apply the changes to your database.

Solution: Compare the models and generate Alter Script in Toad Data Modeler. Then use a third party software to connect to your database and execute the generated script. All your changes are now reflected in database. (Note: Change Script generation is not supported for all databases, for further information see Supported Databases.)

Situation B - Model Update

You or someone else made changes to your existing database (development version of database) and you need to transfer the changes to your model.

Solution: Use the Model Update or Model Merge features to update your physical model.

Physical Model and Logical Model Synchronization

You have created a Logical model in Toad Data Modeler and need to convert the logical model to physical model for the selected database system.

Solution: Use the Model Convert or Simple Model Conversion feature for logical (LER) to physical (PER) model conversion.

Limitations

There are many factors that affect synchronization features in Toad Data Modeler. For example:

• Data types can be defined through logical types that do not exist in physically existing databases, such as Domains, Dictionary Types etc.Toad Data Modeler has to correctly resolve the data types when comparing attribute (table column) properties.
• Objects must be paired by specific rules, for example in your Oracle Database model, schema assignment is not required, but in your database, schema is always present.
• There can be application variables defined in your models, for example <%ParentTableName%> and the application variables must be resolved during the comparison as well.

In result, the synchronization of your model and your database is more complicated than comparison of two physically existing databases. From technical point of view, the comparison is based on more complicated algorithm that requires more resources.

Recommendations

• Use the synchronization features on models of small to average size. If you work with large models, try to split the model to smaller parts (separate models), if possible.
• Use specialized software for comparing and synchronizing two large physically existing databases. You could theoretically reverse engineer both databases, create two models, compare them and try to generate change scripts, but due to limitations mentioned above the process would require more resources and take more time. Other products, that do not work with models, but rather with databases (e.g. Toad for Oracle, Toad for SQL Server, etc.), can offer better performance and additional benefits - like comparison of physical properties and data. Toad Data Modeler is a tool primarily used to create a visual representation of database structure and it should be used during database development. It is not an alternative or competitive tool for specialized schema comparison.

Model Size Limitations

• Toad Data Modeler doesn't have fixed limitations based on number of objects. You can work with models that contain hundreds, or thousands of tables. Please note that large number of objects in a model may hinder performance significantly, depending on your computer specifications. Number of other items in your model (stored procedures, functions, sequences etc.) affects the performance as well. Other factors include:
  • Type of task - you can model large database structure, generate complex SQL scripts or detailed reports, but synchronization tasks might reach the application limits sooner than generation of SQL scripts, for example.
  • Number of running processes on your machine.
  • Limited access to Microsoft Scripting Engine (standard part of operating system, however in some situations access can be monitored by antiviral software etc.)

Print

Tips before You Print

• To set size of the pages, select File | Page Setup. To customize the page size, see the Page Setup dialog | Scale area | and from the Page box, select Custom. Define Height and Width on the right.
• To print your ER diagram on one page, select File | Page Setup | select the Fit to Page checkbox.
• Turn off the display of page boundaries - select Settings | Options | Graphics | clear the Visible Page Boundaries checkbox.
• In Settings | Options | General, clear the Print Gradients checkbox for much faster print performance. (It is disabled by default.)
• Turn off page numbering and a frame around your ER diagram - select File | Print | Settings tab | clear the Print Frame and Print Page Number checkboxes.

To print your model

1. Click   on the toolbar (or select File | Print).

2. Select a printer and click Properties for more configuration options.
3. On tab Settings, select from the available options.