23 DBMS_AWM

Summary of DBMS_AWM Subprograms

Table 23-2 lists the DBMS_AWM subprograms in alphabetical order. Each subprogram is described in detail further in this chapter.

To see the DBMS_AWM subprograms listed by function, refer to "Understanding the DBMS_AWM Procedures".

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

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

• "Managing Sparse Data and Optimizing the Workspace Cube"

• DELETE_AWCOMP_SPEC_MEMBER Procedure

• ADD_AWCOMP_SPEC_MEMBER Procedure

• CREATE_AWCOMP_SPEC Procedure

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

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

• "Managing Sparse Data and Optimizing the Workspace Cube"

• CREATE_AWCOMP_SPEC Procedure

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

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

• "Aggregating the Data in an Analytic Workspace"

• CREATE_AWCUBEAGG_SPEC Procedure

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

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

• "Aggregating the Data in an Analytic Workspace"

• CREATE_AWCUBEAGG_SPEC Procedure

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

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

• "Creating and Populating Workspace Cubes"

• CREATE_AWCUBELOAD_SPEC Procedure

• CREATE_AWCOMP_SPEC Procedure

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

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

• "Creating and Populating Workspace Cubes"

• CREATE_AWCUBELOAD_SPEC Procedure

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.

This procedure allows you to specify the measure name, display name, and description in the analytic workspace. If you do not specify the target names, or if you do not call this procedure at all, the source names from the OLAP Catalog are used.

Syntax

ADD_AWCUBELOAD_SPEC_MEASURE (
               cube_load_spec              IN   VARCHAR2,
               cube_owner                  IN   VARCHAR2,
               cube_name                   IN   VARCHAR2,
               measure_name                IN   VARCHAR2,
               aw_measure_name             IN   VARCHAR2 DEFAULT NULL,
               aw_measure_display_name     IN   VARCHAR2 DEFAULT NULL,
               aw_measure_description      IN   VARCHAR2 DEFAULT NULL);

Parameters

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

• CREATE_AWCUBELOAD_SPEC Procedure

• REFRESH_AWCUBE Procedure

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

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

• "Creating and Populating Workspace Dimensions"

• CREATE_AWDIMLOAD_SPEC Procedure

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

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

• "Aggregating the Data in an Analytic Workspace"

• "CREATE_AWCUBEAGG_SPEC Procedure"

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

Note

You can use the following procedures to modify an existing composite specification:

• SET_AWCOMP_SPEC_CUBE Procedure

• SET_AWCOMP_SPEC_MEMBER_NAME Procedure

• SET_AWCOMP_SPEC_MEMBER_POS Procedure

• SET_AWCOMP_SPEC_MEMBER_SEG Procedure

• SET_AWCOMP_SPEC_NAME Procedure

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

• "Managing Sparse Data and Optimizing the Workspace Cube"

• ADD_AWCOMP_SPEC_MEMBER Procedure

• ADD_AWCOMP_SPEC_COMP_MEMBER Procedure

• ADD_AWCUBELOAD_SPEC_COMP Procedure

• DEFINE COMPOSITE in the Oracle OLAP DML Reference

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, ensuring its compatibility with the OLAP API enablers and with other OLAP administrative tools and utilities.

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

select owner, cube_name         from all_olap2_cubes union all select aw_owner, aw_logical_name          from all_olap2_aw_cubes;

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

• " Creating and Refreshing a Workspace Cube"

• CREATE_AWDIMENSION Procedure

• REFRESH_AWCUBE Procedure

• CREATE_AWCUBE_ACCESS Procedure

• Chapter 3, " Active Catalog Views "

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 the embedded total format required by the OLAP API.

The script can optionally generate OLAP Catalog metadata that maps to the views of the workspace cube. This metadata is required for the OLAP API.

Both dimension views and fact views are required for relational access to the workspace cube. Use the CREATE_AWDIMENSION_ACCESS procedure to generate the scripts that create the dimension views.

To accomplish the cube enablement process in a single step, use the CREATE_AWCUBE_ACCESS_FULL procedure. This procedure both creates and runs the enablement script.

Syntax

CREATE_AWCUBE_ACCESS (
               aw_owner             IN   VARCHAR2,
               aw_name              IN   VARCHAR2,
               aw_cube_name         IN   VARCHAR2,
               access_type          IN   VARCHAR2,
               script_directory     IN   VARCHAR2,
               script_name          IN   VARCHAR2,
               open_mode            IN   VARCHAR2);

Parameters

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

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWCUBE_ACCESS_FULL Procedure"

• "DELETE_AWCUBE_ACCESS Procedure"

• "SET_AWCUBE_VIEW_NAME Procedure"

• "CREATE_AWDIMENSION_ACCESS Procedure"

• "REFRESH_AWCUBE Procedure"

• Chapter 26, "OLAP_TABLE"

CREATE_AWCUBE_ACCESS_FULL Procedure

This procedure accomplishes the entire process of enabling a workspace cube for access by the OLAP API. Like CREATE_AWCUBE_ACCESS it produces an enablement script. However it does not write the script to a file. Instead it writes the script to temporary memory and runs the script.

The resulting views and metadata are identical to those created by the enablement scripts produced by CREATE_AWCUBE_ACCESS.

Syntax

CREATE_AWCUBE_ACCESS_FULL (
               run_id           IN   NUMBER,
               aw_owner         IN   VARCHAR2,
               aw_name          IN   VARCHAR2,
               aw_cube_name     IN   VARCHAR2,
               access_type      IN   VARCHAR2);

Parameters

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWCUBE_ACCESS Procedure"

• "REFRESH_AWCUBE Procedure"

• Chapter 26, "OLAP_TABLE"

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

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

• "Aggregating the Data in an Analytic Workspace"

• ADD_AWCUBEAGG_SPEC_LEVEL Procedure

• ADD_AWCUBEAGG_SPEC_MEASURE Procedure

• "AGGREGATE_AWCUBE Procedure"

• AGGREGATE Command in the Oracle OLAP DML Reference

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

->show obj(property 'aw$loadprgs' 'my_awcube_name')

Note

You can use the following procedures to modify an existing cube load specification:

• SET_AWCUBELOAD_SPEC_CUBE Procedure

• SET_AWCUBELOAD_SPEC_LOADTYPE Procedure

• SET_AWCUBELOAD_SPEC_NAME Procedure

• SET_AWCUBELOAD_SPEC_PARAMETER Procedure

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

• "Creating and Populating Workspace Cubes"

• ADD_AWCUBELOAD_SPEC_COMP Procedure

• REFRESH_AWCUBE Procedure

CREATE_AWDIMENSION Procedure

This procedure creates the multidimensional framework within an analytic workspace to hold a relational dimension.

The relational dimension, consisting of dimension lookup tables and OLAP Catalog metadata, is the source for the target dimension in the analytic workspace. Data and metadata are loaded from the source dimension to the target dimension by the REFRESH_AWDIMENSION procedure.

CREATE_AWDIMENSION executes an OLAP DML UPDATE command to save the changes in the analytic workspace. CREATE_AWDIMENSION does not execute a SQL COMMIT.

The multidimensional framework for the dimension is in database standard form, ensuring its compatibility with the OLAP API enablers and with other OLAP administrative tools and utilities.

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

select owner, dimension_name         from all_olap2_dimensions union all select aw_owner, aw_logical_name         from all_olap2_aw_dimensions;

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

• "Creating and Refreshing a Workspace Dimension"

• REFRESH_AWDIMENSION Procedure

• CREATE_AWDIMENSION_ACCESS Procedure

• CREATE_AWCUBE Procedure

• Chapter 3, " Active Catalog Views "

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 required by the OLAP API.

The script can optionally generate OLAP Catalog metadata that maps to the views of the workspace dimension. This metadata is required for the OLAP API.

Both fact views and dimension views are required for relational access to a workspace cube. Use the CREATE_AWCUBE_ACCESS procedure to generate the scripts that create the fact views.

To accomplish the enablement process in a single step, use the CREATE_AWDIMENSION_ACCESS_FULL procedure. This procedure both creates and runs the enablement script.

Syntax

CREATE_AWDIMENSION_ACCESS (
               aw_owner             IN   VARCHAR2,
               aw_name              IN   VARCHAR2,
               aw_dimension_name    IN   VARCHAR2,
               access_type          IN   VARCHAR2,
               script_directory     IN   VARCHAR2,
               script_name          IN   VARCHAR2,
               open_mode            IN   VARCHAR2);

Parameters

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

• "Enabling Relational Access to the Workspace Cube"

• "DELETE_AWDIMENSION_ACCESS Procedure"

• "SET_AWDIMENSION_VIEW_NAME Procedure"

• Chapter 26, "OLAP_TABLE"

CREATE_AWDIMENSION_ACCESS_FULL Procedure

This procedure accomplishes the entire process of enabling a workspace dimension for access by the OLAP API. Like CREATE_AWDIMENSION_ACCESS it produces an enablement script. However it does not write the script to a file. Instead it writes the script to temporary memory and runs the script.

The resulting views and metadata are identical to those created by the enablement scripts created by CREATE_AWDIMENSION_ACCESS.

Syntax

CREATE_AWDIMENSION_ACCESS_FULL (
               run_id                IN   NUMBER,
               aw_owner              IN   VARCHAR2,
               aw_name               IN   VARCHAR2,
               aw_dimension_name     IN   VARCHAR2,
               access_type           IN   VARCHAR2);

Parameters

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWDIMENSION_ACCESS Procedure"

• "REFRESH_AWDIMENSION Procedure"

• Chapter 26, "OLAP_TABLE"

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

Note

You can use the following procedures to modify an existing dimension load specification:

• SET_AWDIMLOAD_SPEC_DIMENSION Procedure

• SET_AWDIMLOAD_SPEC_LOADTYPE Procedure

• SET_AWDIMLOAD_SPEC_NAME Procedure

• SET_AWDIMLOAD_SPEC_PARAMETER Procedure

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

• "Creating and Populating Workspace Dimensions"

• REFRESH_AWDIMENSION Procedure

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

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

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

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWCUBE_ACCESS Procedure"

• "CREATE_AWCUBE_ACCESS_FULL Procedure"

• "SET_AWCUBE_VIEW_NAME Procedure"

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

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWCUBE_ACCESS_FULL Procedure"

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

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

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

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

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

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

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

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

See Also

• "CREATE_AWDIMENSION_ACCESS Procedure"

• "CREATE_AWCUBE_ACCESS_FULL Procedure"

• "SET_AWDIMENSION_VIEW_NAME Procedure"

• "Creating and Refreshing a Workspace Dimension"

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

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWDIMENSION_ACCESS_FULL Procedure"

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

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

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

Note

All the OLAP Catalog metadata that defines the logical cube, including its dimensionality, measures, and descriptions, is refreshed whenever you refresh the workspace cube. The cube's data is refreshed according to the load specification. For more information, see "Refreshing the Cube's Metadata"

For information about the relationship between the refresh and enablement processes, see "Enablement Metadata in the Analytic Workspace".

For information about the relationship between the refresh and aggregation processes, see "Aggregating the Data in an Analytic Workspace".

Example

The following statements create the target cube AW_ANACUBE from the source cube XADEMO.ANALYTIC_CUBE. They refresh all of target cube's dimensions, then they create a load specification and refresh the target cube's data.

-- create cube, cube load spec, and refresh 
execute dbms_awm.create_awcube
          ('XADEMO', 'ANALYTIC_CUBE','MYSCHEMA', 'MYAW','AW_ANACUBE');
execute dbms_awm.create_awcubeload_spec
          ('AC_CUBELOADSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA')
execute dbms_awm.refresh_awdimension 
          ('MYSCHEMA', 'MYAW', 'AW_CHAN');
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_PROD');
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_GEOG');
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_TIME');
execute dbms_awm.refresh_awcube
          ('MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AC_CUBELOADSPEC')

See Also

• " Creating and Refreshing a Workspace Cube"

• "CREATE_AWCUBE Procedure"

• "REFRESH_AWCUBE Procedure"

• "CREATE_AWCOMP_SPEC Procedure"

• "CREATE_AWCUBE_ACCESS Procedure"

REFRESH_AWCUBE_VIEW_NAME Procedure

This procedure creates metadata in the analytic workspace to support user-defined names for the enablement views. This procedure is not used for cubes created and maintained by the DBMS_AWM package.

This procedure is required if you want to specify your own names for the enablement views in analytic workspaces that were not created by DBMS_AWM. For example, if you used the OLAP Analytic Workspace Java API to refresh the cube, you must call this procedure before calling SET_AWCUBE_VIEW_NAME.

Syntax

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

Parameters

Note

For details about enablement view names, see "Default Fact View Names".

See Also

• "Creating Relational Access to the Workspace Cube"

• "SET_AWCUBE_VIEW_NAME Procedure"

REFRESH_AWDIMENSION Procedure

This procedure loads data and metadata from an OLAP Catalog source dimension into a target dimension in an analytic workspace.

REFRESH_AWDIMENSION executes an OLAP DML UPDATE command to save the changes in the analytic workspace. REFRESH_AWDIMENSION does not execute a SQL COMMIT.

You can include a dimension load specification to determine how the dimension's members will be refreshed in the workspace. If you do not include a load specification, all dimension members are selected for loading, but only new members are actually added to the target dimension.

You can select individual dimension members to load from the source tables by specifying a filter condition (a WHERE clause on the dimension table).

Before the first call to REFRESH_AWCUBE, you must call REFRESH_AWDIMENSION for each of the cube's dimensions. On all subsequent cube refreshes, you only need to call REFRESH_AWDIMENSION if changes have been made to the source dimensions, for example if new time periods have been added to a time dimension.

Syntax

REFRESH_AWDIMENSION (
               aw_owner                IN   VARCHAR2,
               aw_name                 IN   VARCHAR2,
               aw_dimension_name       IN   VARCHAR2,
               dimension_load_spec     IN   VARCHAR2 DEFAULT NULL);

Parameters

Note

All the OLAP Catalog metadata that defines the logical dimension, including its levels, hierarchies, attributes, and descriptions, is refreshed whenever you refresh the workspace dimension. The dimension's data is refreshed according to the load specification. For more information, see "Refreshing the Dimension's Metadata"

For information about the relationship between the refresh and enablement processes, see "Enablement Metadata in the Analytic Workspace".

Example

The following statements refresh the dimensions of the XADEMO.ANALYTIC_CUBE source cube in the analytic workspace MYSCHEMA.MYAW.

-- Create dimension load specs and refresh dimensions

-- CHANNEL dimension
execute dbms_awm.create_awdimload_spec
          ('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'FULL_LOAD');
execute dbms_awm.add_awdimload_spec_filter 
          ('CHAN_DIMLOADSPEC', 'XADEMO', 'CHANNEL', 'XADEMO',
          'XADEMO_CHANNEL', '''CHAN_STD_CHANNEL'' = ''DIRECT''' );
execute dbms_awm.refresh_awdimension 
          ('MYSCHEMA', 'MYAW', 'AW_CHAN', 'CHAN_DIMLOADSPEC');
          
-- PRODUCT dimension
execute dbms_awm.create_awdimload_spec
          ('PROD_DIMLOADSPEC', 'XADEMO', 'PRODUCT', 'FULL_LOAD');
execute dbms_awm.Set_AWDimLoad_Spec_Parameter
          ('PROD_DIMLOADSPEC', 'XADEMO', 'PRODUCT', 'UNIQUE_RDBMS_KEY', 'YES');
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_PROD', 'PROD_DIMLOADSPEC');

-- GEOGRAPHY dimension
execute dbms_awm.create_awdimload_spec
          ('GEOG_DIMLOADSPEC', 'XADEMO', 'GEOGRAPHY', 'FULL_LOAD');
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_GEOG', 'GEOG_DIMLOADSPEC');

-- TIME dimension
execute dbms_awm.create_awdimload_spec
          ('TIME_DIMLOADSPEC', 'XADEMO', 'TIME', 'FULL_LOAD');
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_TIME', 'TIME_DIMLOADSPEC');

See Also

• "Creating and Refreshing a Workspace Dimension"

• CREATE_AWDIMENSION Procedure

• "CREATE_AWDIMLOAD_SPEC Procedure"

• "CREATE_AWDIMENSION_ACCESS Procedure"

REFRESH_AWDIMENSION_VIEW_NAME Procedure

This procedure creates metadata in the analytic workspace to support user-defined names for the enablement views. This procedure is not needed for dimensions created and maintained by the DBMS_AWM package.

This procedure is required if you want to specify your own names for the enablement views in analytic workspaces that were not created by DBMS_AWM. For example, if you used the OLAP Analytic Workspace Java API to refresh the dimension, you must call this procedure before calling SET_AWDIMENSION_VIEW_NAME.

Syntax

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

Parameters

Note

For details about enablement view names, see "Default Dimension View Names".

See Also

• "Creating Relational Access to the Workspace Cube"

• "SET_AWDIMENSION_VIEW_NAME Procedure"

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

See Also

• "Managing Sparse Data and Optimizing the Workspace Cube"

• CREATE_AWCOMP_SPEC Procedure

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

See Also

• "Managing Sparse Data and Optimizing the Workspace Cube"

• CREATE_AWCOMP_SPEC Procedure

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

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

• "Managing Sparse Data and Optimizing the Workspace Cube"

• CREATE_AWCOMP_SPEC Procedure

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

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

• "Managing Sparse Data and Optimizing the Workspace Cube"

• In Oracle9i OLAP DML Reference help, search for "segment width"

• CREATE_AWCOMP_SPEC Procedure

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

See Also

• "Managing Sparse Data and Optimizing the Workspace Cube"

• CREATE_AWCOMP_SPEC Procedure

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.

You can use this procedure to override the default view names established when the cube is refreshed by REFRESH_AWCUBE.

If the cube was refreshed by some other mechanism, such as the OLAP Analytic Workspace Java API, you must call REFRESH_AWCUBE_VIEW_NAME before calling this procedure.

Syntax

SET_AWCUBE_VIEW_NAME (
               aw_owner                   IN   VARCHAR2,
               aw_name                    IN   VARCHAR2,
               aw_cube_name               IN   VARCHAR2,
               hierarchy_combo_number     IN   NUMBER,
               view_name                  IN   VARCHAR2);

Parameters

Note

For details about enablement view names, see "Default Fact View Names".

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWCUBE_ACCESS Procedure"

• "DELETE_AWCUBE_ACCESS Procedure"

• "REFRESH_AWCUBE_VIEW_NAME Procedure"

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.

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

Note

See "Choosing an Aggregation Method" for details on aggregation methods supported in the OLAP Catalog and in the analytic workspace.

See Also

• "Aggregating the Data in an Analytic Workspace"

• CREATE_AWCUBEAGG_SPEC Procedure

• RELATION command entry in Oracle9i OLAP DML Reference help

• Chapter on Aggregation in Oracle OLAP DML Reference

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

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

->show obj(property 'aw$loadprgs' 'my_awcube_name')

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

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

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.

You can use this procedure to override the default view names established when the dimension was refreshed by REFRESH_AWDIMENSION.

If the dimension was refreshed by some other mechanism, such as the OLAP Analytic Workspace Java API, you must call REFRESH_AWDIMENSION_VIEW_NAME before calling this procedure.

Syntax

SET_AWDIMENSION_VIEW_NAME (
               aw_owner              IN   VARCHAR2,
               aw_name               IN   VARCHAR2,
               aw_dimension_name     IN   VARCHAR2,
               hierarchy_name        IN   VARCHAR2,
               view_name             IN   VARCHAR2);

Parameters

Note

For details about enablement view names, see "Default Dimension View Names".

See Also

• "Creating Relational Access to the Workspace Cube"

• "CREATE_AWDIMENSION_ACCESS Procedure"

• "DELETE_AWDIMENSION_ACCESS Procedure"

• "REFRESH_AWDIMENSION_VIEW_NAME Procedure"

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

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

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

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

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