3 Active Catalog Views

This chapter describes the relational views of standard form objects in analytic workspaces. Within the workspace, standard form objects are automatically created and populated by procedures in the DBMS_AWM package.

See Also:

This chapter discusses the following topics:

Standard Form Active Catalog
Example: Query an Analytic Workspace Cube
Summary of Active Catalog Views

3.1 Standard Form Active Catalog

OLAP processing depends on a data model composed of cubes, measures, dimensions, hierarchies, levels, and attributes. OLAP Catalog metadata defines this logical model for relational sources. Standard form metadata defines the logical model within analytic workspaces.

Procedures in the DBMS_AWM package create and maintain standard form metadata when creating and refreshing dimensions and cubes in analytic workspaces. Whereas OLAP Catalog metadata must be explicitly created by a DBA, standard form metadata is actively generated as part of workspace management. Views of this metadata are commonly referred to as the Active Catalog, because they are populated with information that is automatically generated within analytic workspaces.

Active Catalog views use the OLAP_TABLE function to return workspace data in relational format. See Chapter 26 for more information on OLAP_TABLE.

Note:

To improve the performance of queries against the Active Catalog, you can refresh the cached metadata tables that underlie the MRV_OLAP2_AW views. For more information, see "Views of Cached Active Catalog Metadata".

3.1.1 Standard Form Classes

Each standard form workspace object belongs to one of four classes:

Implementation class. Objects in this class implement the logical model.
Catalogs class. Objects in this class hold information about the logical model.
Features class. Objects in this class hold information about specific objects in the logical model.
Extensions class. Objects in this class are proprietary.

3.1.2 Active Catalog and Standard Form Classes

The primary source of information for the Active Catalog views is objects in the Catalogs class. This includes a list of all the cubes, measures, dimensions, levels, and attributes in analytic workspaces.

Active Catalog views also provide information that associates logical objects from the Catalogs class with their source objects in the OLAP Catalog and with their containers in the Implementation class.

Finally, two Active Catalog views provide all the standard form objects and all the properties of those objects.

Note:

Active Catalog views provide information about standard form objects in all analytic workspaces accessible to the current user.

See Also:

Oracle OLAP Application Developer's Guide for information about standard form analytic workspaces
Oracle OLAP DML Reference for information about the OLAP DML and the native objects within analytic workspaces

3.2 Example: Query an Analytic Workspace Cube

Example 3-1 uses the XADEMO cube ANALYTIC_CUBE to illustrate two Active Catalog views.

Example 3-1 Query the Active Catalog for Information about a Workspace Cube

The following statements create the dimensions in the analytic workspace XADEMO.MY_AW.

execute dbms_awm.create_awdimension 
           ('XADEMO','CHANNEL','XADEMO', 'MY_AW', 'AW_CHAN');
execute dbms_awm.create_awdimension 
          ('XADEMO','PRODUCT','XADEMO', 'MY_AW', 'AW_PROD');
execute dbms_awm.create_awdimension 
          ('XADEMO','GEOGRAPHY','XADEMO', 'MY_AW', 'AW_GEOG');
execute dbms_awm.create_awdimension 
          ('XADEMO','TIME','XADEMO', 'MY_AW', 'AW_TIME');

You can view the logical dimensions in the analytic workspace with the following query.

SQL>select * from ALL_OLAP2_AW_DIMENSIONS;

AW_OWNER  AW_NAME  AW_LOGICAL_NAME  AW_PHYSICAL_OBJECT SOURCE_OWNER  SOURCE_NAME
-------- --------  ---------------  ------------------ -----------   ----------
XADEMO     MY_AW      AW_CHAN       AW_CHAN            XADEMO        CHANNEL
XADEMO     MY_AW      AW_PROD       AW_PROD            XADEMO        PRODUCT
XADEMO     MY_AW      AW_GEOG       AW_GEOG            XADEMO        GEOGRAPHY
XADEMO     MY_AW      AW_TIME       AW_TIME            XADEMO        TIME

The following statement creates the cube.

execute dbms_awm.create_awcube 
          ('XADEMO','ANALYTIC_CUBE','XADEMO', 'MY_AW', 'MY_ANALYTIC_CUBE');

You can view the logical cube in the analytic workspace with the following query.

SQL>select * from ALL_OLAP2_AW_CUBES;

AW_OWNER AW_NAME AW_LOGICAL_NAME  AW_PHYSICAL_OBJECT  SOURCE_OWNER  SOURCE_NAME
-------- ------- ---------------  ------------------  ------------  -----------
XADEMO   MY_AW   MY_ANALYTIC_CUBE MY_ANALYTIC_CUBE    XADEMO       ANALYTIC_CUBE

The following query returns the analytic workspace cube with its associated dimensions.

SQL>select * from ALL_OLAP2_AW_CUBE_DIM_USES;

AW_OWNER AW_NAME AW_LOGICAL_NAME  DIMENSION_ DIMENSION_ DIMENSION_   DIMENSION_
                                  AW_OWNER   AW_NAME    SOURCE_OWNER SOURCE_NAME
-------- ------- ---------------- ---------  ---------- ------------ -----------
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_CHAN    XADEMO       CHANNEL
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_GEOG    XADEMO       GEOGRAPHY
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_PROD    XADEMO       PRODUCT
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_TIME    XADEMO       TIME

3.3 Summary of Active Catalog Views

The analytic workspace Active Catalog views are summarized in the following table.

Table 3-1 Active Catalog Views

PUBLIC Synonym	Description
ALL_OLAP2_AWS	List of analytic workspaces.
ALL_OLAP2_AW_ATTRIBUTES	List of dimension attributes in analytic workspaces.
ALL_OLAP2_AW_CUBES	List of cubes in analytic workspaces.
ALL_OLAP2_AW_CUBE_AGG_LVL	List of levels in aggregation plans in analytic workspaces.
ALL_OLAP2_AW_CUBE_AGG_MEAS	List of measures in aggregation plans in analytic workspaces.
ALL_OLAP2_AW_CUBE_AGG_OP	List of aggregation operators in aggregation plans in analytic workspaces.
ALL_OLAP2_AW_CUBE_AGG_SPECS	List of aggregation plans in analytic workspaces.
ALL_OLAP2_AW_CUBE_DIM_USES	List of cubes with their associated dimensions in analytic workspaces.
ALL_OLAP2_AW_CUBE_MEASURES	List of cubes with their associated measures in analytic workspaces.
ALL_OLAP2_AW_DIMENSIONS	List of dimensions in analytic workspaces.
ALL_OLAP2_AW_DIM_HIER_LVL_ORD	List of hierarchical levels in analytic workspaces.
ALL_OLAP2_AW_DIM_LEVELS	List of levels in analytic workspaces.
ALL_OLAP2_AW_PHYS_OBJ	List of standard form objects in analytic workspaces.
ALL_OLAP2_AW_PHYS_OBJ_PROP	List of properties associated with standard form objects in analytic workspaces.

3.4 ALL_OLAP2_AWS

ALL_OLAP2_AWS provides a list of all the analytic workspaces accessible to the current user. This includes both standard form and non-standard analytic workspaces.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the analytic workspace.
`AW`	`VARCHAR2(30)`		Name of the analytic workspace.
`AW_NUMBER`	`NUMBER`	`NOT_NULL`	Unique identifier for the analytic workspace.

3.5 ALL_OLAP2_AW_ATTRIBUTES

ALL_OLAP2_AW_ATTRIBUTES lists attributes in standard form analytic workspaces.

The attributes associated with a dimension are created in an analytic workspace by the DBMS_AWM.REFRESH_AWDIMENSION procedure. See also "Refreshing the Dimension's Metadata".

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_DIMENSION_NAME`	`VARCHAR2(1000)`	Name of the dimension in the analytic workspace.
`AW_LOGICAL_NAME`	`VARCHAR2(90)`	Logical name for the attribute in the analytic workspace.
`AW_PHYSICAL_OBJECT`	`VARCHAR2(1000)`	Standard form name for the attribute in the analytic workspace.
`DISPLAY_NAME`	`VARCHAR2(1000)`	Display name for the attribute.
`DESCRIPTION`	`VARCHAR2(1000)`	Description of the attribute.
`ATTRIBUTE_TYPE`	`VARCHAR2(1000)`	Type of attribute. See Table 11-1, "Reserved Dimension Attributes".
`SOURCE_OWNER`	`VARCHAR2(1000)`	Owner of the source attribute in the OLAP Catalog.
`SOURCE_DIMENSION_NAME`	`VARCHAR2(1000)`	Name of the source dimension in the OLAP Catalog.
`SOURCE_NAME`	`VARCHAR2(1000)`	Name of the source attribute in the OLAP Catalog.

3.6 ALL_OLAP2_AW_CUBES

ALL_OLAP2_AW_CUBES lists the cubes in standard form analytic workspaces.

Standard form cubes are created in analytic workspaces by the DBMS_AWM.CREATE_AWCUBE procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_LOGICAL_NAME`	`VARCHAR2(90)`	Logical name for the cube in the analytic workspace.
`AW_PHYSICAL_OBJECT`	`VARCHAR2(1000)`	Standard form name for the cube in the analytic workspace.
`SOURCE_OWNER`	`VARCHAR2(1000)`	Owner of the source cube in the OLAP Catalog.
`SOURCE_NAME`	`VARCHAR2(1000)`	Name of the source cube in the OLAP Catalog.

3.7 ALL_OLAP2_AW_CUBE_AGG_LVL

ALL_OLAP2_AW_CUBE_AGG_LVL lists the levels in aggregation specifications in standard form analytic workspaces.

Aggregation specifications determine how summary data will be calculated and stored in the analytic workspace. Levels are added to aggregation specifications by the DBMS_AWM.ADD_AWCUBEAGG_LEVEL procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_CUBE_NAME`	`VARCHAR2(90)`	Name of a cube in the analytic workspace.
`AW_AGGSPEC_NAME`	`VARCHAR2(1000)`	Name of an aggregation specification for the cube.
`AW_DIMENSION_NAME`	`VARCHAR2(1000)`	Name of a workspace dimension of the cube.
`AW_LEVEL_NAME`	`VARCHAR2(1000)`	Name of a workspace level of the dimension. This level is in the aggregation specification.

3.8 ALL_OLAP2_AW_CUBE_AGG_MEAS

ALL_OLAP2_AW_CUBE_AGG_MEAS lists the measures in aggregation specifications in standard form analytic workspaces.

Aggregation specifications determine how summary data will be calculated and stored in the analytic workspace. Measures are added to aggregation specifications by the DBMS_AWM.ADD_AWCUBEAGG_SPEC_MEASURE procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_CUBE_NAME`	`VARCHAR2(90)`	Name of a cube in the analytic workspace.
`AW_AGGSPEC_NAME`	`VARCHAR2(1000)`	Name of an aggregation specification for the cube.
`AW_MEASURE_NAME`	`VARCHAR2(1000)`	Name of a workspace measure of the cube. This measure is in the aggregation specification

3.9 ALL_OLAP2_AW_CUBE_AGG_OP

ALL_OLAP2_AW_CUBE_AGG_OP lists the aggregation operators in aggregation specifications in standard form analytic workspaces.

Aggregation specifications determine how summary data will be calculated and stored in the analytic workspace. Aggregation operators are added to aggregation specifications by the DBMS_AWM.SET_AWCUBEAGG_SPEC_AGGOP procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_CUBE_NAME`	`VARCHAR2(90)`	Name of a cube in the analytic workspace.
`AW_MEASURE_NAME`	`VARCHAR2`	Name of a workspace measure to aggregate.
`AW_AGGSPEC_NAME`	`VARCHAR2(1000)`	Name of an aggregation specification for the cube.
`AW_DIMENSION_NAME`	`VARCHAR2(1000)`	Name of a workspace dimension of the cube.
`OPERATOR`	`VARCHAR2(1000)`	Operator for aggregation along this dimension. See Table 1-10, "Aggregation Operators" for a list of valid operators.

3.10 ALL_OLAP2_AW_CUBE_AGG_SPECS

ALL_OLAP2_AW_CUBE_AGG_SPECS lists the aggregation specifications in standard form analytic workspaces.

Aggregation specifications determine how summary data will be calculated and stored in the analytic workspace. Aggregation specifications are created by the DBMS_AWM.CREATE_AWCUBEAGG_SPEC procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_CUBE_NAME`	`VARCHAR2(90)`	Name of the cube in the analytic workspace.
`AW_AGGSPEC_NAME`	`VARCHAR2(1000)`	Name of an aggregation plan for the cube.

3.11 ALL_OLAP2_AW_CUBE_DIM_USES

ALL_OLAP2_AW_CUBE_DIM_USES lists the dimensions of cubes in standard form analytic workspaces.

Dimensions are associated with workspace cubes by the DBMS_AWM.CREATE_AWCUBE procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_LOGICAL_NAME`	`VARCHAR2(90)`	Name of a cube in the analytic workspace.
`DIMENSION_AW_OWNER`	`VARCHAR2(1000)`	Owner of a workspace dimension of the cube.
`DIMENSION_AW_NAME`	`VARCHAR2(1000)`	Name of a workspace dimension of the cube.
`DIMENSION_SOURCE_OWNER`	`VARCHAR2(1000)`	Owner of the source dimension in the OLAP Catalog
`DIMENSION_SOURCE_NAME`	`VARCHAR2(1000)`	Name of the source dimension in the OLAP Catalog.

3.12 ALL_OLAP2_AW_CUBE_MEASURES

ALL_OLAP2_AW_CUBE_MEASURES lists the measures of cubes in standard form analytic workspaces.

Measures are associated with cubes by the DBMS_AWM.REFRESH_AWCUBE procedure. If individual measures were not specified by a call to DBMS_AWM.ADD_AWCUBELOAD_SPEC_MEASURE, then all the cube's measures are loaded when the cube is refreshed.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_CUBE_NAME`	`VARCHAR2(90)`	Name of a cube in the analytic workspace.
`AW_MEASURE_NAME`	`VARCHAR2(1000)`	Logical name of a measure of the cube.
`AW_PHYSICAL_OBJECT`	`VARCHAR2(1000)`	Standard form name of the measure.
`MEASURE_SOURCE_NAME`	`VARCHAR2(1000)`	Name of the source measure in the OLAP Catalog.
`DISPLAY_NAME`	`VARCHAR2(1000)`	Display name for the measure in the analytic workspace.
`DESCRIPTION`	`VARCHAR2(1000)`	Description of the measure in the analytic workspace.
`IS_AGGREGATEABLE`	`VARCHAR2(1000)`	Whether or not this measure can be aggregated with the OLAP DML `AGGREGATE` command. The value is `YES` if the measure is implemented as an OLAP variable or if its underlying storage is a variable. For example, the measure could be implemented as a formula whose value is stored in a variable.

3.13 ALL_OLAP2_AW_DIMENSIONS

ALL_OLAP2_AW_DIMENSIONS lists the dimensions in standard form analytic workspaces.

Workspace dimensions are created by the DBMS_AWM.CREATE_AWDIMENSION procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_LOGICAL_NAME`	`VARCHAR2(90)`	Logical name of the dimension in the analytic workspace.
`AW_PHYSICAL_NAME`	`VARCHAR2(1000)`	Standard form name of the dimension in the analytic workspace.
`SOURCE_OWNER`	`VARCHAR2(1000)`	Owner of the source dimension in the OLAP Catalog.
`SOURCE_NAME`	`VARCHAR2(1000)`	Name of the source dimension in the OLAP Catalog.

3.14 ALL_OLAP2_AW_DIM_HIER_LVL_ORD

ALL_OLAP2_AW_DIM_HIER_LVL_ORD lists the levels in hierarchies in standard form analytic workspaces. It includes the position of each level within the hierarchy.

Workspace dimensions are created by the DBMS_AWM.CREATE_AWDIMENSION procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_DIMENSION_NAME`	`VARCHAR2(90)`	Name of a dimension in the analytic workspace.
`AW_HIERARCHY_NAME`	`VARCHAR2(1000)`	Name of a hierarchy of the workspace dimension.
`IS_DEFAULT_HIER`	`VARCHAR2(1000)`	Whether or not this hierarchy is the default hierarchy
`AW_LEVEL_NAME`	`VARCHAR2(1000)`	Name of a level of the workspace hierarchy.
`POSITION`	`NUMBER`	The position of the level in the hierarchy

3.15 ALL_OLAP2_AW_DIM_LEVELS

ALL_OLAP2_AW_DIM_LEVELS lists the levels of dimensions in standard form analytic workspaces.

Workspace levels are created by the DBMS_AWM.CREATE_AWDIMENSION procedure.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_LOGICAL_NAME`	`VARCHAR2(90)`	Name of a dimension in the analytic workspace.
`LEVEL_NAME`	`VARCHAR2(1000)`	Name of a workspace level of the dimension.
`DISPLAY_NAME`	`VARCHAR2(1000)`	Display name of the level.
`DESCRIPTION`	`VARCHAR2(1000)`	Description of the level.

3.16 ALL_OLAP2_AW_PHYS_OBJ

ALL_OLAP2_AW_PHYS_OBJ lists the standard form objects in analytic workspaces.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_OBJECT_NAME`	`VARCHAR2(90)`	Name of the standard form object in the analytic workspace.
`AW_OBJECT_TYPE`	`VARCHAR2(1000)`	Type of the standard form object. The type may be any of the native object types that can be defined with the OLAP DML, including: dimensions, relations, variables, formulas, composites, and valuesets.
`AW_OBJECT_DATATYPE`	`VARCHAR2(1000)`	Data type of the standard form object. The data type may be any of the native types supported by the OLAP DML, including text, boolean, or integer, or it may be a defined type specific to standard form.

3.17 ALL_OLAP2_AW_PHYS_OBJ_PROP

ALL_OLAP2_AW_PHYS_OBJ_PROP lists the standard form objects with their properties.

Column	Datatype	Description
`AW_OWNER`	`VARCHAR2(30)`	Owner of the analytic workspace.
`AW_NAME`	`VARCHAR2(30)`	Name of the analytic workspace.
`AW_OBJECT_NAME`	`VARCHAR2(90)`	Name of the standard form object in the analytic workspace.
`AW_PROP_NAME`	`VARCHAR2(1000)`	Name of a property of the standard form object.
`AW_PROP_VALUE`	`VARCHAR2(1000)`	Value of the property.