15
Importing and Exporting with the Metadata Loader (MDL)

This begins the archive process. A progress window appears. When the progress bar reaches 100%, the archive process is complete.

If you checked the Show details dialog following a successful archive box, the Archive Results dialog displays. This dialog displays the name of each object type and how many of each were archived, as shown in Table 15-5.

Figure 15-5 Archive Results

Text description of the illustration arch_rsl.gif

For a more detailed look at the archive process, click View Log File. This displays the entire log file, as shown in Table 15-6.

Figure 15-6 Archive Log File

Text description of the illustration arch_log.gif

Importing Metadata

The import utility reads the metadata information from an exported text file and inserts the metadata objects into a repository. The metadata import utility only reads files created by the metadata export utility.

The MDL imports information belonging to exported metadata objects such as table columns and their constraints, data loading configuration parameters, and named attribute sets. You can use the MDL to import objects into a project or a collection.

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 navigation tree. You need to manually copy the metadata for the gateway modules into the correct source module folders.

The following sections describe how to use the Metadata Import Utility:

• Before Importing Metadata

• About the Metadata Import Utility

• Validation Rules Governing Import

• Importing Metadata using Warehouse Builder Client

• Restoring a Project

Before Importing Metadata

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

• Required security privileges: You need the MDL_IMPORT privilege before you begin an import. For more information on security, see "Managing Security with PL/SQL".

• Required access privileges: Only a user with read/write access can use the metadata loader import utility. Because the import utility is altering the repository, the metadata objects must be locked prior to importing. For more details, see "Required Access Privileges for MDL".

• A backup of your current repository: Consider taking a backup of your existing repository (either in the form of an export or a metadata snapshot) before attempting a large or complex import. For more information on exporting metadata, see "Exporting Metadata". For more information on metadata snapshots, see Chapter 16, "Metadata Change Management".

• 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. This setting cannot be altered after installing the repository. Loading differing base language metadata objects into a repository results in error. For more information on setting the base language in a repository, see the Oracle9i Warehouse Builder Installation and Configuration Guide.

About the Metadata Import Utility

You can import metadata into a Warehouse Builder repository using one of the following:

• Metadata Loader Command Line Utility: You can use the command line utility to perform tasks additional tasks not available from the client interface. For example, you can override default values for configuration parameters for loading data. For instructions on importing from the command line, see "Using the Metadata Loader Command Line Utility".

• Warehouse Builder Client Interface: For instructions on using the client interface, see "Importing Metadata".

Validation Rules Governing Import

When you import a set of definitions from exported metadata, the import utility can update existing definitions in a Warehouse Builder 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 does not bind imported mapping operators to their physical objects. If a mapping definition appearing in the source MDL file replaces a mapping definition in the target repository, then the new repository mapping definition is unbound. You may need to reconcile new mapping operators with the physical objects they represent. The MDL generates a warning message to the log file stating that the mapping operators are not bound.

• 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 repository. If the referenced unique or primary keys for any foreign key appearing in the MDL file does not exist in the target repository, the MDL will generate a warning message in the log file. This message will state that the repository does not contain a referenced key for the foreign key.

Importing Metadata using Warehouse Builder Client

Use the metadata import utility to import objects from an MDL file into a Warehouse Builder repository.

1. Select the project to which you will import metadata.

2. From the Warehouse Builder Console, select Project and select MetaData Import.

   Warehouse Builder displays the Metadata Utility Import dialog as shown in Figure 15-7.

Figure 15-7 Metadata Import Utility

Text description of the illustration miupsibn.gif

3. Specify the names and locations for the import file and its log:

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

   Log File: Warehouse Builder records information about the import in a log file. You can change this location by entering a new path and filename. Type the path and file name in the field or click Browse to locate a directory or filename.

4. Select an Import Option. For more information on import options, see "Import Modes". You can select from the following import options:

   Add new metadata only: Adds new objects to a repository.

   Add new metadata and replace existing objects: Adds new objects to a repository and replaces existing objects.

   Add new metadata and merge existing objects: Adds new objects and merges columns into existing objects in your repository.

   Replace existing objects only: Replaces existing objects in your repository.

5. In Match By, specify the matching criteria the utility uses to compare the metadata in the import file against the metadata existing repository. For more information, see "Metadata Matching Criteria".

   Ignore Universal Identifier: The import utility does not use Universal Identifiers to search for objects you are importing.

   Name: Searches your repository using the physical names of the objects you are importing to make sure the objects do not already exist.

   Character Set: Select the type of character set used to create the import file. The default character set is defined by the Warehouse Builder client machine. Use the drop-down list to change the output character set.

   You can add new languages and character sets using the Repository Assistant. For more information, refer to the Oracle9i Warehouse Builder Installation and Configuration Guide.

6. Click Scan to display the exported metadata header information as shown in Figure 15-8. The Header Information dialog displays a summary of the total number of object types contained in the metadata file you selected.

Figure 15-8 Header Information

Text description of the illustration miuimpsc.gif

7. Click Import.

   If you have made any changes before starting the import, the Metadata Import Confirmation dialog displays. Click Commit to save any changes or Rollback to ignore changes and revert to the previously saved version.

   The Metadata Import Confirmation dialog displays as shown in Figure 15-9 if the exported metadata data information has not been reviewed.

Figure 15-9 Metadata Import Confirmation

Text description of the illustration mdi_conf.gif

8. Click Import to continue.

   The Metadata Import Progress panel displays, as shown in Figure 15-10.

Figure 15-10 Metadata Import Results

Text description of the illustration mdi_rslt.gif

This dialog displays the object types and the number of each type that were imported or skipped. For a detailed view of the import process, click View Log File.

Import Modes

The graphical user interface for the metadata import utility operates in one of the following modes:

• Add new metadata only (Create Mode): Adds new objects to a repository. If objects from the MDL file already exist in the repository, they remain unchanged.

• Add new metadata and replace existing objects (Update Mode): Adds new objects to a repository and overwrites existing objects with those in the MDL file.

• Add new metadata and merge existing objects (Merge Mode): Adds new objects and overwrites existing objects in your repository only if they differ with those in the MDL file.

• Replace existing objects only (Replace Mode): Replaces only existing objects in your repository. When importing metadata objects, the MDL will overwrite any existing metadata unless you use this mode.

When you import using the Update or the Replace modes, the import completely replaces the existing object's children 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 repository, 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 are added, and column Status and license_ID are deleted. The final result is that the CUST table in the Warehouse Builder repository contains the same metadata from the CUST table in the export file.

Metadata Matching Criteria

When you use the metadata import utility, it first searches the repository for metadata objects that exist in the repository and compares them to those in the file you are importing. How the comparison is made is determined by the loading mode and by the search method you choose. The following methods are available:

• Physical Name: 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 directory change, and you want to create new UOIDs for those objects.

• Universal Object Identifier: The metadata export utility assigns a unique system-generated identifier to each exported row object called the Universal Object Identifiers or UOIDs. The purpose of the UOID for a row object is to uniquely identify it in an object table. The MDL import utility uses these UOIDs to determine whether a row object needs to be created, replaced, or merged during an import operation. Use this method if you want to maintain UOIDs across different repositories even when object names in the target repository have changed.

By default, the import utility searches by UOIDs. However, the import utility ignores the UOIDs for mappings in the MDL file that already exist in the target repository.

Each search method 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 repository prior to importing.

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

Table 15-2 describes what happens in the available import modes for repository objects that do not match the MDL file names.

Table 15-2 Import Mode without Matching Names  

Table 15-3 describes what happens in the available import modes for repository objects that match the MDL file names.

Table 15-3 Import Mode with Matching Names  

The MDL reads and processes the imported metadata and writes status and diagnostic information in the log file. When the import is complete, the Metadata Import Results dialog displays.

Restoring a Project

Restoring a project allows you to recreate metadata within a Warehouse Builder repository from an external location. Warehouse Builder provides a Restore Wizard to assist you in this process.

You must set up your Archive/Restore settings on the Preferences page before you can archive or restore your project. If you attempt to archive or restore without setting these preferences, you get an error.

Differences Between Restore and Import

Archive and Restore are different from Import and Export. Table 15-4 describes the differences between Restore and Import.

Table 15-4 Differences Between Restore and Import  

Restoring a Project

Follow these instructions to restore a project.

1. Select Restore from the Project menu.

   The Restore Wizard Welcome page displays.

2. Click Next.

   The Select Archive page displays, as shown in Figure 15-11. Browse to or type the Archive File you want to restore.

Figure 15-11 Select Archive Page

Text description of the illustration rest_wia.gif

3. Click Next.

   The Summary page displays a summary of the restore settings prior to running the restore process, as shown in Figure 15-12. If you want to see the details of your restore after the restore process is complete, check the Show details dialog following a successful restore box.

Figure 15-12 Restore Wizard Summary Page

Text description of the illustration rest_wiz.gif

4. Click Finish.

   This begins the restore process. A progress window appears. When the progress bar reaches 100%, the restore process is complete.

   If you checked the Show details dialog following a successful restore box, the Restore Results dialog displays, as shown in Figure 15-13. This dialog displays the name of each object type, how many of each were restored, and how many of each were skipped.

Figure 15-13 Restore Results

Text description of the illustration rest_rsl.gif

For a more detailed look at the archive process, click View Log File. This displays the entire log file, as shown in Figure 15-14.

Figure 15-14 Restore Log File

Text description of the illustration rest_log.gif

Using the Metadata Loader Command Line Utility

You can operate the MDL from the command line instead of the user interface.

This section contains the following topics:

• Creating MDL Parameter Files at the Command Line

• Exporting Metadata Using the Command Line Utility

• Importing Metadata Using the Command Line Utility

For related information on using the Metadata Loader's command line utility for upgrade purposes, refer to the Oracle9i Warehouse Builder Installation and Configuration Guide.

Creating MDL Parameter Files at the Command Line

If you use the command line interface, you can customize how to move your metadata at a more detailed level than you can when using the GUI. For example, when exporting metadata, the command line allows you the flexibility to disable the export of configuration values, vary the separator character within an export file, and maintain parameter files for selected export operations.

For importing metadata, the command line offers the flexibility of creating specific import actions for each object. These operations automate the consolidation or synchronization of metadata in multiple repositories that have a similar project structure.

The scripts for executing both the export and import utilities reside in the $OWBHOME\owb\bin\win32 directory. Both the export and import utilities are driven by a set of parameters. You can specify the MDL parameters by:

• Providing MDL parameters as a response to command-line prompts.

• Creating an MDL parameters file.

• Providing MDL parameters as a response to command-line prompts, and create a MDL parameters file.

Exporting Metadata Using the Command Line Utility

By default, the MDL also exports values for configuration parameters for loading data, but you can override this setting at the command line. You can choose how you want to export the files within a project. For example, if you are exporting three source modules and two target modules, you can choose to export them separately or together.

1. Create the MDL parameters file.

2. Execute the Metadata Export Utility.

   The following command invokes the Metadata Export Utility and specifies the above parameters file:

   w:\owb\bin\win32>exp parfile=e:\MDL\EXP_Directives
   Processing ... Export successful.
   
   
   

   The objects are exported to the file and can be imported into a repository using the metadata import utility.

Keywords for the Export Utility

An MDL parameters file is a text file that contains a set of parameters for the export utility. The format for an export parameter is:

Keyword=Value

You can also form an export parameters by replacing the value with the wildcard character (*), which matches any string, or with a list of named objects:

Keyword=*

Keyword=(value-1, value-2, ..., -k)

For example, you can specify a set of tables to be exported as:

TABLES=(Customers, Products, Days)

Example 15-3 shows a typical parameters file for importing a module.

Example 15-3 Parameters File format

USERID=GCCWH/GCCWH@dwdoc11-pc:1521:ora816
PROJECT=GCCWarehouse
FILE=e:\MDL\GCCWarehouse-exp-JUL01
FIELDSEPARATOR=|
LOG=e:\MDL\GCCWarehouse-exp-JUL01-LOG
CONFIGPARAM=N

Table 15-5 summarizes the keywords used to form export parameters. You can use the comment indicator (#) to document the scripts. Put the indication in the first column of a record and follow it with text.

Table 15-5 Keywords for Export Utility Parameters  

Importing Metadata Using the Command Line Utility

1. Create an MDL parameter file.

2. Execute the Metadata import utility.

   The following command invokes the Import Utility and specifies the above MDL parameter file:

   w:\owb\bin\win32>imp parfile=e:\MDL\IMP_Directives.txt
   Processing ...
   Import successful.
   

Keywords for the Import Utility

Like the MDL export, you can direct the MDL import to import objects from a file by answering prompts or by creating a file with a set of parameters. Example 15-4 shows a typical parameters file for importing a module.

Example 15-4 Parameters File format

USERID=GCCWH/GCCWH@dwdoc11-pc:1521:ora816
FILE=e:\MDL\gccstar-exp
LOG=e:\MDL\gccstar-imp-LOG
MODE=CREATE
CONFIGPARAM=N

Table 15-6 summaries the keywords used to form import parameters.

Table 15-6 Keywords for Import Utility Parameters  

If a MODE parameter is not included, then the default is CREATE.

In addition to running an MDL parameter file from the import utility, you can also specify an action plan within the file that will allow you to specifically define what you want to do with each object in the imported file. First you need to specify if you want the object imported, skipped, or deleted. If you choose to import the object, you can set the import mode to CREATE, UPDATE, REPLACE, or INCREMENTAL UPDATE.

Example 15-5 shows an example of an MDL parameter file that contains an action plan.

Example 15-5 MDL Action Plan

USERID=user_sample/user_sample@test-pc:1521:ora8i
#  
FILE=e:\test\data\sample_file.mdl
LOG=e:\test\log \imp_sample_file.log
# 
MODE=ACTIONPLAN
PHYSICALNAMES=Y
IGNOREUOID=Y
#
# User-Specified Action Plan
#
ACTION=NONE
PROJECT=MY PROJECT
MODULES=(DATAWAREHOUSE)
#
ACTION=CREATE
TABLES=(TABLE_3)
FACTS=(FACT1, FACT2, FACT3)
SEQUENCES=(SEQ_A, SEQ_B, SEQ_C)
#
ACTION=REPLACE
TABLES=(TABLE_1, TABLE_2)
DIMENSIONS=(DIM1, DIM2, DIM3)
#
ACTION=DELETE
 TABLES=(TABLE_A, TABLE_B)
#
# Switching to a different module
ACTION=REPLACE
MODULES=(FLAT_FILE)
FILES=(FILE_1, FILE_2)
#
ACTION=CREATE
FILES=(FILE_3)
#
ACTION=DELETE
FILES=(FILE_X)

Splitter for Exporting and Importing Warehouse Builder Mappings

The Split utility provides a workaround for the memory limitations of the MDL import utility when you are importing a large number of mappings. This utility generates export and import scripts for migrating mappings in pieces as opposed to migrating them all at the same time. The generated scripts have matching MDL parameter files that utilize the CREATE mode. These files can be edited.

If the MDL import fails because of large data, the split utility can be used to re-export and import the mapping data in smaller pieces. All other object types must be exported and imported using the standard MDL utilities. Only mappings can be split into smaller pieces. To export all entities other than mappings, a parameter file containing the following can be used:

• VIEWS=*

• TABLES=*

• SEQUENCES=*

• MATERIALIZEDVIEWS=*

• CUBES=*

• FILES=*

• DIMENSIONS=*

• VIRTUALTABLES=*

• TEMPORARYTABLES=*

• TRANSFORMCATEGORIES=*

The split utility splits the mappings within a module in a Warehouse Builder project. The size of the pieces is determined by a parameter located in a file provided with this application.

The expsplit batch script accepts the following arguments:

• A parameter file, specified in the form c:\temp\owb_apps.txt. The parameter file has special keywords outlined below that identify the number of mappings, data file names, and extensions.

• A parameter target file prefix, specified in the form c:\temp\owb_apps the piece number (numbered from 1). A .txt suffix is added to the generated parameter file, a .bat suffix is added for the export batch file, and an _imp.bat suffix is added to the import batch file.

The following is an example of how to start the split utility:

expsplit exampleparams.txt c:\temp\ora_apps 

The following example uses a parameter file exampleparams.txt. This file contains the following parameters:

• userid=apps/apps@130.35.12.73:1521:orcl0

• PHYSICALNAMES=Y

• LOG=c:\temp\owb_data_apps

• LOGEXT=log

• FILE=c:\temp\owb_data_apps

• FILEEXT=dat

• FIELDSEPARATOR=^

• PROJECT=EDWPRJ

• MODULES=EDW_COMMON_MODULE

• TYPE=MAPPINGS

• COUNT=70

This file is similar to the export parameter file for Warehouse Builder Metadata Loader, with the changes listed in Table 15-7.

Table 15-7 Split Utility Export Parameter Keyword Descriptions  

If the mappings for a Warehouse Builder project are split, the generated parameter files are named as follows:

owb_apps1.txt 
owb_apps2.txt 

A batch file is generated: c:\temp\owb_apps.bat (given the parameter target file prefix) to export the data from the repository. An import batch file is created to import, using create mode, into the same repository. These files can be edited if different target databases are required.

1. Using command line or Warehouse Builder, perform MDL export of all objects other then mappings.

   To export all objects other than mappings in a command line, use a parameter file with the following keywords:

   VIEWS=*

   TABLES=*

   SEQUENCES=*

   MATERIALIZEDVIEWS=*

   FACTS=*

   FILES=*

   DIMENSIONS=*

   VIRTUALTABLES=*

   TEMPORARYTABLES=*

   TRANSFORMCATEGORIES=*

   If you are using Warehouse Builder to perform export, use multi-select to select and export objects other than mappings.

2. Import the new export file into target repository.

3. Split the mappings and export them using split utility

   expsplit exampleparams.txt c:\temp\ora_apps

   The utility connects to the source repository, splits mappings, and creates multiple parameter files according to exampleparams.txt. These parameter files are used during the export. The utility also creates an export batch file and an import batch file.

   Table 15-8 lists the files that are created.

   Table 15-8 Files Created by the Split Utility  

   Description	File Name
   Batch file to perform export	c:\temp\ora_apps.bat
   Batch file to perform import	c:\temp\ora_apps_imp.bat
   Multiple parameter files to be used by export and import batch files	c:\temp\ora_apps1.txt c:\temp\ora_apps2.txt c:\temp\ora_apps3.txt

4. Run the export batch file to export mappings into the location specified in the parameter file (variable FILE specified in step 3).

5. Modify generated parameter files c:\temp\ora_apps1.txt, c:\temp\ora_apps2.txt. Edit the connection information to point to the target repository.

6. Run import batch file c:\temp\ora_apps_imp.bat to complete the import.