4 Analytic Workspace Maintenance Views

This chapter describes the views you can query to obtain information about maintaining standard form analytic workspaces.

See Also: Chapter 1, " Creating Analytic Workspaces with DBMS_AWM" Chapter 23, " DBMS_AWM "

This chapter discusses the following topics:

• Building and Maintaining Analytic Workspaces

• Example: Query Load and Enablement Parameters for Workspace Dimensions

• Summary of 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.

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

4.3 Summary of Analytic Workspace Maintenance Views

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

Table 4-1 Analytic Workspace Maintenance Views

Public Synonym	Description
ALL_AW_CUBE_AGG_LEVELS	Describes the levels in aggregation specifications for cubes.
ALL_AW_CUBE_AGG_MEASURES	Describes the measures in aggregation specifications for cubes.
ALL_AW_CUBE_AGG_PLANS	Describes the aggregation specifications for cubes.
ALL_AW_CUBE_ENABLED_HIERCOMBO	Describes the hierarchy combinations associated with cubes.
ALL_AW_CUBE_ENABLED_VIEWS	Describes the fact views that can be generated for workspace cubes.
ALL_AW_DIM_ENABLED_VIEWS	Describes the dimension views that can be generated for workspace dimensions.
ALL_AW_LOAD_CUBES	Describes the load specifications for cubes.
ALL_AW_LOAD_CUBE_DIMS	Describes the composite specifications for cubes.
ALL_AW_LOAD_CUBE_FILTERS	Describes the filter conditions associated with load specifications for cubes.
ALL_AW_LOAD_CUBE_MEASURES	Describes the measures in cube load specifications.
ALL_AW_LOAD_CUBE_PARMS	Describes parameters of cube load specifications.
ALL_AW_LOAD_DIMENSIONS	Describes the load specifications for dimensions.
ALL_AW_LOAD_DIM_FILTERS	Describes the filter conditions associated with load specifications for dimensions.
ALL_AW_LOAD_DIM_PARMS	Describes parameters of dimension load specifications.
ALL_AW_OBJ	Lists the objects in all analytic workspaces available to the current user. The workspaces may have been created by DBMS_AWM or by another tool, such as the OLAP Analytic Workspace API.
ALL_AW_PROP	Lists the OLAP DML properties and their values in all analytic workspaces available to the current user. The workspaces may have been created by DBMS_AWM or by another tool, such as the OLAP Analytic Workspace API.

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.

Column	Datatype	NULL	Description
owner	varchar2(240)		Owner of the cube.
cube_name	varchar2(240)		Name of the cube.
aggregation_name	varchar2(60)		Name of the aggregation spec.
dimension_owner	varchar2(30)		Owner of the dimension to aggregate.
dimension_name	varchar2(240)		Name of the dimension to aggregate.
level_name	archar2(240)		Name of the level of aggregation for this dimension.

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.

Column	Datatype	NULL	Description
cube_owner	varchar2(240)		Owner of the cube.
cube_name	varchar2(240)		Name of the cube.
aggregation_name	varchar2(60)		Name of the aggregation spec.
measure_name	varchar2(240)		Name of the measure to aggregate.

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.

Column	Datatype	NULL	Description
owner	varchar2(240)		Owner of the cube.
cube_name	varchar2(240)		Name of the cube.
aggregation_name	varchar2(60)		Name of the aggregation spec.

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.

Column	Datatype	NULL	Description
aw_owner	varchar2(30)		Owner of the analytic workspace.
aw_name	varchar2(30)		Name of the analytic workspace.
cube_name	varchar2(1000)		Name of the cube in the analytic workspace.
hiercombo_num	number		Unique number that identifies the hierarchy combination.
hiercombo_str	varchar2(1000)		List of hierarchies that define the dimensionality of a fact view of the enabled cube.

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.

Column	Datatype	NULL	Description
aw_owner	varchar2(30)		Owner of the analytic workspace.
aw_name	varchar2(30)		Name of the analytic workspace.
cube_name	varchar2(1000)		Name of the cube in the analytic workspace.
hiercombo_num	number		Unique number that identifies the hierarchy combination.
hiercombo_str	varchar2(1000)		List of hierarchies that define the dimensionality of a fact view of the enabled cube.
system_viewname	varchar2(1000)		Default view name created by the DBMS_AWM.REFRESH_AWCUBE procedure.
user_viewname	varchar2(1000)		User-defined view name specified by the DBMS_AWM.SET_AWCUBE_VIEWNAME 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.

Column	Datatype	NULL	Description
aw_owner	varchar2(30)		Owner of the analytic workspace.
aw_name	varchar2(30)		Name of the analytic workspace.
dimension_name	varchar2(1000)		Name of the dimension in the analytic workspace.
hierarchy_name	varchar2(1000)		Name of the hierarchy in the analytic workspace.
system_viewname	varchar2(1000)		Default view name created by the DBMS_AWM.REFRESH_AWCUBE procedure.
user_viewname	varchar2(1000)		User-defined view name specified by the DBMS_AWM.SET_AWDIMENSION_VIEWNAME 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.

Column	Datatype	NULL	Description
cube_owner	varchar2(240)		Owner of the OLAP Catalog source cube.
cube_name	varchar2(240)		Name of the OLAP Catalog source cube.
load_name	varchar2(60)		Name of a load specification for the cube.
load_type	varchar2(60)		'LOAD_DATA' -- Load the data from the fact table into the analytic workspace target cube. 'LOAD_PROGRAM' -- Create the load programs in the analytic workspace but do not execute them. You can run the program manually to load the data. Cube load program names are stored in the AW$LOADPGRGS property of the standard form cube in the analytic workspace.

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.

Column	Datatype	NULL	Description
cube_owner	varchar2(240)		Owner of the OLAP Catalog source cube.
cube_name	varchar2(240)		Name of the OLAP Catalog source cube.
cubeload_name	varchar2(60)		Name of a load specification for the cube.
compspec_name	varchar2(30)		Name of a composite specification associated with this load specification.
composite_name	varchar2(30)		Name of a composite that is a member of the specification. A composite contains sparse dimensions of the cube.
segwidth	number		Segment width for storage of the data dimensioned by this member of the specification.
compspec_position	number		Position of the member within the specification.
dimension_owner	varchar2(30)		Owner of an OLAP Catalog source dimension that is a member of the specification.
dimension_name	varchar2(240)		Name of the OLAP Catalog source dimension that is a member of the specification.
composite_position	number		Position of the member within a composite member.
nested_level	number		The level of nesting of the member of the specification. For example, a dense dimension would have a nesting level of 1. A sparse dimension within a composite would have a nesting level of 2, and a nested composite would have a nesting level of 3.
nested_type	varchar2(10)		Type of member of the specification. Either DIMENSION or COMPOSITE.
nested_name	varchar2(30)		Name of the member of the specification. This may be the name of a dimension or the name of a composite.

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.

Column	Datatype	NULL	Description
owner	varchar2(240)		Owner of the OLAP Catalog source cube.
cube_name	varchar2(240)		Name of the OLAP Catalog source cube.
load_name	varchar2(60)		Name of a load specification for the cube.
table_owner	varchar2(30)		Owner of the fact table.
table_name	varchar2(30)		Name of the fact table.
filter_condition	varchar2(4000)		SQL WHERE clause.

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.

Column	Datatype	NULL	Description
owner	varchar2(240)		Owner of the source cube in the OLAP Catalog.
cube_name	varchar2(240)		Name of the source cube in the OLAP Catalog.
load_name	varchar2(60)		Name of the load specification for the source cube.
measure_name	varchar2(240)		Name of a measure of the source cube.
measure_target_name	varchar2(60)		Name of the measure in the analytic workspace.
measure_target_display_name	varchar2(60)		Display name of the measure in the analytic workspace. This may be the display name from the OLAP Catalog, or it may be user-defined.
measure_target_description	varchar2(4000)		Description of the measure in the analytic workspace. This may be the description from the OLAP Catalog, or it may be user-defined.

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.

Column	Datatype	NULL	Description
owner	varchar2(240)		Owner of the source cube in the OLAP Catalog.
cube_name	varchar2(240)		Name of the source cube in the OLAP Catalog.
load_name	varchar2(60)		Name of the load specification for the source cube.
parm_name	varchar2(16)		The name of the parameter. Currently only 'DISPLAY NAME' is available. If you do not set this parameter, the cube display name from the OLAP Catalog is used in the analytic workspace.
parm_value	varchar2(30)		The display name to use for the target cube in the analytic workspace.

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.

Column	Datatype	NULL	Description
owner	varchar2(30)		Owner of the source dimension in the OLAP Catalog.
dimension_name	varchar2(30)		Name of the source dimension in the OLAP Catalog.
load_name	varchar2(60)		Name of the load specification.
load_type	varchar2(60)		'FULL_LOAD_ADDITIONS_ONLY' -- Only new dimension members will be loaded when the dimension is refreshed. (Default) 'FULL_LOAD' -- When the dimension is refreshed, all dimension members in the workspace will be deleted, then all the members of the source dimension will be loaded.

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.

Column	Datatype	NULL	Description
owner	varchar2(30)		Owner of the source dimension in the OLAP Catalog.
dimension_name	varchar2(30)		Name of the source dimension in the OLAP Catalog.
load_name	varchar2(60)		Name of the dimension load specification.
table_owner	varchar2(30)		Owner of the dimension table.
table_name	varchar2(30)		Name of the dimension table.
filter_condition	varchar2(4000)		SQL WHERE clause.

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.

Column	Datatype	NULL	Description
owner	varchar2(30)		Owner of the source dimension in the OLAP Catalog.
dimension_name	varchar2(30)		Name of the source dimension in the OLAP Catalog.
load_name	varchar2(60)		Name of the dimension load specification.
parm_name	varchar2(16)		'UNIQUE_RDBMS_KEY' -- Whether or not the members of this dimension are unique across all levels in the source tables. 'DISPLAY_NAME' -- Display name for the target dimension in the analytic workspace. 'PLURAL_DISPLAY_NAME' -- Plural display name for the target dimension in the analytic workspace.
parm_value	varchar2(4000)		Values of UNIQUE_RDBMS_KEY: NO-- Dimension member names are not unique across levels in the RDBMS tables. The corresponding dimension member names in the analytic workspace include the level name as a prefix. (Default) YES -- Dimension member names are unique across levels in the RDBMS tables. The corresponding dimension member names in the analytic workspace have the same names as in the source relational dimension. Value of DISPLAY_NAME is the display name for the target dimension in the analytic workspace. Value of PLURAL_DISPLAY_NAME is the plural display name for the target dimension in the analytic workspace.

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.

Column	Datatype	NULL	Description
OWNER	VARCHAR2	NOT NULL	User name of the analytic workspace owner
AW_NUMBER	NUMBER	NOT NULL	Unique identifier within the database for the analytic workspace
AW_NAME	VARCHAR2		Name of the analytic workspace
OBJ_ID	NUMBER		Unique identifier for the object within the analytic workspace
OBJ_NAME	VARCHAR2		Name of the object
OBJ_TYPE	NUMBER		Data type of the object
PART_NAME	VARCHAR2		Name of the partition for the object

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.

Column	Datatype	NULL	Description
OWNER	VARCHAR2	NOT NULL	User name of the analytic workspace owner
AW_NUMBER	NUMBER	NOT NULL	Unique identifier within the database for the analytic workspace
AW_NAME	VARCHAR2		Name of the analytic workspace
OBJ_ID	NUMBER		Unique identifier for the object within the analytic workspace
OBJ_NAME	VARCHAR2		Name of the object
PROPERTY_NAME	VARCHAR2		Name of the property
PROPERTY_TYPE	VARCHAR2		Data type of the property value
PROPERTY_VALUE	VARCHAR2		Value of the property