CNV_CWM.TO.ECM Program

CNV_CWM.TO.ECM is a DML program that creates analytic workspace objects from OLAP catalog metadata and loads data into these objects. It runs in eight stages, and updates the analytic workspace after completing each stage. It does not rerun stages whose results are already saved in the analytic workspace. You can run CNV_CWM.TO.ECM in stages, check the results, and modify them before continuing to the next stage.

Return Value

None

Syntax

CNV_CWM.TO.ECM(aw_name, [stage], ['DEBUG'], [selection], [directory, filename]

where:

stage is a text expression identifying one of the following keywords:

GET_CWM_METADATA
GEN_RDB_STRUCTURES
GET_RDB_STRUCTURES
CRT_DIM_STRUCTURES
DFN_MEASURES
CRT_ECM_METADATA
GEN_MEASURES
GET_MEASURES

selection is a text expression in one of the following formats:

CATALOG::catalog
CUBE::owner::cube
MEASURE::owner::cube::measure

Arguments

aw_name

A text expression that identifies the name of the analytic workspace in which the objects will be created and stored. If the workspace exists, then it is attached read/write. (Note that it cannot already be attached to your session.) If it does not exist, then a new analytic workspace is created.

The workspace should be used only to store objects created by this program. Custom DML programs and data acquired from other sources can be stored in separate analytic workspaces.

stage

By specifying a stage, you interrupt the utility so that you can review and edit the contents of the analytic workspace before continuing. For example, the definition of a variable identifies its dimensions, and the order in which the dimensions are listed, from fastest to slowest varying, indicates how the data is stored. Depending on how users will most frequently view the data, you might want to change the order of the dimensions. If the data is sparse, you might want to create a composite dimension and use it to dimension the variable.

The following are descriptions of the various stages:

'GET_CWM_METADATA'

Stage 1: Loads all of the metadata from the database into objects in the analytic workspace.

'GEN_RDB_STRUCTURES'

Stage 2: Generates DML programs to fetch level values, attributes, and parent-child relationships.

'GET_RDB STRUCTURES'

Stage 3: Executes the DML programs created in stage 2.

'CRT_DIM_STRUCTURES'

Stage 4: Creates analytic workspace dimensions, attributes, and hierarchies.

'DFN_MEASURES'

Stage 5: Generates and executes DML programs that determine sparsity patterns and define measures and composites.

'CRT_ECM_METADATA'

Stage 6: Creates workspace objects.

'GEN_MEASURES'

Stage 7: Generates DML programs to load data from the fact tables.

'GET_MEASURES'

Stage 8: Executes the DML programs created in stage 7. (Default)

'DEBUG'

A text expression that specifies running in debug mode. In debug mode, the workspace is attached read-only and remains attached at the end of the build. You cannot save the analytic workspace when it runs in this mode.

When this argument is NA, CNV_CWM.TO.ECM runs in standard mode. The workspace is attached read/write, and all objects are saved at the completion of each stage. When CNV_CWM.TO.ECM is done, it detaches the workspace.

CATALOG

Limits the build to include only the metadata associated with a particular catalog or metadata folder. When this argument is omitted, all OLAP metadata in the schema is included in the build.

CUBE

Limits the build to include only the metadata for cube. When this argument is omitted, all cubes in catalog are included in the build.

MEASURE

Limits the build to include only the metadata for a particular measure. When this argument is omitted, all measures in cube are included in the build.

catalog

Identifies a particular measure folder in the schema.

owner

Identifies the owner of the schema that contains the dimension tables and fact tables.

cube

Identifies a particular cube in catalog.

measure

Identifies a particular measure in cube.

Notes

Using multiple analytic workspaces: The analytic workspace created by CNV_CWM.TO.ECM should be used only for this purpose so that you can periodically delete and recreate it. You can store DML programs and data from other sources in separate analytic workspaces. A session can attach numerous analytic workspaces, and objects in different analytic workspaces are fully accessible to each other.

Recreating an analytic workspace: CNV_CWM.TO.ECM rebuilds an analytic workspace. If you have customized a workspace that was created by CNV_CWM.TO.ECM, then you should export all customizations before rebuilding.

1. Export your customizations to an EIF file, using a DML command like the following one:

   export myprogs to eif file 'temp/dmlprogs.eif' rewrite
   
   

   Where:

   temp is the name of a directory alias to which you have write access.

   dmlprogs.eif is the name of the EIF file.

2. Delete the old analytic workspace. Then create a new analytic workspace and populate it using CNV_CWM.TO.ECM.
3. Import the custom objects from the EIF file into the original workspace, using a command like the following one:

   import all from eif file 'temp/dmlprogs.eif' dfns
    
   

4. Issue UPDATE and COMMIT commands.

Creating aggregate data: The CNV_CWM.TO.ECM program reads and loads only stored low-level source data from relational tables into the analytic workspace, and creates the objects that define and support dimension hierarchies. No aggregate data is loaded into the analytic workspace. The OLAP DML has very sophisticated tools for aggregating data. Refer to the chapter on aggregating data in the Oracle9i OLAP Developer's Guide to the OLAP DML.

Examples

This example creates or attaches an analytic workspace named alldata and creates all of the objects defined by the OLAP metadata for the current schema.

CALL CNV_CWM.TO.ECM('alldata')

The next example creates or attaches an analytic workspace named saleshist and creates all of the objects defined by the OLAP metadata for the SH_CAT catalog.

CALL CNV_CWM.TO.ECM('saleshist', na, na, 'CATALOG::SH_CAT')

The following example creates or attaches an analytic workspace named sales and creates the dimensions, attributes, and hierarchies associated with the SALES_QUANTITY measure.

CALL CNV_CWM.TO.ECM('sales', 'CRT_DIM_STRUCTURES', na, -
     'MEASURE::SH::SALES_CUBE::SALES_QUANTITY')