Migrate ERwin data models to Rational Data Architect

IBM® Rational® Data Architect delivers unique features not available in many of today's data modeling tools in the market, including CA ERwin. Migrate existing data models created in CA ERwin Data Modeler to Rational Data Architect, and explore some of Rational Data Architect's features data modelers and data architects use on a daily basis.

Share:

Denis Vasconcelos (denisv@br.ibm.com), Data Specialist, IBM

Denis VasconcelosDenis Vasconcelos is a Data Specialist for IBM Global Business Services in Brazil. He is a co-author of "Tivoli Management Services Warehouse and Reporting" and "IBM Information Analyzer & Data Quality Assessment" IBM Redbook publications. His areas of expertise include database administration, data modeling, heterogeneous database migration, and project management. Denis has a Bachelor's degree in computer science and a post-graduate degree in project management.



13 September 2007

Also available in Chinese Russian

Introduction

Rational Data Architect is a data modeling and integration design tool designed to help data architects understand information assets, map assets to each other, and create integration schemas.

As a data modeler or a data architect, you may need to work not only with a data modeling tool, but also with version control tools, requisite tools, and multiple data models. Many data modeling tools, such as ERwin, don't offer the same degree of integration offered by Rational Data Architect. With Rational Data Architect, you have a tool fully integrated with a suite of applications that cover the entire software development lifecycle, reducing risks, and increasing predictability. Furthermore, you have many enterprise data modeling features available only in Rational Data Architect, such as federated design, unique mapping, and mapping discovery capabilities. The first step to start using these new features available in Rational Data Architect is to migrate your existing data models created in ERwin to Rational Data Architect and get knowledgeable about the features most used in your daily tasks.


Overview of the Rational Data Architect graphical user interface

Product name changeh

On December 16th, 2008 IBM announced that as of Version 7.5.1, Rational Data Architect is renamed to InfoSphere Data Architect to feature its role in InfoSphere Foundation Tools.

Rational Data Architect's GUI is based on Eclipse, an open source software framework written in Java language. If you have used Eclipse or any other Eclipse-based tool, you are already familiar with the user experience provided by that common interface. Rational Data Architect can run on both Windows® and Linux® with the same interface, so you'll feel at home when working with either of the underlying operating systems.

When you first open Rational Data Architect, it automatically sets its environment for a data perspective. You can verify this when the icon, as illustrated in Figure 1, in the right, top side is highlighted.

Figure 1. Rational Data Architect's data perspective selected
Rational Data Architect's data perspective selected

Figure 2, below, shows an overview of Rational Data Architect's environment in the data perspective mode:

Figure 2. Rational Data Architect's environment
Rational Data Architect's environment

In the data perspective, the GUI has six main sections or panes, as follows:

  1. Data project explorer: This pane shows all the projects you have under the same workspace and their objects in a hierarchical structure. You can have different types of projects on the same workspace (in other words, data design and data development projects). The project's objects are created based on your actions (for example, when creating a new data model under a data design project and so forth).
  2. Database explorer: This pane presents all the database connections you have setup, either through Rational Data Architect, or outside it (in other words, a cataloged DB2 database). You can at any time create a new database connection from this pane to any of the supported database systems.
  3. Properties pane: This pane shows the properties of the object that is open and active (in other words, a data model, or an entity inside a logical data model). This pane also has some other tabs besides the main one, properties (for example error log, and so on).
  4. Editor: Every time you open a data model, and or a diagram, they are shown in this pane, located in the center of the screen.
  5. Object palette: It is not really a pane by itself, but is dependent of the previous pane mentioned. This palette has all the objects that can be added to them.
  6. Outline: This pane shows all the objects that exist in the diagram open in the editor pane. You may choose to view this information as a thumbnail of the entire data model, with a grey, shaded area that represents the area visible at the moment in the editor, or you may choose to view it as a hierarchical structure.

Major differences between the tools

Before importing an ERwin file, you need to be aware of some major differences between the tools. In Rational Data Architect you can have multiple projects open in the same workspace, and each project can have multiple data models. This is because Rational Data Architect creates a folder or directory for each project, and the project's objects (in other words, data models) are created into the same directory, if you specify so.

The most important difference is that Rational Data Architect creates the data model in separate files -- one for the logical data model, and another one for the physical data model.

Figure 3. Files approach
Files approach

This different approach of working with data models may make you scratch your head for a moment if you come from an ERwin background, but this same approach is also used by PowerDesigner. This approach forces you to make a synchronization between the logical and physical data models, but this article covers that later. This may be seem like a bad thing at first, but it enables you to generate multiple physical data models from the same logical model. Those physical data models can target different database systems (in other words, DB2, and Informix), environments, or both.

Modeling notations supported by Rational Data Architect:

  • Logical data model
    • IE - Information Engineering
  • Physical data model
    • IE - Information Engineering
    • UML

Some terminology differences:

Table 1. Terminology differences
ERwinRational Data Architect
Stored display and subject areaDiagram
CommentsDocumentation

Migrating the data model

Before importing

Before importing a data model, you must have at least one workspace created and open, so you can import or create a data model.

Let's create a workspace so we can import the ERwin data model.

Select File > Switch Workspace. In Figure 4, below, notice that the screen doesn't talk about creating but switching workspaces. However, if you specify a workspace that doesn't exist, Rational Data Architect will create one for you.

Figure 4. Creating a workspace
Creating a workspace

You have a workspace to work on. Now you need to create a project. But because we are going to work with data models, you need a specific type of project, called data design project.

To create a project, select File > New > Project. You are then presented with the wizard shown in Figure 5:

Figure 5. Creating a data design project
Creating a data design project

Figure 6 shows your recently created project.

Figure 6. Project created
Project created

How to import

The import process itself is simple and straight forward.

To begin the import process, select File > import. The import wizard then opens (see Figure 7).

Figure 7. Import wizard
Import wizard

Select Data Model Import Wizard under the Data category, then select Next.

Figure 8. Import wizard - Data model
Import wizard - Data model

The next screen asks you information about the data model you want to import. The information asked is as follows:

  1. Model format: You need to choose what version your ERwin data model created was among:
    • CA ERwin, Version 3.x (ERX format)
    • CA AllFusion ERwin Data Modeler, Version 4.x (ER1 format)
    • CA AllFusion ERwin Data Modeler, Version 4.x (XML format)
  2. Model: Just specify the file name and full path for the file you want to import
  3. Target project: Specify on what project you are importing your data model
  4. Model type: You can specify if you want to import just the logical or physical data model from the ERwin file, or both, using the auto-select option. This is the best option if you want to import the whole file, without needing to open it, to find out if that file contains only the logical or only the physical data model.
  5. File Name: Specify how you want the new data model file to be named. If you are importing both logical and physical, don't worry because the files are created with the same name but different suffix.
    The suffixes are:
    • ldm - Logical data model
    • pdm - Physical data model

Enter the information needed, and then select Next.

The screen shown in Figure 9 gives you the import options available. (For more information on the import options, see the "Import options" section.) Click Next.

Figure 9. Import wizard - Import options
Import wizard - Import options

The next screen, illustrated in Figure 10, shows you a summary of all errors, warnings, or both, if any at all, found during the import process. Select Finish.

Figure 10. Import wizard - Summary
Import wizard - Summary

The data model has been created under your data design project, named Migrated in the example shown in Figure 11:

Figure 11. Imported model
Imported model

Import options

During the import, you have some import options to select. Let's take a closer look at them:

Figure 12. Import options
Import options

The first part on this screen is the Options and Values table:

  • Apply binary characters filter (Default: True): Valid only when using an XML file as the source file. Specifies if invalid binary characters should be filtered or not from the file before importing. Be aware that applying this filter may remove some Unicode characters
  • Import views (Default: As views): Specifies how views are imported:
    • As View: Import a view as a view object
    • Both as Table and View: For each view in the ERwin model, Rational Data Architect creates a table and view inside the imported data model. This option may be useful if you import the model in the Meta Integration Repository and you intend to use views as sources of a data movement
  • Import IDs (Default: True): Every existing object in the ERwin 4 XML file has an unique id. If you choose True Rational Data Architect, it will set the NativeId property equal to that unique id
  • Import UDPs (Default: As Metadata): A user-defined property (UDP) is a property definition object that has a default value. You can specify how property definitions and values are imported into Rational Data Architect:
    • As metadata: Only import explicit value, and they are set as property value. The default value is kept on the property type only
    • As metadata, migrate default values: Import both explicit and implicit values as property value objects
    • In description, migrate default values: Append the property name and value to the object's description property only even if they are implicit
    • Both, migrate default values: Import the UDP value, both as metadata and in the object's description
  • Import relationship name (Default: From relationship name):
    Specifies how Rational Data Architect should name the relationships imported. You can use this if you want the name more descriptive or not:
    • From relationship name: From the existing name property in ERwin file
    • From relationship description: From the description property located in the ERwin file
  • Import subject areas (default: Imported as diagrams):
    You can specify how Rational Data Architect will handle subject areas from ERwin
    • Imported as diagrams: Import as diagrams only. If you choose this option, you will have only one package named the same as the original data model name and all subject areas imported as diagrams inside this package
    • Imported as packages and diagrams: Import as packages and diagrams. You can use this option when you want a package and digram created for each subject area
    • Do not import subject areas: Do not import subject areas

The second part on this screen has:

  • Validity check (Default: basic validity check): The validity check verifies that the data model follows the semantic rules for the model format
  • Target database (Default: Auto Detect): Select the database system that you want to have as target for the generated model. If you choose Auto Detect, Rational Data Architect will use the same DBMS specified in the ERwin file

Model imported

You now have the data model imported. Try navigating through the panes, and check your objects. The next section of the article discusses the features that you will probably use in a daily basis as a data modeler.


Most used features

This section covers the features most used by a data modeler on a daily basis.

Now that you have the data model imported and created, and you already navigated on it, let's take a look at the main features a data modeler uses in a daily basis. Understanding these feature may help you feel more comfortable navigating and working with the tool.

Transformation

As mentioned before, the logical and physical data models are not automatically updated when you change one of them. For example, you have created a new attribute in the employee entity in the logical data model. That new attribute, if not a logical-only attribute, is only created in the physical data model by Rational Data Architect when you choose to transform the logical model in a physical model.

To complete the transformation process, you need to first select the data model you want to transform:

Figure 13. Data model selected for transformation
Data model selected for transformation

Then select Data > Transform > Logical Data Model. The logical data model is the only option in this case because we selected a physical data model to be transformed.

Figure 14. Target data model
Target data model

Every time you choose to do this process, you can choose between creating a new physical data model or updating an existing one. If you choose to create a new data model, Rational Data Architect requests file name and destination folder (see Figure 15):

Figure 15. Create a new data model
Create a new data model

If you choose to update an existing data model, Rational Data Architect requests what file should be updated (see Figure 16):

Figure 16. Update an existing data model
Update an existing data model

Regardless if you choose create a new data model or update an existing one, Rational Data Architect requests some additional information, which varies, depending on the transformation chosen:

Figure 17. Physical-to-logical transformation options
Import options
Figure 18. Logical-to-physical transformation options
Import options

After you select Next, the transformation process runs and lets you know when it's completed (see Figure 19):

Figure 19. Transformation output
Transformation output

The option to create a new model is good to implement multiple target databases from the same logical model. It is also good if you want to make a very simple versioning of your data model. However, remember that Rational Data Architect can work with CVS, so you can have robust and fully featured versioning software to do that for you.

Reports

Rational Data Architect already comes with some sample reports designed for you.. The output of those reports can be PDF or HTML, so you can easily distribute or share the data model among others in your business or project without the need for them to know how to use the tool or have it installed on the workstation.

To generate a report in HTML format, first select the data model you want, and then click Data > Publish > Web. The HTML output is called "Web" inside Rational Data Architect. The output is similar to the output generated by the JavaDoc tool.

Figure 20. Report - Web format
Report - Web format

In Figure 20, you can see the options you can choose to generate the report. Familiarize yourself with them so you can choose which options best fit your needs.

To generate a report in PDF format, first select the data model you want, and then click Data > Publish > Report. Based on the data model type you select, the report templates available differ.

Figure 21. Report - PDF format
Report - PDF format

In Figure 21, you can see the options you can choose to generate the report. Familiarize yourself with them, and closely analyze the different report templates, so you can choose which ones best fit your needs.

Generate DDL

You can create a DDL from your data model easily. Just select the target database or the schema name, then right-click, and choose Generate DDL (see Figure 22):

Figure 22. Choosing the database
Choosing the database

Now that you've chosen which database to generate DDL for, you have to choose what to include in the DDL script (see Figure 23).

Figure 23. DDL options
DDL options

Next step is to choose what objects you want to generate DDL for (see Figure 24).

Figure 24. Choosing objects
DDL options

You also have to choose if you want to just run the DDL on the server, or if you want to open it to edit (see Figure 25).

Figure 25. Save and run
DDL options

The next screen gives you a summary of all options chosen (see Figure 26):

Figure 26. Summary
DDL summary

Reverse engineer

Many times you don't have a data model created for your database during the development or earlier stages, but that shouldn't be used as an excuse for not having a data model at all.

You can easily create a data model from your database using Rational Data Architect's reverse engineer feature. Rational Data Architect will go through the catalog tables, also known as system tables in some database systems, and create a data model based on that information. But creating a data model based on a database's catalog tables is not the only option. You may choose to do a reverse engineering based on a DDL file as well.

First, select the Data Models category under the project where you want to create the new reverse engineered data model, and then right-click on the options New > Physical Data Model (see Figure 27):

Figure 27. Reverse engineer
Reverse engineer

Now you need to provide some information:

  • Destination folder: Where Rational Data Architect physically saves the data model file
  • File name: How Rational Data Architect names the data model file
  • Database: What database system the physical data model has as target
  • Version: What version the target database system is for the physical data model
  • Create from template: Create a blank data model, which is not used in this case
  • Create from reverse engineering: Create a data model based on the information retrieved during the reverse engineering process

Provide the information needed, then choose the Create from reverse engineering option, and select Next (see Figure 28):

Figure 28. Reverse engineer - Model file options
Reverse engineer - Model file options

On the next screen, you need to specify the source. Choose between database and DDL:

Figure 29. Reverse engineer - Source
Reverse engineer - Source

Let's look at the database option first. (The DDL script option is covered later.)

Database option

When you pick the database option, the next screen requests your connection information. You can create a new connection or work with an existing one. Be aware, though, that existing connection in this case means a connection previously set up and connected at that moment.
This example works with an existing connection. Select Use an existing connection, then specify the connection you want to work with. When ready, select Next (Figure 30).

Figure 30. Reverse engineer - Database connection
Reverse engineer - Database connection

On next page, you can choose specific schema names to import objects only under that schema. Select Next (Figure 31).

Figure 31 Reverse engineer - Schema filter
Reverse engineer - Schema filter

Because this example is importing from a DB2 for z/OS database, you can also filter to proceed by database name. Select Next (Figure 32).

Figure 32. Reverse engineer - Database filter
Reverse engineer - Database filter

Choose what elements you want to import, then select Next (Figure 33).

Figure 33. Reverse engineer - Elements filter
Reverse engineer - Elements filter

Last step is the data model options. Select Next (Figure 34).

Figure 34. Reverse engineer - Data model options
Reverse engineer - Data model options

After you select next, your data model is created under your project.

Let's now go back to the DDL script option.

DDL option

After you decide on the DDL option, you need to provide the file name and full path for it so Rational Data Architect can access it (Figure 35).

Figure 35. Reverse engineer - Script file
Reverse engineer - Script file

As shown in Figure 36, Rational Data Architect requests some options before creating the data model. Select Next, and Rational Data Architect starts the process.

Figure 36. Reverse engineer - Options
Reverse engineer - Options

After the process is finished, Rational Data Architect displays a summary of warnings, errors, or both if any occurred (Figure 37).

Figure 37. Reverse engineer - Summary
Reverse engineer - Summary

You now have a data model created based on reverse engineering from a database or from a DDL script.

Compare and synchronize

You can compare and synchronize in three different manners:

  • Between data models
  • Data model against database
  • Data model against a DDL file

All the information is displayed in a table so you can easily see what the differences are and better spend your time analyzing what information is correct, so you can keep it synchronized.

To compare between objects, you need to first select the two objects you want to compare (Figure 38).

Figure 38. Compare - Objects
Compare - Objects

After you choose to compare, a table is generated with the differences between the data models. In this case, the differences are entire tables. But if a table exists on both models, only the differing columns would be shown (Figure 39).

Figure 39. Compare - Table differences
Compare - Table differences

On the compare pane, you have six options you may choose from (Figure 40):

Figure 40. Compare - Options
Compare - Options
  1. Copy from left to right: Copy the object from left to right, or drop the object on right if it doesn't exist on left.
  2. Copy from right to left: Copy the object from right to left, or drop the object on left if it doesn't exist on right.
  3. Analyze left impact: Analyze the impact of the changes before applying them
  4. Analyze right impact: Analyze the impact of the changes before applying them
  5. Generate left delta DDL: This option is only enabled after you make any change using the compare panel. It generates the DDL needed to apply the changes on the database.
  6. Generate right delta DDL: This option is only enabled after you make any change using the compare panel. It generates the DDL needed to apply the changes on the database.

For example, let's make the PDP schema equal to the DENISV schema.
Select Area, and choose to copy from right to left (Figure 41).

Figure 41. Copy from right to left
Copy from right to left

As a result of the last action, the table is dropped from the PDP schema, and the option to generate left delta DDL is enabled (Figure 42).

Figure 42. Generate left delta ddl enabled
Generate left delta ddl enabled

If you select the generate left delta DDL button, the DDL needed to apply the changes is displayed (Figure 43).

Figure 43. Delta DDL
Delta DDL

When you select next, Rational Data Architect tells you where the script is saved (Figure 44).

Figure 44. DDL script saved
DDL script saved

Besides comparing between objects in the same project, you can choose to compare the data model against a database or DDL script. However, only data models created as a result from reverse engineer can choose this option.

To perform this type of compare, select the schema or database under the data model that was created as a result from reverse engineer, then right-click, and select Compare With > Original Source. It seeks the same method used to create the data model, a database connection, or a DDL script, then performs the compare, and displays the results in the compare pane.


Conclusion

This article provided an overview of Rational Data Architect's GUI and described how Rational Data Architect can import ERwin files with a few easy steps, quickly enabling you to use this great tool. This article also demonstrated the main features used on a daily basis by a data modeler.

After you have migrated, you can use some of the unique features and advantages that are only available in Rational Data Architect. For example:

  • Integration with a suite of applications that cover the entire software development lifecycle, reducing risks and increasing predictability:
    • IBM Rational RequisitePro - Rational Data Architect can link the data model to and from requirements that exist in the RequisitePro repository
    • IBM Rational ClearCase - Rational Data Architect can work together with ClearCase or CVS to version control, giving you better team support and versioning of your data models
    • IBM Rational Software Architect - You transform between class model and logical data model
  • Unique mapping and mapping discovery capabilities:
    • Rational Data Architect can discover relationships automatically or manually between data assets (columns in tables) across databases, even different databases from different vendors. Through Rational Data Architect, you can also map data structures to SOA interfaces and XML schemas. Rational Data Architect also has thesaurus support, so you can establish connection between column names (in other words, Id = customer = account = cust_id)
  • Federated design:
    • Based on Rational Data Architect's unique mapping and mapping discovery capabilities, or going through a manual process, you can federate data models, or, in other words, better relate and integrate different data models, even from different databases
  • Strong DB2 support - Rational Data Architect allows you to generate, edit, import, test, debug, deploy, compare, export, and batch deploy SQL code, including stored procedures and user-defined functions, from one unique environment (tool)
  • Support for different OS platforms - Linux and Windows OS are supported, the most-used client operating systems, and with a single interface
  • Import and export to multiple tools - Rational Data Architect can import and export to tools such as Sybase PowerDesigner, Rational Data Modeler, and ERwin
  • Integration with IBM Information Server - Rational Data Architect can import and export naming model to and from WebSphere Business Glossary, and export a physical data model to WebSphere DataStage

As you can see, you have many reasons to migrate to Rational Data Architect, and use its unique features.

Resources

Learn

Get products and technologies

  • Rational Data Architect: Download a free trial version.
  • Download IBM trial software and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Rational
ArticleID=255418
ArticleTitle=Migrate ERwin data models to Rational Data Architect
publish-date=09132007