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.
None
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
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.
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 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.
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 catalog
.
Identifies a particular measure in cube
.
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.
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.
CNV_CWM.TO.ECM
.import all from eif file 'temp/dmlprogs.eif' dfns
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.
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')
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|