Creating an Export Parameter File

Each procedure in the CWM2_OLAP_EXPORT package accepts a parameter that identifies an Export parameter file. The contents of this file are a series of comments and a table specification that can be used by the Oracle Export utility.

You can create an Export parameter file without creating a metadata command script. You can use the parameter file to export and import the base tables, but without a metadata command script you will not be able to restore the metadata.

Example 13-2 shows the Export parameter file for the GLOBAL.PRODUCT dimension.

Example 13-2 Export Parameter File for GLOBAL.PRODUCT

The following command creates an Export parameter file for the GLOBAL.PRODUCT dimension. It does not create a metadata command script file.

>execute cwm2_olap_export.export_dimension
            ('GLOBAL', 'PRODUCT','/myuser/scripts',' ' ,'GLOBALPROD_EXP_PARAM.DAT');

To export the dimension table used by GLOBAL.PRODUCT, run the Export utility in SQL*Plus using a command like the following.

>exp username/password PARFILE=GLOBALPROD_EXP_PARAM.DAT

The contents of GLOBALPROD_EXP_PARAM.DAT are shown as follows.

#Export Dimension: GLOBAL.PRODUCT   Directory: /myuser/scripts   Command File:
#Table File: GLOBALPROD_EXP_PARAM.DAT
#
#ORACLE RDBMS EXPORT UTILITY PARFILE
#
# Cube "GLOBAL.PRICE_CUBE"
#   Dimension "GLOBAL.PRODUCT" Mapped to Table "GLOBAL.PRODUCT_DIM"
#
# Cube "GLOBAL.UNITS_CUBE"
#   Dimension "GLOBAL.PRODUCT" Mapped to Table "GLOBAL.PRODUCT_DIM"
TABLES = (
GLOBAL.PRODUCT_DIM
)

To re-create the dimension table for GLOBAL.PRODUCT, transfer the dump file generated by the Export utility (by default, expdat.dmp) to a directory that can be accessed by the database. In SQL*Plus, navigate to this directory and run the Import utility with a command like the following.

>imp username/password FILE=expdat.dmp

Summary of CWM2_OLAP_Export Subprograms

Table 13-1 CWM2_OLAP_DELETE

Subprogram	Description
EXPORT_CUBE Procedure	Exports a cube in the OLAP Catalog.
EXPORT_DIMENSION Procedure	Exports a dimension in the OLAP Catalog.
EXPORT_OLAP_CATALOG Procedure	Exports all the metadata in the OLAP Catalog.

EXPORT_CUBE Procedure

EXPORT_CUBE produces a metadata command script and an Export parameter file for a cube that is based on relational tables.

You can use the Export parameter file to export the underlying fact and dimension tables to a dump file, then you can import the dump file to re-create the tables. You can run the metadata command script to rebuild the metadata that maps to the tables. See "Exporting and Importing OLAP Catalog Metadata".

You can identify a group of cubes to export by specifying wildcard characters in the cube_owner and cube_name parameters. See "Using Wildcards to Identify Metadata Entities".

EXPORT_CUBE includes each of the cube's dimensions. You do not have to export the dimensions separately.

OLAP Catalog cubes are displayed in the view ALL_OLAP2_CUBES.

Syntax

EXPORT_CUBE (
          cube_owner                   IN   VARCHAR2,
          cube_name                    IN   VARCHAR2,
          directory_name               IN   VARCHAR2,
          metadata_command_file_name   IN   VARCHAR2,
          export_parameter_file_name   IN   VARCHAR2);

Parameters

Table 13-2 EXPORT_CUBE Procedure Parameters

Parameter	Description
`cube_owner`	The owner of the cube. See "Using Wildcards to Identify Metadata Entities".
`cube_name`	The name of the cube. See "Using Wildcards to Identify Metadata Entities".
`directory_name`	The directory where the metadata command file and the Export parameter file will be written.
`metadata_command_file_name`	The name of the metadata command file that will contain the SQL and PL/SQL commands for rebuilding the metadata.
`export_parameter__file_name`	The name of the Export parameter file that will identify the dimension tables and fact tables for the Oracle Export utility.

Example

See "Creating a Metadata Command Script" and "Creating an Export Parameter File" . These examples illustrate the process of exporting and importing a dimension. The process is exactly the same for a cube.

EXPORT_DIMENSION Procedure

EXPORT_DIMENSION produces a metadata command script and an Export parameter file for a dimension that is based on relational dimension tables.

You can use the Export parameter file to export the underlying dimension tables to a dump file, then you can import the dump file to re-create the tables. You can run the metadata command script to rebuild the metadata that maps to the tables. See "Exporting and Importing OLAP Catalog Metadata".

You can identify a group of dimensions to export by specifying wildcard characters in the dimension_owner and dimension_name parameters. See "Using Wildcards to Identify Metadata Entities".

Dimensions are exported along with cubes. You only need to use EXPORT_DIMENSION if the dimension does not participate in a cube or if the owning cube will not be exported.

OLAP Catalog dimensions are displayed in the view ALL_OLAP2_DIMENSIONS.

Syntax

EXPORT_DIMENSION (
          dimension_owner              IN   VARCHAR2,
          dimension_name               IN   VARCHAR2,
          directory_name               IN   VARCHAR2,
          metadata_command_file_name   IN   VARCHAR2,
          export_parameter_file_name   IN   VARCHAR2);

Parameters

Table 13-3 EXPORT_DIMENSION Procedure Parameters

Parameter	Description
`dimension_owner`	The owner of the dimension. See "Using Wildcards to Identify Metadata Entities".
`dimension_name`	The name of the dimension. See "Using Wildcards to Identify Metadata Entities".
`directory_name`	The directory where the metadata command file and the Export parameter file will be written.
`metadata_command_file_name`	The name of the metadata command file that will contain the SQL and PL/SQL commands for rebuilding the metadata.
`export_parameter__file_name`	The name of the Export parameter file that will identify the dimension tables for the Oracle Export utility.

Example

See "Creating a Metadata Command Script" and "Creating an Export Parameter File".

EXPORT_OLAP_CATALOG Procedure

EXPORT_OLAP_CATALOG produces a metadata command script and an Export parameter file for all the metadata (both CWM1 and CWM2) in the OLAP Catalog.

OLAP Catalog metadata is displayed in the OLAP Catalog metadata views, described in Chapter 5.

Syntax

EXPORT_OLAP_CATALOG (
          directory_name               IN   VARCHAR2,
          metadata_command_file_name   IN   VARCHAR2,
          export_parameter_file_name   IN   VARCHAR2);

Parameters

Table 13-4 EXPORT_OLAP_CATALOG Procedure Parameters

Parameter	Description
`directory_name`	The directory where the metadata command file and the Export parameter file will be written.
`metadata_command_file_name`	The name of the metadata command file that will contain the SQL and PL/SQL commands for rebuilding the metadata
`export_parameter__file_name`	The name of the Export parameter file that will identify the fact and dimension tables for the Oracle Export utility.

Example

See "Creating a Metadata Command Script" and "Creating an Export Parameter File". These examples illustrate the process of exporting and importing a dimension. To use EXPORT_OLAP_CATALOG, follow the same procedure without specifying a dimension name.