10 Version and History Management

Warehouse Builder provides several solutions for copying and moving metadata for the purposes of backup, history management, and version management. You can either take snapshots of your metadata or use the Metadata Loader (MDL) utility.

Note: A third solution is to utilize Transportable Modules, which requires the licensing of the Oracle Warehouse Builder Enterprise ETL Option. Refer to Chapter 12, "Moving Large Volumes of Data with Transportable Modules" for more information.

Snapshots enable you to capture the metadata definitions of design objects created in the Project Explorer. You can capture all the design objects in a project or selectively choose objects in a project to include in a snapshot.

The Metadata Loader utility enables you to copy and move all types of metadata objects in a repository. With this utility, you can move metadata between Oracle Database repositories that reside on platforms with different operating systems.

With both solutions, you can export the metadata into a third-party version control tool such as Oracle Repository, ClearCase, or SourceSafe.

This chapter includes the following topics:

Snapshots Versus the Metadata Loader
About Snapshots
Version and History Management with the Metadata Loader (MDL)
Using Metadata Loader in the Design Center

Snapshots Versus the Metadata Loader

Because snapshots and the Metadata Loader have overlapping capabilities, it may not be readily apparent when to use one versus the other. In general, snapshots address the needs a development team for tracking changes to an evolving design; the Metadata Loader facilitates the more traditional adminstrative tasks of backing up, restoring, and migrating repositories.

A key factor to understand is that snapshots and the Metadata Loader differ in the scope of objects they handle. Snapshots are limited to handling only the design objects users create in the Project Explorer. The Metadata Loader, however, handles everything in the repository including all design objects and information related to location and security.

Refer to Table 10-1 for a list of important differences between the two tools.

Table 10-1 Snapshots Versus the Metadata Loader

Feature	Snapshots	Metadata Loader
Scope	Captures metadata related to objects in the Project Explorer only. Captures metadata for mappings, process flows, and other design objects. Does not capture information about locations, security, and user defined objects.	Captures all metadata related to a repository including all design objects and information about locations, security, and user defined objects.
Output	You can create a full snapshot from which to restore objects. Or you can create a signature snapshot which requires less space and enables you to track changes but not restore objects. Both types of snapshots are stored in the Oracle Database.	When exporting, the Metadata Loader creates a ZIP file with the extension .mdl.You can use the output to restore a repository or populate a new repository on a different Oracle Database.
Integration with Version Control Tools	You can save the output as a file and then export the file into a version control tool.	You can export MDL files into a version control tool. When using the GUI, first extract the MDL file before putting the objects into the version control system. When using the OMB Plus scripting language, set ZIPFILEFORMAT=N as described in "About Metadata Loader Control Files".
Compatibility	Snapshots are not compatible between releases of Warehouse Builder.	MDL files are upwards compatible with newer releases of Warehouse Builder. When you import an MDL file from a previous release, the utility automatically upgrades the metadata to the new release.
Object Locking		To import, the Metadata Loader must obtain a lock on all the primary objects. If a necessary lock is not available, the import fails and the Metadata Loader rolls back any changes.
Compare	You can compare snapshots by launching a command in the user interface.	You can compare MDL files by using a third party diff tool.

About Snapshots

Using snapshots, you can backup and restore your metadata, maintain a history of metadata changes, and compare different versions of the metadata.

A snapshot captures all the metadata information about the selected objects and their relationships at a given point in time. While an object can only have one current definition in a workspace, it can have multiple snapshots that describe it at various points in time. Warehouse Builder supports the following types of snapshots:

Full snapshots provide backup and restore functionality.
Signature snapshots provide historical records for comparison.

Snapshots are stored in the Oracle Database, in contrast to Metadata Loader exports, which are stored as separate disk files. You can, however, export snapshots to disk files.

Snapshots can be useful to both warehouse managers and designers. Managers can use full snapshots to perform large-scale actions, such as deploying a warehouse or restoring a warehouse to a previous point in history. Designers can create full snapshots of a particular component under development so that, if necessary, they can restore the component to its previous state. They can also use signature snapshots to track the changes made to a particular component.

When used with other facilities, such as MDL metadata imports and impact analyses, snapshots can help you manage your metadata. For example, you can use snapshots to determine the impact an MDL metadata import will have on the current metadata workspace. With the knowledge gained by the comparison, you might import the metadata at a lower level of detail to avoid overwriting the definition of related metadata objects.

Snapshots are also used to support the recycle bin, providing the information needed to restore a deleted metadata object.

This chapter describes the metadata change management feature using the graphical user interface. For information about creating and managing snapshots using scripts, see Oracle Warehouse Builder API and Scripting Reference.

Creating Snapshots

When you take a snapshot, you capture the metadata of all or specific objects in your workspace at a given point in time. You can use a snapshot to detect and report changes in your metadata.

You can create snapshots of any objects that you can access from the Project Explorer.

Note that a snapshot of a collection is not a snapshot of just the shortcuts in the collection but a snapshot of the actual objects.

Opening the Create Snapshot Wizard

To open the Create Snapshot wizard and create a snapshot:

In the Project Explorer, select all the components you want to include in the snapshot. You do not need to select parent or child objects, because the wizard does that for you.

For example, if you select a collection that contains two cubes, then the snapshot includes both cubes and any gives you the option to include any dependent objects such as dimensions and source tables.
Right-click and select Snapshot, then New.

or

From the Design menu, select Snapshot, then New.
On the Name page, specify a name and the type of snapshot.

You can optionally provide a description.
Click Next to open the Components page of the wizard.

The page displays the components to be captured in the snapshot. If a component is a node level object, such as a module, then select the Cascade option to include the subcomponents.
On the Dependency page, specify the depth of dependency to include dependent objects in the snapshot.
Click Next to view the Finish page which provides the details of the snapshot.
Click Finish to create the snapshot.

Adding Components to a Snapshot

After you create a snapshot, you can add more components. Keep in mind, however, that when you add components, you are changing the historical record provided by the snapshot, so its creation date changes.

To update a snapshot, use the Add to Snapshot wizard.

Opening the Add to Snapshot Wizard

To open the Add to Snapshot wizard and add new components:

In the Design Center Project Explorer, select all the components you want to add to the snapshot. For example, you can select tables, mappings, and dimensions from an Oracle module.
From the Design menu, select Snapshot, then Add to Existing.

or

Right-click and select Snapshot, then Add to Existing.

The Welcome page of the Add to Snapshot wizard is displayed. The Welcome page lists the steps of the wizard.
Click Next.

The Snapshot page is displayed. From the list, select the snapshot to which you want to add the components.
Click Next.

The Components page is displayed. The page displays the components to be added to the snapshot. If a component is a folder level object, such as a module, then select the Cascade option to include the subcomponents.
Click Next.

The Finish page displays the details of the components to be added.
Click Finish to add the components to the snapshot.

Managing Snapshots

You can manage your snapshots from the Metadata Change Management window in the Design Center. To open this window, select Change Manager from the Tools menu.

The Metadata Change Management window contains a menu bar and a toolbar. You can start most tasks in several different ways, either by using the menus, clicking the tools, or right-clicking a snapshot or a component.

You can perform the following activities from the Metadata Change Management window:

Managing Snapshot Access Privileges
Comparing Snapshots
Restoring Workspace Objects From Snapshots
Exporting and Importing Snapshots
Deleting Snapshots

Figure 10-1 shows the Metadata Change Management window.

Figure 10-1 Metadata Change Management Window

Description of "Figure 10-1 Metadata Change Management Window"

Managing Snapshot Access Privileges

You can control access to snapshots just like any other object. By default, everyone has full access rights to the snapshots.

To change access privileges:

In the left section of the Metadata Change Management window, right-click the name of the snapshot.
Click Security from the menu.

The Snapshot Privilege Management dialog box is displayed.
For each role and user, select the privileges you want to grant and clear the privileges you want to deny. Click Help for additional information.

Comparing Snapshots

You can compare two snapshots or a snapshot with a current workspace object. The results list all objects and identify which objects are identical, which objects appear only in one place, and which objects appear in both but are not the same.

If you take a snapshot of a parent object and a child object changes, a snapshot comparison will show the parent object as having changed. For example, if you create a Cascade snapshot of a project, all the modules in that project are its children. If any of the modules change, a comparison of the snapshot to the workspace will show that the project has changed.

The Metadata Change Manager uses Universal Object Identifiers (UOIDs) as the basis for all comparisons. When you delete an object and re-create it with the same metadata, the object has a different UOID although it may be identical to the deleted object in all other aspects. When you compare the re-created object to its original version, the results show that all the metadata has changed. Likewise, objects will not match if they have been deleted and re-created during an import or Intelligence Object derivation.

Comparing Two Snapshots

Use one of the following methods:

From the Metadata Change Management window, select Compare from the Snapshot menu.

Or

Select the two snapshots you want to compare, right-click, and select Compare.

The Compare Two Snapshots dialog box is displayed. Snapshots that were selected in the Metadata Change Management window are also selected in the Compare Two Snapshots dialog box.
If you have not selected two snapshots for comparison yet, select one from each list.
Click Compare.

The Snapshot Comparison window displays the differences between the two objects. If there are none, then a message informs you that the objects are the same. For more information, click Help.

Comparing a Workspace Object with a Snapshot Component

To compare the current version of an object with a snapshot version:

Select the object you want to compare from the Design Center Project Explorer.
Right-click, select Snapshot, and then Compare.

The Choose Snapshot dialog box is displayed with a list of all the snapshots containing versions of that object.
Select the snapshot you want to compare with the current workspace object.
Click OK.

The Snapshot Comparison window displays the differences between the two objects. If there are none, then a message informs you that the objects are the same.

Converting a Full Snapshot to a Signature Snapshot

You can convert full snapshots to signature snapshots when they are no longer needed for backup. Conversion preserves the snapshot history and results in significant space savings in your workspace.

Note:

Converting a full snapshot to a signature snapshot means that you can no longer use that snapshot to restore metadata objects.

To convert a full snapshot:

Open the Metadata Change Management window, as described in "Managing Snapshots".
Select the snapshot that you want to convert.
From the Snapshot menu, select Convert to Signature.
On the Warning box, click Yes.

Restoring Workspace Objects From Snapshots

You can replace the current definition of an object in the workspace with the snapshot image of that object. You can only use full snapshots; you cannot restore objects from signature snapshots. You can restore all components or only selected components of the snapshot.

Note:

When you restore a collection from a snapshot, you restore both the collection and the actual objects.

Similarly, when you restore a container, all its child objects are also restored.

To restore only the collection or selected objects within a container, use the Components tab.

To restore objects from a snapshot:

Save any work that you have done in the current session.
Open the Metadata Change Management window, as described in "Managing Snapshots".
Select the snapshot that contains the version of the objects you want to restore.
To restore all objects, right-click the snapshot and select Restore.

or

To restore selected components, select them on the Components tab, right-click, and select Restore.

The Restore Snapshot dialog box is displayed.
Review the objects in the list and verify that the correct objects will be restored.
Select Cascade Up to restore a component whose parents no longer exists in the workspace. Warehouse Builder creates new parent objects for the component. Otherwise, the procedure will fail.
Click Restore.

Exporting and Importing Snapshots

You can export a full snapshot to an MDL file and use this file to re-create metadata objects either in the same database or a different one. You cannot export signature snapshots or individual components of a snapshot.

To export a snapshot:

Open the Metadata Change Management window and select the full snapshot you want to export.
From the Snapshot menu, select Export.

The Metadata Export dialog box is displayed.
Click Help for information about completing the dialog box.

To import a snapshot:

Open the Design Center and select Import from the Design menu.
Select Warehouse Builder Metadata from the menu.

The Metadata Import dialog box is displayed.
Click Help for information about completing the dialog box.

The imported snapshot will be listed in the Metadata Change Management window.

Deleting Snapshots

You can delete snapshots or components within a snapshot from the Metadata Change Management window.

To delete a snapshot:

Open the Metadata Change Management window and select the snapshot you want to delete.
Right-click the snapshot and select Delete.

Or

From the Snapshot menu, select Delete.

Warehouse Builder displays a delete confirmation box.
Click Yes to delete the selected snapshot from the workspace.

To delete components from a snapshot:

Open the Metadata Change Management window and select the snapshot from which you want to delete components.
On the Components tab, select the components, right-click, and select Delete.

Or

From the Snapshot menu, select Delete.

Warehouse Builder displays a delete confirmation dialog box.
Click Yes to delete the selected component from the snapshot.

Version and History Management with the Metadata Loader (MDL)

Using the Metadata Loader (MDL) utility, you can import and export metadata from any object in the Project Explorer, Global Explorer, and Connection Explorer. You can then move exported files into a third-party version control tool such as Oracle Repository, ClearCase, or SourceSafe. You can enter annotations for your MDL export file to keep track of the information contained in the file.

The Metadata Loader (MDL) enables you to populate a new repository and transfer, update, or restore a backup of existing repository metadata. You can copy or move metadata objects between repositories, even if those repositories reside on platforms with different operating systems.

Accessing the Metadata Loader

You can access the Metadata Loader using either the graphical user interface described in this chapter 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 enbables you to manage a control file.

About Metadata Loader Control Files

When you use the OMB Plus commands related to the Metadata Loader, a control file provides you with greater control over how objects are imported or exported. For example, by default, the Metadata Loader exports objects into a binary zip format. To override the default of exporting to a zip file, use OMBEXPORT MDL_FILE with the CONTROL_FILE option with a control file that contains the option ZIPFILEFORMAT=N.

For more information about using a control file with the Metadata Loader, refer to the appendix in Oracle Warehouse Builder API and Scripting Reference. For information about each command, look up the following commands also in the Oracle Warehouse Builder API and Scripting Reference.

OMBIMPORT
OMBEXPORT
OMUIMPORT
OMUIMPORT MDL_FILE
OMBIMPORT MDL_FILE
OMUEXPORT MDL_FILE
OMBEXPORT MDL_FILE

Using Metadata Loader in the Design Center

You can use the Design Center to run the Metadata Loader utilities. The Design Center provides a graphical interface that guides you through the process of exporting and importing metadata.

Exporting Metadata from the Design Center

You can use Design Center to export objects from a workspace into an MDL file. This includes objects that are part of the Project Explorer, Connection Explorer, and Global Explorer. The information related to the exported objects, such as table columns and their constraints, data loading configuration parameters, and named attribute sets, are also exported.

You have two option for exporting metadata. You could either export the entire design or you could export selected objects in the workspace. If you want to export select objects, then those objects must be within the same explorer. For example, you cannot export a table from the Project Explorer and a public transformation from the Global Explorer at the same time. You can export them in two separate steps.

To export metadata from a workspace using the Design Center:

Ensure that you are the only user accessing the objects to be exported.

To ensure that you are exporting the most up-to-date metadata, it is adviseable to ask all other users to log out of the workspace. For more details, see "Multiple Session Concurrency and MDL".

Also ensure you have the READ privilege on the objects to be exported. For details, see "Access Privileges Required to Export Metadata".
From the Design Center, select the object or objects you want to export.

You can select multiple objects by holding down the Ctrl key and selecting the objects.

You can export individual objects such as tables or groups of objects. When you export projects nodes, or modules, you also export the objects they contain. When you export collections, you also export the objects they reference.
From the Design menu, select Export and then Warehouse Builder Metadata.

If you made changes to the repository metadata prior to running the export utility, a warning dialog box is displayed. Click Save to save changes or Revert to revert to the previously saved version.

If you have not made any changes to the repository metadata after last saving the design, the Metadata Export Dialog Box is displayed.
Ensure the destination computer has sufficient disk storage.

If you lack sufficient disk space on the computer to which you export the metadata, the export fails. Your destination computer must be able to contain the entire metadata file. The export utility cannot save portions of the metadata file.

Access Privileges Required to Export Metadata

Before you attempt to export metadata, ensure you have READ privileges on any object that you want to export. You also need to have READ privileges on folder objects. If you do not have READ privileges on a folder object, such as projects or modules, the folder object and all objects that it contains will not be exported. During an export, the Metadata Export Utility skips objects for which you do not have READ privileges. It logs information about the list of objects that have not been exported due to lack of security privileges in the Metadata Loader Log File.

By default, READ privileges are provided on all the workspace objects to all registered users. If you want to export security information such as users, roles, role assignments, and object privileges, see "Export Advanced Options Dialog Box".

Metadata Export Dialog Box

The Metadata Export dialog box displays the names and the types of objects being exported. It also contains the following:

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. For more information about log files, 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:
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. For more information about the advanced options, see "Export Advanced Options Dialog Box".

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

Metadata Progress Dialog Box

The Metadata Progress dialog box displays a progress bar that indicates the progress of the metadata export, import, or upgrade activity. If the export or import is successful, a message indicating this is displayed just above the progress bar. An error in the process is displayed too.

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 Export Results dialog box is displayed. For more information about this dialog box, see "About Metadata Loader Results".

Once the export or import completes, the Close button is enabled. To exit the Metadata Export or Metadata Import Utility, click Close.

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.

Languages

The Base Language field displays the base language of the repository. Warehouse Builder exports data in the base language.

You can specify additional languages to export for the objects that contain translations for their business names and descriptions. The Available Languages list displays the list of languages that are installed in the repository. To export additional languages, select the language and click the arrows to move the language from the Available Languages list to the Selected Languages list. You can choose multiple languages at the same time by holding down the Ctrl or Shift key while making your selection.

Note:

The Available Languages list will contain language entries only if you installed additional languages in the repository.

For example, the repository has the base language as American English and additional languages Spanish and French. While exporting metadata from the repository, you can select French as the additional language. The Metadata Export Utility then exports the base language of the object, American English, and the additional language French for objects that contain a French translation. Note that additional languages will be exported for an object only if they contains translations for the business names and descriptions.

Administration

You can export additional metadata if you have administrator privileges. The options you can choose to export additional metadata are as follows:

Export user-defined definition: Select this option to export the definitions of user-defined objects and user-defined properties. This option is enabled only if you have created any user-defined objects or properties for the objects being exported.
Export security information: Select this option to include security information such as object privileges or role assignments made to users. For more information about security, see Oracle Warehouse Builder Installation and Administration Guide.

After you specify the options on the Export Advanced Options dialog box, click OK to close this dialog box and return to the Metadata Export dialog box.

Importing Metadata Using the Design Center

You can use the Design Center to import metadata. The Metadata Import Utility also automatically upgrades metadata that was created using an earlier version of Warehouse Builder to the current version. For more information about upgrading metadata, see "Upgrading Metadata from Previous Versions".

Before Importing Metadata

Before you attempt to import metadata, ensure you have the following:

Required access privileges : To import metadata, the user performing the import must have the following privileges:
- EDIT privilege on existing objects that are being replaced by the import.
- CREATE privilege on existing folder objects under which new objects will be created by the import.
By default, the FULL_CONTROL privilege is assigned on all workspace objects to registered users. The Metadata Import Utility skips objects for which the user importing metadata does not have the required privileges. The list of objects that have not been imported due to security privileges are logged to the Metadata Loader Log File.

You can import security information such as users and roles as described in "Import Advanced Options Dialog Box". When importing user metadata, if a corresponding database user does not exist for a user, the import will fail and an error message is written to the Metadata Loader Log File.

Because the Metadata Import Utility is altering the repository, the metadata objects must be locked prior to importing. For more details, see "Multiple Session Concurrency and MDL".
A backup of your current workspace: Consider taking a backup of your existing workspace (either in the form of an export or a metadata snapshot) before attempting a large or complex import.
Multiple Language Support base language compatibility: The base language is the default language used in the repository and is set using the Repository Assistant during installation. You cannot alter this setting after installing the repository. For more information about setting the base language in a repository, see Oracle Warehouse Builder Installation and Administration Guide.

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

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

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.

If you have not made any changes to the repository metadata after last saving the design, the Metadata Import dialog box is displayed.

Metadata Import Dialog Box

Use the Metadata Import dialog box to specify the information required to import metadata contained in an export file. Specify the following information on this dialog box:

File Name
Log File
Object Selection
Import Option
Match By

Click Advanced to import metadata for additional languages, security information, or user-defined properties. For more information about advanced options, see "Import Advanced Options Dialog Box".

Click Show Summary to view a summary of the export file contents. For more information about the export file contents, see "File Summary Dialog Box".

After specifying the options on the Metadata Import dialog box, click Import to import the metadata from the MDL file. The Metadata Import Progress dialog box that indicates the progress of the import is displayed. For more information about this dialog box, see "Metadata Progress Dialog Box".

Note:

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 box, see "Metadata Upgrade 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. For more information about log files, see "Metadata Loader Log File".

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. This dialog box contains two sections: Available and Selected. The Available section contains the primary objects such as projects, modules, tables, views, connections that 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 being imported can also contain administrative objects. To import these administrative objects, the user performing the import must have administrative privileges. If the user performing the import does not have the required privileges:
- If the MDL file contains some administrative objects, the Available section of the Import Object Selection Page dialog box does not displays these objects.
- If the MDL file contains only administrative objects, the Import Utility displays an alert informing that the user does not have the required administrative privileges to perform the import.

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 adds new objects to a workspace. It is referred to as the create mode.
Update metadata (replace existing objects and create new metadata)

This option is referred to as the update mode. Selecting this option adds new objects to a workspace and replaces existing objects with those in the MDL file being imported.
Merge metadata (merge existing objects and create new metadata)

When you select this option, the MDL adds new objects and overwrites existing objects in the workspace only if they differ from those in the MDL file. This option is referred to as the merge mode. The merge mode does not delete existing objects.

Note:
You cannot import metadata using the Merge mode for mappings, pluggable mappings, and data auditors.
Replace existing objects only

Selecting this option replaces existing objects in your workspace but does not add new objects. When importing metadata objects, the Metadata Import Utility overwrites any existing metadata when you use this mode. This mode is called the replace mode.

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 already 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.

Tip:

Using the replace and update modes can result in lost 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 can restore your workspace, from a backup, to that state it was in prior to importing metadata in replace or update mode.

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 file you are importing. 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 search method you choose.

The Match By section provides the following options for matching criteria:

Universal Identifier: Searches your workspace using the Universal Object Identifiers (UOIDs) of the objects you are importing. The Metadata Import Utility uses these UOIDs to determine whether an object needs to 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: Searches your workspace using the names of the objects that you are importing. Physical names are exported to the export file. The physical name determines whether an object needs to be created, replaced, or merged during an import operation. Use this method when object names in the target schema change, and you want to create new UOIDs for those objects.

By default, the import utility searches by UOIDs.

Note:

MDL import does not support merging existing mappings.

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 such as object privileges and role assignments to users

The Import Advanced Options dialog box contains the following sections: Languages and Administration.

Languages

The Base Language displays the base language of the repository. By default, data is imported in the base language.

You can specify additional languages to import. The Metadata Import Utility imports the translations of the object for business name and description. The Available Languages list displays the list of languages that are specified in the MDL file. For example, the MDL file contains the additional languages French, German, and Spanish. But your repository contains only Spanish and German as the additional languages. Then the Available Languages list displays only Spanish and German. Select the language you want to import and click the arrow to move the language to the Selected Languages list. You can choose multiple languages at the same time by holding down the Ctrl or Shift key while making your selection. For more information about importing metadata in additional languages, see "Import Different Base Languages".

Administration

This option is available only if you have administrator privileges and the metadata exists in the MDL file being imported. This section enables you to import the following additional metadata:

User-defined definitions: To import the definitions for the user-defined objects and the user-defined properties, select the Import User-defined Definitions option.
Security Grants: Select Import security information to import security information such as object privileges and role assignments made to users.

If the MDL file contains any of these objects, then you can import this additional metadata.

When you import and MDL file into a new workspace, if you want to inherit the security information from the old workspace, you must import the security information before you import other objects. To do this you need to be connected to the workspace as a user with administrator privileges.

After you make your selections on the Import Advanced Options dialog box, click OK to save your selections and return to the Metadata Import dialog box.

Name Conflicts

Name conflicts can occur in one of the following cases:

A different object with the same name already exists in the target workspace.
A different object with the same business name already exists in the target workspace.
A different object with the same UOID already exists in the workspace.

When a name conflict occurs, the MDL reports an error and terminates the import.

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, Administration, and Statistics.

File

The 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

The 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

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

Combining Import Modes and Matching Criteria

Each search method used as matching criteria can be combined with an import mode in several different combinations. Each combination can offer different results in the import process. The mode that you select determines how the metadata import utility will search 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 10-2 describes what happens in the available import modes for repository objects that do not match the object names.

Table 10-2 Import Mode without Matching Names

Import Mode	Result
Create Mode	A new object is created.
Replace Mode	A warning message is written to the log file that the object cannot be replaced because it does not exist in the workspace. The object is skipped.
Update Mode	A new object is created.
Merge Mode	A new object is created.

Table 10-3 describes what happens in the available import modes for repository objects that match the object names.

Table 10-3 Import Mode with Matching Names

Import Mode	Result
Create Mode	A message is written to the log file that the object already exists and the object is skipped.
Replace Mode	The object is replaced.
Update Mode	The object is replaced.
Merge Mode	The object is merged.

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 can be different from the language settings in the export file. For example, the target repository can have the base language as English and additional language as French and German. But the export file can have the base language as French and additional language as English and German. This section describes how the MDL handles these conditions.

Base Language of the MDL Import File Different From the Base Language of the Target Repository

When you import metadata, MDL compares the ISO identification of the base language in the import file with the ISO identification of the base language of the target repository. The ISO identification consists of the language ID followed by the locale, in the format language_locale. For example, en_US is American English and fr_FR is French.

If the base ISO identification languages are different, MDL displays a warning dialog box informing you that the base languages are different and warns you that Oracle recommends that you import metadata with the same character set and base language. You have the option to continue with the import. Click Yes to continue with the import. Click No to cancel the import.

WARNING:

Under certain circumstances, continuing to import metadata when the base languages are different could mean corruption of the metadata being imported.

It is recommended that you move metadata between repositories with the same character set and base languages.

If the base ISO identification languages are the same, but the locales are different, the Metadata Import Utility displays a warning dialog box asking if you want to continue with the import. For example, the export file contains English and the base language of the repository is American English. Click Yes to import metadata. Click No to cancel the import.

Importing Supported Languages

During an import, MDL checks if the additional languages in the import file exist in the target repository. If the import file contains additional languages that do not exist in the target repository, and you specify that these additional languages are to be imported, the Metadata Import utility writes a warning message in the MDL log file stating that the additional languages are not installed in the repository.

Validation Rules Governing Import

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

Mapping Definitions. The Metadata Import Utility will bind 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 unbound. The Metadata Import Utility writes a warning message in the log file stating 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 can contain 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 does not exist in the target workspace, the MDL Import Utility writes a warning message in the log file. This message will state that the workspace does not contain a referenced key for the foreign key.

Upgrading Metadata from Previous Versions

While importing metadata, the Metadata Import Utility automatically upgrades metadata created using previous versions to Oracle Warehouse Builder 11g Release 1 (11.1). You do not need to manually upgrade your metadata from a previous version of Warehouse Builder.

When you import an MDL file, the version used to create the file is detected. If the MDL file was created using an earlier version earlier, the Metadata Upgrade dialog box is displayed. This dialog box enables you to upgrade the MDL file to the current version. For more information about the contents of this dialog box, see "Metadata Upgrade Dialog Box".

If you import an .mdl file containing metadata for gateway Modules, such as DB2 or Informix, from an older version of Warehouse Builder, the file may not import the metadata into the corresponding source module folders in a project. The imported files are stored under the Others node in the Project Explorer. You need to manually copy the metadata for the gateway modules into the correct source module folders.

The production versions of Warehouse Builder from which metadata is automatically upgraded to Oracle Warehouse Builder 11g Release 1 (11.1) are as follows:

Oracle Warehouse Builder 2.0.4 and 2.0.5
Oracle Warehouse Builder 2.1.1
Oracle Warehouse Builder 3.0 and 3.1
Oracle Warehouse Builder 9.0.2, 9.0.3, and 9.0.4
Oracle Warehouse Builder 9.2
Oracle Warehouse Builder 10g Release 1
Oracle Warehouse Builder 10g Release 2

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.

Changes to Workspace Objects After Upgrading to Oracle Warehouse Builder 11g Release 1

When you upgrade from previous versions to the current version, the upgrade utility makes the following changes to objects in the workspace:

My Project: The sample project that is prepackaged in Warehouse Builder is renamed from My Project to MY_PROJECT to comply with physical name requirements.
External Processes: External processes are upgraded to external process activities in a process flow. If you defined an external process in a mapping in a previous release, MDL Upgrade Utility redefines the object as an external process in a process flow.
Business Areas: Business areas are upgraded to Collections. If you defined a business area in a previous release, the MDL File Upgrade Utility prefixes the module name to the business area name and redefines it as a collection. For example, a business area named ORDERS in a module named REGION1 is upgraded to a collection named REGION1_ORDERS.
External process mappings: External process mappings will be migrated to process flows.
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 already 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.
Cube Dimension Reference: If a foreign key in the cube does not reference a unique key in the lowest level of a dimension, the dimension reference is not imported. A warning is written to the import log file.
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 Runtime Repository Connections: Locations and runtime repository 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 will display 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.
MIV Objects: MIV objects 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 Loader Utilities

The Metadata Loader consists of the following two utilities:

Metadata Export Utility

Use the Metadata Export Utility to export metadata from a workspace.
Metadata Import Utility

Use the Metadata Import Utility to import metadata into a workspace.

MDL uses its own format, and the Metadata Import Utility only reads files of MDL format (files created by the Metadata Export utility). The Metadata Loader file is a formatted ZIP file.

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 a .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 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

If you are exporting a subset of objects, make sure you export all referenced objects and import them as well. You can 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 DEPT and it contains a foreign key reference to the table EMP, you can choose to export EMP along with DEPT.

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 belonging 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. For more information about the automatic upgrade of MDL files, see "Upgrading Metadata from Previous Versions".

Multiple Session Concurrency and MDL

The repository allows multiple clients to access the same workspace concurrently. Warehouse Builder uses locks to allow only one client to change repository objects. While an object is locked, other clients can only view it as it existed after the last transaction instigated by any user is saved.

When replacing or merging objects, the MDL acquires locks on the primary objects that exist both in the repository and in the MDL file. Primary objects include, but are not limited to projects, modules, tables, dimensions, cubes, mappings, views, and flat files. Secondary objects, such as columns and mapping attributes, are not locked. If locks cannot be obtained because other users are locking the primary objects, then the import fails. Therefore, you must be able to hold locks for primary objects that you are importing.

Tip:

To ensure a successful metadata import, you may need to be the sole client accessing the workspace.

The MDL saves changes made to the workspace after a successful metadata import (any import with no error messages, including imports with only information or warning messages). The MDL also executes a rollback after an unsuccessful import.

Metadata Loader Log File

Whenever you export or import repository metadata, the MDL writes diagnostic and statistical information to a log file. You can specify the location of the log file when you call the MDL.

The log file enables you to monitor and troubleshoot export and import activities in detail and contains the following information:

Name of the data file
Start time and end time of the export or import
Time taken for the export or import in hours, minutes, and seconds (in hh:mi:ss format) or milliseconds
Object types exported or imported
Number of objects of each object type exported or imported

The import log file also displays the total number of objects that have been added, replaced, skipped, and deleted.
Status messages

Status messages provide information about the import or export process. They are of the following types:
- Informational: Provides information about the import or export, such as missing metadata objects, whether objects were imported, and any reasons why objects were not imported or exported.
- Warning: Cautions you about the import or export of an object but does not indicate a failed or terminated export or import. A warning notifies you of the possibility of unexpected results that could occur as a result of the export or import.
- Error: Indicates that the MDL export or import was terminated and did not complete successfully. The error message provides a brief description of the reason for the failure.

About Metadata Loader Results

When you use the Metadata Loader Export or Import utilities, you can view the results of a successful export or import task. Use the Metadata Export Results dialog box or the Metadata Import Results dialog box to ensure that all of the objects were exported or imported. To view the results dialog box, click Show Details on the Metadata Export Progress dialog box or the Metadata Import Progress dialog box. This displays the Message Log. Click Show Statistics at the end of this log.

The results dialog box contains the following information:

The name of the project exported or imported (if applicable).
The number of objects of each type exported or imported.
The number of objects of each object type skipped.

Details about the number of skipped objects is displayed only when you import metadata.