Exporting and Importing OLAP Catalog Metadata

You can use the CWM2_OLAP_EXPORT package to export individual cubes or dimensions or the entire contents of the OLAP Catalog. CWM2_OLAP_EXPORT exports CWM2 metadata created by the CWM2 PL/SQL packages and CWM1 metadata created by Oracle Enterprise Manager.

You can use CWM2_OLAP_EXPORT if your mapped data is stored in relational tables or views of relational tables. If your data is stored in analytic workspaces, use the OLAP DML to export and import the contents of the workspace. See "Procedure: Import a workspace from a 9i Database into a 10g Database".

Procedures in CWM2_OLAP_EXPORT produce a metadata command script and an Export parameter file. The metadata command script contains the CWM1, CWM2, and Oracle Database commands that build the metadata. The Export parameter file can be used with the Oracle Export utility to export the dimension tables and fact tables that underlie the metadata.

Exporting and importing OLAP Catalog metadata is a four-step process:

Run the CWM2_OLAP_EXPORT procedure, specifying a metadata command script file and an Export parameter file.
Run the Oracle Export utility, using the Export parameter file you produced in Step 1. The Export utility will create an Export dump file.
In the database instance where you want to re-create the data and metadata, run the Oracle Import utility using the Export dump file you produced in Step 2. The Import utility will import the underlying dimension tables and fact tables.
After running the Oracle Import utility, run the metadata command script you produced in Step 1. This script will rebuild the metadata that maps to the underlying dimension tables and fact tables.

Note:

The database in which you re-create your OLAP Catalog metadata must be OLAP-enabled. You can only use CWM2_OLAP_EXPORT to replicate your OLAP Catalog metadata within an environment where the OLAP Catalog is already defined.

Rebuilding OLAP Catalog Metadata

To rebuild the OLAP Catalog metadata for a relational data source, you can export the data and metadata, delete it, then import it. Use the CWM2_OLAP_EXPORT package and the Oracle Export utility to do the export. Use CWM2_OLAP_DELETE to delete the metadata. Drop the tables, then use the Oracle import utility to do the import. See Chapter 10, "CWM2_OLAP_DELETE".

To rebuild analytic workspaces, use the OLAP DML to export the contents of the workspace to an EIF file, then import it in a new workspace. See "Procedure: Import a workspace from a 9i Database into a 10g Database". If you are running in Oracle9i compatibility mode, you will need to re-enable the workspaces and re-create the metadata for the workspaces. See "Enabling Relational Access".

Using the Oracle Export and Import Utilities

The CWM2_OLAP_EXPORT package works with the Export and Import utilities that are invoked with the exp and imp commands. In Oracle Database Utilities these are called the original Export and Import utilities to differentiate them from the new Data Pump Export and Import utilities available with Oracle Database 10g. The CWM2_OLAP_EXPORT package was not designed to work with the new Data Pump Export and Import utilities.

The original Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.

The Export parameter file created by CWM2_OLAP_EXPORT specifies the tables where your dimension and fact data are stored. The Export utility supports many options and parameters. Refer to Oracle Database Utilities for specific information about exporting and importing tables with exp and imp.

Using Wildcards to Identify Metadata Entities

You can use wildcard characters to export cubes and dimensions whose names meet certain criteria.

Wildcard characters are the underscore "_" and the percent sign "%". An underscore replaces any single character, and a percent sign replaces any zero or more characters. An underscore, but not a percent sign, is also a legal character in a metadata owner or entity name. Any underscore character in the owner or entity name is treated as a wildcard, unless you precede it with a backslash "\" which acts as an escape character.

For example, the following command exports all the cubes belonging to the owner 'GLOBAL'.

>execute cwm2_olap_export.export_cube('GLOBAL', '%', '/scripts_dir',
                                     'global_cmd_file', 'global_tbl_file');

The following command exports all the cubes in the GLOBAL schema whose names start with 'a'.

>execute cwm2_olap_export.export_cube('GLOBAL', 'a%', '/scripts_dir', 
                                     'global_cmd_file', 'global_tbl_file');

If your database includes users 'TESTUSER1' and 'TESTUSER2', you could export the 'TEST' cube belonging to each of these users with the following command.

>execute cwm2_olap_export.export_cube('TESTUSER_', 'TEST', '/scripts_dir', 
                                     'global_cmd_file', 'global_tbl_file');

If your database includes users 'TEST_USER1' and 'TEST_USER2', you could export the 'TEST' cube belonging to each of these users with the following command.

>execute cwm2_olap_export.export_cube('TEST/_USER_', 'TEST', '/scripts_dir',
                                     'global_cmd_file', 'global_tbl_file');