Example: Query Load and Enablement Parameters for Workspace Dimensions

Example 4-1 Query Load Parameters and Enablement View Names for CHANNEL and TIME

The following statements create the dimensions AW_CHAN and AW_TIME in the analytic workspace MY_SCHEMA.MY_AW.

execute dbms_awm.create_awdimension 
           ('XADEMO','CHANNEL','MY_SCHEMA', 'MY_AW', 'AW_CHAN');
execute dbms_awm.create_awdimension 
          ('XADEMO','TIME','MY_SCHEMA', 'MY_AW', 'AW_TIME');

The following statements create the load specifications for the dimensions.

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.create_awdimload_spec 
          ('TIME_DIMLOADSPEC', 'XADEMO', 'TIME', 'FULL_LOAD');
execute dbms_awm.add_awdimload_spec_filter 
          ('TIME_DIMLOADSPEC', 'XADEMO', 'TIME', 'XADEMO', 'XADEMO_TIME',
           '''TIME_STD_YEAR'' = ''1997''' );

The following query returns the filter conditions associated with the dimension load specifications.

SQL>select * from all_aw_load_dim_filters;

OWNER     DIMENSION_NAME   LOAD_NAME          TABLE_OWNER      TABLE_NAME       FILTER_CONDITION
-------- ---------------   ----------------   --------------   ---------------  -----------------------------
XADEMO     TIME            TIME_DIMLOADSPEC     XADEMO         XADEMO_TIME      'TIME_STD_YEAR' = '1997'
XADEMO     CHANNEL         CHAN_DIMLOADSPEC     XADEMO         XADEMO_CHANNEL   'CHAN_STD_CHANNEL' = 'DIRECT'

The following statements load the dimensions in the analytic workspace. The system-generated names that will be used for the enablement views are created in the workspace as part of the load process.

execute dbms_awm.refresh_awdimension 
          ('MY_SCHEMA', 'MY_AW', 'AWCHAN', 'CHAN_DIMLOADSPEC');
execute dbms_awm.refresh_awdimension 
          ('MY_SCHEMA', 'MY_AW', 'AWTIME', 'TIME_DIMLOADSPEC');

The following query returns the system-generated enablement view names for the dimensions.

SQL>select * from all_aw_dim_enabled_views;

AW_OWNER   AW_NAME     DIMENSION_NAME  HIERARCHY_NAME   SYSTEM_VIEWNAME                     USER_VIEWNAME
---------- ---------- ---------------  ---------------  ---------------------------------  --------------
MY_SCHEMA    MY_AW     AWCHAN          STANDARD         MY_S_MY_AW_AWCHA_STAND35VIEW
MY_SCHEMA    MY_AW     AWTIME          STANDARD         MY_S_MY_AW_AWTIM_STAND36VIEW
MY_SCHEMA    MY_AW     AWTIME          YTD              MY_S_MY_AW_AWTIM_YTD37VIEW