1 Creating Analytic Workspaces with DBMS_AWM

1.1.1 Creating OLAP Catalog Metadata for the Source Cube

Before you can use the DBMS_AWM procedures, you must create a cube in the OLAP Catalog and map it to the source fact table and dimension tables. The source tables must be organized in a basic star or snowflake schema.

You can use Enterprise Manager, or you can write scripts that use the CWM2 PL/SQL packages, as described in Chapter 2. You can also use Oracle Warehouse Builder to create OLAP Catalog metadata.

This cube is the Relational Source Cube identified in Figure 1-1.

1.1.2 Creating and Populating Workspace Dimensions

For each dimension of a cube defined in the OLAP Catalog, you must run a set of procedures in the DBMS_AWM package to accomplish the following general tasks:

1. Create a dimension load specification, which contains instructions for populating the dimension in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source dimension tables.

2. Create containers for the dimension in an analytic workspace.

3. Use the dimension load specification to populate the dimension in the analytic workspace from the source dimension tables.

1.1.3 Creating and Populating Workspace Cubes

After creating the cube's dimensions, run another set of procedures to create and populate the cube itself.

1. Create a cube load specification, which contains instructions for populating the cube's measures in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source fact table.

2. Create a composite specification, which contains instructions for ordering the cube's dimensions and storing sparse data in the analytic workspace.

3. Add the composite specification to the cube load specification.

4. Create containers for the cube in an analytic workspace.

5. Use the cube load specification to populate the cube's measures in the analytic workspace from the source fact table.

This cube is the Multidimensional Target Cube identified in Figure 1-1.

1.1.4 Aggregating the Cube's Data in the Analytic Workspace

For the workspace cube, run a set of procedures to accomplish the following:

1. Create an aggregation specification, which contains instructions for storing summary data in the analytic workspace.

2. Use the aggregation specification to aggregate the workspace cube.

1.1.5 Enabling Relational Access to the Workspace Cube

Once you have created, populated, and aggregated the cube in an analytic workspace, run another set of procedures to enable relational access. The enablement process consists of generating and running a set of enablement scripts. These scripts create the relational views that use the OLAP_TABLE function to access the workspace cube. The scripts may also create an OLAP Catalog cube that maps to the views.

The cube created by the enablement scripts is the Relational Target Cube identified in Figure 1-1.

To enable a workspace cube, you can either generate the scripts and run them yourself or you can use a one-step procedure to create and run the scripts automatically.

1.1.6 Viewing Metadata Created by DBMS_AWM

Two sets of views reveal metadata related to analytic workspaces. The Active Catalog views reveal metadata stored within analytic workspaces. The Analytic Workspace Maintenance views reveal metadata stored within the OLAP Catalog.

1.2.1 Methods on Dimensions

The methods you can perform on a dimension are described in Table 1-2.

1.2.2 Methods on Cubes

The methods you can perform on a cube are described in Table 1-3.

1.2.3 Methods on Dimension Load Specifications

The methods you can perform on a dimension load specification are described in Table 1-4.

1.2.4 Methods on Cube Load Specifications

The methods you can perform on a cube load specification are described in Table 1-5.

1.2.5 Methods on Aggregation Specifications

The methods you can perform on an aggregation specification are described in Table 1-6.

1.2.6 Methods on Composite Specifications

The methods you can perform on a composite specification are described in Table 1-7.

1.3.1 Refreshing the Dimension's Metadata

CREATE_AWDIMENSION ensures that the generic standard form objects that support dimensions exist in the workspace, and it registers the specified dimension in the workspace. However, the metadata that defines the logical structure of this particular dimension is not instantiated in the workspace until you call REFRESH_AWDIMENSION.

For example, if you have just created a dimension AW_PROD in a workspace MYAW in XADEMO from a source dimension XADEMO.PRODUCT, you can query the Active Catalog to check the workspace.

SQL>select * from ALL_OLAP2_AW_DIMENSIONS WHERE AW_LOGICAL_NAME in 'AW_PROD';

AW_OWNER   AW_NAME         AW_LOGICAL_NAME           SOURCE_OWNER    SOURCE_NAME
---------- --------------- ------------------------- --------------- -----------
XADEMO     MYAW            AW_PROD                   XADEMO          PRODUCT

The following query shows that there are no levels associated with the dimension. The levels, hierarchies, attributes, and descriptions will be instantiated when the dimension is refreshed.

SQL>select * from ALL_OLAP2_AW_DIM_LEVELS where AW_LOGICAL_NAME in 'AW_PROD';

no rows selected

1.3.2 When To Refresh a Dimension

You must refresh a dimension whenever changes occur in the source dimension tables. These changes could be additions or deletions of dimension members, for example removing a product from a Product dimension, or they could be changes to the dimension's metadata, such as adding a Day level to a time dimension.

When you refresh a dimension, you must also refresh each cube in which it participates.

1.3.3 What To Do After a Dimension Refresh

When you refresh a dimension because of structural metadata changes to its hierarchies, you must re-enable the dimension and its related cubes. When you refresh a dimension because of data changes, you do not need to re-enable.

When you refresh a dimension whose cube has associated stored summaries in the analytic workspace (the result of an aggregation specification), you must also reaggregate the cube.

1.4.1 Data Type Conversion

The measures in the source fact table may have numeric, text, or date data types. When REFRESH_AWCUBE loads the data into a workspace cube, it converts the RDBMS data types to types that are native to analytic workspaces. The data type conversion is described in Table 1-8.

If a source measure has a data type not described in Table 1-8, the measure is ignored by REFRESH_AWCUBE and none of its data or metadata is loaded into the analytic workspace.

1.4.2 Refreshing the Cube's Metadata

CREATE_AWCUBE ensures that the generic standard form objects that support cubes exist in the workspace, and it registers the specified cube in the workspace. However, the metadata that defines the logical structure of this particular cube is not instantiated in the workspace until you call REFRESH_AWCUBE.

For example, if you have just created a cube AW_ANACUBE in a workspace MYAW in MYSCHEMA from the source cube XADEMO.ANALYTIC_CUBE, you can query the Active Catalog to check the workspace.

SQL>select * from ALL_OLAP2_AW_CUBES where AW_LOGICAL_NAME in 'AW_ANACUBE';

AW_OWNER   AW_NAME     AW_LOGICAL_NAME       SOURCE_OWNER    SOURCE_NAME
---------- ----------- --------------------- --------------- --------------
MYSCHEMA   MYAW        AW_ANACUBE            XADEMO          ANALYTIC_CUBE

The following query shows that there are no measures associated with the cube. The measures, dimensions, and descriptions will be instantiated when the cube is refreshed.

SQL>select * from ALL_OLAP2_AW_CUBE_MEASURES where AW_CUBE_NAME in 'AW_ANACUBE';

no rows selected

1.4.3 When To Refresh a Cube

You must refresh a cube whenever changes occur in the source fact table. These changes could be additions or deletions of data, for example updating sales figures, or they could be changes to the cube's metadata, such as adding a measure or renaming a description.

When you refresh a cube, you must first refresh any of its dimensions that have changed.

1.4.4 What To Do After a Cube Refresh

When you refresh a cube because of structural metadata changes to its dimension hierarchies, you must re-enable the cube and its related dimensions. When you refresh a cube because of data changes, you do not need to re-enable.

Everytime you refresh a cube that has an associated aggregation specification, you must reaggregate the cube.

If you make changes to the composite specification associated with a cube, you must drop the cube and re-create it in the analytic workspace. You cannot refresh a cube with a modified composite specification.

1.5.1 Dimension Order

Dimension order determines how the cube's data is stored and accessed in the analytic workspace. The first dimension in the dimension's definition is the fastest-varying and the last is the slowest-varying.

By default, REFRESH_AWCUBE defines a workspace cube's dimensionality with Time as the fastest varying dimension followed by a composite of all the other dimensions. The dimensions in the composite are ordered according to their size. The dimension with the most members is first and the dimension with the least members is last. For example, the default dimensionality of the ANALYTIC_CUBE in an analytic workspace would be as follows.

<time comp_name<geography, product, channel>>

You can override the default dimensionality by specifying a composite specification and including it in the cube load specification.

For information on ordering dimensions and specifying segment size for dimension storage, see the Oracle OLAP Application Developer's Guide.

1.5.2 Creating and Modifying a Composite Specification

The statements in Example 1-3 create a composite specification called comp1 for the ANALYTIC_CUBE.

exec dbms_awm.create_awcomp_spec 
           ('comp1', 'xademo', 'analytic_cube');
exec dbms_awm.add_awcomp_spec_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_time', 'dimension', 
           'xademo', 'time');
exec dbms_awm.add_awcomp_spec_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_channel', 'dimension', 
           'xademo', 'channel');
exec dbms_awm.add_awcomp_spec_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog', 'composite');
exec dbms_awm.add_awcomp_spec_comp_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog',
           'comp1_product' ,'dimension', 'xademo', 'product');
exec dbms_awm.add_awcomp_spec_comp_member
          ('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog',
           'comp1_geography' ,'dimension', 'xademo', 'geography');
exec dbms_awm.add_awcubeload_spec_comp
           ('my_cube_load', 'xademo', 'analytic_cube', 'comp1');

exec dbms_awm.set_awcomp_spec_member_pos
          ('comp1', 'xademo', 'analytic_cube', 'comp1_channel', 1);

1.6.1 Creating an Aggregation Specification

Example 1-4 shows sample procedure calls for preaggregating the Costs and Quota measures of the analytic workspace cube AC2, which was created from XADEMO.ANALYTIC_CUBE.

The quarter totals (level 'L2' of TIME) for product groups (level 'L3' of PRODUCT), product divisions (level 'L2' of PRODUCT), and all channels (level 'STANDARD-2' of CHANNEL) are calculated and stored in the analytic workspace.

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', 'XADEMOAW', 'UK', 'AC2', 'XXF_COSTS');
execute dbms_awm.add_awcubeagg_spec_measure
          ('AGG1', 'XADEMOAW', 'UK', 'AC2', 'XXF_QUOTA');
execute dbms_awm.aggregate_awcube('MYSCHEMA', 'MYAW', 'AC2', 'AGG1');

execute dbms_aw.execute  ('aw attach MYSCHEMA.MYAW ro');
execute dbms_aw.execute  ('fulldsc agg1');

DEFINE AGG1 DIMENSION TEXT
LD List of Measures which use this AggPlan
PROPERTY 'AW$CLASS' -
'IMPLEMENTATION'
PROPERTY 'AW$CREATEDBY' -
'AW$CREATE'
PROPERTY 'AW$LASTMODIFIED' -
'.*
PROPERTY 'AW$LOGICAL_NAME' -
'AGG1'
PROPERTY 'AW$PARENT_NAME' -
'AC2'
PROPERTY 'AW$ROLE' -
'AGGDEF'
PROPERTY 'AW$STATE' -
'ACTIVE'

execute dbms_aw.execute('rpr agg1')

AGG1
--------------
XXF.COSTS
XXF.QUOTA

execute dbms_aw.execute('fulldsc agg1_product');

DEFINE AGG1_PRODUCT VALUESET PRODUCT_LEVELLIST
LD List of Levels for this AggPlan
PROPERTY 'AW$AGGOPERATOR' -
'SUM'
PROPERTY 'AW$CLASS' -
'IMPLEMENTATION'
PROPERTY 'AW$CREATEDBY' -
'AW$CREATE'
PROPERTY 'AW$LASTMODIFIED' -
'.*
PROPERTY 'AW$PARENT_CUBE' -
'AC2'
PROPERTY 'AW$PARENT_DIM' -
'PRODUCT'
PROPERTY 'AW$PARENT_NAME' -
'AGG1'
PROPERTY 'AW$ROLE' -
'AGGDEF_LEVELS'
PROPERTY 'AW$STATE' -
'ACTIVE'

execute dbms_aw.execute('shw values(agg1_product)');

L3
L2

1.6.2 Choosing an Aggregation Method

An aggregation method specifies the operation used to summarize the data by level. The default aggregation method is addition. For example, sales data is typically aggregated over time by adding the values for each time period.

The OLAP Catalog supports a set of aggregation methods, which may be included to the definition of a cube. These aggregation methods are listed in Table 1-10.

When a workspace cube is refreshed, the aggregation operators specified in the OLAP Catalog are converted to the corresponding operators supported by the OLAP DML RELATION command. These operators are incorporated in the aggregation map that controls dynamic aggregation for the cube.

To specify a different operator for your stored aggregates, you can use the SET_AWCUBEAGG_SPEC_AGGOP procedure. This procedure enables you to specify any of the operators supported by the OLAP DML RELATION command to preaggregate your data.

The OLAP Catalog and corresponding OLAP DML aggregation operators are described in Table 1-10.

1.7.1 Procedure: Generate and Run the Enablement Scripts

Use the following steps to enable a workspace cube for access by the OLAP API and BI Beans:

1. Determine how your system is configured to write to files. The enabler procedures accept either a directory object or a directory path. If you specify a directory object, make sure that your user ID has been granted the appropriate access rights to it. If you specify a path, make sure that it is the value of the UTL_FILE_DIR initialization parameter for the instance.

2. Run the REFRESH_AWCUBE and REFRESH_AWDIMENSION procedures to refresh the cube. These procedures create metadata in the analytic workspace to track the generations of enablement view names.

   NOTE: If you use some other process to refresh the cube (for example, the OLAP Analytic Workspace Java API), this metadata is not created. If you want to specify your own names for the enablement views (as described in the following step), you must create this metadata by calling the REFRESH_AWDIMENSION_VIEW_NAME and REFRESH_AWCUBE_VIEW_NAME procedures.

3. The enablement process automatically provides system-generated names for the enablement views. To provide your own view names, call the SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME procedures.

4. Call the CREATE_AWDIMENSION_ACCESS procedure for each of the cube's dimensions. Set the access_type parameter to OLAP. Each procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the dimension views and an OLAP Catalog dimension that maps to the views.

5. Call the CREATE_AWCUBE_ACCESS procedure. Set the access_type parameter to OLAP. This procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the fact views and an OLAP Catalog cube that maps to the views.

6. Run the enablement scripts. The scripts will delete any previous generation of views and metadata before creating new views and metadata.

1.7.2 Procedure: Run the Enablement Scripts Automatically

To create and run the enablement scripts automatically, use the following steps:

1. Refresh the cube and its dimensions in the analytic workspace, as described in "Procedure: Generate and Run the Enablement Scripts".

2. Call CREATE_AWDIMENSION_ACCESS_FULL for each of the cube's dimensions. This procedure creates the enablement scripts in temporary memory and runs the scripts to create the dimension views and OLAP Catalog metadata. The scripts delete any previous views and OLAP Catalog metadata before creating new views and metadata.

3. Call the procedure CREATE_AWCUBE_ACCESS_FULL to create the fact views for the cube. This procedure accomplishes the same basic steps as the corresponding procedure for dimensions.

1.7.3 The OLAP API Enabler Procedures

The OLAP API enabler procedures are listed in Table 1-11.

1.7.4 Enablement Metadata in the Analytic Workspace

The REFRESH_AWDIMENSION and REFRESH_AWCUBE procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the views that will be created by the enablement scripts.

Whenever you refresh, new view names are generated. If you have previously created your own names (SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME), the refresh process uses them as the basis for the new names.

If you refresh and there has been no change to the source cube's metadata, you do not need to re-create the enablement scripts.

1.7.5 Disabling Relational Access

The enablement procedures automatically delete any previous generation of views and OLAP Catalog metadata. However, in some circumstances, you might want to drop the views and metadata without re-creating them. In particular, if you drop the workspace cube or the workspace itself, you will need to clean up the orphaned views and metadata.

In this case, you can run the DELETE_AWDIMENSION_ACCESS and DELETE_AWCUBE_ACCESS procedures to generate scripts that will drop the views and metadata that enable relational access to the cube. These scripts do not delete any enablement metadata that is stored within the analytic workspace.

To delete all the enablement views and metadata for a dimension or a cube, use DELETE_AWCUBE_ACCESS_ALL and DELETE_AWDIMENSION_ACCESS_ALL.

1.7.6 Default Dimension View Names

REFRESH_AWDIMENSION constructs default names for the views. You can override the default names by calling SET_AWDIMENSION_VIEW_NAME.

The default view name is: aaaa_bbbbb_ccccc_ddddd#view, where:

aaaa is the first four characters of the analytic workspace owner

bbbbb is the first five characters of the analytic workspace name

ccccc is the first five characters of the analytic workspace dimension name

ddddd is the first five characters of the analytic workspace hierarchy name

# is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.

Default names are also generated for the abstract objects (ADTs) populated by OLAP_TABLE. For example, the workspace dimension AWGEOG, in a workspace called AWTEST in the XADEMO schema could have the following system-generated names for the STANDARD hierarchy.

1.7.7 Default Fact View Names

The REFRESH_AWCUBE procedure constructs default names for the views. You can override the default names by calling SET_AWCUBE_VIEW_NAME.

The default view name is: aaaa_bbbbb_cccccccc#view, where:

aaaa is the first four characters of the analytic workspace owner

bbbbb is the first five characters of the analytic workspace name

cccccccc is the first eight characters of the analytic workspace cube name

# is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.

Default names are also generated for the abstract objects (ADTs) populated by OLAP_TABLE. For example, the workspace cube AWCUBE, in a workspace called AWTEST in the XADEMO schema could have the following system-generated names.

1.7.8 Column Structure of Dimension Enablement Views

The enablement process generates a separate view for each dimension hierarchy. For example, a workspace cube with the four dimensions shown in Table 1-12 would have six separate dimension views since two of the dimensions have two hierarchies.

The dimension views are level-based, and they include the full lineage of every level value in every row. This type of dimension table is considered solved, because the fact table related to this dimension includes embedded totals for all level combinations.

Each dimension view contains the columns described in Table 1-13.

GID PARENT_GID  ET KEY     PARENT_ET_KEY TOTAL_US  REGION      STATE  
--- ----------  ------     ------------  --------  ------      -----
0   1           MA         Northeast     USA       Northeast   MA     
0   1           NY         Northeast     USA       Northeast   NY     
0   1           GA         Southeast     USA       Southeast   GA     
0   1           CA         Southwest     USA       Southwest   CA
0   1           AZ         Southwest     USA       Southwest   AZ
1   3           Northeast  USA           USA       Northeast
1   3           Southeast  USA           USA       Southeast
1   3           Southwest  USA           USA       Southwest
3   NA          USA        NA            USA

TOTAL_US  REGION      STATE  
--------  ------      -----
USA       Southwest   
0         0           1

TOTAL_GEOG COUNTRY REGION     STATE  CITY
---------- ------- -------    ------ -------
World      USA     Northeast
0          0       0           1      1

1.7.9 Column Structure of Enablement Fact Views

The CREATE_AWCUBE_ACCESS procedure generates a separate view for each dimension/hierarchy combination. For example, an analytic workspace cube with the four dimensions shown in Table 1-12, would have four separate fact views, one for each hierarchy combination show in Table 1-14.

The fact views are fully solved. They contain embedded totals for all level combinations. Each view has columns for the cube's measures, and key columns that link the fact view with its associated dimension views.

Each fact view contains the columns described in Table 1-15.

1.7.10 Example: Enable a Workspace Cube for Access by the OLAP API

The following example creates, refreshes, and enables a cube AWUSR.AWTEST based on the source cube XADEMO.ANALYTIC_CUBE.

-- SET UP
set serveroutput on size 1000000
execute cwm2_olap_manager.set_echo_on;
execute cwm2_olap_manager.begin_log ('/users/awuser/scripts' , 'awtest.log');  

--- CREATE AW
execute dbms_aw.execute ('aw create ''AWTEST''');

-- CREATE DIMENSIONS 
execute dbms_awm.create_awdimension 
          ('XADEMO','CHANNEL',   'AWUSR', 'AWTEST', 'AWCHAN');
execute dbms_awm.create_awdimension 
          ('XADEMO','GEOGRAPHY', 'AWUSR', 'AWTEST', 'AWGEOG');
execute dbms_awm.create_awdimension 
          ('XADEMO','PRODUCT',   'AWUSR', 'AWTEST', 'AWPROD');
execute dbms_awm.create_awdimension 
          ('XADEMO','TIME',      'AWUSR', 'AWTEST', 'AWTIME');

-- CREATE CUBE 
execute dbms_awm.create_awcube  
          ('XADEMO', 'ANALYTIC_CUBE','AWUSR', 'AWTEST','AWCUBE');

-- REFRESH DIMENSIONS 
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWCHAN');
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWGEOG');
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWPROD');
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWTIME');

-- REFRESH CUBE 
execute dbms_awm.refresh_awcube ('AWUSR', 'AWTEST', 'AWCUBE');

-- SET DIMENSION VIEW NAMES
exec dbms_awm.set_awdimension_view_name 
          ('AWUSR', 'AWTEST', 'awprod', 'standard',     'prod_std_view');
exec dbms_awm.set_awdimension_view_name 
          ('AWUSR', 'AWTEST', 'awchan', 'standard',     'chan_std_view');
exec dbms_awm.set_awdimension_view_name 
          ('AWUSR', 'AWTEST', 'awgeog', 'consolidated', 'geog_csd_view');
exec dbms_awm.set_awdimension_view_name 
          ('AWUSR', 'AWTEST', 'awgeog', 'standard',     'geog_std_view');
exec dbms_awm.set_awdimension_view_name 
          ('AWUSR', 'AWTEST', 'awtime', 'standard',     'time_std_view');
exec dbms_awm.set_awdimension_view_name 
          ('AWUSR', 'AWTEST', 'awtime', 'ytd',          'time_ytd_view');

-- SET CUBE VIEW NAMES
exec dbms_awm.set_awcube_view_name 
          ('AWUSR', 'AWTEST', 'awcube', 1, 'AWCUBE_view1');
exec dbms_awm.set_awcube_view_name 
          ('AWUSR', 'AWTEST', 'awcube', 2, 'AWCUBE_view2');
exec dbms_awm.set_awcube_view_name 
          ('AWUSR', 'AWTEST', 'awcube', 3, 'AWCUBE_view3');
exec dbms_awm.set_awcube_view_name 
          ('AWUSR', 'AWTEST', 'awcube', 4, 'AWCUBE_view4');

-- ENABLE DIMENSIONS
exec dbms_awm.create_AWdimension_access 
          ('AWUSR', 'AWTEST', 'awprod', 'olap', 
           '/users/awuser/scripts', 'awprod_views.sql', 'w');
exec dbms_awm.create_AWdimension_access 
          ('AWUSR', 'AWTEST', 'awchan', 'olap', 
           '/users/awuser/scripts', 'awchan_views.sql', 'w');
exec dbms_awm.create_AWdimension_access 
          ('AWUSR', 'AWTEST', 'awgeog', 'olap', 
           '/users/awuser/scripts', 'awgeog_views.sql', 'w');
exec dbms_awm.create_AWdimension_access 
          ('AWUSR', 'AWTEST', 'awtime', 'olap', 
           '/users/awuser/scripts', 'awtime_views.sql', 'w');

-- ENABLE CUBE
exec dbms_awm.create_AWcube_access 
          ('AWUSR', 'AWTEST', 'awcube', 'olap', 
           '/users/awuser/scripts', 'awcube_views.sql', 'w');

-- COMMIT and WRAPUP
commit;
execute cwm2_olap_manager.end_log; 

The following queries show the resulting workspace cube and dimensions with their source cubes and dimensions in the OLAP Catalog.

select * from all_olap2_aw_dimensions where AW_OWNER = 'AWUSER';

AW_OWNER  AW_NAME  AW_LOGICAL_NAME AW_PHYSICAL_OBJECT SOURCE_OWNER  SOURCE_NAME
--------- --------- --------------- ------------------ ------------- ---------------
AWUSER     AWTEST    AWCHAN          AWCHAN             XADEMO        CHANNEL
AWUSER     AWTEST    AWGEOG          AWGEOG             XADEMO        GEOGRAPHY
AWUSER     AWTEST    AWPROD          AWPROD             XADEMO        PRODUCT
AWUSER     AWTEST    AWTIME          AWTIME             XADEMO        TIME

select * from all_olap2_aw_CUBEs where AW_OWNER = 'AWUSER';

AW_OWNER AW_NAME  AW_LOGICAL_NAME AW_PHYSICAL_OBJECT  SOURCE_OWNER  SOURCE_NAME
-------- -------- --------------- ------------------- ------------- ---------------
AWUSER    AWTEST   AWCUBE          AWCUBE              XADEMO        ANALYTIC_CUBE

The following queries show the system names and user names for the dimension enablement views.

select * from all_aw_dim_ENABLED_VIEWS where AW_OWNER = 'AWUSER';

AW_OWNER AW_NAME  DIMENSION_ HIERARCHY_   SYSTEM_VIEWNAME                USER_VIEWNAME
-------- -------- ---------- ----------   ----------------------------   ----------------
AWUSER    AWTEST   AWCHAN     STANDARD     AWUS_AWTES_AWCHA_STAND144VIEW  CHAN_STD_VIEW
AWUSER    AWTEST   AWGEOG     CONSOLIDATED AWUS_AWTES_AWGEO_CONSO145VIEW  GEOG_CSD_VIEW
AWUSER    AWTEST   AWGEOG     STANDARD     AWUS_AWTES_AWGEO_STAND146VIEW  GEOG_STD_VIEW
AWUSER    AWTEST   AWPROD     STANDARD     AWUS_AWTES_AWPRO_STAND147VIEW  PROD_STD_VIEW
AWUSER    AWTEST   AWTIME     STANDARD     AWUS_AWTES_AWTIM_STAND148VIEW  TIME_STD_VIEW
AWUSER    AWTEST   AWTIME     YTD          AWUS_AWTES_AWTIM_YTD149VIEW    TIME_YTD_VIEW

The following queries show the system names and user names for the cube enablement views. Included are the hierarchy combination numbers, in this case 1 - 4, and the hierarchy strings, consisting of each unique combination of dimension hierarchies for this cube.

select * from all_aw_CUBE_ENABLED_VIEWS where AW_OWNER = 'AWUSER';

AW_OWN AW_NA  CUBE_NAM  HIER  HIERCOMBO_STR                         SYSTEM_VIEWNAME            USER_VIEWNAME
------ ------ --------  ----  -----------------------------------  -----------------------   -----------
AWUSER  AWTEST  AWCUBE   1     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE151VIEW  AWCUBE_VIEW1
                              /HIER:CONSOLIDATED;DIM:AWPROD/HIER:
                              STANDARD;DIM:AWTIME/HIER:STANDARD
AWUSER  AWTEST  AWCUBE   2     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE152VIEW  AWCUBE_VIEW2
                              /HIER:CONSOLIDATED;DIM:AWPROD/HIER:
                              STANDARD;DIM:AWTIME/HIER:YTD
AWUSER   AWTEST  AWCUBE  3     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE153VIEW  AWCUBE_VIEW3
                              /HIER:STANDARD;DIM:AWPROD/HIER:STAN
                              DARD;DIM:AWTIME/HIER:STANDARD
AWUSER   AWTEST  AWCUBE  4     DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG   AWUS_AWTES_AWCUBE154VIEW   AWCUBE_VIEW4
                              /HIER:STANDARD;DIM:AWPROD/HIER:STAN
                              DARD;DIM:AWTIME/HIER:YTD

The final step is to run the enablement scripts to generate the views and OLAP Catalog metadata for the analytic workspace cube. The scripts produced by this example are described as follows.