Creating and Refreshing a Workspace Cube

Once you have defined a cube in the OLAP Catalog for your star schema, you can create the cube in the analytic workspace.

You must call CREATE_AWDIMENSION to create each of the cube's dimensions before calling CREATE_AWCUBE to create the cube. To populate the cube, you must call REFRESH_AWDIMENSION to populate each of the cube's dimensions before calling REFRESH_AWCUBE to refresh the cube's measures.

Within an analytic workspace, dimensions can be shared by more than one cube. When creating a new workspace cube, you will only call CREATE_AWDIMENSION for OLAP Catalog dimensions that have not been used as the source for dimensions of cubes that already exist in the workspace.

Example 1-2 shows the procedure calls for creating and populating the XADEMO.ANALYTIC_CUBE cube 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' , 'anacube.log');


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

--- CREATE AND REFRESH THE DIMENSIONS
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.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');

--- CREATE AND REFRESH THE CUBE 
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_awcube
          ('MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AC_CUBELOADSPEC');

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

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

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.

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

You should 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. If you want to drop or add a dimension to a cube, you must drop the cube and re-create it.

Every time 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.