Creating and Refreshing a Workspace Dimension

Once you have defined a dimension in the OLAP Catalog for your source dimension table, you can create the dimension in the analytic workspace.

Only one workspace dimension may be created from a given dimension in the OLAP Catalog. For example, if you have used the OLAP Catalog PRODUCT dimension as the source for the PROD_AW dimension in an analytic workspace, you cannot create another dimension PROD_AW2 from the same source dimension in the same workspace.

Note:

CREATE_AWDIMENSION opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.

The analytic workspace must already exist before you call CREATE_AWDIMENSION or any other procedures in the DBMS_AWM package.

Example 1-1 shows the procedure calls for defining and populating workspace objects for the XADEMO.CHANNEL dimension. The load specification includes a filter condition that causes only the row for 'DIRECT' to be loaded.

Example 1-1 Creating the CHANNEL Dimension in an Analytic Workspace

--- SET UP
set serveroutput on
execute cwm2_olap_manager.set_echo_on;
create or replace directory myscripts as '/users/myxademo/myscripts';
execute cwm2_olap_manager.begin_log
          ('MYSCRIPTS' , 'channel.log');

--- CREATE THE ANALYTIC WORKSPACE
execute dbms_aw.execute ('aw create ''myaw''');

--- CREATE AND POPULATE THE DIMENSION
execute dbms_awm.create_awdimension
          ('XADEMO', 'CHANNEL', 'MYSCHEMA','MYAW', 'AW_CHAN');
execute dbms_awm.create_awdimload_spec
          ('CHAN_LOAD', 'XADEMO', 'CHANNEL', 'FULL_LOAD');
execute dbms_awm.add_awdimload_spec_filter 
          ('CHAN_LOAD', 'XADEMO', 'CHANNEL', 'XADEMO',
          'XADEMO_CHANNEL', '''CHAN_STD_CHANNEL'' = ''DIRECT''' );
execute dbms_awm.refresh_awdimension
          ('MYSCHEMA', 'MYAW', 'AW_CHAN', 'CHAN_LOAD');

--- COMMIT AND WRAP UP
commit;
execute cwm2_olap_manager.set_echo_off;
execute cwm2_olap_manager.end_log

When you query the Active Catalog view ALL_OLAP2_AW_DIMENSIONS, you will see the following row.

AW_OWNER    AW_NAME     AW_LOGICAL_NAME      SOURCE_OWNER   SOURCE_NAME
----------  ----------  -------------------  -------------  -----------
MYSCHEMA    MYAW        AW_CHAN              XADEMO         CHANNEL

CREATE_AWDIMENSION creates the standard form metadata for the dimension in the workspace. REFRESH_AWDIMENSION loads the dimension members and attribute values.

You should 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.

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.