Skip Headers
Oracle® OLAP Reference
10g Release 2 (10.2)

Part Number B14350-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Parameters of DBMS_AWM Subprograms

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 26-1.

Table 26-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 26-13, "CREATE_AWCUBE Procedure Parameters".

aw_dimension_name

Name of the target dimension within an analytic workspace. For information on naming requirements, see Table 26-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.



Summary of DBMS_AWM Subprograms

Table 26-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 26-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.

UPGRADE_AW_TO_10_2 Procedure


Converts an analytic workspace from 10.1.0.4 to 10.2 format.

CREATE_AWDIMLOAD_SPEC Procedure


Creates a load specification for a dimension.

CREATE_DYNAMIC_AW_ACCESS Procedure


Upgrades standard form metadata to the current release, which supports queries from the OLAP API without the need for relational views.

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 for a cube.

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 for a dimension.

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.



ADD_AWCOMP_SPEC_COMP_MEMBER Procedure

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 26-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


ADD_AWCOMP_SPEC_MEMBER Procedure

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 26-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


ADD_AWCUBEAGG_SPEC_LEVEL Procedure

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 26-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


ADD_AWCUBEAGG_SPEC_MEASURE Procedure

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 26-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


ADD_AWCUBELOAD_SPEC_COMP Procedure

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 26-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


ADD_AWCUBELOAD_SPEC_FILTER Procedure

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 26-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


ADD_AWCUBELOAD_SPEC_MEASURE Procedure

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.

You can use this procedure to specify the target name of the measure, its display name, and its 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 26-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


ADD_AWDIMLOAD_SPEC_FILTER Procedure

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 26-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


AGGREGATE_AWCUBE Procedure

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 26-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


CREATE_AWCOMP_SPEC Procedure

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 26-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


CREATE_AWCUBE Procedure

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.


Note:

Before executing CREATE_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 26-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


CREATE_AWCUBE_ACCESS Procedure

This procedure generates a script that creates relational fact views of a cube in an analytic workspace. The views are in embedded total format. The script can optionally generate OLAP Catalog metadata that maps to the views of the workspace cube.

Relational views enable applications to query an analytic workspace using standard SQL. Relational views are not used by 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,
               caller               IN   VARCHAR2   DEFAULT NULL,
               spreadsheet_mode     IN   VARCHAR2   DEFAULT 'YES',
               auto_adt_mode        IN   VARCHAR2   DEFAULT 'NO');

Parameters

Table 26-14 CREATE_AWCUBE_ACCESS 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 in the analytic workspace.

access_type

Controls whether or not the script generates OLAP Catalog metadata for the views. Specify one of the following values:

  • 'SQL' does not generate metadata.

  • 'OLAP' generates metadata

script_directory

The directory that will contain the script. This may be either a directory object or a path set by the UTL_FILE_DIR parameter.

script_name

Name of the script file.

open_mode

One of the following modes for opening the script file:

  • 'W' overwrites any existing contents of the script file

  • 'A' appends the new script to the existing contents of the script file.

caller

This parameter was used in earlier releases to identify the caller of the procedure. It is not used in the current release. By default, this parameter is null. It also accepts the value, 'EXTERNAL'.

spreadsheet_mode

Whether or not to use a MODEL clause in the SELECT FROM OLAP_TABLE statement in the view definition. A SQL MODEL significantly improves the performance of queries that use OLAP_TABLE. By default, a MODEL clause is used. See Chapter 34, "OLAP_TABLE".

auto_adt_mode

Whether or not the abstract data types used by OLAP_TABLE are automatically generated at runtime. By default, the abstract data types are predefined and are not automatically generated by OLAP_TABLE. See Chapter 34, "OLAP_TABLE".


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


CREATE_AWCUBE_ACCESS_FULL Procedure

This procedure accomplishes the entire process of enabling a workspace cube for relational access. 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.

Relational views enable applications to query an analytic workspace using standard SQL. Relational views are not used by the OLAP API.

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,
               spreadsheet_mode     IN   VARCHAR2   DEFAULT 'YES',
               auto_adt_mode        IN   VARCHAR2   DEFAULT 'NO');

Parameters

Table 26-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:

  • 'SQL' does not generate metadata

  • 'OLAP' generates metadata

spreadsheet_mode

Whether or not to use a MODEL clause in the SELECT FROM OLAP_TABLE statement in the view definition. A SQL MODEL significantly improves the performance of queries that use OLAP_TABLE. By default, a MODEL clause is used. See Chapter 34, "OLAP_TABLE".

auto_adt_mode

Whether or not the abstract data types used by OLAP_TABLE are automatically generated at runtime. By default, the abstract data types are predefined and are not automatically generated by OLAP_TABLE. See Chapter 34, "OLAP_TABLE".


See Also


CREATE_AWCUBEAGG_SPEC Procedure

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 26-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


CREATE_AWCUBELOAD_SPEC Procedure

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 26-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 and metadata for an OLAP Catalog cube into the analytic workspace target cube.

'LOAD_PROGRAM' -- This argument is no longer used.


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


CREATE_AWDIMENSION Procedure

CREATE_AWDIMENSION uses a source dimension in the OLAP Catalog to create the standard form metadata for a target dimension in an analytic workspace. The dimension members and attribute values are loaded 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.


Note:

Before executing CREATE_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 CREATE_AWDIMENSION.


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 26-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


CREATE_AWDIMENSION_ACCESS Procedure

This procedure generates a script that creates relational views of a dimension in an analytic workspace. The views are in the embedded total format. The script can optionally generate OLAP Catalog metadata that maps to the views of the workspace dimension.

Relational views enable applications to query an analytic workspace using standard SQL. Relational views are not used by 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 dimension 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,
               caller               IN   VARCHAR2   DEFAULT NULL,
               spreadsheet_mode     IN   VARCHAR2   DEFAULT 'YES',
               auto_adt_mode        IN   VARCHAR2   DEFAULT 'NO');

Parameters

Table 26-19 CREATE_AWDIMENSION_ACCESS Procedure Parameters

Parameter Description

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 the script generates OLAP Catalog metadata for the views. Specify one of the following values:

  • 'SQL' does not generate metadata.

  • 'OLAP' generates metadata

script_directory

The directory that will contain the script. This may be either a directory object or a path set by the UTL_FILE_DIR parameter.

script_name

Name of the script file.

open_mode

One of the following modes for opening the script file:

  • 'W' overwrites any existing contents of the script file

  • 'A' appends the new script to the existing contents of the script file.

caller

This parameter was used in earlier releases to identify the caller of the procedure. It is not used in the current release. By default, this parameter is null. It also accepts the value, 'EXTERNAL'.

spreadsheet_mode

Whether or not to use a MODEL clause in the SELECT FROM OLAP_TABLE statement in the view definition. A SQL MODEL significantly improves the performance of queries that use OLAP_TABLE. By default, a MODEL clause is used. See Chapter 34, "OLAP_TABLE".

auto_adt_mode

Whether or not the abstract data types used by OLAP_TABLE are automatically generated at runtime. By default, the abstract data types are predefined and are not automatically generated by OLAP_TABLE. See Chapter 34, "OLAP_TABLE".


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


CREATE_AWDIMENSION_ACCESS_FULL Procedure

This procedure accomplishes the entire process of enabling a workspace dimension for relational access. 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.

Relational views enable applications to query an analytic workspace using standard SQL. Relational views are not used by the OLAP API.

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,
               spreadsheet_mode      IN   VARCHAR2   DEFAULT 'YES',
               auto_adt_mode         IN   VARCHAR2   DEFAULT 'NO');

Parameters

Table 26-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:

  • 'SQL' does not generate metadata

  • 'OLAP' generates metadata

spreadsheet_mode

Whether or not to use a MODEL clause in the SELECT FROM OLAP_TABLE statement in the view definition. A SQL MODEL significantly improves the performance of queries that use OLAP_TABLE. By default, a MODEL clause is used. See Chapter 34, "OLAP_TABLE".

auto_adt_mode

Whether or not the abstract data types used by OLAP_TABLE are automatically generated at runtime. By default, the abstract data types are predefined and are not automatically generated by OLAP_TABLE. See Chapter 34, "OLAP_TABLE".


See Also


CREATE_AWDIMLOAD_SPEC Procedure

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 26-21 CREATE_AWDIMLOAD_SPEC Procedure Parameters

Parameter Description

dimension_load_spec

Name of the load specification.

You can use the SET_AWDIMLOAD_SPEC_NAME procedure to alter the name.

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:

'FULL_LOAD_ADDITIONS_ONLY' -- Only new dimension members will be loaded when the dimension is refreshed. (Default)

'FULL_LOAD' -- All dimension members in the workspace will be deleted, then all the members of the source dimension will be loaded.


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


CREATE_DYNAMIC_AW_ACCESS Procedure

This procedure upgrades standard form metadata created in a previous release of the Oracle Database to the standard form used in the current release. The workspace must already be in 10g storage format before the metadata can be upgraded.

Current standard form metadata supports direct queries by the OLAP API without the need for relational views, abstract data types, or OLAP Catalog metadata.

If you do not call CREATE_DYNAMIC_AW_ACCESS, the first DBMS_AWM procedure that you call will attempt to upgrade the metadata.

Syntax

CREATE_DYNAMIC_AW_ACCESS (
               aw_owner         IN   VARCHAR2,
               aw_name          IN   VARCHAR2);

Parameters

Table 26-22 CREATE_DYNAMIC_AW_ACCESS Procedure Parameters

Parameter Description

aw_owner

Owner of the analytic workspace.

aw_name

Name of the analytic workspace.


Example

The following statement upgrades the standard form metadata in the GLOBAL_AW analytic workspace in the GLOBAL schema.

execute dbms_awm.create_dynamic_aw_access('global', 'global_aw');

See Also


DELETE_AWCOMP_SPEC Procedure

This procedure deletes a composite specification.

Syntax

DELETE_AWCOMP_SPEC (
               composite_spec     IN   VARCHAR2,
               cube_owner         IN   VARCHAR2,
               cube_name          IN   VARCHAR2);

Parameters

Table 26-23 DELETE_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.


See Also

CREATE_AWCOMP_SPEC Procedure


DELETE_AWCOMP_SPEC_MEMBER Procedure

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 26-24 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


DELETE_AWCUBE_ACCESS 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 26-25 DELETE_AWCUBE_ACCESS 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 in the analytic workspace.

access_type

Specifies whether or not OLAP Catalog metadata exists for the views:

  • 'SQL' No metadata exists.

  • 'OLAP' OLAP Catalog metadata exists

script_directory

The directory that will contain the script. This may be either a directory object or a path set by the UTL_FILE_DIR parameter.

script_name

Name of the script file.

open_mode

One of the following modes for opening the script file:

  • 'W' overwrites any existing contents of the script file

  • 'A' appends the new script to the existing contents of the script file.


See Also


DELETE_AWCUBE_ACCESS_ALL Procedure

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 26-26 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:

  • 'SQL' does not generate metadata

  • 'OLAP' generates metadata


See Also


DELETE_AWCUBEAGG_SPEC Procedure

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 26-27 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


DELETE_AWCUBEAGG_SPEC_LEVEL 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 26-28 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


DELETE_AWCUBEAGG_SPEC_MEASURE 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 26-29 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


DELETE_AWCUBELOAD_SPEC 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 26-30 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


DELETE_AWCUBELOAD_SPEC_COMP 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 26-31 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


DELETE_AWCUBELOAD_SPEC_FILTER 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 26-32 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


DELETE_AWCUBELOAD_SPEC_MEASURE 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 26-33 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"


DELETE_AWDIMENSION_ACCESS 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 26-34 DELETE_AWDIMENSION_ACCESS Procedure Parameters

Parameter Description

aw_owner

Analytic workspace owner

aw_name

Analytic workspace name

aw_dimension_name

Analytic workspace dimension name.

access_type

Specifies whether or not OLAP Catalog metadata exists for the views:

  • 'SQL' No metadata exists.

  • 'OLAP' OLAP Catalog metadata exists

script_directory

The directory that will contain the script. This may be either a directory object or a path set by the UTL_FILE_DIR parameter.

script_name

Name of the script file.

open_mode

One of the following modes for opening the script file:

  • 'W' overwrites any existing contents of the script file

  • 'A' appends the new script to the existing contents of the script file.


See Also


DELETE_AWDIMENSION_ACCESS_ALL Procedure

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 26-35 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:

  • 'SQL' does not generate metadata

  • 'OLAP' generates metadata


See Also


DELETE_AWDIMLOAD_SPEC Procedure

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 26-36 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


DELETE_AWDIMLOAD_SPEC_FILTER 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 26-37 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


REFRESH_AWCUBE 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 26-38 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 "Creating and Refreshing a Workspace Cube"

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


REFRESH_AWCUBE_VIEW_NAME Procedure

This procedure creates metadata in the analytic workspace to support user-defined names for the enablement views of a cube. Call SET_AWCUBE_VIEW_NAME to specify the view names.

Syntax

REFRESH_AWCUBE_VIEW_NAME (
               aw_owner              IN   VARCHAR2,
               aw_name               IN   VARCHAR2,
               aw_cube_name          IN   VARCHAR2);

Parameters

Table 26-39 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 "Specifying Names for Fact Views".

See Also


REFRESH_AWDIMENSION Procedure

This procedure loads the dimension members and attribute values 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 26-40 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 "Creating and Refreshing a Workspace Dimension"

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


REFRESH_AWDIMENSION_VIEW_NAME Procedure

This procedure creates metadata in the analytic workspace to support user-defined names for the enablement views of a cube. Call SET_AWDIMENSION_VIEW_NAME to specify the view names.

Syntax

REFRESH_AWDIMENSION_VIEW_NAME (
               aw_owner                   IN   VARCHAR2,
               aw_name                    IN   VARCHAR2,
               aw_dimension_name          IN   VARCHAR2);

Parameters

Table 26-41 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 "Specifying Names for Dimension Views".

See Also


SET_AWCOMP_SPEC_CUBE Procedure

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 26-42 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


SET_AWCOMP_SPEC_MEMBER_NAME Procedure

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 26-43 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


SET_AWCOMP_SPEC_MEMBER_POS Procedure

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 26-44 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


SET_AWCOMP_SPEC_MEMBER_SEG Procedure

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 26-45 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


SET_AWCOMP_SPEC_NAME Procedure

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 26-46 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


SET_AWCUBE_VIEW_NAME Procedure

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.

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 26-47 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 "Specifying Names for Fact Views".

See Also


SET_AWCUBEAGG_SPEC_AGGOP Procedure

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:

The DBMS_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 26-48 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 "Aggregating the Data in an Analytic Workspace" for details on aggregation methods supported in the OLAP Catalog and in the analytic workspace.

See Also


SET_AWCUBELOAD_SPEC_CUBE Procedure

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 26-49 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


SET_AWCUBELOAD_SPEC_LOADTYPE 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 26-50 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 and metadata for an OLAP Catalog cube into the analytic workspace target cube.

'LOAD_PROGRAM' -- This argument is no longer used.


See Also

CREATE_AWCUBELOAD_SPEC Procedure


SET_AWCUBELOAD_SPEC_NAME 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 26-51 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


SET_AWCUBELOAD_SPEC_PARAMETER 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 26-52 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


SET_AWDIMENSION_VIEW_NAME 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.

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 26-53 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 "Specifying Names for Dimension Views".

See Also


SET_AWDIMLOAD_SPEC_DIMENSION Procedure

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 26-54 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


SET_AWDIMLOAD_SPEC_LOADTYPE 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 26-55 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:

'FULL_LOAD_ADDITIONS_ONLY' -- Only new dimension members will be loaded when the dimension is refreshed. (Default)

'FULL_LOAD' -- When the dimension is refreshed, all dimension members in the workspace will be deleted, then all the members of the source dimension will be loaded.


See Also

CREATE_AWDIMLOAD_SPEC Procedure


SET_AWDIMLOAD_SPEC_NAME 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 26-56 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


SET_AWDIMLOAD_SPEC_PARAMETER 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 26-57 SET_AWDIMLOAD_SPEC_PARAMETER 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.

parameter_name

One of the following:

'UNIQUE_RDBMS_KEY' -- Whether or not the members of this dimension are unique across all levels in the source tables.

'DISPLAY_NAME' -- Display name for the target dimension in the analytic workspace.

'P_DISPLAY_NAME' -- Plural display name for the target dimension in the analytic workspace.

parameter_value

Values of UNIQUE_RDBMS_KEY can be either 'YES' or 'NO'. The default is 'NO'. NO -- Dimension member names are not unique across levels in the RDBMS tables. The corresponding dimension member names in the analytic workspace include the level name as a prefix. (Default) YES -- Dimension member names are unique across levels in the RDBMS tables. The corresponding dimension member names in the analytic workspace have the same names as in the source relational dimension.

Value of DISPLAY_NAME is the display name for the target dimension in the analytic workspace. If you do not specify this parameter, the display name for the source dimension in the OLAP Catalog will be used as the display name for the target dimension in the analytic workspace.

Value of P_DISPLAY_NAME is the plural display name for the target dimension in the analytic workspace. If you do not specify this parameter, the plural display name for the source dimension in the OLAP Catalog will be used as the plural display name for the target dimension in the analytic workspace.


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

CREATE_AWDIMLOAD_SPEC Procedure


UPGRADE_AW_TO_10_2 Procedure

This procedure upgrades an analytic workspace from 10.1.0.4 to 10.2. It first converts the database format, if necessary, then converts the standard form metadata.

Syntax

UPGRADE_AW_TO_10_2 (
               aw_owner     IN   VARCHAR2,
               aw_name                 IN   VARCHAR2;

Parameters

Table 26-58 UPGRADE_AW_TO_10_2 Parameters

Parameter Description

aw_owner

Owner of the analytic workspace.

aw_name

Name of the analytic workspace.


Example

The following SQL command upgrades an analytic workspace named GLOBAL, owned by GLOBAL_AW, from 10.1.0.2 to 10.2.

execute dbms_awm.upgrade_aw_to_10_2('global_aw', 'global');