4 Analytic Workspace Maintenance Views

4.1 Building and Maintaining Analytic Workspaces

The DBMS_AWM package manages the life cycle of standard form analytic workspaces. This includes the creation of workspace cubes from relational sources, data loads, and the enablement of workspace cubes for relational access.

The DBMS_AWM package stores information about workspace builds in the OLAP Catalog. You can query the Analytic Workspace Maintenance views to obtain this information. For example, you could obtain a list of workspace cubes with their relational sources, a list of load specifications, or a list of composite specifications.

The DBMS_AWM package stores information about workspace enablement within the analytic workspace itself. The Analytic Workspace Maintenance views use OLAP_TABLE functions to return information about the enablement of workspace cubes. You can query these views to obtain the names of enablement views and hierarchy combinations.

4.2 Example: Query Load and Enablement Parameters for Workspace Dimensions

The following example uses the XADEMO dimensions CHANNEL and TIME to illustrate several Analytic Workspace Maintenance views.

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');

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''' );

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'

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

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

4.3 Summary of Analytic Workspace Maintenance Views

The analytic workspace maintenance views are summarized in the following table.

4.4 ALL_AW_CUBE_AGG_LEVELS

ALL_AW_CUBE_AGG_LEVELS lists the levels in aggregation specifications for cubes.

Aggregation specifications determine how data will be aggregated along the dimensions of a cube in an analytic workspace. Aggregation specifications are created by the DBMS_AWM.CREATE_AWCUBEAGG_SPEC procedure.

4.5 ALL_AW_CUBE_AGG_MEASURES

ALL_AW_CUBE_AGG_MEASURES lists the measures in aggregation specifications for cubes.

Aggregation specifications determine how the measures will be aggregated along the dimensions of a cube in an analytic workspace. Aggregation specifications are created by the DBMS_AWM.CREATE_AWCUBEAGG_SPEC procedure.

4.6 ALL_AW_CUBE_AGG_PLANS

ALL_AW_CUBE_AGG_PLANS lists the aggregation specifications for cubes.

Aggregation specifications determine how data will be aggregated along the dimensions of a cube in an analytic workspace. Aggregation specifications are created by the DBMS_AWM.CREATE_AWCUBEAGG_SPEC procedure.

4.7 ALL_AW_CUBE_ENABLED_HIERCOMBO

ALL_AW_CUBE_ENABLED_HIERCOMBO lists the hierarchy combinations associated with cubes in analytic workspaces.

Each hierarchy combination is identified by a unique number. The OLAP API Enabler creates a separate fact view for each hierarchy combination.

The information in this view is available for all standard form cubes that have been refreshed. See the DBMS_AWM.REFRESH_AWCUBE procedure and the DBMS_AWM.CREATE_AWCUBE_ACCESS procedure.

4.8 ALL_AW_CUBE_ENABLED_VIEWS

ALL_AW_CUBE_ENABLED_VIEWS describes the fact views that can be generated for cubes in analytic workspaces.

Descriptions of the views are created when the cube is refreshed. The view is not instantiated until the DBMS_AWM.CREATE_AWCUBE_ACCESS has executed and the resulting script has been run.

ALL_AW_CUBE_ENABLED_VIEWS shows the descriptions of the views. The views themselves do not necessarily exist.

Metadata about fact views is generated by the DBMS_AWM.REFRESH_AWCUBE procedure. Scripts to create views of workspace cubes are created by the DBMS_AWM.CREATE_AWCUBE_ACCESS procedure.

4.9 ALL_AW_DIM_ENABLED_VIEWS

ALL_AW_DIM_ENABLED_VIEWS describes the dimension views that can be generated for dimensions in analytic workspaces.

Descriptions of the views are created when the dimension is refreshed. The view is not instantiated until the DBMS_AWM.CREATE_AWDIMENSION_ACCESS has executed and the resulting script has been run.

ALL_AW_DIM_ENABLED_VIEWS shows the descriptions of the views. The views themselves do not necessarily exist.

Metadata about dimension views is generated by the DBMS_AWM.REFRESH_AWDIMENSION procedure. Scripts to create views of workspace dimensions are created by the DBMS_AWM.CREATE_AWDIMENSION_ACCESS procedure.

4.10 ALL_AW_LOAD_CUBES

ALL_AW_LOAD_CUBES lists the load specifications for cubes.

Load specifications determine how data will be loaded from the source fact table into the analytic workspace. Cube load specifications are created by the DBMS_AWM.CREATE_AWCUBELOAD_SPEC procedure.

4.11 ALL_AW_LOAD_CUBE_DIMS

ALL_AW_LOAD_CUBE_DIMS describes the composite specifications for cubes.

Composite specifications determines how the cube's dimensions will be optimized in the analytic workspace. Composite specifications are created by the DBMS_AWM.CREATE_AWCOMP_SPEC procedure.

4.12 ALL_AW_LOAD_CUBE_FILTERS

ALL_AW_LOAD_CUBE_FILTS lists the filter conditions associated with load specifications for cubes.

Filter conditions are SQL WHERE clauses that identify a subset of the data to be loaded from the fact table to the analytic workspace.

Filter conditions are created by the DBMS_AWM.ADD_AWCUBELOAD_SPEC_FILTER procedure.

4.13 ALL_AW_LOAD_CUBE_MEASURES

ALL_AW_LOAD_CUBE_MEASURES lists the measures in cube load specifications with their corresponding target measures in standard form analytic workspaces.

Measures are added to cube load specifications by the DBMS_AWM.ADD_AWCUBELOAD_SPEC_MEASURE procedure. This procedure enables you to specify a target name and display name for the measure in the analytic workspace. If you do not call this procedure, or if you do not specify the target names, the OLAP Catalog names are used.

4.14 ALL_AW_LOAD_CUBE_PARMS

ALL_AW_LOAD_CUBE_PARMS lists the parameters in cube load specifications.

Cube load specifications determine how a cube's data will be loaded from the fact table into the analytic workspace.

Parameters are set for cube load specifications by the DBMS_AWM.SET_AWCUBELOAD_SPEC_PARAMETER procedure.

4.15 ALL_AW_LOAD_DIMENSIONS

ALL_AW_LOAD_DIMENSIONS lists the load specifications for dimensions.

Dimension load specifications are created by the DBMS_AWM.CREATE_AWDIMLOAD_SPEC procedure.

4.16 ALL_AW_LOAD_DIM_FILTERS

ALL_AW_LOAD_DIM_FILTERS lists the filter conditions associated with load specifications for dimensions.

Filter conditions are SQL WHERE clauses that identify a subset of the data to be loaded from the dimension table to the analytic workspace.

Filter conditions are created by the DBMS_AWM.ADD_AWDIMLOAD_SPEC_FILTER procedure.

4.17 ALL_AW_LOAD_DIM_PARMS

ALL_AW_LOAD_DIM_PARMS lists the parameters in dimension load specifications.

Dimension load specifications determine how dimension members will be loaded from the dimension table into the analytic workspace.

Parameters are set for dimension load specifications by the DBMS_AWM.SET_AWDIMLOAD_SPEC_PARAMETER procedure.

4.18 ALL_AW_OBJ

ALL_AW_OBJ lists the current objects in all analytic workspaces that are accessible to the user. The workspaces may have been created by DBMS_AWM or by another tool, such as the OLAP Analytic Workspace API.

4.19 ALL_AW_PROP

ALL_AW_PROP lists the current OLAP DML properties and their values in all analytic workspaces that are accessible to the user. The workspaces may have been created by DBMS_AWM or by another tool, such as the OLAP Analytic Workspace API.