12 Using the Metadata Loader

Importing Objects

To import objects from an export file using the Design Center:

1. From the Design Center, select File, select Import, and then select Warehouse Builder Metadata.

   
   Description of the illustration importing_01.gif
   
   

2. [Optional] If you had made changes to the repository metadata prior to running the import utility, a warning dialog box is displayed.

   Click Save to save changes or Revert to revert to the previously saved version.

   
   Description of the illustration importing_02.gif
   
   

3. In the Metadata Import dialog box:

   • Click Browse to specify the File Name (name and location) of the import file.

   • Click Browse to specify the Log File (name and location).

   • Under Object Selection, choose either Import all objects from file or Import selected objects from file.

   • Under Import Option, select one of Create new metadata only, Update metadata replace existing objects and create new metadata, Merge metadata merge existing objects and create new metadata, or Replace existing objects only.

   • Under Match by, select one of Universal Identifier or Names.

   • [Optional] Click Show Summary to view a summary of the contents of the export file. See "File Summary Dialog Box".

   • [Optional] Click Advanced to import metadata for additional languages, security information, or user-defined properties. See "Import Advanced Options Dialog Box".

   See "Metadata Import Dialog Box".

4. Click Import.

5. The Metadata Import Progress dialog box is displayed; it indicates the progress of the import. Note that you can cancel an in-progress Metadata Import by clicking Cancel. See "Metadata Progress Dialog Box".

If the MDL file that you selected for import was created using an earlier version of Warehouse Builder, clicking Show Summary, Advanced, or Import displays the Metadata Upgrade dialog box. This dialog box enables you to automatically upgrade the selected MDL file to the current version of Warehouse Builder. For more information about this dialog, see "Metadata Upgrade Dialog Box".

Combining Import Modes and Matching Criteria

Each search method used as matching criteria may be combined with an import mode. Each combination offers different results in the import process. The mode that you select determines how the metadata import utility searches for metadata objects in the workspace, prior to importing.

For example, if the search is by the name of a repository object in the export file, the Metadata Import Utility searches the workspace for the object's name. If an object with the corresponding name is not found, the resulting actions are based on the import mode you select.

Table 12-1 describes what happens in the available import modes for repository objects that either match or do not match the object names.

The MDL reads and processes the imported metadata and writes status and diagnostic information in the log file.

Import Different Base Languages

When you import metadata in multiple languages, the language settings in the target repository may be different from the language settings in the export file. For example, the target repository may have the base language as English and additional language as French and German. But the export file may have the base language as French and additional language as English and German. This section describes how the MDL handles these conditions.

Import Validation Rules

When you import a set of definitions from previously exported metadata, the import utility updates existing definitions within a project. However, certain metadata definitions require attention to ensure that they are updated correctly. The following are examples of some of the errors you may see:

• Mapping Definitions. The Metadata Import Utility binds imported mapping operators to their physical objects if the associated objects exist in the workspace. However, if the associated physical objects do not exist in the workspace, the imported mapping operators are not bound. The Metadata Import Utility writes a warning message in the log file, which states that the mapping operators are not bound. You must then synchronize the new mapping operators with the physical objects they represent.

• Foreign Key Definitions. It is possible that a source MDL file contains foreign key references to unique or primary keys that are not in the target workspace. If the referenced unique or primary keys for any foreign key appearing in the MDL file do not exist in the target workspace, the MDL Import Utility writes a warning message in the log file, which states that the workspace does not contain a referenced key for the foreign key.

Changes to Workspace Objects When Upgrading from OWB 9.2

When you upgrade from OWB 9.2, the upgrade utility makes the following changes to objects in the workspace:

• Dimension and Cube Mapping Operators: The mapping operators for dimensions and cubes are converted to table operators. These table operators use the physical tables created by the MDL Upgrade Utility for dimensions and cubes.

• Dimensions: An associated dimension table is created with the same name as the dimension. The table contains the columns, constraints, and attribute sets defined in the Dimension Editor Table Properties of the dimension in the previous release.

• Mapping Display Sets for Dimension Hierarchies: Any mapping sets originally created based on the named attribute set for a dimension hierarchy are removed. This is because display sets for dimension hierarchies are no longer automatically created and maintained.

• Dimension Attributes: For each level attribute upgraded, a dimension attribute with the same name is created, if it does not exist in the dimension.

• Cubes: An associated cube table is created with the same name as the cube. The cube table contains columns, constraints, and attribute sets defined in the Cube Editor Table Properties of the cube in the previous release.

• Intelligence Objects and Reports: In the previous release, intelligence objects and reports were available only using OMB Plus scripting. These objects are not upgraded.

• Locations and Control Center Connections: Locations and Control Center connections are moved out of the projects that own them so that they can be shared across the entire workspace. Thus the statistics in the import log file displays an additional project for these objects.

• Control Centers and Locations: After an upgrade, there is no association between the locations and the control centers that they reference. You must review the control center details using the Edit Control Center dialog box and select the locations associated with this control center.

• Advanced Queues: An associated queue table is created based on the property AQ queue table name. The queue table created by the MDL File Upgrade Utility contains a column whose data type is the object type for that advanced queue.

• Advanced Queue Operator in a Mapping: Mapping Advanced Queue operators are changed to contain only one attribute called PAYLOAD. For Mapping Advanced Queue operators that are used as a source, a new Expand operator is added after the Mapping Advanced Queue operator. For Mapping Advanced Queue operators that are used as a target, a new Construct operator is added before the Mapping Advanced Queue operator.

• Mapping Operator Names: The MDL Upgrade Utility ensures that the physical names and business names of all mapping operators are unique.

Changes to Workspace Objects When Upgrading from OWB 10.1, 10.2 or 11.1

When you upgrade from OWB 10.1, 10.2, or 11.1 to OWB 11.2, the upgrade utility makes the following changes to objects in the workspace:

• Mapping Table Function Operators: The mapping operators for table functions are upgraded to have only one input group and one output group. Old input groups that were configured as a REF cursor type are upgraded to a SYS_REFCURSOR type, and a new construct operator is added to the map; its input group values are the same as in the REF cursor type input group, and the output group attribute is set to SYS_RECURSOR type.

• Mapping Key Lookup Operators: The mapping operators for key lookups are upgraded to the new lookup operator.

• Stream Capture Process Objects: Stream capture process objects are not upgraded.

• Business Presentation Modules: Business presentation modules and their contents are not upgraded.

• BI Beans Locations: BI beans locations are not upgraded.

Checking for Warnings and Error Messages

After upgrading the metadata, check the log file for warnings and errors.

• If you receive warnings during the upgrade, the upgrade utility completes and logs the warnings. If you receive errors, the upgrade utility terminates and logs the errors.

• If warnings and errors are shown after an upgrade, search for the words Warning and Error in the log file to determine the problem.

• If an unexpected error occurs and the upgrade terminates, the log file contains the details. Check your log file or contact Oracle Support.

Metadata Export Dialog Box

The Metadata Export dialog box displays the names and the types of the exported objects.

The Metadata Export dialog box also contains the following items:

• Annotations: Use this field to enter any comments about the file that contains the exported objects.

• File Name: Displays a default path and file name for the export file. You can retain this default or specify a directory and file name. Type the name of the export file to create or click Browse to locate a directory or file. The file name extension commonly used is .mdl.

• Log File: Use this field to specify the file name and path for the log file that stores diagnostic and statistical information about the export. See "Metadata Loader Log File"

• Export all object dependencies: Select this option to export all the dependencies of the objects being exported. For example, when you export a table, the location to which the table is deployed is also exported.

  Note that public objects such as locations, public transformations, public experts, public icon sets, or public data rules belong to a project called PUBLIC_PROJECT. You can export the PUBLIC_PROJECT and its objects if the selected exported objects have a dependency on the public objects and if you select the Export all object dependencies option.

• Advanced: Use the Advanced button to export additional metadata such as user-defined properties, security information, and additional languages. See "Export Advanced Options Dialog Box".

Click Export to export the metadata for the selected objects. The Metadata Export Progress dialog box is displayed. See "Metadata Export Dialog Box" for more information about the contents of this dialog box.

Export Advanced Options Dialog Box

Use the Export Advanced Options dialog box to export any of the following:

• Additional language metadata

• User-defined definitions

• Security information

This dialog box contains two sections: Languages and Administration.

Metadata Import Dialog Box

Use the Metadata Import dialog box to specify the information required to import metadata in an export file.

Specify the following information on this dialog box:

• File Name: Type the name of the MDL file or click Browse to locate the MDL file you want to import.

• Log File: Type the name of the log file, along with the path, that will store diagnostic and statistical information about the import. You can also click Browse to locate the log file. See "Metadata Loader Log File" for more information about log files.

• Object Selection: The Metadata Import Utility enables you to select the objects that you want to import from the MDL file. The Object Selection section contains the following options:

  • Import all objects from file: Select this option to import all objects contained in the export file.

  • Import selected objects from file: Select this option to import only some of the objects contained in the MDL file. Click Select Objects to select the objects that you want to import. The Import Object Selection dialog box is displayed.

    The Import Object Selection dialog box is displayed contains two sections: Available and Selected. The Available section contains primary objects such as projects, modules, tables, views, and connections, which are specified in the MDL file. Expand the nodes in this section to view the objects they contain. When you select a node, all the objects that it contains are included in the import. For example, if you select a module node, all the objects contained in the module are imported. Use the shuttle buttons to move the selected objects from the Available section to the Selected section.

    The MDL file that is imported may also contain administrative objects. To import these administrative objects, you must have administrative privileges. If these privileges are absent, the following occurs:

    • The Available section of the Import Object Selection Page dialog box does not display any administrative objects that the MDL file may contain.

    • The Import Utility displays an alert that the user does not have the required administrative privileges to perform the import, provided the MDL file contains only administrative objects.

• Import Option: Use the Import Option section to select the import mode. You can select one of the following options for the import mode:

  • Create new metadata only: This option, known as create mode, adds new objects to a workspace.

  • Update metadata (replace existing objects and create new metadata): This option, known as update mode, adds new objects to a workspace and also replaces existing objects with those in the MDL file that is imported.

  • Merge metadata (merge existing objects and create new metadata): When you select this option, known as merge mode, the MDL adds new objects and overwrites existing objects in the workspace only if they differ from those in the MDL file. The merge mode does not delete existing objects. Note that you cannot import metadata using the merge mode for mappings, pluggable mappings, and data auditors.

  • Replace existing objects only: This option, known as replace mode, replaces existing objects in your workspace but does not add new objects. In this mode, the Metadata Import Utility overwrites any existing metadata.

  When you import metadata using the update or the replace modes, the import completely replaces the child objects of existing objects, so that the final object is exactly the same as the source object. Any existing children of a repository object that are not replaced or added are deleted. This occurs regardless of whether a child object occurs in a mapping or is a foreign, primary, or unique key column in a table or view.

  For example, in the MDL export file, the CUST table contains three columns with the physical names: last_name, first_name, and middle_init. In the workspace, the same table exists, and contains four columns with the physical names: last_name, first_name, status, and license_ID. During a replace operation, the columns last_name and first_name are replaced, column middle_init is added, and column status and license_ID are deleted. The final result is that the CUST table in the workspace contains the same metadata from the CUST table in the export file.

  Note that using the replace and update modes may cause loss of data constraints, metadata physical property settings, data loading properties, and mapping attribute connections. If you choose to use replace or update modes, ensure that you have the option of restoring the workspace from a backup.

• Match By: When you use the metadata import utility, it first searches the workspace for metadata objects that exist in the workspace and compares them to those in the import file. To compare metadata in the import file with the existing workspace metadata, it uses the matching criteria. How the comparison is made is determined by the import mode and by the chosen search method.

  The Match By section provides the following options for matching criteria. By default, the import utility searches by UOIDs. Note that MDL import does not support merging existing mappings.

  • Universal Identifier option searches the workspace using the Universal Object Identifiers (UOIDs) of the objects that are imported. The Metadata Import Utility uses the UOIDs to determine whether an object must be created, replaced, or merged during the import operation. Use this method if you want to maintain UOIDs across different workspaces even when object names in the target workspace have changed.

  • Names option searches the workspace using the names of the objects that are imported. Physical names are exported to the export file; the physical name determines whether an object must be created, replaced, or merged during an import operation. Use this method if you want to create new UOIDs for those objects when object names in the target schema change.

Import Advanced Options Dialog Box

Use the Import Advanced Options dialog box to import any of the following:

• Additional language metadata

• User-defined definitions

• Security information

This dialog box contains two sections: Languages and Administration.

File Summary Dialog Box

The File Summary dialog box contains a brief summary of the contents of the export file. The information on this page is divided into the following sections:

• File section contains the name of the data file, the creation timestamp, the name of the export user, the workspace connection information, the version of the Design Center used for the export, and annotations.

• Administration section contains information about the users and roles. It also lists the following details:

  • Base language of the export file

  • Additional languages in the export file

  • Whether security information were included in the export file

  • Whether user-defined definitions were included in the export file

• Statistics section contains details about the types of objects contained in the export file and the number of objects of each type.

Metadata Upgrade Dialog Box

This dialog box is displayed automatically when it is determined that the MDL file being imported was created using a previous version. Use the File Name field to specify the name of the file that stores the upgraded MDL file. You can also click Browse to locate a directory or MDL file.

Click Upgrade to upgrade the MDL file to the current version. After the Upgrade completes, the Metadata Upgrade dialog box is closed. Click Cancel if you do not want to upgrade the MDL file.

Metadata Progress Dialog Box

The Metadata Progress dialog box is titled either Metadata Export Progress, Metadata Import Progress, or Metadata Upgrade Progress. It displays a progress bar that indicates the relative completeness (as a percentage) of the metadata export, import, or upgrade activity. If the operation is successful, a message indicating this displays above the progress bar; it may also display that an error occurred.

To view detailed information about the metadata export or import, click Show Details. The message log is displayed. The message log contains the following information:

• Start time of the export or import

• Names and types of objects exported or imported

• Warning or error messages

• End time of the export or import

• Location of the export or import log file

• Total export or import time in hh:mi:ss or milliseconds

You can hide the message log by clicking Hide Details.

To view details about the exported or imported objects, click Show Statistics. The Metadata Results dialog is displayed. See "About Metadata Loader Results".

Note that you can cancel an in-progress Metadata Export or Import by clicking Cancel.

Once the export or import completes, click OK in the Metadata Progress dialog.

Metadata Export Utility

The Metadata Export Utility extracts metadata objects from a workspace and writes the information into a ZIP format file. This ZIP file has an .mdl extension and contains the following files:

• Metadata Loader XML file: This file contains the objects extracted from the workspace and formatted in XML. It has the same name as the name of the ZIP file, but with the extension .mdx.

• Catalog: The catalog file is called mdlcatalog.xml and it contains internal information about the Metadata Loader XML file.

The Metadata Export Utility enables you to specify a file name and a path for the exported MDL file. For example, you export the repository metadata into a file called sales.mdl. When you unzip this MDL ZIP file, you obtain two files. The file sales.mdx contains the repository objects. The file mdlcatalog.xml contains internal information about the MDL XML file.

You can export an entire project, collections, public objects, locations, or any subset of objects. If you export a subset of objects, the MDL exports definitions for each object that you have selected and the parent objects to which the subset belongs. This enables the MDL to maintain the tree relationships for those objects during metadata import.

For example, if you export a single dimension, the export file contains definitions for the dimension, the module to which the dimension belongs, the project to which the module belongs, and the configurations for the dimension.

If you are exporting a subset of objects, ensure that you both export and import all referenced object. You may export the objects referenced by a set of objects by selecting the Export All Dependencies option on the Metadata Export dialog box. For example, if you export a table EMPLOYEES and it contains a foreign key reference to the table DEPARTMENTS, you may choose to export both tables.

Metadata Import Utility

The Metadata Import Utility reads the metadata information from an exported MDL file and creates, replaces, or merges the metadata objects into a workspace. It imports information that belongs to exported metadata objects, such as table columns and their constraints, data loading configuration parameters, and named attribute sets. The Metadata Import Utility enables you to import repository objects even if the references for those objects cannot be satisfied.

You can use the Metadata Import Utility to import objects into a project or a collection. The Metadata Import Utility only reads files created by the metadata export utility.

If the MDL file being imported was created using an earlier product version, the Metadata Import Utility automatically upgrades it to the current version.

Accessing the Metadata Loader

You can access the Metadata Loader using either the graphical user interface described in this section or using the OMB Plus scripting language described in Oracle Warehouse Builder API and Scripting Reference.

While the graphical interface guides you through the most commonly performed export and import tasks, the OMB Plus scripting language enables you to perform more specialized export and import tasks and enables you to manage a control file.