| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The Analytic Workspace Manager package, DBMS_AWM, provides procedures for loading data from a relational data warehouse into an analytic workspace and enabling the workspace for access by the OLAP API and BI Beans.
This chapter discusses the following topics:
The parameters cube_name, dimension_name, measure_name, and level_name refer to the metadata entities in the OLAP Catalog that map to the relational source cube.
The parameters aw_cube_name or aw_dimension_name refer to the target cube or dimension within an analytic workspace.
Parameters with the suffix _spec refer to the named specifications for loading, aggregating, and optimizing a target cube in an analytic workspace.
|
See Also: "Overview" for definitions of the terms, "relational source cube", "multidimensional target cube", and "relational target cube". |
DBMS_AWM parameters are summarized in Table 23-1.
Table 23-1 Parameters of DBMS_AWM Procedures
| Parameter | Description |
|---|---|
cube_owner |
Owner of the OLAP Catalog cube associated with the relational source tables (star schema). |
cube_name |
Name of the OLAP Catalog cube associated with the relational source tables (star schema). |
dimension_owner |
Owner of the OLAP Catalog dimension associated with the source dimension lookup table. |
dimension_name |
Name of the OLAP Catalog dimension associated with the source dimension lookup table. |
aw_owner |
Owner of the analytic workspace. Also the owner of cubes and dimensions within the workspace. |
aw_cube_name |
Name of the target cube within an analytic workspace. For information on naming requirements, see Table 23-13, "CREATE_AWCUBE Procedure Parameters". |
aw_dimension_name |
Name of the target dimension within an analytic workspace. For information on naming requirements, see Table 23-18, "CREATE_AWDIMENSION Procedure Parameters". |
dimension_load_spec |
The name of a specification for loading an OLAP Catalog source dimension into a target dimension in an analytic workspace. |
cube_load_spec |
The name of a specification for loading an OLAP Catalog source cube into a target cube in an analytic workspace. |
aggregation_spec |
The name of a specification for creating the stored summaries for a target cube in an analytic workspace. |
composite_spec |
The name of a specification for defining composites and dimension order for a target cube in an analytic workspace. |
Table 23-2 lists the DBMS_AWM subprograms in alphabetical order. Each subprogram is described in detail further in this chapter.
To see the DBMS_AWM subprograms listed by function, refer to "Understanding the DBMS_AWM Procedures".
Table 23-2 DBMS_AWM Subprograms
| Subprogram | Description |
|---|---|
| ADD_AWCOMP_SPEC_COMP_MEMBER Procedure |
Adds a member to a composite in a composite specification. |
| ADD_AWCOMP_SPEC_MEMBER Procedure |
Adds a member to a composite specification. |
| ADD_AWCUBEAGG_SPEC_LEVEL Procedure |
Adds a level to an aggregation specification. |
| ADD_AWCUBEAGG_SPEC_MEASURE Procedure |
Adds a measure to an aggregation specification. |
| ADD_AWCUBELOAD_SPEC_COMP Procedure |
Adds a composite specification to a cube load specification. |
| ADD_AWCUBELOAD_SPEC_FILTER Procedure |
Adds a WHERE clause to a cube load specification. |
| ADD_AWCUBELOAD_SPEC_MEASURE Procedure |
Adds a measure to a cube load specification. |
| ADD_AWDIMLOAD_SPEC_FILTER Procedure |
Adds a WHERE clause to a dimension load specification. |
| AGGREGATE_AWCUBE Procedure |
Creates stored summaries for a cube in an analytic workspace. |
| CREATE_AWCOMP_SPEC Procedure |
Creates a composite specification for a cube. |
| CREATE_AWCUBE Procedure |
Creates containers within an analytic workspace to hold a cube defined in the OLAP Catalog. |
| CREATE_AWCUBE_ACCESS Procedure |
Creates a script to enable relational access to a cube in an analytic workspace. |
| CREATE_AWCUBE_ACCESS_FULL Procedure |
Enables relational access to a cube in an analytic workspace. |
| CREATE_AWCUBEAGG_SPEC Procedure |
Creates an aggregation specification for a cube. |
| CREATE_AWCUBELOAD_SPEC Procedure |
Creates a load specification for a cube. |
| CREATE_AWDIMENSION Procedure |
Creates containers within an analytic workspace to hold a dimension defined in the OLAP Catalog. |
| CREATE_AWDIMENSION_ACCESS Procedure |
Creates a script to enable relational access to a dimension in an analytic workspace. |
| CREATE_AWDIMENSION_ACCESS_FULL Procedure |
Enables relational access to a dimension in an analytic workspace. |
| CREATE_AWDIMLOAD_SPEC Procedure |
Creates a load specification for a dimension. |
| DELETE_AWCOMP_SPEC Procedure |
Deletes a composite specification. |
| DELETE_AWCOMP_SPEC_MEMBER Procedure |
Deletes a member of a composite specification. |
| DELETE_AWCUBE_ACCESS Procedure |
Creates a script that deletes the enablement views and metadata for a cube in an analytic workspace. |
| DELETE_AWCUBE_ACCESS_ALL Procedure |
Deletes the enablement views and metadata for a cube in an analytic workspace. |
| DELETE_AWCUBEAGG_SPEC Procedure |
Deletes an aggregation specification. |
| DELETE_AWCUBEAGG_SPEC_LEVEL Procedure |
Removes a level from an aggregation specification. |
| DELETE_AWCUBEAGG_SPEC_MEASURE Procedure |
Removes a measure from an aggregation specification. |
| DELETE_AWCUBELOAD_SPEC Procedure |
Deletes a cube load specification. |
| DELETE_AWCUBELOAD_SPEC_COMP Procedure |
Removes a composite specification from a cube load specification. |
| DELETE_AWCUBELOAD_SPEC_FILTER Procedure |
Removes a WHERE clause from a cube load specification. |
| DELETE_AWCUBELOAD_SPEC_MEASURE Procedure |
Removes a measure from a cube load specification. |
| DELETE_AWDIMENSION_ACCESS Procedure |
Creates a script that deletes the enablement views and metadata for a dimension in an analytic workspace. |
| DELETE_AWDIMENSION_ACCESS_ALL Procedure |
Deletes the enablement views and metadata for a dimension in an analytic workspace. |
| DELETE_AWDIMLOAD_SPEC Procedure |
Deletes a dimension load specification. |
| DELETE_AWDIMLOAD_SPEC_FILTER Procedure |
Removes a WHERE clause from a dimension load specification. |
| REFRESH_AWCUBE Procedure |
Loads the data and metadata of an OLAP Catalog source cube into a target cube in an analytic workspace. |
| REFRESH_AWCUBE_VIEW_NAME Procedure |
Creates metadata in the analytic workspace to support user-defined enablement view names. |
| REFRESH_AWDIMENSION Procedure |
Loads the data and metadata of an OLAP Catalog source dimension into a target dimension in an analytic workspace. |
| REFRESH_AWDIMENSION_VIEW_NAME Procedure |
Creates metadata in the analytic workspace to support user-defined enablement view names. |
| SET_AWCOMP_SPEC_CUBE Procedure |
Changes the cube associated with a composite specification. |
| SET_AWCOMP_SPEC_MEMBER_NAME Procedure |
Renames a member of a composite specification. |
| SET_AWCOMP_SPEC_MEMBER_POS Procedure |
Changes the position of a member in a composite specification. |
| SET_AWCOMP_SPEC_MEMBER_SEG Procedure |
Changes the segment size associated with a member of a composite specification. |
| SET_AWCOMP_SPEC_NAME Procedure |
Renames a composite specification. |
| SET_AWCUBE_VIEW_NAME Procedure |
Renames the relational views of an analytic workspace cube. |
| SET_AWCUBEAGG_SPEC_AGGOP Procedure |
Specifies an aggregation operator for aggregating measures along a dimension of a cube. |
| SET_AWCUBELOAD_SPEC_CUBE Procedure |
Changes the cube associated with a cube load specification. |
| SET_AWCUBELOAD_SPEC_LOADTYPE Procedure |
Changes the type of a cube load specification. |
| SET_AWCUBELOAD_SPEC_NAME Procedure |
Renames of a cube load specification. |
| SET_AWCUBELOAD_SPEC_PARAMETER Procedure |
Sets parameters for a cube load specification. |
| SET_AWDIMENSION_VIEW_NAME Procedure |
Renames the relational views of an analytic workspace dimension. |
| SET_AWDIMLOAD_SPEC_DIMENSION Procedure |
Changes the dimension associated with a dimension load specification. |
| SET_AWDIMLOAD_SPEC_LOADTYPE Procedure |
Changes the type of a dimension load specification. |
| SET_AWDIMLOAD_SPEC_NAME Procedure |
Renames a dimension load specification. |
| SET_AWDIMLOAD_SPEC_PARAMETER Procedure |
Sets a parameter for a dimension load specification. |
This procedure adds a member to a composite in a composite specification. The member may be a dimension or it may be a nested composite.
Composite members must be added in order. If you want to reorder the members, you must drop and re-create the composite. Call DELETE_AWCOMP_SPEC_MEMBER and ADD_AWCOMP_SPEC_MEMBER.
Syntax
ADD_AWCOMP_SPEC_COMP_MEMBER (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
composite_name IN VARCHAR2,
nested_member_name IN VARCHAR2,
nested_member_type IN VARCHARs,
dimension_owner IN VARCHAR2 DEFAULT NULL,
dimension_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-3 ADD_AWCOMP_SPEC_COMP_MEMBER Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
composite_name |
Name of a composite in the composite specification. |
nested_member_name |
Name of the member to add to the composite. |
nested_member_type |
Type of the new member. The type can be either 'DIMENSION'or 'COMPOSITE'. |
dimension_owner |
Owner of the OLAP Catalog source dimension to add to the composite. If the new member is a nested composite instead of a dimension, this parameter should be NULL (default). |
dimension_name |
Name of the OLAP Catalog source dimension to add to the composite. If the new member is a nested composite instead of a dimension, this parameter should be NULL (default). |
Example
The following statements add a composite COMP1, consisting of the PRODUCT and GEOGRAPHY dimensions, to the composite specification AC_COMPSPEC.
execute DBMS_AWM.Create_AWComp_spec
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'COMP1' ,'COMPOSITE');
execute DBMS_AWM.Add_AWComp_Spec_Comp_Member
('AC_COMPSPEC','XADEMO','ANALYTIC_CUBE', 'COMP1','PROD_COMP',
'DIMENSION','XADEMO','PRODUCT');
execute DBMS_AWM.Add_AWComp_Spec_Comp_Member
('AC_COMPSPEC','XADEMO','ANALYTIC_CUBE', 'COMP1','GEOG_COMP',
'DIMENSION','XADEMO','GEOGRAPHY');
See Also
This procedure adds a member to a composite specification. The members of a composite specification are composites and dimensions.
Syntax
ADD_AWCOMP_SPEC_MEMBER (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
member_name IN VARCHAR2,
member_type IN VARCHAR2,
dimension_owner IN VARCHAR2 DEFAULT NULL,
diimension_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-4 ADD_AWCOMP_SPEC_MEMBER Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
member_name |
Name of the member of the composite specification. |
member_type |
Type of the member. The type can be either 'DIMENSION' or 'COMPOSITE'. |
dimension_owner |
Owner of the OLAP Catalog source dimension to add to the composite specification. If the new member is a composite instead of a dimension, this parameter should be NULL (default). |
dimension_name |
Name of the OLAP Catalog source dimension to add to the composite specification. If the new member is a composite instead of a dimension, this parameter should be NULL (default). |
Example
The following statements add the Time dimension and a composite called COMP1 to the composite specification AC_COMPSPEC.
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'TIMECOMP_MEMBER' ,
'DIMENSION' ,'XADEMO' ,'TIME');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'COMP1' ,'COMPOSITE');
See Also
This procedure adds a level to an aggregation specification.
Syntax
ADD_AWCUBEAGG_SPEC_LEVEL (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
aw_level_name IN VARCHAR2);
Parameters
Table 23-5 ADD_AWCUBEAGG_SPEC_LEVEL Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of an aggregation specification for a cube in an analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube within the analytic workspace. |
aw_dimension_name |
Name of a dimension of the cube. |
aw_level_name |
Name of a level of the dimension. |
Example
The following statements add two levels of Product, one level of Channel, and one level of Time to the aggregation specification AC_AGGSPEC.
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AW_PROD', 'L3')
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AW_PROD', 'L2')
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC','MYSCHEMA','MYAW','AW_ANACUBE','AW_CHAN','STANDARD_2')
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AW_TIME', 'L2')
See Also
This procedure adds a measure to an aggregation specification.
Syntax
ADD_AWCUBEAGG_SPEC_MEASURE (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
aw_measure_name IN VARCHAR2);
Parameters
Table 23-6 ADD_AWCUBEAGG_SPEC_MEASURE Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of an aggregation specification for a cube in an analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube within the analytic workspace. |
aw_measure_name |
Name of one of the measures of the cube. |
Example
The following statements add the Costs and Quota measures to the aggregation specification for the cube AW_ANACUBE in the analytic workspace MYAW.
execute dbms_awm.add_awcubeagg_spec_measure
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'XXF.COSTS')
execute dbms_awm.add_awcubeagg_spec_measure
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'XXF.QUOTA')
See Also
This procedure adds a composite specification to a cube load specification.
Syntax
ADD_AWCUBELOAD_SPEC_COMP (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
composite_spec IN VARCHAR2);
Parameters
Table 23-7 ADD_AWCUBELOAD_SPEC_COMP Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
composite_spec |
Name of the composite specification to add to the cube load specification. |
Example
The following statement adds the composite specification AC_COMPSPEC to the cube load specification AC_CUBELOADSPEC.
execute DBMS_AWM.add_AWCubeLoad_Spec_Comp
('AC_CUBELOADSPEC' ,'XADEMO', 'ANALYTIC_CUBE', 'AC_COMPSPEC');
See Also
This procedure adds a filter condition to a cube load specification. The filter is a SQL WHERE clause that will be used in the query against the source fact table.
Syntax
ADD_AWCUBELOAD_SPEC_FILTER (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
fact_table_owner IN VARCHAR2,
fact_table_name IN VARCHAR2,
where_clause IN VARCHAR2);
Parameters
Table 23-8 ADD_AWCUBELOAD_SPEC_FILTER Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
fact_table_owner |
Owner of the fact table that is mapped to the OLAP Catalog source cube. |
fact_table_name |
Name of the fact table that is mapped to the OLAP Catalog source cube |
where_clause |
A SQL WHERE clause that specifies which rows to load from the fact table. |
Example
The following statements create a cube load specification called AC_CUBELOADSPEC2. When the target cube in the analytic workspace is refreshed with this specification, only sales figures less than 25 will be loaded.
execute dbms_awm.create_awcubeload_spec
('AC_CUBELOADSPEC2', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA');
execute dbms_awm.add_awcubeload_spec_measure
('AC_CUBELOADSPEC2', 'XADEMO', 'ANALYTIC_CUBE', 'F.SALES',
'AW_SALES', 'Sales');
execute dbms_awm.add_awcubeload_spec_filter
('AC_CUBELOADSPEC2', 'XADEMO', 'ANALYTIC_CUBE',
'XADEMO', 'XADEMO_ANALYTIC_FACTS', '''SALES'' < 25');
See Also
This procedure adds a measure to a cube load specification.
If you add one or more measures to a cube load specification, only those measures will be loaded. If you do not add measures to the cube load specification, then all the cube's measures will be loaded.
This procedure allows you to specify the measure name, display name, and description in the analytic workspace. If you do not specify the target names, or if you do not call this procedure at all, the source names from the OLAP Catalog are used.
Syntax
ADD_AWCUBELOAD_SPEC_MEASURE (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
measure_name IN VARCHAR2,
aw_measure_name IN VARCHAR2 DEFAULT NULL,
aw_measure_display_name IN VARCHAR2 DEFAULT NULL,
aw_measure_description IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-9 ADD_AWCUBELOAD_SPEC_MEASURE Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
measure_name |
Name of the OLAP Catalog source measure. |
aw_measure_name |
Name of the target measure in the analytic workspace. If you do not specify a name, the measure name from the OLAP Catalog is used. |
aw_measure_display_name |
Display name for the target measure in the analytic workspace. If you do not specify a display name, the display name for the measure in the OLAP Catalog is used. |
aw_measure_description |
Description for the target measure in the analytic workspace. If you do not specify a description, the description for the measure in the OLAP Catalog is used. |
Example
The following statements create a cube load specification called AC_CUBELOADSPEC2. When the target cube in the analytic workspace is refreshed with this specification, only the sales measure will be loaded.
The target sales measure will have the logical name AW_SALES, and its description will be 'Sales'.
execute dbms_awm.create_awcubeload_spec
('AC_CUBELOADSPEC2', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA');
execute dbms_awm.add_awcubeload_spec_measure
('AC_CUBELOADSPEC2', 'XADEMO', 'ANALYTIC_CUBE', 'F.SALES',
'AW_SALES', 'Sales');
See Also
This procedure adds a filter condition to a dimension load specification. The filter is a SQL WHERE clause that will be used in the query against the source dimension tables.
Syntax
ADD_AWDIMLOAD_SPEC_FILTER (
dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
dimension_table_owner IN VARCHAR2,
dimension_table_name IN VARCHAR2,
where_clause IN VARCHAR2);
Parameters
Table 23-10 ADD_AWDIMLOAD_SPEC_FILTER Procedure Parameters
| Parameter | Description |
|---|---|
dimension_load_spec |
Name of a dimension load specification. |
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
dimension_table_owner |
Owner of the dimension table that is mapped to the OLAP Catalog source dimension. |
dimension_table_name |
Name of the dimension table that is mapped to the OLAP Catalog source dimension. |
where_clause |
A SQL WHERE clause that specifies which rows to load from the dimension table into an analytic workspace. |
Example
The following statements create a load specification for the CHANNEL dimension in XADEMO. When the target dimension is refreshed with this specification, only the member DIRECT will be loaded.
execute dbms_awm.create_awdimload_spec
('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'FULL_LOAD');
execute dbms_awm.add_awdimload_spec_filter
('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'XADEMO',
'XADEMO_CHANNEL', '''CHAN_STD_CHANNEL'' = ''DIRECT''' );
See Also
This procedure uses an aggregation specification to precompute and store aggregate data for a cube in an analytic workspace.
The REFRESH_AWCUBE procedure loads detail data and sets up the internal workspace structures that support dynamic aggregation. If you want to precompute and store summarized data for the cube, you must use the AGGREGATE_AWCUBE procedure.
You must rerun AGGREGATE_AWCUBE after every refresh to ensure that the stored summaries are consistent with the data.
AGGREGATE_AWCUBE executes an OLAP DML UPDATE command to save the changes in the analytic workspace. AGGREGATE_AWCUBE does not execute a SQL COMMIT.
Syntax
AGGREGATE_AWCUBE (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
aggregation_spec IN VARCHAR2);
Parameters
Table 23-11 AGGREGATE_AWCUBE Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube within the analytic workspace. |
aggregation_spec |
Name of an aggregation specification for the cube. |
Example
The following statements create an aggregation plan AGG1 for the target cube AC2 in the analytic workspace MYSCHEMA.MYAW. The target cube was created from the source cube XADEMO.ANALYTIC_CUBE.
---- Create agg plan for analytic cube ----------------------------------------
---- with levels 2 and 3 of product, standard_2 of channel, and 2 of time ----
---- with measures costs and quota -------------------------------------------
execute dbms_awm.create_awcubeagg_spec
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2')
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'PRODUCT', 'L3')
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'PRODUCT', 'L2')
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'CHANNEL', 'STANDARD_2')
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'TIME', 'L2')
execute dbms_awm.add_awcubeagg_spec_measure
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'XXF.COSTS')
execute dbms_awm.add_awcubeagg_spec_measure
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'XXF.QUOTA')
execute dbms_awm.aggregate_awcube('MYSCHEMA', 'MYAW', 'AC2', 'AGG1')
See Also
This procedure creates a composite specification for an OLAP Catalog source cube. The composite specification determines how sparse data will be stored in the target cube in an analytic workspace. It also determines the dimension order, which affects the efficiency of data loads and queries.
A composite is a list of dimension value combinations that provides an index into one or more sparse measures. Composites are named objects within an analytic workspace. Composites are defined and maintained with OLAP DML commands.
Members of a composite specification are composites (whose members are dimensions) and individual dimensions.
Syntax
CREATE_AWCOMP_SPEC (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2);
Parameters
Table 23-12 CREATE_AWCOMP_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
Note
You can use the following procedures to modify an existing composite specification:
Example
The following statements create a composite specification for the ANALYTIC_CUBE in XADEMO. It consists of the Time dimension followed by a composite called COMP1.
execute DBMS_AWM.Create_AWComp_spec
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'TIMECOMP_MEMBER' ,
'DIMENSION' ,'XADEMO' ,'TIME');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'COMP1' ,'COMPOSITE');
See Also
DEFINE COMPOSITE in the Oracle OLAP DML Reference
This procedure creates the multidimensional framework within an analytic workspace to hold a relational cube.
The relational cube, consisting of a star schema and OLAP Catalog metadata, is the source for the target multidimensional cube in the analytic workspace. Data and metadata are loaded from the source cube to the target cube by the REFRESH_AWCUBE procedure.
CREATE_AWCUBE executes an OLAP DML UPDATE command to save the changes in the analytic workspace. CREATE_AWCUBE does not execute a SQL COMMIT.
The multidimensional framework for the cube is in database standard form, ensuring its compatibility with the OLAP API enablers and with other OLAP administrative tools and utilities.
|
Note: Before executingCREATE_AWCUBE to create a new workspace cube, you must execute CREATE_AWDIMENSION for each of the cube's dimensions. |
Syntax
CREATE_AWCUBE (
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-13 CREATE_AWCUBE Procedure Parameters
| Parameter | Description |
|---|---|
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name for the target cube within the analytic workspace.
If you specify a name for the cube in the analytic workspace, the name must conform to general object naming conventions for SQL, and it must be unique within the schema that owns the analytic workspace. To test uniqueness, use a statement like the following. select owner, cube_name from all_olap2_cubes union all select aw_owner, aw_logical_name from all_olap2_aw_cubes; Within the analytic workspace, you can generally reference the cube by its simple target cube name. However, database standard form also supports a full name for logical objects. For cubes, the full name is: aw_owner.aw_cube_name.CUBE |
Example
The following statements create the structures for the XADEMO.ANALYTIC_CUBE in the analytic workspace MYSCHEMA.MYAW. The name of the cube in the workspace is AW_ANACUBE.
--- Create the dimensions in the analytic workspace ----
execute dbms_awm.create_awdimension
('XADEMO','CHANNEL','MYSCHEMA', 'MYAW', 'AW_CHAN');
execute dbms_awm.create_awdimension
('XADEMO','GEOGRAPHY','MYSCHEMA','MYAW', 'AW_GEOG');
execute dbms_awm.create_awdimension
('XADEMO','PRODUCT','MYSCHEMA', 'MYAW', 'AW_PROD');
execute dbms_awm.create_awdimension
('XADEMO','TIME','MYSCHEMA', 'MYAW', 'AW_TIME');
--- Create the cube in the analytic workspace ----
execute dbms_awm.create_awcube
('XADEMO', 'ANALYTIC_CUBE','MYSCHEMA', 'MYAW','AW_ANACUBE');
You can use statements like the following to verify that the cube has been created in the analytic workspace.
--- View the cube in the analytic workspace ----
execute dbms_aw.execute
('aw attach MYSCHEMA.MYAW');
execute dbms_aw.execute
('limit name to obj(property''AW$ROLE'') eq ''CUBEDEF''');
execute dbms_aw.execute
('report w 40 name');
NAME ---------------------------------------- AW_ANACUBE
Alternatively, you can query the Active Catalog to verify that the cube has been created.
select * from all_olap2_aw_cubes
where owner in 'myschema' and
aw_name in 'myaw' and
aw_logical_name in 'aw_anacube';
See Also
This procedure generates a script that creates relational fact views of a cube in an analytic workspace. The views are in the embedded total format required by the OLAP API.
The script can optionally generate OLAP Catalog metadata that maps to the views of the workspace cube. This metadata is required for the OLAP API.
Both dimension views and fact views are required for relational access to the workspace cube. Use the CREATE_AWDIMENSION_ACCESS procedure to generate the scripts that create the dimension views.
To accomplish the cube enablement process in a single step, use the CREATE_AWCUBE_ACCESS_FULL procedure. This procedure both creates and runs the enablement script.
Syntax
CREATE_AWCUBE_ACCESS (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
access_type IN VARCHAR2,
script_directory IN VARCHAR2,
script_name IN VARCHAR2,
open_mode IN VARCHAR2);
Parameters
Table 23-14 CREATE_AWCUBE_ACCESS Procedure Parameters
Example
The following statement creates an enablement script called aw_anacube_enable.sql in the /dat1/scripts directory. You can run the script to create fact views of the AW_ANACUBE cube in workspace XADEMO.MYAW. The script will also generate an OLAP Catalog cube called AW_ANACUBE that maps to the views.
execute dbms_awm.create_awcube_access
('XADEMO', 'MYAW', 'AW_ANACUBE', 'OLAP',
'/dat1/scripts/', 'aw_anacube_enable.sql', 'w');
See Also
This procedure accomplishes the entire process of enabling a workspace cube for access by the OLAP API. Like CREATE_AWCUBE_ACCESS it produces an enablement script. However it does not write the script to a file. Instead it writes the script to temporary memory and runs the script.
The resulting views and metadata are identical to those created by the enablement scripts produced by CREATE_AWCUBE_ACCESS.
Syntax
CREATE_AWCUBE_ACCESS_FULL (
run_id IN NUMBER,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
access_type IN VARCHAR2);
Parameters
Table 23-15 CREATE_AWCUBE_ACCESS_FULL Procedure Parameters
| Parameter | Description |
|---|---|
run_id |
An assigned slot in a global temporary table for holding the record associated with this operation. In most cases, simply specify "1". |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube in the analytic workspace. |
access_type |
Controls whether or not to generate OLAP Catalog metadata in addition to the enablement views. Specify one of the following values:
|
See Also
This procedure creates an aggregation specification for an OLAP Catalog cube. The aggregation specification determines the summary data that will be stored with the target cube in the analytic workspace.
The aggregation specification determines which of the cube's levels will be pre-summarized. You can aggregate all of the cube's measures to these levels, or you can choose individual measures. All of the measures are aggregated to the same levels.
Any levels that are not pre-aggregated will be aggregated dynamically as they are queried. Determining which data to preaggregate will involve an evaluation of storage and memory constraints and typical client queries. If you do not provide an aggregation specification, no summaries will be stored and all aggregation will be performed on demand.
An aggregation specification uses the aggregation subsystem of the OLAP DML. This includes the AGGREGATE command, aggregation maps, and related functionality.
Syntax
CREATE_AWCUBEAGG_SPEC (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2);
Parameters
Table 23-16 CREATE_AWCUBEAGG_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of an aggregation specification for a cube in an analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube in the analytic workspace. |
Note
You can use the following procedure to modify an existing aggregation specification: SET_AWCUBEAGG_SPEC_AGGOP Procedure
Example
The following statements create an aggregation specification for the target cube AW_ANACUBE in the analytic workspace MYSCHEMA.MYAW. It specifies that the Costs and Sales measures should include stored totals for the third level of PRODUCT, the STANDARD_2 level of CHANNEL, and the second level of TIME.
execute dbms_awm.create_awcubeagg_spec
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE');
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AW_PROD', 'L3');
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AW_CHAN',
'STANDARD_2');
execute dbms_awm.add_awcubeagg_spec_level
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AW_TIME', 'L2');
execute dbms_awm.add_awcubeagg_spec_measure
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'XXF.COSTS');
execute dbms_awm.add_awcubeagg_spec_measure
('AC_AGGSPEC', 'MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'XXF.SALES');
See Also
AGGREGATE Command in the Oracle OLAP DML Reference
This procedure creates a load specification for an OLAP Catalog cube. The load specification determines how the cube's data will be loaded from the relational fact table into an analytic workspace by the REFRESH_AWCUBE procedure.
A cube load specification defines a load type, which indicates whether the data or only the load instructions should be loaded into the analytic workspace. The load instructions are OLAP DML programs. If you choose to load only the instructions, you can run these programs to perform the data load at a later time.
A separate specification created by CREATE_AWCOMP_SPEC can be associated with a cube load specification. This specification specifies dimension order and determines how sparse data will be stored within the analytic workspace.
Syntax
CREATE_AWCUBELOAD_SPEC (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
load_type IN VARCHAR2);
Parameters
Table 23-17 CREATE_AWCUBELOAD_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
load_type |
'LOAD_DATA' -- Load the data from the fact table into the analytic workspace target cube.
->show obj(property 'aw$loadprgs' 'my_awcube_name') |
Note
You can use the following procedures to modify an existing cube load specification:
Example
The following statement creates a cube load specification for the source cube XADEMO.ANALYTIC_CUBE. The load specification is used to refresh the target cube AW_ANACUBE in MYSCHEMA.MYAW.
execute dbms_awm.create_awcubeload_spec
('AC_CUBELOADSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA');
execute dbms_awm.refresh_awcube
('MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AC_CUBELOADSPEC');
See Also
This procedure creates the multidimensional framework within an analytic workspace to hold a relational dimension.
The relational dimension, consisting of dimension lookup tables and OLAP Catalog metadata, is the source for the target dimension in the analytic workspace. Data and metadata are loaded from the source dimension to the target dimension by the REFRESH_AWDIMENSION procedure.
CREATE_AWDIMENSION executes an OLAP DML UPDATE command to save the changes in the analytic workspace. CREATE_AWDIMENSION does not execute a SQL COMMIT.
The multidimensional framework for the dimension is in database standard form, ensuring its compatibility with the OLAP API enablers and with other OLAP administrative tools and utilities.
|
Note: Before executingCREATE_AWCUBE to create a new workspace cube, you must execute CREATE_AWDIMENSION for each of the cube's dimensions.
The workspace must already exist before the first call to |
Syntax
CREATE_AWDIMENSION (
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2 DEFAULT NULL),
Parameters
Table 23-18 CREATE_AWDIMENSION Procedure Parameters
| Parameter | Description |
|---|---|
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_dimension_name |
Name for the target dimension within the analytic workspace.
If you specify a name for the dimension in the analytic workspace, the name must conform to general object naming conventions for SQL, and it must be unique within the schema that owns the analytic workspace. To test uniqueness, use a statement like the following. select owner, dimension_name from all_olap2_dimensions union all select aw_owner, aw_logical_name from all_olap2_aw_dimensions; Within the analytic workspace, you can generally reference the dimension by its simple target dimension name. However, database standard form also supports a full name for logical objects. For dimensions, the full name is: aw_owner.aw_dimension_name.DIMENSION |
Example
The following statements create analytic workspace dimensions for CHANNEL, GEOGRAPHY, PRODUCT, TIME, and DIVISION in the workspace MYAW in the XADEMO schema.
execute dbms_awm.create_awdimension
('XADEMO','CHANNEL','MYSCHEMA', 'MYAW', 'AW_CHAN');
execute dbms_awm.create_awdimension
('XADEMO','GEOGRAPHY','MYSCHEMA','MYAW', 'AW_GEOG');
execute dbms_awm.create_awdimension
('XADEMO','PRODUCT','MYSCHEMA', 'MYAW', 'AW_PROD');
execute dbms_awm.create_awdimension
('XADEMO','TIME','MYSCHEMA', 'MYAW', 'AW_TIME');
execute dbms_awm.create_awdimension
('XADEMO','DIVISION','MYSCHEMA', 'MYAW', 'AW_DIV');
You can use statements like the following to verify that the dimensions have been created in the analytic workspace.
execute dbms_aw.execute
('aw attach MYSCHEMA.MYAW');
execute dbms_aw.execute
('limit name to obj(property''AW$ROLE'') eq ''DIMDEF''');
execute dbms_aw.execute
('report w 40 name');
NAME
----------------------------------------
AW_CHAN
AW_GEOG
AW_PROD
AW_TIME
AW_DIV
Alternatively, you can query the Active Catalog to verify that the dimensions have been created.
select * from all_olap2_aw_dimensions
where aw_owner in 'myschema' and aw_name in 'myaw';
See Also
This procedure generates a script that creates relational views of a dimension in an analytic workspace. The views are in the embedded total format required by the OLAP API.
The script can optionally generate OLAP Catalog metadata that maps to the views of the workspace dimension. This metadata is required for the OLAP API.
Both fact views and dimension views are required for relational access to a workspace cube. Use the CREATE_AWCUBE_ACCESS procedure to generate the scripts that create the fact views.
To accomplish the enablement process in a single step, use the CREATE_AWDIMENSION_ACCESS_FULL procedure. This procedure both creates and runs the enablement script.
Syntax
CREATE_AWDIMENSION_ACCESS (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
access_type IN VARCHAR2,
script_directory IN VARCHAR2,
script_name IN VARCHAR2,
open_mode IN VARCHAR2);
Parameters
Table 23-19 CREATE_AWDIMENSION_ACCESS Procedure Parameters
Example
The following statement creates an enablement script called aw_prod_enable in the /dat1/scripts directory. You can run the script to create views of the AW_PROD dimension in workspace XADEMO.MYAW. The script will also generate an OLAP Catalog dimension called AW_PROD that maps to the view.
execute dbms_awm.create_awdimension_access
('XADEMO', 'MYAW', 'AW_PROD', 'OLAP',
'/dat1/scripts/', 'aw_prod_enable', 'w');
See Also
This procedure accomplishes the entire process of enabling a workspace dimension for access by the OLAP API. Like CREATE_AWDIMENSION_ACCESS it produces an enablement script. However it does not write the script to a file. Instead it writes the script to temporary memory and runs the script.
The resulting views and metadata are identical to those created by the enablement scripts created by CREATE_AWDIMENSION_ACCESS.
Syntax
CREATE_AWDIMENSION_ACCESS_FULL (
run_id IN NUMBER,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
access_type IN VARCHAR2);
Parameters
Table 23-20 CREATE_AWDIMENSION_ACCESS_FULL Procedure Parameters
| Parameter | Description |
|---|---|
run_id |
An assigned slot in a global temporary table for holding the record associated with this operation. In most cases, simply specify "1". |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_dimension_name |
Name of the dimension in the analytic workspace. |
access_type |
Controls whether or not to generate OLAP Catalog metadata in addition to the enablement views. Specify one of the following values:
|
See Also
This procedure creates a load specification for an OLAP Catalog dimension. The load specification determines how the dimension will be loaded from relational dimension tables into an analytic workspace by the REFRESH_AWDIMENSION procedure.
If you refresh a dimension without a load specification, only new dimension members are loaded.
Syntax
CREATE_AWDIMLOAD_SPEC (
dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
load_type IN VARCHAR2);
Parameters
Table 23-21 CREATE_AWDIMLOAD_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
dimension_load_spec |
Name of the load specification.
You can use the |
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
load_type |
Specify one of the following:
|
Note
You can use the following procedures to modify an existing dimension load specification:
Example
The following statements create a load specification for the XADEMO.CHANNEL source dimension and use it to load the target dimension AW_CHAN in the analytic workspace MYSCHEMA.MYAW. The load specification includes a filter condition (WHERE clause) that causes only the dimension member 'DIRECT' to be loaded.
execute dbms_awm.create_awdimload_spec
('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'FULL_LOAD');
execute dbms_awm.add_awdimload_spec_filter
('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'XADEMO',
'XADEMO_CHANNEL', '''CHAN_STD_CHANNEL'' = ''DIRECT''' );
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_CHAN', 'CHAN_DIMLOADSPEC');
See Also
This procedure deletes a composite specification.
Syntax
DELETE_AWCOMP_SPEC (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2);
Parameters
Table 23-22 DELETE_AWCOMP_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_ownere |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
See Also
This procedure removes a member of a composite specification. The member can be either a dimension or composite.
Syntax
DELETE_AWCOMP_SPEC_MEMBER (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
member_name IN VARCHAR2,);
Parameters
Table 23-23 DELETE_AWCOMP_SPEC_MEMBER Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
member_name |
Name of the dimension or composite to delete. |
See Also
ADD_AWCOMP_SPEC_MEMBER Procedure
This procedure generates a script that you can run to drop the views and OLAP Catalog metadata associated with a workspace cube. The script does not delete the enablement metadata that is stored in the analytic workspace.
If you drop the workspace cube or the workspace itself, you should run this procedure to clean up the associated enablement views and metadata.
You do not need to run this procedure if you are creating a new generation of enablement views and metadata. The enablement process itself drops the previous generation before creating the new views and metadata.
Syntax
DELETE_AWCUBE_ACCESS (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
access_type IN VARCHAR2,
script_directory IN VARCHAR2,
script_name IN VARCHAR2,
open_mode IN VARCHAR2);
Parameters
Table 23-24 DELETE_AWCUBE_ACCESS Procedure Parameters
See Also
This procedure deletes all the enablement views and metadata for a cube. It writes a script to a temporary location in memory and runs the script.
Syntax
DELETE_AWCUBE_ACCESS_ALL (
run_id IN NUMBER,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
access_type IN VARCHAR2);
Parameters
Table 23-25 DELETE_AWCUBE_ACCESS_ALL Procedure Parameters
| Parameter | Description |
|---|---|
run_id |
An assigned slot in a global temporary table for holding the record associated with this operation. In most cases, simply specify "1". |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube in the analytic workspace. |
access_type |
Controls whether or not to generate OLAP Catalog metadata in addition to the enablement views. Specify one of the following values:
|
See Also
This procedure deletes an aggregation specification.
Syntax
DELETE_AWCUBEAGG_SPEC (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2);
Parameters
Table 23-26 DELETE_AWCUBEAGG_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of an aggregation specification for a cube in an analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube in the analytic workspace. |
See Also
CREATE_AWCUBEAGG_SPEC Procedure
This procedure removes a level from an aggregation specification.
Syntax
DELETE_AWCUBEAGG_SPEC_LEVEL (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
aw_level_name IN VARCHAR2);
Parameters
Table 23-27 DELETE_AWCUBEAGG_SPEC_LEVEL Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of an aggregation specification for a cube in an analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the cube in the analytic workspace. |
aw_dimension_name |
Name of a dimension of the cube. |
aw_level_name |
Name of a level of the dimension. |
See Also
ADD_AWCUBEAGG_SPEC_LEVEL Procedure
This procedure removes a measure from an aggregation specification.
Syntax
DELETE_AWCUBEAGG_SPEC_MEASURE (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
aw_measure_name IN VARCHAR2);
Parameters
Table 23-28 DELETE_AWCUBEAGG_SPEC_MEASURE Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of an aggregation specification for a cube in an analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of target cube in the analytic workspace. |
aw_measure_name |
Name of the measure to remove. |
See Also
ADD_AWCUBEAGG_SPEC_MEASURE Procedure
This procedure deletes a cube load specification.
Syntax
DELETE_AWCUBELOAD_SPEC (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2);
Parameters
Table 23-29 DELETE_AWCUBELOAD_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
See Also
CREATE_AWCUBELOAD_SPEC Procedure
This procedure removes a composite specification from a cube load specification.
Syntax
DELETE_AWCUBELOAD_SPEC_COMP (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
composite_spec IN VARCHAR2);
Parameters
Table 23-30 DELETE_AWCUBELOAD_SPEC_COMP Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
composite_spec |
Name of the composite specification to delete. |
See Also
ADD_AWCUBELOAD_SPEC_COMP Procedure
This procedure removes the filter condition (WHERE clause) from a cube load specification.
Syntax
DELETE_AWCUBELOAD_SPEC_FILTER (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
fact_table_owner IN VARCHAR2,
fact_table_name IN VARCHAR2);
Parameters
Table 23-31 DELETE_AWCUBELOAD_SPEC_FILTER Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
fact_table_owner |
Owner of the fact table that is mapped to this OLAP Catalog source cube. |
fact_table_name |
Name of the fact table that is mapped to this OLAP Catalog source cube |
See Also
ADD_AWCUBELOAD_SPEC_FILTER Procedure
This procedure removes a measure from a cube load specification.
Syntax
DELETE_AWCUBELOAD_SPEC_MEASURE (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
measure_name IN VARCHAR2);
Parameters
Table 23-32 DELETE_AWCUBELOAD_SPEC_MEASURE Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
measure_name |
Name of the measure to delete. |
See Also
"ADD_AWCUBELOAD_SPEC_MEASURE Procedure"
This procedure generates a script that you can run to drop the views and OLAP Catalog metadata associated with a workspace dimension. The script does not delete the enablement metadata that is stored in the analytic workspace.
If you drop the workspace dimension or the workspace itself, you should run this procedure to clean up the associated enablement views and metadata.
You do not need to run this procedure if you are creating a new generation of enablement views and metadata. The enablement process itself drops the previous generation before creating the new views and metadata.
Syntax
DELETE_AWDIMENSION_ACCESS (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
access_type IN VARCHAR2,
script_directory IN VARCHAR2,
script_name IN VARCHAR2,
open_mode IN VARCHAR2);
Parameters
Table 23-33 DELETE_AWDIMENSION_ACCESS Procedure Parameters
See Also
This procedure deletes all the enablement views and metadata for a dimension. It writes a script to a temporary location in memory and runs the script.
Syntax
DELETE_AWDIMENSION_ACCESS_ALL (
run_id IN NUMBER,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
access_type IN VARCHAR2);
Parameters
Table 23-34 DELETE_AWDIMENSION_ACCESS_ALL Procedure Parameters
| Parameter | Description |
|---|---|
run_id |
An assigned slot in a global temporary table for holding the record associated with this operation. In most cases, simply specify "1". |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_dimension_name |
Name of the dimension in the analytic workspace. |
access_type |
Controls whether or not to generate OLAP Catalog metadata in addition to the enablement views. Specify one of the following values:
|
See Also
This procedure deletes a dimension load specification.
Syntax
DELETE_AWDIMLOAD_SPEC (
dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2);
Parameters
Table 23-35 DELETE_AWDIMLOAD_SPEC Procedure Parameters
| Parameter | Description |
|---|---|
dimension_load_spec |
Name of a dimension load specification. |
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
See Also
CREATE_AWDIMLOAD_SPEC Procedure
This procedure removes the filter condition (WHERE clause) from a dimension load specification.
Syntax
DELETE_AWDIMLOAD_SPEC_FILTER (
dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2);
dimension_table_owner IN VARCHAR2,
dimension_table_name IN VARCHAR2);
Parameters
Table 23-36 DELETE_AWDIMLOAD_SPEC_FILTER Procedure Parameters
| Parameter | Description |
|---|---|
dimension_load_spec |
Name of a dimension load specification. |
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
dimension_table_owner |
Owner of the dimension table that is mapped to the OLAP Catalog source dimension. |
dimension_table_name |
Name of the dimension table that is mapped to the OLAP Catalog source dimension. |
See Also
ADD_AWDIMLOAD_SPEC_FILTER Procedure
This procedure loads data and metadata from an OLAP Catalog source cube into a target cube in an analytic workspace.
REFRESH_AWCUBE executes an OLAP DML UPDATE command to save the changes in the analytic workspace. REFRESH_AWCUBE does not execute a SQL COMMIT.
You can include a cube load specification to determine how the cube's data will be refreshed. The cube load specification determines whether to load the data or only the load program for execution at a later time. The cube load specification may include a composite specification, which determines dimension order and handling of sparse data.
If you do not include a load specification, all the data is loaded. If you do not include a composite specification, the dimensions are ordered with Time as the fastest-varying followed by a composite of all the other dimensions. The dimensions in the composite are ordered in descending order according to size (number of dimension members).
Unless the load specification for the cube identifies individual measures (ADD_AWCUBELOAD_SPEC_MEASURE), all of the cube's measures are loaded into the workspace. Unless the load specification for the cube includes a filter condition (a WHERE clause on the fact table), all the measures' data is loaded into the workspace.
Before the first call to REFRESH_AWCUBE, you must call REFRESH_AWDIMENSION for each of the cube's dimensions. Before refreshing a cube that already contains data, you must refresh any of its dimensions that have changed since the last refresh.
Syntax
REFRESH_AWCUBE (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
cube_load_spec IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-37 REFRESH_AWCUBE Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the target cube in the analytic workspace. |
cube_load_spec |
Name of the cube load specification. If you do not include a load specification, all the fact data is loaded (default). |
Note
All the OLAP Catalog metadata that defines the logical cube, including its dimensionality, measures, and descriptions, is refreshed whenever you refresh the workspace cube. The cube's data is refreshed according to the load specification. For more information, see "Refreshing the Cube's Metadata"
For information about the relationship between the refresh and enablement processes, see "Enablement Metadata in the Analytic Workspace".
For information about the relationship between the refresh and aggregation processes, see "Aggregating the Data in an Analytic Workspace".
Example
The following statements create the target cube AW_ANACUBE from the source cube XADEMO.ANALYTIC_CUBE. They refresh all of target cube's dimensions, then they create a load specification and refresh the target cube's data.
-- create cube, cube load spec, and refresh
execute dbms_awm.create_awcube
('XADEMO', 'ANALYTIC_CUBE','MYSCHEMA', 'MYAW','AW_ANACUBE');
execute dbms_awm.create_awcubeload_spec
('AC_CUBELOADSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA')
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_CHAN');
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_PROD');
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_GEOG');
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_TIME');
execute dbms_awm.refresh_awcube
('MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AC_CUBELOADSPEC')
See Also
This procedure creates metadata in the analytic workspace to support user-defined names for the enablement views. This procedure is not used for cubes created and maintained by the DBMS_AWM package.
This procedure is required if you want to specify your own names for the enablement views in analytic workspaces that were not created by DBMS_AWM. For example, if you used the OLAP Analytic Workspace Java API to refresh the cube, you must call this procedure before calling SET_AWCUBE_VIEW_NAME.
Syntax
REFRESH_AWCUBE_VIEW_NAME (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2);
Parameters
Table 23-38 REFRESH_AWCUBE_VIEW_NAME Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Analytic workspace owner. |
aw_name |
Analytic workspace name. |
aw_cube_name |
Analytic workspace cube name. |
Note
For details about enablement view names, see "Default Fact View Names".
See Also
This procedure loads data and metadata from an OLAP Catalog source dimension into a target dimension in an analytic workspace.
REFRESH_AWDIMENSION executes an OLAP DML UPDATE command to save the changes in the analytic workspace. REFRESH_AWDIMENSION does not execute a SQL COMMIT.
You can include a dimension load specification to determine how the dimension's members will be refreshed in the workspace. If you do not include a load specification, all dimension members are selected for loading, but only new members are actually added to the target dimension.
You can select individual dimension members to load from the source tables by specifying a filter condition (a WHERE clause on the dimension table).
Before the first call to REFRESH_AWCUBE, you must call REFRESH_AWDIMENSION for each of the cube's dimensions. On all subsequent cube refreshes, you only need to call REFRESH_AWDIMENSION if changes have been made to the source dimensions, for example if new time periods have been added to a time dimension.
Syntax
REFRESH_AWDIMENSION (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
dimension_load_spec IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-39 REFRESH_AWDIMENSION Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_dimension_name |
Name of the target dimension within the analytic workspace. |
dimension_load_spec |
Name of a dimension load specification. If you do not include a load specification, new members are appended to the target dimension (default) |
Note
All the OLAP Catalog metadata that defines the logical dimension, including its levels, hierarchies, attributes, and descriptions, is refreshed whenever you refresh the workspace dimension. The dimension's data is refreshed according to the load specification. For more information, see "Refreshing the Dimension's Metadata"
For information about the relationship between the refresh and enablement processes, see "Enablement Metadata in the Analytic Workspace".
Example
The following statements refresh the dimensions of the XADEMO.ANALYTIC_CUBE source cube in the analytic workspace MYSCHEMA.MYAW.
-- Create dimension load specs and refresh dimensions
-- CHANNEL dimension
execute dbms_awm.create_awdimload_spec
('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'FULL_LOAD');
execute dbms_awm.add_awdimload_spec_filter
('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'XADEMO',
'XADEMO_CHANNEL', '''CHAN_STD_CHANNEL'' = ''DIRECT''' );
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_CHAN', 'CHAN_DIMLOADSPEC');
-- PRODUCT dimension
execute dbms_awm.create_awdimload_spec
('PROD_DIMLOADSPEC', 'XADEMO', 'PRODUCT', 'FULL_LOAD');
execute dbms_awm.Set_AWDimLoad_Spec_Parameter
('PROD_DIMLOADSPEC', 'XADEMO', 'PRODUCT', 'UNIQUE_RDBMS_KEY', 'YES');
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_PROD', 'PROD_DIMLOADSPEC');
-- GEOGRAPHY dimension
execute dbms_awm.create_awdimload_spec
('GEOG_DIMLOADSPEC', 'XADEMO', 'GEOGRAPHY', 'FULL_LOAD');
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_GEOG', 'GEOG_DIMLOADSPEC');
-- TIME dimension
execute dbms_awm.create_awdimload_spec
('TIME_DIMLOADSPEC', 'XADEMO', 'TIME', 'FULL_LOAD');
execute dbms_awm.refresh_awdimension
('MYSCHEMA', 'MYAW', 'AW_TIME', 'TIME_DIMLOADSPEC');
See Also
This procedure creates metadata in the analytic workspace to support user-defined names for the enablement views. This procedure is not needed for dimensions created and maintained by the DBMS_AWM package.
This procedure is required if you want to specify your own names for the enablement views in analytic workspaces that were not created by DBMS_AWM. For example, if you used the OLAP Analytic Workspace Java API to refresh the dimension, you must call this procedure before calling SET_AWDIMENSION_VIEW_NAME.
Syntax
REFRESH_AWDIMENSION_VIEW_NAME (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2);
Parameters
Table 23-40 REFRESH_AWDIMENSION_VIEW_NAME Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Analytic workspace owner. |
aw_name |
Analytic workspace name. |
aw_dimension_name |
Analytic workspace dimension name. |
Note
For details about enablement view names, see "Default Dimension View Names".
See Also
This procedure associates a composite specification with a different cube.
Syntax
SET_AWCOMP_SPEC_CUBE (
composite_spec IN VARCHAR2,
old_cube_owner IN VARCHAR2,
old_cube_name IN VARCHAR2,
new_cube_owner IN VARCHAR2,
new_cube_name IN VARCHAR2);
Parameters
Table 23-41 SET_AWCOMP_SPEC_CUBE Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification. |
old_cube_owner |
Owner of the old OLAP Catalog source cube. |
old_cube_name |
Name of the old OLAP Catalog source cube. |
new_cube_owner |
Owner of the new OLAP Catalog source cube. |
new_cube_name |
Name of the new OLAP Catalog source cube. |
See Also
This procedure changes the name of a member of a composite specification. The member may be either a dimension or a composite.
Syntax
SET_AWCOMP_SPEC_MEMBER_NAME (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
old_member_name IN VARCHAR2,
new_member_name IN VARCHAR2);
Parameters
Table 23-42 SET_AWCOMP_SPEC_MEMBER_NAME Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
old_member_name |
Old member name. Either a dimension or a composite. |
new_member_name |
New member name. |
See Also
This procedure sets the position of a member of a composite specification. The member can be either a dimension or a composite.
Syntax
SET_AWCOMP_SPEC_MEMBER_POS (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
member_name IN VARCHAR2,
member_position IN NUMBER);
Parameters
Table 23-43 SET_AWCOMP_SPEC_MEMBER_POS Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
member_name |
Member of the composite specification. Either a dimension or a composite. |
member_position |
Position of the member within the composite specification. |
Example
The following statements create a composite specification for the ANALYTIC_CUBE in XADEMO. It includes two members: a time dimension called TIMECOMP_MEMBER and a composite called COMP1.
---- The logical members of the specification are:
--- <TIME COMP1<PRODUCT, GEOGRAPHY>.
---------------------------------------------------------
execute DBMS_AWM.Create_AWComp_spec
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'TIMECOMP_MEMBER' ,
'DIMENSION' ,'XADEMO' ,'TIME');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'COMP1' ,'COMPOSITE');
execute DBMS_AWM.Add_AWComp_Spec_Comp_Member
('AC_COMPSPEC','XADEMO','ANALYTIC_CUBE', 'COMP1','PROD_COMP',
'DIMENSION','XADEMO','PRODUCT');
execute DBMS_AWM.Add_AWComp_Spec_Comp_Member
('AC_COMPSPEC','XADEMO','ANALYTIC_CUBE', 'COMP1','GEOG_COMP',
'DIMENSION','XADEMO','GEOGRAPHY');
---- With the following statement, the logical members of the specification
---- are reordered as follows.
--- <COMP1<PRODUCT, GEOGRAPHY> TIME>.
---------------------------------------------------------
execute DBMS_AWM.Set_AWComp_Spec_Member_Pos
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'COMP1' ,1);
See Also
This procedure sets the segment size for a member of a composite specification. A member is either a dimension or a composite.
A segment is an internal buffer used by the OLAP engine for storing data. The size of segments affects the performance of data loads and queries against the data.
Syntax
SET_AWCOMP_SPEC_MEMBER_SEG (
composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
member_name IN VARCHAR2,
member_segwidth IN NUMBER DEFAULT NULL);
Parameters
Table 23-44 SET_AWCOMP_SPEC_MEMBER_SEG Procedure Parameters
| Parameter | Description |
|---|---|
composite_spec |
Name of a composite specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
member_name |
Name of the dimension or composite. |
member_segwidth |
Segment size associated with a dimension or composite. If you do not specify a segment size for a dimension, the value is the maximum size of the dimension (number of dimension members). If you do not specify a segment size for a composite, the value is 10 million. |
Example
The following statements set the segment size for the time dimension to zero (the default setting in the analytic workspace) and the segment size for the COMP1 composite to 10,000,000.
execute DBMS_AWM.Create_AWComp_spec
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'TIME_DIM' ,
'DIMENSION' ,'XADEMO' ,'time');
execute DBMS_AWM.Add_AWComp_Spec_Member
('AC_COMPSPEC' ,'XADEMO' ,'ANALYTIC_CUBE' ,'COMP1' ,'COMPOSITE');
execute DBMS_AWM.Add_AWComp_Spec_Comp_Member
('AC_COMPSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'COMP1', 'COMP1_PROD',
'DIMENSION', 'XADEMO', 'product');
execute DBMS_AWM.Add_AWComp_Spec_Comp_Member
('AC_COMPSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'COMP1', 'COMP1_GEOG',
'DIMENSION', 'XADEMO', 'geography');
execute DBMS_AWM.Set_AWComp_Spec_Member_Seg
('AC_COMPSPEC' , 'XADEMO', 'ANALYTIC_CUBE', 'TIME_DIM', 0);
execute DBMS_AWM.Set_AWComp_Spec_Member_Seg
('AC_COMPSPEC' , 'XADEMO', 'ANALYTIC_CUBE', 'COMP1', NULL);
See Also
In Oracle9i OLAP DML Reference help, search for "segment width"
This procedure renames a composite specification.
Syntax
SET_AWCOMP_SPEC_NAME (
old_composite_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
new_composite_spec IN VARCHAR2);
Parameters
Table 23-45 SET_AWCOMP_SPEC_NAME Procedure Parameters
| Parameter | Description |
|---|---|
old_composite_spec |
Old name of a composite specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
new_composite_spec |
New name of the composite specification. |
See Also
This procedure renames the relational views of an analytic workspace cube. The names are stored in the analytic workspace and instantiated when you generate and run new enablement scripts.
You can use this procedure to override the default view names established when the cube is refreshed by REFRESH_AWCUBE.
If the cube was refreshed by some other mechanism, such as the OLAP Analytic Workspace Java API, you must call REFRESH_AWCUBE_VIEW_NAME before calling this procedure.
Syntax
SET_AWCUBE_VIEW_NAME (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
hierarchy_combo_number IN NUMBER,
view_name IN VARCHAR2);
Parameters
Table 23-46 SET_AWCUBE_VIEW_NAME Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Analytic workspace owner. |
aw_name |
Analytic workspace name. |
aw_cube_name |
Analytic workspace cube name. |
hierarchy_combo_number |
Number of the hierarchy combination. |
view_name |
Name for the fact view for this hierarchy combination. |
Note
For details about enablement view names, see "Default Fact View Names".
See Also
This procedure sets the operator for aggregation along one of the dimensions in an aggregation specification.
You can specify any aggregation operator that can be used with the OLAP DML RELATION command. The default operator is addition (SUM). You can use this procedure to override the aggregation operator associated with the source cube in the OLAP Catalog.
|
Note: TheDBMS_AWM package currently does not support weighted aggregation operators. For example, if the OLAP Catalog specifies a weighted sum or weighted average for aggregation along one of the cube's dimensions, it is converted to the scalar equivalent (sum or average) in the analytic workspace. Weighted operators specified by SET_AWCUBEAGG_SPEC_AGGOP are similarly converted. |
Syntax
SET_AWCUBEAGG_SPEC_AGGOP (
aggregation_spec IN VARCHAR2,
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_cube_name IN VARCHAR2,
aw_measure_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
aggregation_operator IN VARCHAR2);
Parameters
Table 23-47 SET_AWCUBEAGG_SPEC_AGGOP Procedure Parameters
| Parameter | Description |
|---|---|
aggregation_spec |
Name of the aggregation specification in the analytic workspace. |
aw_owner |
Owner of the analytic workspace. |
aw_name |
Name of the analytic workspace. |
aw_cube_name |
Name of the target cube in the analytic workspace. |
| aw_measure_name | Name of a measure to aggregate. |
aw_dimension_name |
Name of a dimension of the cube. |
aggregation_operator |
Aggregation operator for aggregation along this dimension. See Table 1-10, "Aggregation Operators". |
Note
See "Choosing an Aggregation Method" for details on aggregation methods supported in the OLAP Catalog and in the analytic workspace.
See Also
RELATION command entry in Oracle9i OLAP DML Reference help
Chapter on Aggregation in Oracle OLAP DML Reference
This procedure associates a cube load specification with a different cube.
Syntax
SET_AWCUBELOAD_SPEC_CUBE (
cube_load_spec IN VARCHAR2,
old_cube_owner IN VARCHAR2,
old_cube_name IN VARCHAR2,
new_cube_owner IN VARCHAR2,
new_cube_name IN VARCHAR2);
Parameters
Table 23-48 SET_AWCUBELOAD_SPEC_CUBE Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
old_cube_owner |
Owner of the old OLAP Catalog source cube. |
old_cube_name |
Name of the old OLAP Catalog source cube. |
new_cube_owner |
Owner of the new OLAP Catalog source cube. |
new_cube_name |
Name of the new OLAP Catalog source cube. |
See Also
CREATE_AWCUBELOAD_SPEC Procedure
This procedure resets the load type for a cube load specification. The load type indicates how data will be loaded into the analytic workspace.
Syntax
SET_AWCUBELOAD_SPEC_LOADTYPE (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
load_type IN VARCHAR2);
Parameters
Table 23-49 SET_AWCUBELOAD_SPEC_LOADTYPE Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a load specification for a cube. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
load_type |
'LOAD_DATA' -- Load the data from the fact table into the analytic workspace target cube.
->show obj(property 'aw$loadprgs' 'my_awcube_name') |
See Also
CREATE_AWCUBELOAD_SPEC Procedure
This procedure renames a cube load specification.
Syntax
SET_AWCUBELOAD_SPEC_NAME (
old_cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
new_cube_load_spec IN VARCHAR2);
Parameters
Table 23-50 SET_AWCUBELOAD_SPEC_NAME Procedure Parameters
| Parameter | Description |
|---|---|
old_cube_load_spec |
Old name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
new_cube_load_spec |
New name of the cube load specification. |
See Also
CREATE_AWCUBELOAD_SPEC Procedure
This procedure sets parameters for a cube load specification.
Syntax
SET_AWCUBELOAD_SPEC_PARAMETER (
cube_load_spec IN VARCHAR2,
cube_owner IN VARCHAR2,
cube_name IN VARCHAR2,
parameter_name IN VARCHAR2,
parameter_value IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-51 SET_AWCUBELOAD_SPEC_PARAMETER Procedure Parameters
| Parameter | Description |
|---|---|
cube_load_spec |
Name of a cube load specification. |
cube_owner |
Owner of the OLAP Catalog source cube. |
cube_name |
Name of the OLAP Catalog source cube. |
parameter_name |
'DISPLAY_NAME' -- Whether to use the OLAP Catalog source cube name or the target cube display name as the display name for the target cube in the analytic workspace. |
parameter_value |
Value of DISPLAY_NAME is the display name for the target cube in the analytic workspace. If you do not specify this parameter, the display name for the source cube in the OLAP Catalog will be used as the display name for the target cube in the analytic workspace. |
Example
The following statement specifies a target cube display name for the AC_CUBELOADSPEC cube load specification.
execute dbms_awm.set_awcubeload_spec_parameter
('AC_CUBELOADSPEC', 'XADEMO', 'ANALYTIC_CUBE',
'DISPLAY_NAME', 'My AW Analytic Cube Display Name')
See Also
CREATE_AWCUBELOAD_SPEC Procedure
This procedure renames the relational views of an analytic workspace dimension. The names are stored in the analytic workspace and instantiated when you generate and run new enablement scripts.
You can use this procedure to override the default view names established when the dimension was refreshed by REFRESH_AWDIMENSION.
If the dimension was refreshed by some other mechanism, such as the OLAP Analytic Workspace Java API, you must call REFRESH_AWDIMENSION_VIEW_NAME before calling this procedure.
Syntax
SET_AWDIMENSION_VIEW_NAME (
aw_owner IN VARCHAR2,
aw_name IN VARCHAR2,
aw_dimension_name IN VARCHAR2,
hierarchy_name IN VARCHAR2,
view_name IN VARCHAR2);
Parameters
Table 23-52 SET_AWDIMENSION_VIEW_NAME Procedure Parameters
| Parameter | Description |
|---|---|
aw_owner |
Analytic workspace owner |
aw_name |
Analytic workspace name |
aw_dimension_name |
Analytic workspace dimension name |
hierarchy_name |
Analytic workspace hierarchy name |
view_name |
Name for the view of the dimension hierarchy. |
Note
For details about enablement view names, see "Default Dimension View Names".
See Also
This procedure associates a dimension load specification with a different dimension.
Syntax
SET_AWDIMLOAD_SPEC_DIMENSION (
dimension_load_spec IN VARCHAR2,
old_dimension_owner IN VARCHAR2,
old_dimension_name IN VARCHAR2,
new_dimension_owner IN VARCHAR2,
new_dimension_name IN VARCHAR2);
Parameters
Table 23-53 SET_AWDIMLOAD_SPEC_DIMENSION Procedure Parameters
| Parameter | Description |
|---|---|
dimension_load_spec |
Name of a dimension load specification. |
old_dimension_owner |
Owner of the old OLAP Catalog source dimension. |
old_dimension_name |
Name of the old OLAP Catalog source dimension. |
new_dimension_owner |
Owner of the new OLAP Catalog source dimension. |
new_dimension_name |
Name of the new OLAP Catalog source dimension. |
See Also
CREATE_AWDIMLOAD_SPEC Procedure
This procedure resets the load type for a dimension load specification. The load type indicates how dimension members will be loaded into the analytic workspace.
By default only new members are loaded when the dimension is refreshed.
Syntax
SET_AWDIMLOAD_SPEC_LOADTYPE (
dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
load_type IN VARCHAR2);
Parameters
Table 23-54 SET_AWDIMLOAD_SPEC_LOADTYPE Procedure Parameters
| Parameter | Description |
|---|---|
dimension_load_spec |
Name of a dimension load specification. |
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
load_type |
Specify one of the following:
|
See Also
CREATE_AWDIMLOAD_SPEC Procedure
This procedure renames a dimension load specification.
Syntax
SET_AWDIMLOAD_SPEC_NAME (
old_dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
new_dimension_load_spec IN VARCHAR2);
Parameters
Table 23-55 SET_AWDIMLOAD_SPEC_NAME Procedure Parameters
| Parameter | Description |
|---|---|
old_dimension_load_spec |
Old name of the dimension load specification. |
dimension_owner |
Owner of the OLAP Catalog source dimension. |
dimension_name |
Name of the OLAP Catalog source dimension. |
new_dimension_load_spec |
New name for the dimension load specification. |
See Also
CREATE_AWDIMLOAD_SPEC Procedure
This procedure sets parameters for a dimension load specification.
Syntax
SET_AWDIMLOAD_SPEC_PARAMETER (
dimension_load_spec IN VARCHAR2,
dimension_owner IN VARCHAR2,
dimension_name IN VARCHAR2,
parameter_name IN VARCHAR2,
parameter_value IN VARCHAR2 DEFAULT NULL);
Parameters
Table 23-56 SET_AWDIMLOAD_SPEC_PARAMETER Procedure Parameters
Example
The following statements set parameters for the product dimension in the load specification PROD_LOADSPEC. These parameters prevent level prefixes on dimension member names, and they specify a display name and plural display name for the target dimension.
execute dbms_awm.Set_AWDimLoad_Spec_Parameter
('PROD_LOADSPEC', 'XADEMO', 'PRODUCT', 'UNIQUE_RDBMS_KEY', 'YES')
execute dbms_awm.Set_AWDimLoad_Spec_Parameter
('PROD_LOADSPEC', 'XADEMO', 'PRODUCT', 'DISPLAY_NAME',
'My AW Product Display Name')
execute dbms_awm.Set_AWDimLoad_Spec_Parameter
('PROD_LOADSPEC', 'XADEMO', 'PRODUCT', 'P_DISPLAY_NAME',
'My AW Product Plural Display Name')
See Also
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|