16 Importing from CA ERwin and Other Third-Party Design Tools

Scenario

A movie rental company uses tools from different vendors for data modelling, extraction, transformation and loading (ETL), and reporting purposes. Using a variety of tools has led to several metadata integration issues for this company. Often, the design work done using one tool cannot be completely integrated or reused in another. This company wants to find a method to streamline and integrate all its metadata designs and ETL processes using a single tool.

Solution

Warehouse Builder enables the company to import and integrate metadata designs from different tools and use them for data modelling and ETL purposes using only one tool. Warehouse Builder uses the seamlessly integrated technology from Meta Integration Technology Inc. (MITI) to import the metadata and reuse the data models designed by other third-party tools.

This case study shows you how to easily import design files developed using CA ERwin into Warehouse Builder. You can then reuse the metadata for ETL design and reporting using a single tool. You can follow this model to import files from other tools such as Sybase PowerDesigner and Business Objects Designer.

Case Study

This case study shows you how the movie rental company can migrate their ERwin data model designs into Warehouse Builder. They can also use this model to import designs from other third party tools and consolidate their design metadata in a central workspace. Follow these steps:

1. Download Metadata from CA ERwin

2. Install the Meta Integration Model Bridge

3. Create an MDL File from the CA ERwin Data

4. Import the MDL file into Warehouse Builder

Use Warehouse Builder Transfer Wizard to import the ERwin metadata into Warehouse Builder.

Download Metadata from CA ERwin

Download the design metadata from CA ERwin to your local system.

Install the Meta Integration Model Bridge

Warehouse Builder enables you to integrate with Meta Integration Model Bridges (MIMB). These bridges translate metadata from a proprietary metadata file or repository to the standard CWM format that can be imported into Warehouse Builder using the Warehouse Builder Transfer Wizard. To import files from different design tools into Warehouse Builder, you must first obtain an MIMB license and install the bridges on your system. Follow these steps to complete the installation.

To download MIMB:

1. Download the Model Bridge product from the following Web site:

   http://www.metaintegration.net/Products/Downloads/

2. Install the MIMB by running the setup on your system.

3. During installation, select Typical with Java Extensions as the installation type from the Setup Type page.

   If the set up program is not able to find a JDK on your computer, you must provide the JNI library directory path name. Your path environment variable must contain the metaintegration directory. If not, you must add it to the path:

   c:\program files\metaintegration\win32

Create an MDL File from the CA ERwin Data

Create an MDL file from CA ERwin using Warehouse Builder.

After you install the MIMB product, follow these steps to create an MDL file from ERwin and other third party design tools:

1. From the Project Explorer, select and expand the Project node to which you want to import the metadata. In this example, the ERwin files are imported into MY_PROJECT.

2. From the Design menu, select Import, Bridges to start the Warehouse Builder Transfer Wizard.

   This wizard seamlessly integrates with the MITI technology to translate the third-party metadata into a standard CWM format that is imported into Warehouse Builder. Follow the wizard to complete the import.

3. In the Metadata Source and Target Identification page, select CA ERwin 4.0 SP1 to 4.1 in the From field.

4. In the Transfer Parameter Identification page, provide the path where the ERwin files are located in the Erwin4 Input File field. In this example, the company wants to import the Emovies.xml file from ERwin.

5. Accept the default options for all other fields.

   In the OWB Project field, enter the Warehouse Builder project where you want to import the ERwin file. In the Warehouse Builder MDL field, enter a name and select the location to store the .mdl file that will be generated.

6. Complete the remaining wizard steps and finish the import process.

Import the MDL file into Warehouse Builder

Import the MDL file to import metadata from the CA ERwin file into Warehouse Builder. To import the MDL file:

1. Select MY_PROJECT and from the Design menu, select Import, Warehouse Builder Metadata to open the Metadata Import dialog box.

2. In the File Name field, specify the name of the mdl file you generated in "Create an MDL File from the CA ERwin Data".

3. Click Import to import the metadata into Warehouse Builder.

   If the metadata file version and the workspace version are not compatible, then the Metadata Upgrade window pops up. Click Upgrade to upgrade the .mdl file.

4. After you finish importing the ERwin files into Warehouse Builder, expand the MY_PROJECT folder, then the Databases node, and then the Oracle node. You can see the imported source metadata objects, as shown in Figure 16–1.

   Figure 16-1 Metadata Objects Imported from CA Erwin

   
   Description of "Figure 16-1 Metadata Objects Imported from CA Erwin"
   
   

5. Double-click the table names to see the properties for each of these tables. Warehouse Builder imports all the metadata including descriptions and detailed information on table columns and constraints, as shown in Figure 16–2.

   Figure 16-2 Table Properties Imported from CA Erwin

   
   Description of "Figure 16-2 Table Properties Imported from CA Erwin"
   
   

6. The designers at the movie rental company can use these sources tables to model ETL designs in Warehouse Builder, generate ETL code, and run reports on them. Furthermore, Warehouse Builder enables them to easily import all the scattered third-party design metadata and consolidate all their design and development efforts.