migrating from ca erwin to infosphere data architect - ibm

Migrating from CA ERwin to InfoSphere Data Architect

October 2010

Authors:

Norma Mullin ([email protected]) – Consulting Client Technical Professional

Seeling Cheung ([email protected]) – Customer and Business Partner Enablement

Level: Intermediate

The recommendations given on this document apply to the current version of InfoSphere Data Architect (7.5.3) and previous releases of InfoSphere Data Architect or Rational Data Architect (Versions 7.0.0.x and 7.5.x)

of 51

Migrating from CA® ERwin® to InfoSphere™ Data Architect

mailto:[email protected]

mailto:[email protected]

Migrating from CA ERwin to InfoSphere Data ArchitectAbout this document

IBM® InfoSphere™ Data Architect is a collaborative data design solution to discover, model, relate, and standardize diverse and distributed data assets. This product was previously known as Rational® Data Architect, and some of the existing materials on the Web may still refer to this name..

If you’ve had experience with CA ERwin Data Modeler and you are now starting to learn InfoSphere Data Architect, this paper will speed up your model migration process and ease your learning process of the new environment. You will learn the differences between the two design environments, and you will also learn a set of best practices for using features of InfoSphere Data Architect that will put you on the fast track of working productively in InfoSphere Data Architect. Some of the best practices recommended for the migration process include:

• Models that contain entities related to different subject areas in ERwin should be organized into smaller packages (subpackages) in InfoSphere Data Architect.

• Models that are large or would be shared and changed by multiple people in a team environment in ERwin should be partitioned into submodels in InfoSphere Data Architect. This offers the benefits of team collaboration, less consumption of system resources, and improvement of model performance.

• Models that have more than one package (such as more than one subject area) in ERwin should use the unique InfoSphere Data Architect navigation diagrams to quickly traverse from one diagram to another.

• As of IDA 7.5.2 Fixpack 1, physical objects, such as tablespaces, that have been defined in an ERwin physical data model can be imported with the extended import option using the valid DDL used to create the storage objects. Note that this feature is currently available for DB2 LUW and DB for z/OS only.

To view additional best practices for InfoSphere Data Architect, visit: http://www.ibm.com/developerworks/data/bestpractices/informationmodeling/

Finally, we provide a list of additional educational materials, at the end of this guide, to help new users acquire more knowledge of the key features and unique capabilities of InfoSphere Data Architect.

of 51


http://www.ibm.com/developerworks/data/bestpractices/informationmodeling/

Contents

Contents 31 Preparing your ERwin Models in ERwin Data Modeler 4

1.1 Remove duplicate objects in the model 4 1.2 Save the model in a tool-independent format 4 1.3 Export your naming standards to a csv format file 5 1.4 Verify the data types that you used for your attributes and domains 5

2 Understanding the Eclipse workbench environment 5 2.1 Eclipse IDE overview and benefits 5 2.2 Setting up and understanding your Eclipse IDE 5

2.2.1 Workspace 5 2.2.2 Perspectives 6 2.2.2.1 Selecting The Data Perspective ............................................................................. 6 2.2.3 Projects 8 2.2.3.1 Creating a data design project ................................................................................ 8

3 Importing your ERwin models into InfoSphere Data Architect 10 3.1 Import Format 10 3.2 Model type 11 3.3 More on import options 11 3.4 Importing DB2 LUW and z/OS Storage Objects (database specific) 13 3.5 Model layer differences 16 3.6 Differences in model objects terminology and management 17

4 Post-import reorganization of model objects into separate models 18 4.1 Hints and tips for reorganizing the imported logical data model 18 4.2 Reorganizing the imported model into smaller separate packages 19

4.2.1 Step 1: Create a new subpackage for each respective organizational area ........... 19 4.2.2 Step 2: Drag related entities into the new subpackage 20

4.3 Repeat previous steps 20

5 Model partitioning and team collaboration best practices 21 5.1 Creating a submodel from a subpackage in a logical data model 22 5.2 Improving modeling performance: loading or unloading a submodel 23

6 Creating InfoSphere Data Architect’s unique navigation diagram structures 25 6.1 Create subject-based data diagrams 25 6.2 Create a top level diagram in the main model - Navigation Diagram 27 6.3 Add diagram shortcuts (links) into the top-level diagram 27

of 51


6.4 Creating diagram shortcuts for easy bi-directional navigation 30

7 Additional post-import tasks and InfoSphere Data Architect Best Practices 31 7.1 Setting up naming standards in InfoSphere Data Architect and reusing definitions from

ERwin 31 7.2 Post-import synchronization between logical and physical data models 32

7.2.1 Transform logical data model to a new physical data model 32 7.2.2 Object-based Comparing and Synchronizing Feature 35 7.2.3 Cascade changes in logical data model to an existing physical data model ........... 38

7.3 Domain model 41 7.3.1 Create new domains or modify the properties of existing ones 43 7.3.2 Convert ERwin domains to Domain Models 43

8 More on terminology and feature differences 47 9 Conclusion 49 10 Appendix Additional Educational Resources 49 http://www.ibm.com/developerworks/data/library/techarticle/dm-

1009infospherexml/index.html 51 11 Acknowledgments and Notices 51

1 Preparing your ERwin Models in ERwin Data Modeler

The sample ERwin data model used in this guide consists of five subject areas: Access Control, Content Management Navigation, Content Management Administration, Internationalization, and Localization. The model also contains the ERwin mandatory “Main Subject” area that contains all of the entities. This section provides a list of suggestions for how to prepare your ERwin models to enable a smoother import to InfoSphere Data Architect.

1.1 Remove duplicate objects in the model

It is a good practice to avoid using duplicate names on entities or tables. Duplicate objects, needed in some cases to be represented in different areas of interest (subject areas), will be imported only once to prevent duplication.

1.2 Save the model in a tool-independent format

Save the model in XML to increase the import speed and to allow the import to work independently of the original tool. If you import the model, using ERwin file format (e.g. .er1, Erwin), you must have a valid license of ERwin installed while doing the import.

of 51


1.3 Export your naming standards to a csv format file

If you are using ERwin naming standards files, export the file so the business terms defined in ERwin can be copied into an InfoSphere Data Architect glossary model.

1.4 Verify the data types that you used for your attributes and domains

If you are using data types (logical or physical) that you created within ERwin or are unique to ERwin, verify and document them so you can modify your imported model accordingly.

2 Understanding the Eclipse workbench environment

This section provides a list of suggestions for preparing your InfoSphere Data Architect environment before the import process.

2.1 Eclipse IDE overview and benefits

InfoSphere Data Architect is comprised of the base Eclipse SDK plus the InfoSphere Data Architect capabilities that are delivered in a set of Eclipse plug-ins. This means that InfoSphere Data Architect contains all of the base functionality of Eclipse. The plug-in architecture also allows seamless integration with other compatible Eclipse-based tools.

The Eclipse IDE is sometimes called ″the workbench″. The term workbench refers to the desktop development environment. The workbench aims to achieve seamless tool integration and controlled openness by providing a common paradigm for creating, managing, and navigating resources. Each workbench window contains one or more perspectives. Perspectives contain default collections of views and editors that control what appears in certain menus and tool bars. Multiple workbench windows can exist on the desktop at any time.

2.2 Setting up and understanding your Eclipse IDE

In this section, you will learn how to work with projects, views, files, and perspectives in the workbench. These skills will enable you to use the workbench more efficiently to perform other tasks, such as creating new data models and diagrams.

2.2.1 Workspace

The central hub for your data files is called a workspace. The workspace is the physical location (directory) where your work will be stored. You can choose the workspace during startup of IDA or via the menu File-> Switch Workspace. Best practice is to use one workspace to keep

of 51


all your data design projects together. If you are using Rational ClearCase to manage your projects, it is a good practice to maintain a separate workspace for each Rational ClearCase view that you use.

2.2.2 Perspectives

A perspective defines the initial set and layout of views in the Workbench window. One or more perspectives can exist in a single Workbench window.

2.2.2.1 Selecting The Data Perspective

The Data perspective in the workbench is where you will do all of your work in this migration guide and it is the default perspective for InfoSphere Data Architect. The Data perspective consists of several views and editors, including:

• Data Project Explorer

• Data Source Explorer

• Properties

• SQL Results

• Outline

• Editor Area

of 51


Figure 1. Data perspective screen

As with any perspective in the workbench, you can customize the Data perspective to include other views, or exclude the default views. In this guide, you will use the default views provided with the Data perspective.

To open the Data perspective (if the Data perspective has been previously closed):

1. Click Window > Open Perspective > Other from the main menu bar.

2. In the window that opens, select Data, and click OK. If you do not see Data in the list, click Show all.

The Data perspective opens with the default views. If you have not previously created any data projects, the Data Project Explorer is blank.

The Data Source Explorer contains two folders: the Database Connections folder and the Configuration Repositories folder. The Database Connections folder contains a connection to the Derby sample database, which is included with this product. Database connections are created automatically in the Data Source Explorer view when DB2® aliases have been

of 51


configured. You can modify the behavior of these connections in the Preferences window. Other databases can be added or removed from the Data Source Explorer.

Expand (double click) the Derby Sample Connection folder to see the database connection properties.

Figure 2. Data Source Explorer view

2.2.3 Projects

In the workbench, files are stored in folders called projects. A workspace are simply directories on the system that contain one or more projects. Projects are organized within workspaces

Before you can create or import any files, you must create a project to contain them.

2.2.3.1 Creating a data design project

Before you create data models or other data design objects, you need to create a data design project to store your objects.

A data design project is primarily used to store modeling objects. You can store the following types of objects in a data design project:

• Logical data models

• Physical data models

of 51


http://127.0.0.1:2728/help/topic/com.ibm.datatools.connection.ui.doc/topics/tdbconn_pref_db2.html

• Domain models

• Glossary models

• SQL scripts, including DDL scripts

• Mapping models and

• XML schemas

Any other file types, such as doc files, text files, presentations, or spreadsheets can also be stored in a data design project. Any files other than the files listed above or Eclipse-specific files (such as .project files) are displayed in the Other files folder under a data design project in the Data Project Explorer.

Data design projects can be shared among team members by using the source code management capabilities of the workbench. If you are working in a team environment, you can share the project using the team support feature.

In this step, you will create a data design project in which to store your objects.

To create a data design project:

1. From the main menu, click File > New > Data Design Project. The New Data Design Project wizard opens.

2. In the Project Name field, type the name of your project and click Finish. In our example the name of the project is ERwin2IDA.

The Data Project Explorer of the workbench displays the new data project and its logical folders. This prepares your environment for the next steps of importing the ERwin models into this data design project.

of 51


Figure 3. Data Project Explorer view

3 Importing your ERwin models into InfoSphere Data Architect

3.1 Import Format

The import of metadata from ERwin and other data modeling solutions, such as Embarcadero ER Studio and Sybase PowerDesigner, is accomplished by using the Data Model Import Wizard.

To begin importing an ERwin model:

1. From the main menu, click File > Import.

2. Expand the Data folder, select Data Model Import Wizard and click Next. The Data Model Import Wizard opens.

There are two ways to import models from ERwin: Import from the native ERwin format [for example, .er1 (version 4.x), or .erwin (version 7.x)] Import an XML version of an ERwin file (Save As … in ERwin) (recommended)

The XML format does not need the ERwin tool to be installed on the workstation while importing models. The content of the import from either format is the same.

of 51


3.2 Model type

The Model type option enables you to specify if you want to import just the logical or physical data model from the ERwin file, or both, using the Auto detect option. The Auto detect option is the recommended option if you keep the logical and physical data model in the same ERwin file.

Figure 4. Data Model Import wizard: first screen

Click Next on the Data Model Import wizard.

3.3 More on import options

The Import wizard options appear next.

The import allows the following options: Apply binary character filter (ERwin 4.x only)– Specifies if invalid binary characters should be

filtered out of the file a a pre-process, before parsing the XML document. This option is only

of 51


available for a workaround for a bug in the ERwin4 tool which sometimes generates a file that is not conformant with the XML specification, with binary characters in it. It should be set to True to prevent binary characters from being incorrectly reported.

Import views – should be set to As View to prevent duplication of views to tables. This option is only available when importing from an ERwin version prior to 7.x

Import IDs – does not have any effect on the quality of the input. Each object defined in the ERwin file has an ID. This option allows you to specify whether the wizard should import them in the NativeId property. True is the default which means that the ID is saved in the NativeId property.

Import UDPs – should be set to As Metadata, Migrate default values to create annotations to the imported objects with all implicit and explicit values. Note that the default is ‘as metadata’

Import relationship name – should be set to From relationship name to get the best possible model similarity

Import subject areas (most important option; default is Imported as diagrams) – allows you to specify how InfoSphere Data Architect will handle subject areas from Erwin: Imported as diagrams: If you choose this option, you will have only one package ( )

named the same as the original data model name, and all the subject areas are imported as diagrams and will be part of this package. This is the easiest and most straight-forward option to import a data model in the one form of the main subject area without any additional structure. If you have duplicate entities, importing the subject areas as plain diagrams is the best and recommended choice.

Imported as packages and diagrams: Use this option if you want InfoSphere Data Architect to create a subpackage and diagram created for each subject area. This might be preferable if your ERwin model has many subject areas. But it might take more consideration, especially if there are duplicated entities or tables. For example, if you have an entity that is duplicated in numerous subject areas in the ERwin models, the import process will identify a package owner for the entity. You will have no control over which package in InfoSphere Data Architect will be assigned and you would need to change the package that owns the entity, if the one selected by the import is not the desired package. However, the diagrams will be imported as they exist in ERwin, including cross-package entities.

Validity check – should be Basic Validity Check to validate the model as it gets imported. Target database – should be Auto Detect, as IDA would detect the database from your ERwin

data model file. If you need to import database-specific properties like storage objects from your ERwin DB2 LUW and DB2 z/OS physical data models, please refer to section 3.4 .

The import options shown in Figure 5 will create a good base model that you can work on in later reorganization steps in InfoSphere Data Architect to create the best workable model structures.

of 51


Figure 5. Data Model Import wizard second screen

As of IDA 7.5.2.1 you have the option to generate a DDL script after finishing the import. If selected, an extra step will be performed by the wizard. IDA will generate a DDL script, which can be found in the SQL Scripts logical folder of your project. This is useful if you already have DDL scripts and want to manually compare them or if you are importing database specific properties (Section 3.4) and want to manually compare the DDL generated by IDA and the DDL imported from ERwin.

3.4 Importing DB2 LUW and z/OS Storage Objects (database specific)

As of IDA 7.5.2.1 the Data Model Import wizard was enhanced to facilitate the integration of the database-specific information into the physical data model imported by the bridge.

This is accomplished by optionally providing into the new import wizard the DDL file or the DDL file directory containing the script(s) that have database-specific properties, such as tablespaces, bufferpools, views, etc. To enable the DDL section the target database must be

of 51


selected with any version of either DB2 LUW or DB2 z/OS. Note that, as of the current release of IDA (7.5.3), this capability is only enabled for DB2 z/OS and DB2 LUW. You can, optionally, allow the DDL script to override any other properties of the physical data model (e.g. table, columns) by selecting the ‘Overwrite model properties from DDL’ option.

Figure 6 shows an example of the DDL option enabled when the appropriate database is selected during the import.

Figure 6. Data Model Import wizard – enabling DDL

The wizard supports the selection of a ddl script (.sql or .ddl) or a file directory. When a directory is selected, all the scripts (.,sql and .ddl) within this directory are used during the import. Once the script or directory is provided, the wizard will take the necessary actions and merge the information from the DDL into the IDA physical data model to be created by the wizard.

of 51


It is important to verify that the DDL scripts are valid and contain all required database objects. The DDL must be one that was previously generated for the specific model or one that you manually generate in ERwin. Either way, ensure you validate the DDL as any errors encountered in the DDL, would prevent the model to be imported.

The summary page, displayed on the last page of the import wizard, displays the additional information obtained when it processed the DDL. If the wizard encountered errors in the DDL script, they are displayed in the Summary page and the Finish button is disabled. If the import and the DDL are okay, the Finish button is enabled. Click on the Finish button to create the models. Logical and Physical, if you selected both, or to create the physical data model, if you are only importing a physical data model.

Below is the DDL used on this example and the physical data model created by the import with the additional database specific objects, tablespaces on this case.

Figure 7. Data Model Import wizard – enabling DDL

Current Restrictions:

ERwin DDL Generation: In order for the DDL to be imported correctly, the owner (schema) needs to be specified when the DDL is generated from ERwin, using the ‘Other Options’ option and selecting ‘Owner’.

of 51


DDL Syntax: The DDLs to be imported must be valid SQL and should follow the SQL DB2 reference for z/OS or LUW. If any DDL syntax errors are found during the process, the import will stop on error and it will not generate any IDA models,

Carriage Return embedded in a middle of a word: DDL files that have carriage return embedded in a middle of a word are treated as invalid SQL. To prevent this, unselect the ‘Wrap File Output’ option in the ERwin Forward Engineer Schema Generation dialog when generating DDL, if available.

3.5 Model layer differences

There are some major differences between ERwin and InfoSphere Data Architect model files. If you are used to the traditional approach in ERwin, you will have a model file with two views: one representing a logical data model and the other a physical data model. In this approach, changes made in the logical model are instantly reflected in the physical data model, because ERwin maintains one model with two different representations.

By contrast, InfoSphere Data Architect maintains a separate file for each model type. During the import process, both logical and physical data models and associated diagrams from ERwin are migrated to InfoSphere Data Architect (when you specify auto-detect). InfoSphere Data Architect creates the data model in separate files: one for the logical data model and another one for the physical data model. This approach, also adopted by other data modeling tools, requires you to synchronize the logical and the physical data models whenever you make changes to the model. The synchronization topic is covered in Section 7 of this guide.

Figure8. ERwin and IDA model files

of 51


Figure 9. The imported ERwin model file

In the Data Project Explorer, expand your project folder and the Data Models folder. You should see the migrated logical and physical data models in InfoSphere Data Architect.

In our example we are expanding the ERwin2IDA project folder and the Data Models folder and we see the logical and physical data models Imported2IDA.ldm and Imported2IDA.dbm respectively. The import process automatically created a dependency between the two models. This dependency link will become useful if user wants to invoke the Analyze Impact feature in InfoSphere Data Architect to understand the impact of changes on a model object.

The associated diagrams imported from ERwin models include the main subject area, <Main Subject Area>, and the diagrams from other subject areas Access Control, Content Management Administration, Content Management Navigation, Internationalization, and Localization.

3.6 Differences in model objects terminology and management

In InfoSphere Data Architect, the concept of a top level (root) package ( ) is similar to the concept of a main subject area in ERwin. Smaller packages (also known as subpackages) in InfoSphere Data Architect are conceptually similar to subject areas within ERwin. However, there are some key differences in these architectures. One difference is that InfoSphere Data Architect offers you the capability to offload a subpackage into a submodel. The submodel is treated as its own model. You can work in the smaller model and close the parent model in order to use fewer system resources. A second benefit to this is that when using a team-sharing repository such as Rational ClearCase or CVS, working in submodels can avoid collisions at check-in time. This is discussed in more detail in Sections 4 and 5.

of 51


Using our example, the BIGMODEL package of the Imported2IDA.ldm logical data model is the root package of the model. In later steps, we will reorganize and divide this root package to create smaller separate models using packages (subpackages).

In InfoSphere Data Architect, you can also create multiple views of your model by creating additional diagrams. The primary difference between how ERwin handles the concept of a data model and how InfoSphere Data Architect handles it is that the Main Subject diagram is the data model in ERwin. In InfoSphere Data Architect, the tree structure in the Data Project Explorer is where the data model resides. A diagram in InfoSphere Data Architect is simply a visualization layer over the data model. This can cause some confusion for ERwin users when they first start using InfoSphere Data Architect. This is explained in further detail in Section 6.

4 Post-import reorganization of model objects into separate models

Best practices and benefits of reorganization

There are some suggested best practices to reorganize the imported ERwin model into smaller models to create the best workable model structure in InfoSphere Data Architect. Because InfoSphere Data Architect can work with any number of models at the same time and use objects on diagrams from different models, you should organize models in the way that is best for your organization. InfoSphere Data Architect is very flexible in terms of the structure of models.

To view overall best practices for InfoSphere Data Architect, visit: http://www.ibm.com/developerworks/data/bestpractices/informationmodeling/

4.1 Hints and tips for reorganizing the imported logical data model

In this section, we will show you how to divide the root package. (BIGMODEL in our example), into several smaller packages to leverage the flexibility of InfoSphere Data Architect. This process requires some planning. Here are some hints: Create a model to match your organizational structure. For example, if there is a group of

employees that deals with everything around internationalization, you could create a model with all the entities they deal with.

Import your ERwin subject areas as diagrams (the default), and in InfoSphere Data Architect create subpackages for each of those subject areas.

If you have repeated entities on various subject areas, decide which package owns the entity. For example, the party entity might be in various subject areas or in various diagrams, but it is owned by one package.

of 51



Do not think in terms of diagrams when defining ownership of the objects. Remember that in InfoSphere Data Architect, diagrams are just a graphical representation of your metadata. You can have multiple diagrams per package.

4.2 Reorganizing the imported model into smaller separate packages

The first step is to create packages according to your desired organizational structure (see hints listed above). For this exercise, we are using the subject areas represented in the imported ERwin diagram, such as Content Management Administration. Using the BIGMODEL example in this guide, we put all of the entities that deal with Content Management Administration into one package, all entities that deal with Content Management Navigation into another package, and so on. This can be done in a two-step process:

4.2.1 Step 1: Create a new subpackage for each respective organizational areaYou can use the Data Project Explorer in the workbench to create a new subpackage under the root package. In our example the root package is called BIGMODEL. In this step, we use the example of the subject area Content Management Administration from the imported model.

1. Expand the Data Models folder and the logical data model to locate the root package (e.g. BIGMODEL).

2. Right-click on the root package, select Add Data Object > Package. The Data Project Explorer displays a new subpackage named Package1 under your root package.

3. Select Package1 and use the Properties view of this subpackage to rename it. In our example we renamed it to CONTENT MANAGEMENT ADMINSTRATION.

of 51


Figure 10. Creating a subpackage

4.2.2 Step 2: Drag related entities into the new subpackage It is essential to drag the entities from the main package of the imported model into the new subpackage to preserve all of the relationships that exist between the entities and to preserve the existence of those entities on diagrams.

In the Data Project Explorer, select an entity under the root package (i.e. BIGMODELin our example) that relates to the subpackage you created (i.e. CONTENT MANAGEMENT ADMINISTRATION in our example) and drag it to the subpackage. An example of the related entity would be CONTENT MANAGEMENT CHECKED OUT, shown below.

BEFORE AFTER

Figure 11. Dragging entities to the subpackage

4.3 Repeat previous stepsRepeat the previous steps until you have created subpackages for all of the desired areas and you achieve a level of detail that contains subpackages that group only related entities. Using the imported model example, the resulting reorganization looks like this:

of 51


Figure 12. Multiple subpackages of a logical data model

In our example, the root package BIGMODEL contains subpackages mapped to subject areas.

If you imported the subject areas as diagrams you can now drag/drop the related subject area diagrams from the root package to the specific subpackages..

5 Model partitioning and team collaboration best practices

InfoSphere Data Architect offers strong support for team collaboration. If a single user is going to use the data model, there is no need to partition the logical data model. But if you are working in a team environment or if you are working on a large data model, it is best practice to partition the logical data model.

The preferred method for partitioning a logical data model is to create submodels from subpackages. Partitioning offers some key benefits, as stated in Section 3.5, including reduced consumption of system resources, more flexibility for teamwork that avoids collisions, and merging changes at check-in time. When deciding how to partition models to optimize for configuration management and team development, these steps are recommended:

1. Stabilize abstraction levels. Do not partition the model until the top-level subsystems are clearly defined. Once the subsystems are mature and stable, you can separate them to enable parallel development and to improve the speed with which the model opens. When an individual subsystem’s contents stabilize, you can then separate the subsystem from the model.

of 51


2. Minimize dependencies between models. By minimizing dependencies between models, you can reduce the likelihood that a change in one model will affect another. If you allow dependencies between models, widespread conflicts can occur that must be resolved with out-of-context model merges, which can be challenging.

3. Establish ownership policies. Assigning different model files to different team members can help speed integration and can help avoid conflicts when you deliver the model to a shared work area. You should establish the size and scope of each model so that a single person can work on it.

4. Avoid broken references. Whenever you move a model partition outside of your configuration management system, you break that model file's references. You need to repair these broken references whenever you reintegrate the model back into the Source Control Management system. It can be time-consuming and error-prone to resolve a large number of references in a complicated model.

5.1 Creating a submodel from a subpackage in a logical data model

You can use the workbench to create submodels ( ) from existing subpackages in a logical model. This is one way to partition a large logical data model to enable team collaboration. In this step, we will create a submodel from the subpackage CONTENT MANAGEMENT ADMINISTRATION.

1. Open the logical data model (Imported2IDA.ldm file, in our example).

2. Expand the root package (BIGMODEL in our example) in the Data Project Explorer. Select the subpackage (CONTENT MANAGEMENT ADMINISTRATION in our example). That is, any package other than the root package.

3. Right-click the subpackage, and select Create Submodel from Package.

of 51


Figure 13. Submodel created from a subpackage

In our example, the submodel CONTENT MANAGMENET ADMINISTRATION is created and displayed in the Data Project Explorer as a new .ldm file, with the same name as the original subpackage. A shortcut to the submodel is displayed under the main model, BIGMODEL. The subpackage icon ( ) changes to indicate that the package object has been changed to be a shortcut to a submodel ( ).

You can open and access the submodel through the shortcut in the main model, or you can open and work with the submodel directly. Containment is maintained between the main model and the submodel so that logical-to-physical data model transformation, impact analysis, and Web publishing can be run transparently from the main model.

5.2 Improving modeling performance: loading or unloading a submodel

Submodels in a partitioned logical data model can either be loaded or unloaded in the main model. If a submodel is loaded, actions such as model analysis, Web publishing, or impact analysis that are performed on the main model are also automatically performed on the submodel. If a submodel is not loaded, these actions on the main model are not performed on the submodel. You can improve modeling performance by loading only the submodels that you are currently working on.

To load a submodel, right-click it in the Data Project Explorer, and select Load. The model is open in the Data Project Explorer, and the icon and label decoration change to indicate that the object is loaded.

The diagram below shows the submodel before being loaded ( ):

of 51


Figure 14. Unloaded submodel

The diagram below shows the loaded submodel ( ).

Figure 15. Loaded submodel

To unload a submodel, right-click it in the Data Project Explorer and select Unload. The model is closed in the Data Project Explorer, and the icon and label decoration change to indicate that the object is unloaded ( ).

of 51


6 Creating InfoSphere Data Architect’s unique navigation diagram structures

Data diagrams in InfoSphere Data Architect and ERwin

The reorganization (and partitioning, if needed) of models will create manageable models that you can work on. Data diagrams facilitate visualization and editing of objects You can create context-specific data diagrams for subject areas. You can also create overview diagrams to provide a high-level overview of underlying diagrams. A unique feature of InfoSphere Data Architect is the navigation diagram which faciliates easy navigation between diagrams, regardless of the model or the project.

In ERwin, the main subject area diagram is considered metadata. Once the metadata are deleted from a diagram in the main subject area, they are deleted from the data model.

By contrast, with InfoSphere Data Architect, removing data objects such as entities and tables in diagrams, including diagrams from the root package do not remove them from the underlying data models automatically. You must explicitly select the action to remove them from the model.

The following are steps that you can do using InfoSphere Data Architect’s unique navigation diagrams to quickly go from one diagram to another.

6.1 Create subject-based data diagramsThis section demonstrates how to create a data diagram for each of the top-level packages explaining the next level of organization.

If you imported your ERwin subject areas as diagrams and within InfoSphere Data Architect created subpackages, you can move (drag and drop) the diagrams that you imported to the specific subpackages.

You can always create multiple diagrams within each subject area and change the characteristic of the diagram for any object within the diagram. For example, highlight an attribute, select entities where you only want to show the entity name, like reference entities, etc.

The following are steps to create a subject-based data diagram within a subpackage.

Using our example of BIGMODEL root package, we will create a diagram for each of these subpackages: ACCESS CONTROL, CONTENT MANAGEMENT ADMINISTRATION, CONTENT MANAGEMENT NAVIGATION, INTERNATIONALIZATION, and LOCALIZATION. To create a diagram for the ACCESS CONTROL package:

of 51


1. Expand the folder for ACCESS CONTROL in the Data Project Explorer.

2. Right-click the Diagrams folder, and select New Blank Diagram to create an empty diagram.

3. Select the new diagram in the Data Project Explorer, and type Access Control in the Diagram name field in the General tab of the Properties view.

4. Drag each of the entities from the ACCESS CONTROL package in the Data Project Explorer onto the diagram. These entities are HELP CENTRAL USER, HELP CENTRAL ROLE REFERENCE, and USER ROLE. The following diagram shows the resulting package.

Figure 16. Diagram for package ACCESS CONTROL

Repeat this step until you create a data diagram for each of the packages. If you imported your ERwin model using the default you can also drag and drop the imported diagram to the appropriate package.

of 51


6.2 Create a top level diagram in the main model - Navigation DiagramNavigation diagram is a diagram that all users could start with as a way to navigate through various diagrams, packages and models within the project. Name the diagram Master Overview or something else every user can identify as the starting point.

1. Right-click the Diagrams folder under root package. In our example, the root package name is BIGMODEL and the imported model name is Imported2IDA.ldm.

2. Select New Blank Diagram. This diagram is the root diagram.

3. Rename the diagram to something meaningful using the Properties view. In our example, we named the diagram MyOverview.

The following diagram shows the resulting screen.

Figure 17. Creating diagram MyOverview

6.3 Add diagram shortcuts (links) into the top-level diagram This section shows you how to create diagram shortcuts from existing subject area diagrams to the top-level diagram. With this feature, you can link diagrams together to easily group related diagrams, annotate the top-level diagram to describe the relationships, and navigate between the diagrams.

of 51


1. In the Data Project Explorer, click one of the diagrams that you created previously. In our example we are using Internationalization diagram from the Diagrams folder located under the INTERNATIONALIZATION package.

2. Drag the Internalization diagram onto the new MyOverview diagram. An oval diagram object is displayed on the MyOverview diagram. You can apply new color using the Fill Color pull-down menu from the right-click menu on the diagram object.

Figure 18. Adding diagram from package Internationalization

3. Add another subject area diagram to the MyOverview diagram, and use annotation objects to document the relationship between the two diagrams.

4. Click and drag the Localization diagram from the Diagrams folder under the LOCALIZATION package onto the MyOverview diagram surface.

Figure 6. Adding diagram from package Localization

5. Use lines and text boxes to annotate the diagram with information about how these diagrams are related.

6. Click a Line object from the diagram editor palette, and click and drag between the Internalization diagram object and the Localization object on the MyOverview diagram.

of 51


Figure 20. Adding lines between the two diagrams

7. Right-click an empty space in the MyOverview diagram. Click Add > Text. Click near the line object. Type some text that describes how these two areas are related.

Figure 21. Adding text

8. Save the .ldm file. You can now open the diagrams for the various subject areas, simply by selecting it from the navigation diagram. In our example, we can navigate to Internationalization and Localization diagrams from the MyOverview diagram by double-clicking the new diagram objects.

9. Repeat the process until you have added the diagram objects for all the organizational subject areas diagrams onto the MyOverview diagram as shown below.

of 51


Figure 22. The navigation diagram

6.4 Creating diagram shortcuts for easy bi-directional navigationTo create a bi-directional navigation you create on each subject area diagram a link back to the MyOverview diagram (the next higher level of hierarchy). With this, you can now navigate in any direction between diagrams and easily move from one subject area diagram to the next.

For example, to add a diagram shortcut for the MyOverview diagram to the Access Control diagram:

1. In the Data Project Explorer, double-click to open the Access Control diagram from the Diagrams folder located under the ACCESS CONTROL subpackage.

2. In the Data Project Explorer, click the MyOverview diagram from the Diagrams folder located under the BIGMODEL root package.

3. Drag the MyOverview diagram onto the Access Control diagram. A shortcut for the MyOverview diagram is displayed on the Access Control diagram, as shown below.

of 51


Figure 73. MyOverview added to the Access Control diagram

After you create the diagram shortcut, you can double-click it to open the MyOverview diagram that the shortcut references in the diagram editor. With this, you can navigate in any direction between the diagrams.

7 Additional post-import tasks and InfoSphere Data Architect Best Practices

7.1 Setting up naming standards in InfoSphere Data Architect and reusing definitions from ERwin

Naming standards promote better communication and consistency across different groups and stakeholders in an organization. InfoSphere Data Architect helps you define data object naming standards. You can specify terms chosen for data object names in a glossary model. InfoSphere Data Architect currently does not support automatic migration of the ERwin glossary model during the import process. You can still use the definitions from your ERwin glossary model with the following steps:

of 51


1. Create a glossary model by clicking File > New > Glossary Model.

2. The New Glossary Model wizard is displayed. Fill in the wizard options. You can select the check box to Add to project properties as project naming standard.

3. Click Finish. The glossary model is displayed in the Data Project Explorer under the destination folder you specified.

4. You can copy and paste definitions from sources such as Microsoft® Word or Microsoft Excel, or from other sources like a plain CSV file. If the format is the same, you can just copy and paste all entries at once to copy definitions from an ERwin naming standard file to the InfoSphere Data Architect glossary model.

7.2 Post-import synchronization between logical and physical data models

As mentioned in Section 3, InfoSphere Data Architect creates the data model in separate files: one for the logical data model and another one for the physical data model. Any changes applied to the logical data model which you want to implement in the physical data model requires you to transform the logical data model into an existing physical data model, which would enable the compare and synchronize feature of IDA. The process enables you to generate multiple schemas or update existing ones. You can also transform the logical data model to a new physical data model. This process enables you to generate multiple physical data models from the same logical data model. Thus, physical data models can target different database systems or deployment environments. .

7.2.1 Transform logical data model to a new physical data model

To transform a logical data model to a new physical data model:

1. Select the logical data model (Imported2IDA.ldm in our example) in the Data Project Explorer.

2. Navigate to Data > Transform > Physical data model. The Transform to Physical Data Model wizard opens.

3. Keep Create new model checked. Click Next. The next Transform To Physical Data Model wizard appears.

4. Ensure the Generate traceability check box is selected. This will add a dependency to each column on the physical data model so that you can use the Analyze Impact feature. The Analyze Impact feature shows you the objects that would be impacted.

of 51


Figure 24. Transforming a logical data model to a physical data model

5. Click Next and click on Finish on the next screen. This will create your new physical data model.

6. Expand your physical data model under the Data Models folder in the Data Project Explorer to see all the tables.

of 51


Figure 25. Physical data model

To verify that the data types you used are in InfoSphere Data Architect, select the Preferences option from the Window menu. From the Preferences screen, select Data Management > Transform > Data Type Map, as shown in Figure 26.

Figure 26. Data Type Map screen

of 51


7.2.2 Object-based Comparing and Synchronizing Feature

InfoSphere Data Architect offers the crucial capability to maintaining changes to data models and ,databases and keep them in synchronization with each other. This section will offer an overview of of the Compare and Synchronize feature which can be used for managing model and objects synchronization, including during model transformation to an existing model.

● InfoSphere Data Architect provides an editor to compare two objects:

Logical data models (*.ldm)

Objects that are contained in a logical data model

Physical data models (*.dbm)

Objects that are contained in a physical data model

Domain models (*.ddm)

Objects that are contained in a domain model

Glossary models (*.ndm)

Database objects that are contained in a database connection in the Data Source Explorer

● There are two layers to specify comparison filter options:

The first layer is shown in figure 27 , persisted at the global Preferences as default options.

The second layer is the on-the-fly filter options specified at each invocation of compare and synchronization. Users are provide with the option to save this second layer of filter options to the global Preferences level.

Click on Window, in the top menu bar. Then select Preferences. Click on the + sign in front of Data Management. Click on Data Object Compare. Look at the various filter options related to object types as shown below:

of 51


Figure 27. workspace filter options

Click on Comparison Filter Configuration. Look at the various filter options related to properties. Review the selections in the drop down menus, Filtering criteria for and Database. Then click on Cancel to close the wizard.

of 51


Figure 28. workspace filter options

● Context menus are provided for the purpose of comparing and synchronizing data objects in a model. From the Data Project Explorer:

You can select a data model object and select the ‘Compare With->Another Data Object’ option.

Or, you can select two compatible objects (e.g. two logical models) and perform the ‘Compare With->Each Other’ option.

Additionally, you can select the ‘Compare With->Original Source’ for a data object if the object (e.g. a schema in a physical data model) has an original source (e.g. the database ) associated with it.

of 51


● After you merge changes to data objects in the comparison editor

Generate a delta DDL script

Export the comparison results to an XML file

7.2.3 Cascade changes in logical data model to an existing physical data model

You can make changes to your logical data model. For example, you can make some changes to the columns for entities IMAGE_T and LINK_FILTER_T. Then, you can use the model transformation feature to update your existing physical data model with these changes. The Compare and Sync feature enables you to remove some model objects and properties that you might not want to synchronize. For example, it is possible that the imported physical data model was imported with labels. If during the transformation you keep the default option of using the name (instead of a label) this will cause a discrepancy between the two models. You can ignore this option using the filter capability of Compare and Sync

To transform a logical data model to an existing physical data model:

1. Select the logical data model (Imported2IDA.ldm in our example) in the Data Project Explorer.

2. Navigate to Data > Transform > Physical data model. The Transform to Physical Data Model wizard opens.

3. Select Update existing model . Click Browse and select the existing physical data model (Imported2IDA.ddm in our example). Click Next. The next Transform To Physical Data Model wizard appears as shown in Figure 24.

4. Ensure the Generate traceability check box is selected. This will add a dependency to each column on the physical data model so that you can use the Analyze Impact feature. The Analyze Impact feature shows you the objects that would be impacted.

5. Click Next. The next Filtering Criteria wizard appears as shown. Select the filters options that relate to the type of changes made in the logical data model. In our example, since we make changes to a column, we select column-related filter options.

of 51


Figure 29. Comparison editor filter options

6. Click Next. Click Finish on next wizard. This will bring up the comparison editor as shown in Figure 29.

of 51


Figure 30. Comparison editor filter options

Differences are visualized within a table-tree structure. You can navigate through each of the differences and determine if each changed element should be copied to the existing target physical data model. You can use the Copy Left to Right button to perform the copy command. Once you are done with the update, you can use the Generate Right Delta DDL to generate the necessary DDL scripts for deploying the changes to the target database.

After you click the Generate Right Delta DDL button, the Generate DDL wizard shows up and you can examine the generated delta DDL script. You can run the script immediately on the target server or save the script to your project for later processing.

of 51


Figure 31. Generate DDL

7.3 Domain model

Domain types are migrated automatically to InfoSphere Data Architect during the import process. They are imported as part of the logical data model. For the sample model, below figure shows the imported domains.

of 51


Figure 32. Domains imported from ERwin file

Domain model objects can be stored in a domain model (*.ddm) file or in a logical data model (*.ldm) file. You can create one domain model and share it across multiple projects and users for maximum efficiency. It is a best practice to group all domains into one or more domain models, so that domains can be reused in future projects.

Although ERwin domains are imported as part of the logical data model, section 7.3.2 will guide you on creating a domain model using the compare and synchronize feature of InfoSphere Data Architect.

Domain model objects are always contained in a package object. As with the logical data model, there is always one root package, but you can add additional packages under the root package to group similar domain objects together.

Figure 33. Domain Model

of 51


7.3.1 Create new domains or modify the properties of existing ones

You can specify constraints for the base data type and, unique to InfoSphere Data Architect, you can document the level of classification given to the domain. You can specify options such as personal identification information (PII or confidential), the level of enforcement (Enforced, Not Enforced, Best Practice), and the masking method to use (if masking is required) on the domain. All of these options are used for modeling and reporting purposes. Data masking is used to ensure data privacy in non-production environments for purposes such as software development and testing, software user training, or data mining and research. You can use InfoSphere Data Architect with the IBM® Optim™ product to mask your enterprise test data.

Figure 34. Data Privacy tab of an Atomic Domain

If you decide to use a domain model for an imported ERwin model, remember to change the attributes in your logical and physical data models to point to the new domain model. Best practice is to change the logical data model and transform this updated logical data model to your existing physical data model.

7.3.2 Convert ERwin domains to Domain Models

ERwin domains are imported as part of the logical data model, which means they cannot be shared with other models. This section will guide you on creating a domain model using the compare and synchronize feature of InfoSphere Data Architect.

After your models are imported,:

1. Create a domain model

• Right click on the Data Models folder from your project, select New and then Domain Model

of 51


• Give your domain model a name and click Finish to create your blank domain model. In our example, the domain model is Enterprise Domain Model

Figure 35. Creating a Domain Model

2. Compare domains from the imported logical data model with new domain model

• Right click on the <<Domains>> package from your imported logical data model, select Compare With and Another Data Object. Choose the target domain model (ddm) for your compare. In our example the domain model is called Enteprise Domain Model.ddm

Figure 36. Compare logical data model domain with domain model

of 51


3. Filter the compare and synchronize to Atomic, Union and List Domains

Figure 37. Compare with Another Data Object filtering criteria

4. Synchronize the domains

• In the compare/synchronize screen, select all the domains from the logical data model and then select Copy from Left to Right to synchronize the model.

of 51


Figure 38. Synchronize logical data model domain with domain model

• Save your domain model . Your domain model will now have the synchronized domains.

Figure 39. Resulted domain model

5. Ensure that your domain model is a source to the project

• Check the properties and domain references of your project. In our example, our project is ERwin2IDA. Add the domain model if there is no domain reference for your project.

of 51


Figure 40. Ensuring that the Domain Model is referenced in your project

6. You are now ready to use the domains that came from ERwin into any of your new data models. Note that there is a current limitation on IDA, when the domain model and the logical data model local domain package contain the same domain names, y default IDA will use the local domain.

8 More on terminology and feature differences

ERwin and InfoSphere Data Architect use different terminology to describe some of their model objects and features. The following table compares some of the key concepts in these two tools.

of 51


Concepts InfoSphere Data Architect ERwin

Model Organization Workspace and projects. A workspace can have multiple projects. A project can have multiple data models.

File based

Business Area Package and subpackages.

A package can have multiple packages (subpackages).

Logical data models can be partitioned by creating submodels from the subpackages.

Main Subject Area and individual subject areas.

Diagrams Diagrams are treated as visual representation of the metadata of the model. Multiple diagrams can be created within each package or subpackage.

Diagrams can be linked together to easily group related diagrams, describe relationships between packages and navigate between diagrams.

The diagram that exist in the main subject area is treated as the metadata and any object deleted from the main subject area is deleted from the model.

Notations IE for Logical data model

IE and UML for physical data model

IE and IDEF for both logical and physical data models

User-defined properties Annotation (for name value pair), dependency, URL

UDP

Model validation Analyze model Model validator

Model comparisons Compare and sync Complete compare

Impact analysis Analyze Impact None

Report 1. ‘Web publishing’ – a navigable hypertext-linked Web of HTML pages.

2. BIRT reporting with built-inpre-defined templates. Templates are customizable.

ERwin report designer

Enterprise Terminology IDA local glossary model for naming standards. Provides integration with InfoSphere Business Glossary which is the IBM solution for centralizing enterprise wide business vocabulary

Naming Standards model

of 51


Concepts InfoSphere Data Architect ERwin

Domain support Domain models. Domain model objects can be stored in a domain model (*.ddm) file, or in a logical data model (*.ldm) file.

data privacy information, such as classification, privacy policy and masking algorithms can be added to atomic domain data types.

Domains can be drag and drop to the object to create the entity/table or attribute/column

Team Support and RepositoryUse standard SCM for versioning and parallel team collaboration. In addition, ‘sub packages’ can be created into ‘sub models’; each ‘sub model’ is a separate physical file. This flexibility facilitates check in/check out and minimize collisions.

Requires the purchase of ModelMart, need to maintain a separate proprietary server

Dimensional Model Dimensional model notations and objects are supported in logical and physical data models

Dimensional models are supported in physical data models

Layout Arrange All provides multiple ways to auto arrange the diagram, depending on the user’s preference.

9 Conclusion

This guide facilitated the migration of CA ERwin logical and physical data models to InfoSphere Data Architect and offered you a better understanding of InfoSphere Data Architect.

10 Appendix Additional Educational Resources

Rational Data Architect was renamed to InfoSphere Data Architect in December 2008. Although many of the articles mentioned in these resources refer to Rational Data Architect, the information applies to InfoSphere Data Architect as well, with possible minor changes because InfoSphere Data Architect is based on a later version of Eclipse 3.4.

•Core features of InfoSphere Data Architect:

1. Migrate ERwin data models to RDA

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0709vasconcelos/

of 51


http://www.ibm.com/developerworks/db2/library/techarticle/dm-0709vasconcelos/

2. Best Practices Information Modeling with Rational Data Architect Version 7


3. Use IBM RDA to take control of your DB2 database

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0710kokkat/

4. Use IBM RDA to model your Oracle databases


5. Use RDA to define and enforce data object naming standards

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0701liu/

6. Extend IBM Rational Data Architect to meet your specific data modeling and integration requirements, Part 1:


7. Extend IBM Rational Data Architect to meet your specific data modeling and integration requirements, Part 2:


•Unique features of InfoSphere Data Architect:

1. Making your application model and data model work together by integrating Rational Software Architect and Rational Data Architect

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0708chang/

2. Integrate Web Sphere Business Modeler and Rational Data Architect

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0711selvage/

3. Use RDA to integrate data sources

http://www.ibm.com/developerworks/library/ar-rdaint/

4. Access and integrate enterprise metadata with Rational Data Architect

http://www.ibm.com/developerworks/edu/ar-dw-ar-wbirda.html

5. Discover schema relationships with Rational Data Architect

of 51


http://www.ibm.com/developerworks/edu/ar-dw-ar-wbirda.html

http://www.ibm.com/developerworks/library/ar-rdaint/

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0711selvage/

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0708chang/







http://www.ibm.com/developerworks/edu/ar-dw-ar-rdamap.html

6. The information perspective of SOA design, Part 5: The value and use of Rational Data Architect in SOA

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0803sauter2/index.html

7. Using InfoSphere Data Architect to import and export XML

http://www.ibm.com/developerworks/data/library/techarticle/dm-1009infospherexml/index.html

11 Acknowledgments and Notices

The authors would like to thank Joe Cullen, Principal Consultant from Dimension Software Consulting Inc. for providing valuable information in converting ERwin domains to IDA Domain Models.

© Copyright IBM Corporation 2010 IBM Software Group Route 100 Somers, NY 10589

Produced in the United States of AmericaOctober 2010All Rights Reserved

DB2, IBM, the IBM logo, InfoSphere, and Rational are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries or both.

Other company, product or service names may be trademarks or service marks of others.

References in this publication to IBM products or services do not imply that IBM intends to make them available in all countries in which IBM operates. All statements regarding IBM’s future direction and intent are subject to change or withdrawal without notice.

The information contained in this document is provided for informational purposes only. While efforts were made to verify the completeness and accuracy of the information contained in this document, it is provided “as is” without warranty of any kind, express or implied. In addition, this information is based on current plans and strategy, which are subject to change by IBM without notice. IBM shall not be responsible for any damages arising out of the use of, or otherwise relating to, this document or any other documents. Nothing in this document is intended to, nor shall have the effect of, creating any warranties or representations from IBM Software.

of 51


http://www.ibm.com/developerworks/data/library/techarticle/dm-1009infospherexml/index.html

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0803sauter2/index.html

http://www.ibm.com/developerworks/edu/ar-dw-ar-rdamap.html

migrating from ca erwin to infosphere data architect - ibm

Documents