|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Creating an Analytic Workspace From Relational Tables, 3 of 6
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.
stage is a text expression identifying one of the following keywords:
selection is a text expression in one of the following formats:
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.
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.
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:
Stage 1: Loads all of the metadata from the database into objects in the analytic workspace.
Stage 2: Generates DML programs to fetch level values, attributes, and parent-child relationships.
Stage 3: Executes the DML programs created in stage 2.
Stage 4: Creates analytic workspace dimensions, attributes, and hierarchies.
Stage 5: Generates and executes DML programs that determine sparsity patterns and define measures and composites.
Stage 6: Creates workspace objects.
Stage 7: Generates DML programs to load data from the fact tables.
Stage 8: Executes the DML programs created in stage 7. (Default)
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
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.
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.
Limits the build to include only the metadata for
cube. When this argument is omitted, all cubes in
catalog are included in the build.
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.
Identifies a particular measure folder in the schema.
Identifies the owner of the schema that contains the dimension tables and fact tables.
Identifies a particular cube in
Identifies a particular measure in
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.
temp is the name of a directory alias to which you have write access.
dmlprogs.eif is the name of the EIF file.
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.
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.
The next example creates or attaches an analytic workspace named
saleshist and creates all of the objects defined by the OLAP metadata for the
The following example creates or attaches an analytic workspace named
sales and creates the dimensions, attributes, and hierarchies associated with the