|Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This chapter includes the following:
You can create a source module that connects to an Oracle Designer repository. When the definitions for an application are stored and managed in an Oracle Designer repository, the time required to connect to the application is reduced.
Designer 6i/9i repositories use workareas to control versions of an object. By selecting a workarea, you can specify a version of a repository object. With Designer 6i/9i, you can also group objects into container elements within workareas. Container Elements contain definitions for namespace and ownership of objects, and enable you to view objects even if they are owned by a different user. Because Designer 6i/9i container elements are controlled by workareas, they are version controlled. See the Designer 6i/9i documentation for more information about workareas and container elements.
All visible objects of a workarea or a container element in Designer 6i/9i are available for use as data sources. To select Designer 6i/9i objects as a source:
Specify a workarea, and
Specify the container element in the workarea
The Module Editor detects the Designer version to which you are connected. If it finds Designer 6i/9i, the Metadata Location tab shows two lists, Workarea and Container Element. When you select a workarea, the Container Element list will show the container elements in that workarea.
The list of repository objects available for import is determined by the following criteria:
The object type must be supported by Warehouse Builder (Table, View, Sequence, and Synonyms).
The object must be accessible in the specified workarea. This determines the version of objects accessed.
The object must be visible in the specified container element. The list displays objects owned by the specified container element and other objects shared by the specified container element, but not owned by it.
To import definitions from a Designer 6i/9i source, you must follow the steps outlined in "Importing Definitions from a Database".
To create a Designer 6i/9i source module:
Create a database source module.
Double-click the name of the newly created module to open the Module Editor.
In the Metadata Location tab, select the source type as Oracle Designer Repository. Also select the database location containing the Designer object.
When you select the source type as Oracle Designer Repository, two new lists, Workarea and Container Element, are visible in the Metadata Location tab.
Select the Designer 6i/9i object from the workarea and select the specific container element.
Note:The database you specify as source must contain a Designer 6i/9i object. If not, then the Workarea and Element Container lists will be empty.
For related information, see the following sections:
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.
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.
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:
Use Warehouse Builder Transfer Wizard to import the ERwin metadata into Warehouse Builder.
Download the design metadata from CA ERwin to your local system.
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:
Download the Model Bridge product from the following Web site:
Install the MIMB by running the setup on your system.
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:
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:
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
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.
In the Metadata Source and Target Identification page, select CA ERwin 4.0 SP1 to 4.1 in the From field.
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.
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.
Complete the remaining wizard steps and finish the import process.
Import the MDL file to import metadata from the CA ERwin file into Warehouse Builder. To import the MDL file:
Select MY_PROJECT and from the Design menu, select Import, Warehouse Builder Metadata to open the Metadata Import dialog box.
In the File Name field, specify the name of the
mdl file you generated in "Create an MDL File from the CA ERwin Data".
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
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 5–2.
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 5–3.
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.