| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
This chapter shows you how to import definitions from different data sources for use in Warehouse Builder. You first import these definitions into the source modules.
This chapter includes the following topics:
Importing is also known as reverse engineering. It saves design time by bringing metadata definitions of existing database objects into Warehouse Builder. You use the Import Metadata Wizard to import metadata definitions into target modules.
The Welcome page of the Import Metadata Wizard lists the steps you follow to import metadata from source applications into the appropriate module. You can also use this wizard to import PL/SQL transformations into a warehouse module.The Import Metadata Wizard supports importing tables, views, dimensions, cubes, external tables, sequences, queues, user defined types, and PL/SQL transformations directly or through object lookups using synonyms.
Importing a table includes importing its columns, primary keys, unique keys, and foreign keys, which enable import of secondary tables. When you import an external table, Warehouse Builder also imports the associated location and directory information for the associated flat file.
These definitions can be imported from either the Oracle Database catalog or Designer/2000 (Oracle Designer).
Use the Import Metadata Wizard to import metadata from a database into a module. You can import metadata from an Oracle Database, a non-Oracle Database, or a Designer repository.
To import definitions from an Oracle Data Dictionary:
Right-click a data source module name and select Import.
The Welcome page of the Import Metadata Wizard is displayed. This page lists the steps to import object metadata. Click Next to proceed with the import.
If you did not specify the location details for the Oracle module, Warehouse Builder displays the Warehouse Builder Warning dialog. This dialog informs you that you must first specify the location details. Click OK. The Edit Oracle Database Location dialog for the Oracle module is displayed. Use this dialog to specify the location information. Clicking OK on this dialog displays the Welcome page of Import Metadata Wizard.
Complete the following pages:
Use the Filter Information page to limit the search of the data dictionary. Use one of the following methods to limit the search:
Selecting the Object Type The Object Type section displays the types of database objects that you can import. This include tables, dimensions, external tables, sequences, queues, cubes, views, PL/SQL transformations, and user-defined types. Select the types of object you want to import. For example, to import three tables and one view, select the Tables and Views options in the Object Type section.
Search Based on the Object Name Use the Only select objects that match the pattern option to type a search pattern. Warehouse Builder searches for objects whose names match the pattern specified. Use % as a wild card match for multiple characters and _ as a wild card match for a single character. For example, you can type a warehouse project name followed by a % to import objects that begin with that project name.
Click Next and Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page.
Select items to import from the Available list and click the arrow to move them to the Selected list.
To search for specific items by name, click the Find Objects icon that displays as a flashlight.
To move all items to the Selected Objects list, click the double arrow.
Importing Dependent Objects The Import Metadata wizard enables you import the dependent objects of the object being imported. If you are re-importing definitions, previously imported objects appear in bold.
To specify if dependent objects should be included in the import, select one of the following options:
None: Moves only the selected object to the Selected list. No dependencies are imported when you select this option.
One Level: Select this option to move the selected object and the objects it references to the Selected list. This is the default selection.
All Levels: Select this option to move the selected object and all its references directly or indirectly to the Selected list.
Click Next and the Summary and Import page is displayed.
Importing Dimensions When you import a dimension that uses a star schema relational implementation, the implementation table that stores the dimension data is not imported. You must explicitly import this table by moving the table from the Available list to the Selected list on the Object Selection page. Also, after the import, you will need to bind the dimension to its implementation table. For more information on how to perform binding, see "Binding".
This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reimported or created. Verify the contents of this page and add descriptions for each of the objects.
If the objects you selected on the Object Selection page already exist in the module into which you are attempting to import them, you can specify additional properties related to the reimport. Click Advanced Import Options to specify options related to reimporting objects. The Advanced Import Options dialog is displayed. For more information on the contents of this dialog, see "Advanced Import Options".
Click Finish to import the selected objects. The Importing Progress Dialog shows the progress of the import activity. After the import completes, the Import Results page is displayed.
This page summarizes the import and lists the objects and details about whether the object was created or synchronized.
Click OK to accept the changes. To save an MDL file associated with this import, click Save. Click Undo to cancel the import.
Warehouse Builder stores the definitions in the database module from which you performed the import.
Re-importing your source database definitions enables you to import changes made to your source metadata since your previous import. You do not have to remove the original definitions from the repository. Warehouse Builder provides you with options that also enable you to preserve any changes you may have made to the definitions since the previous import. This includes any new objects, foreign keys, relationships, and descriptions you may have created in Warehouse Builder.
To re-import definitions:
Right-click a data source module name and select Import.
The Welcome page for the Import Metadata Wizard is displayed.
Click Next.
The Filter Information page is displayed.
Complete the Filter Information Page and Object Selection Page, selecting the same settings used in the original import to ensure that the same objects are re-imported.
The Summary and Import page displays as shown in Figure 16-1. The Reimport action is displayed for the objects that already exist in the repository or that you are re-importing.
If the source contains new objects related to the object you are re-importing, the wizard requires that you import the new objects at the same time. The Create action displays for these objects.
Figure 16-1 Summary and Import Page Showing Reconcile Action
Click Advanced Import Options and make selections. (Optional)
Click Finish.
Warehouse Builder reconciles and creates objects. When this is complete, the Import Results dialog displays, as shown in Figure 16-2.
The report lists the actions performed by Warehouse Builder for each object.
Click Save to save the report. You should use a naming convention that is specific to the re-import.
Click OK to proceed.
Click Undo to undo all changes to your repository.
The Advanced Import Options dialog displays the options that you can configure while importing objects. This dialog enables you to preserve any edits and additions made to the object definitions in the Warehouse Builder repository. Figure 16-3 displays the Advanced Import Options dialog for tables.
Figure 16-3 Advanced Import Options Dialog for Tables
By default, all options on this dialog are checked. Clear boxes to have these repository objects replaced and not preserved.
For example, after importing tables or views for the first time, you manually add descriptions to the table or view definitions. If you want to make sure that these descriptions are not overwritten while reimporting the table or view definitions, you must select the Preserve Existing Definitions option. This ensures that your descriptions are not overwritten.
The contents of this dialog depend on the type of objects being imported. For more information about the advanced import options for each type of objects, refer to the following sections:
Advanced Import Options for Views and External Tables Select these options for reconciling views or external tables:
Import descriptions: The descriptions of the view or external table are imported. Existing descriptions in the repository are not preserved.
Preserve repository added columns: The columns you added to the object in the repository are preserved.
Advanced Import Options for Tables Select these options for reconciling tables:
Preserve repository added columns: Select this option to retain any columns added the table in the repository.
Preserve repository added constraints: The constraints you added to the table in Warehouse Builder are preserved.
Import indexes: Select this option to specify additional details about how indexes should be imported. Importing indexes consist of the following options:
Preserve repository added indexes: Select this option to retain any indexes added to the repository table.
Import physical properties of indexes: Select this option to indicate how indexes should be imported. Select the Preserve repository added physical properties of indexes option below this option to specify that any physical properties added to the indexes should be preserved.
Import index partitioning: Select this option to indicate how index partitions should be imported. Select the Preserve repository added index partitioning option to specify that any index partitions added to the repository table must be preserved.
Import Partitioning: Select this option to specify additional details about how partitions should be imported. Importing partitions contains the following options:
Preserve repository added partitioning: Select this option to retain all partitions added to the repository table.
Import physical properties of partitioning: Use this option to indicate how the physical properties of partitions should be imported. Select Preserve repository added physical properties of partitioning to indicate that all physical properties of the partitions in the repository table should be retained.
Import physical properties: Select this option to indicate how the physical properties of the table should be imported. Select the Preserve repository added physical properties option to specify that all physical properties added to the repository table must be preserved.
Import descriptions: Select this option to import the descriptions of the table.
Advanced Import Options for Object Types
Import descriptions: Select this option to import the descriptions of the object type.
Preserve repository added attributes : Select this option to retain the attributes added to the object type in the repository.
Advanced Import Options for Queue Tables, Advanced Queues, Streams Queues, and SQL Collections (Nested Tables and Varrays)
Import descriptions: Select this option to import the descriptions of the queue table, advanced queue, streams queue, nested table, or Varray.
The Edit Module dialog enables you to edit the name, metadata location, and the data location of a source module.
To update the database definitions:
Double-click any Oracle module.
The Edit Module dialog displays. You can edit the metadata location as well as the data location of the database.
To edit the metadata location, click the Metadata Location tab and specify the following:
Source Type: The source type identifies the location of the data and the metadata. It can be either Oracle Data Dictionary or Oracle Designer Repository. Select Oracle Data Dictionary if the metadata is stored in the default repository of the Oracle Database. Select Oracle Designer Repository if the metadata is stored in an Oracle Designer repository.
Location: Identifies the location of the module. You can select a location from the drop-down list.
To edit the data location, click the Data Location tab. You can either select from the existing locations or create a new location. To create a new location, click New. The Edit Oracle Database Location window displays. You can specify the data location here.
In Warehouse Builder, you can create a source module that connects with 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 Element contains definitions for namespace and ownership of objects and enables you to view objects even though they are owned by a different user. Because Designer 6i/9i container elements are controlled by workareas, they have version control. 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 in Warehouse Builder. To select Designer 6i/9i objects as Warehouse Builder sources:
Specify a workarea and
Specify the container element in the workarea
The Module Editor detects the Designer version. If it finds Designer 6i/9i, the Metadata Location tab shows two drop-down 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 drop-down lists, Workarea and Container Element, are visible in the Metadata Location tab as shown in Figure 16-4.
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.Click OK.
For related information, see the following sections:
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
