Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
This chapter describes the OLAP Catalog metadata views. All OLAP metadata, whether created with the CWM2
PL/SQL packages or with Enterprise Manager, is presented in these views.
Note: A second set of views, called the OLAP API Metadata Reader views, presents much of the same information as the OLAP Catalog views. The Metadata Reader views are structured to facilitate fast queries by the OLAP API. See Chapter 16 for more information. |
This chapter discusses the following topics:
The OLAP Catalog read API consists of two sets of corresponding views:
ALL_
views displaying all valid OLAP metadata accessible to the current user.
DBA_
views displaying all OLAP metadata (both valid and invalid) in the entire database. DBA_
views are intended only for administrators.
Note: The OLAP Catalog tables are owned byOLAPSYS . To create OLAP metadata in these tables, the user must have the OLAP_DBA role. |
The columns of the ALL_
and DBA_
views are identical. Only the ALL_
views are listed in this chapter.
The following views show the basic dimensional model of OLAP metadata.
For more information on the logical model, see the Oracle OLAP Application Developer's Guide.
Table 5-1 OLAP Catalog Dimensional Model Views
View Name Synonym | Description |
---|---|
ALL_OLAP2_CATALOGS |
List all measure folders (catalogs) within the Oracle instance. |
ALL_OLAP2_CATALOG_ENTITY_USES |
Lists the measures within each measure folder. |
ALL_OLAP2_CUBES |
Lists all cubes in an Oracle instance. |
ALL_OLAP2_CUBE_DIM_USES |
Lists the dimensions within each cube. |
ALL_OLAP2_CUBE_MEASURES |
Lists the measures within each cube. |
ALL_OLAP2_CUBE_MEAS_DIM_USES |
Shows how each measure is aggregated along each of its dimensions. |
ALL_OLAP2_DIMENSIONS |
Lists all OLAP dimensions in an Oracle instance. |
ALL_OLAP2_DIM_ATTRIBUTES |
Lists the dimension attributes within each dimension. |
ALL_OLAP2_DIM_ATTR_USES |
Shows how level attributes are associated with each dimension attribute. |
ALL_OLAP2_DIM_HIERARCHIES |
Lists the hierarchies within each dimension. |
ALL_OLAP2_DIM_HIER_LEVEL_USES |
Show how levels are ordered within each hierarchy. |
ALL_OLAP2_DIM_LEVELS |
Lists the levels within each dimension. |
ALL_OLAP2_DIM_LEVEL_ATTRIBUTES |
Lists the level attributes within each level. |
ALL_OLAP2_ENTITY_DESC_USES |
Lists the reserved attributes that have application-specific meanings. Examples are dimension attributes that are used for long and short descriptions and time-series calculations (end date, time span, period ago, and so on). |
ALL_OLAP2_ENTITY_EXT_PARMS |
Lists the OLE DB for OLAP extended metadata descriptors. |
ALL_OLAP2_ENTITY_PARAMETERS |
Lists the OLE DB for OLAP metadata descriptors. |
The following views show how the basic dimensional model is mapped to relational tables or views.
Table 5-2 OLAP Catalog Mapping Views
View Synonym Name | Description |
---|---|
ALL_OLAP2_CUBE_MEASURE_MAPS |
Shows the mapping of each measure to a column. |
ALL_OLAP2_DIM_LEVEL_ATTR_MAPS |
Shows the mapping of each level attribute to a column. |
ALL_OLAP2_FACT_LEVEL_USES |
Shows the joins between dimension tables and fact tables in a star or snowflake schema. |
ALL_OLAP2_FACT_TABLE_GID |
Shows the Grouping ID column for each hierarchy in each fact table. |
ALL_OLAP2_HIER_CUSTOM_SORT |
Shows the default sort order for level columns within hierarchies. |
ALL_OLAP2_JOIN_KEY_COLUMN_USES |
Shows the joins between two levels in a hierarchy. |
ALL_OLAP2_LEVEL_KEY_COL_USES |
Shows the mapping of each level to a unique key column. |
ALL_OLAP2_AGGREGATION_USES
lists the aggregation operators associated with cubes that map to relational tables organized as star or snowflake schemas.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimensions of the cube. |
HIERARCHY_NAME |
VARCHAR2(30) |
Name of the hierarchies of the cube's dimensions. | |
DIM_HIER_COMBO_ID |
NUMBER |
NOT NULL |
Identifier of a hierarchy combination within the cube. |
AGGREGATION_NAME |
VARCHAR2(240) |
Name of the aggregation operator for this dimension. (See Table 1-10, "Aggregation Operators". | |
AGGREGATION_ORDER |
NUMBER |
The order of precedence of the aggregation operator. | |
TABLE_OWNER |
VARCHAR2(30) |
Owner of the table that contains the weightby factors for weighted operators. If the operator is not weighted, this column is null. | |
TABLE_NAME |
VARCHAR2(30) |
Name of the table that contains the weightby factors for weighted operators. If the operator is not weighted, this column is null. | |
COLUMN_NAME |
VARCHAR2(30) |
Name of the column that contains the weightby factors for weighted operators. If the operator is not weighted, this column is null. |
ALL_OLAP2_CATALOGS
lists all the measure folders (catalogs) within the Oracle instance.
Column | Data Type | NULL | Description |
---|---|---|---|
CATALOG_ID |
NUMBER |
NOT NULL |
ID of the measure folder. |
CATALOG_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the measure folder. |
PARENT_CATALOG_ID |
NUMBER |
ID of the parent measure folder. This column is null for measure folders at the root of the measure folder tree. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the measure folder. |
ALL_OLAP2_CATALOG_ENTITY_USES
lists the measures within each measure folder.
Column | Data Type | NULL | Description |
---|---|---|---|
CATALOG_ID |
NUMBER |
NOT NULL |
ID of the measure folder. |
ENTITY_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the measure's cube. |
ENTITY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the measure's cube. |
CHILD_ENTITY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the measure in the measure folder. |
ALL_OLAP2_CUBES
lists all cubes in an Oracle instance.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube that contains the measure. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube that contains the measure. |
INVALID |
VARCHAR2(2) |
NOT NULL |
Whether or not this cube is in an invalid state. See "Validating and Committing OLAP Metadata". |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the cube. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the cube. | |
MV_SUMMARYCODE |
VARCHAR2(2) |
If this cube has an associated materialized view, the MV summary code specifies whether it is in Grouping Set (groupingset ) or Rolled Up (rollup ) form.
|
ALL_OLAP2_CUBE_DIM_USES
lists the dimensions within each cube.
A dimension may be associated more than once with the same cube, but each association is specified in a separate row, under its own unique dimension alias.
Column | Data Type | NULL | Description |
---|---|---|---|
CUBE_DIMENSION_USE_ID |
NUMBER |
NOT NULL |
ID of the association between a cube and a dimension. |
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube. |
DIMENSION_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
DIMENSION_ALIAS |
VARCHAR2(30) |
Alias of the dimension, to provide unique identity of dimension use within the cube. | |
DEFAULT_CALC_HIERARCHY_NAME |
VARCHAR2(30) |
The default hierarchy to be used for drilling up or down within the dimension. | |
DEPENDENT_ON_DIM_USE_ID |
NUMBER |
ID of the cube/dimension association on which this cube/dimension association depends. |
ALL_OLAP2_CUBE_MEASURES
lists the measures within each cube.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube that contains the measure. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube that contains the measure. |
MEASURE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the measure. |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the measure. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the measure. |
ALL_OLAP2_CUBE_MEASURE_MAPS
shows the mapping of each measure to a column.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube. |
MEASURE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the measure contained in this cube. |
DIM_HIER_COMBO_ID |
NUMBER |
NOT NULL |
ID of the association between this measure and one combination of its dimension hierarchies. |
FACT_TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the fact table. |
FACT_TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the fact table. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the column in the fact table where this measure's data is stored. |
ALL_OLAP2_CUBE_MEAS_DIM_USES
shows how each measure is aggregated along each of its dimensions. The default aggregation method is addition.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube that contains this measure. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube that contain this measure. |
MEASURE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the measure. |
DIMENSION_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of a dimension associated with this measure. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
DIMENSION_ALIAS |
VARCHAR2(30) |
Alias of the dimension. | |
DEFAULT_AGGR_FUNCTION_USE_ID |
NUMBER |
The default aggregation method used to aggregate this measure's data over this dimension. If this column is null, the aggregation method is addition. |
ALL_OLAP2_DIMENSIONS
lists all the OLAP dimensions in the Oracle instance.
OLAP dimensions created with the CWM2
APIs have no association with database dimension objects. OLAP dimensions created in Enterprise Manager are based on database dimension objects.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
PLURAL_NAME |
VARCHAR2(30) |
Plural name for the dimension. Used for display. | |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the dimension. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the dimension. | |
DEFAULT_DISPLAY_HIERARCHY |
VARCHAR2(30) |
NOT NULL |
Default display hierarchy for the dimension. |
INVALID |
VARCHAR2(1) |
NOT NULL |
Whether or not the dimension is valid. See "Validating and Committing OLAP Metadata" |
DIMENSION_TYPE |
VARCHAR2(10) |
Not used. |
ALL_OLAP2_DIM_ATTRIBUTES
lists the dimension attributes within each dimension.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
ATTRIBUTE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension attribute. |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the dimension attribute. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the dimension attribute. | |
DESC_ID |
NUMBER |
If the attribute is reserved, its type is listed in this column. Examples of reserved dimension attributes are long and short descriptions and time-related attributes, such as end date, time span, and period ago. |
ALL_OLAP2_DIM_ATTR_USES
shows how level attributes are associated with each dimension attribute.
The same level attribute can be included in more than one dimension attribute.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
DIM_ATTRIBUTE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension attribute. |
LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of a level within the dimension. |
LVL_ATTRIBUTE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of an attribute for this level. This level attribute is included in the dimension attribute. |
ALL_OLAP2_DIM_HIERARCHIES
lists the hierarchies within each dimension.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
HIERARCHY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the hierarchy. |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the hierarchy. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the hierarchy. | |
SOLVED_CODE |
VARCHAR2(2) |
NOT NULL |
The solved code may be one of the following:
For information about mapping hierarchies with different solved codes, see "Joining Fact Tables with Dimension Tables". |
ALL_OLAP2_DIM_HIER_LEVEL_USES
shows how levels are ordered within each hierarchy.
Within separate hierarchies, the same parent level may be hierarchically related to a different child level.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
HIERARCHY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the hierarchy. |
PARENT_LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the parent level. |
CHILD_LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the child level. |
POSITION |
NUMBER |
NOT NULL |
Position of this parent-child relationship within the hierarchy, with position 1 being the most detailed. |
ALL_OLAP2_DIM_LEVELS
lists the levels within each dimension.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension containing this level. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension containing this level. |
LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the level. |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the level. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the level. | |
LEVEL_TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension table that contains the columns for this level. |
LEVEL_TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension table that contains the columns for this level. |
ALL_OLAP2_DIM_LEVEL_ATTRIBUTES
lists the level attributes within each level.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension containing the level. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension containing the level. |
ATTRIBUTE_NAME |
VARCHAR2(30) |
Name of the level attribute. If no attribute name is specified, the column name is used. | |
DISPLAY_NAME |
VARCHAR2(30) |
Display name for the level attribute. | |
DESCRIPTION |
VARCHAR2(2000) |
Description of the level attribute. | |
DETERMINED_BY_LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the level. |
ALL_OLAP2_DIM_LEVEL_ATTR_MAPS
shows the mapping of each level attribute to a column.
The mapping of level attributes to levels is dependent on hierarchy. The same level may have different attributes when it is used in different hierarchies.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
HIERARCHY_NAME |
VARCHAR2(30) |
Name of the hierarchy containing this level. | |
ATTRIBUTE_NAME |
VARCHAR2(30) |
Name of a dimension attribute grouping containing this level attribute. | |
LVL_ATTRIBUTE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the level attribute, or name of the column if the level attribute name is not specified. |
LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the level. |
TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension table containing the level and level attribute. |
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension table containing the level and level attribute columns. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the column containing the level attribute. |
DTYPE |
VARCHAR2(10) |
NOT NULL |
Data type of the column containing the level attribute. |
ALL_OLAP2_ENTITY_DESC_USES
lists the reserved attributes and shows whether or not dimensions are time dimensions.
Column | Data Type | NULL | Description |
---|---|---|---|
DESCRIPTOR_ID |
NUMBER |
NOT NULL |
Name of the reserved attribute or dimension type.
The reserved dimension attributes are listed in Table 11-1, "Reserved Dimension Attributes". The reserved level attributes are listed in Table 14-1, "Reserved Level Attributes". |
ENTITY_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the metadata entity. |
ENTITY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the metadata entity. |
CHILD_ENTITY_NAME |
VARCHAR2(30) |
Name of the child entity (if applicable). A dimension attribute is a child entity of a dimension. A level attribute is a child entity of a dimension attribute. | |
SECONDARY_CHILD_ENTITY_NAME |
VARCHAR2(30) |
Name of the secondary child entity name (if applicable). A dimension attribute is a child entity of a dimension. A level attribute is a child entity of a dimension attribute. A level attribute could be the secondary child entity of a dimension. |
ALL_OLAP2_ENTITY_EXT_PARMS
lists the following OLE DB metadata descriptors: Default Member
, Dense Indicator
, Fact Table Join
, and Estimated Cardinality
.
The OLE DB metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".
Column | Data Type | NULL | Description |
---|---|---|---|
DESCRIPTOR_ID |
NUMBER (38) |
ID of the metadata descriptor. | |
DESCRIPTOR_NAME |
VARCHAR2(240) |
One of the following metadata descriptor names:
| |
ENTITY_OWNER |
VARCHAR2(240) |
Schema of the cube or dimension. | |
ENTITY_NAME |
VARCHAR2(240) |
Name of the cube or dimension. | |
CHILD_ENTITY_NAME |
VARCHAR2(30) |
Name of a child of the cube or dimension. For example, a dimension attribute is a child of a dimension, and a measure is a child of a cube. If the descriptor applies to a cube or dimension, this parameter is NULL. | |
SECONDARY_CHILD_ENTITY_NAME |
VARCHAR2(30) |
Name of a child of the child entity. For example, a level attribute is a child of a level, which is a child of a dimension. If the descriptor applies to a cube or dimension, or a child of a cube or dimension, this parameter is NULL. | |
PARAMETER_NAME |
VARCHAR2(80) |
User-defined label for the descriptor. | |
PARAMETER_VALUE |
VARCHAR2(4000) |
Value of the descriptor. For the Fact Table Join descriptor, this parameter contains the table owner. | |
PARAMETER_VALUE2 |
VARCHAR2(4000) |
Table name for Fact Table Join descriptor. | |
PARAMETER_VALUE3 |
VARCHAR2(4000) |
Column name for Fact Table Join descriptor. | |
PARAMETER_VALUE4 |
VARCHAR2(4000) |
Hierarchy name for Fact Table Join descriptor. | |
POSITION |
NUMBER |
Position in mult-column key for Fact Table Join descriptor. |
ALL_OLAP2_ENTITY_PARAMETERS
lists the OLE DB metadata descriptors not listed in ALL_OLAP2_ENTITY_EXT_PARMS. Additionally, it includes all the descriptors from ALL_OLAP2_ENTITY_DESC_USES.
The OLE DB metadata descriptors are described in Table 8-1, "OLAP Catalog Metadata Descriptors".
Column | Data Type | NULL | Description |
---|---|---|---|
DESCRIPTOR_ID |
NUMBER (38) |
ID of metadata descriptor. | |
DESCRIPTOR_NAME |
VARCHAR2(240) |
Name of the metadata descriptor. | |
ENTITY_OWNER |
VARCHAR2(240) |
Schema of the cube or dimension. | |
ENTITY_NAME |
VARCHAR2(240) |
Name of the cube or dimension. | |
CHILD_ENTITY_NAME |
VARCHAR2(240) |
Name of a child of the cube or dimension. For example, a dimension attribute is a child of a dimension, and a measure is a child of a cube. If the descriptor applies to a cube or dimension, this parameter is NULL. | |
SECONDARY_CHILD_ENTITY_NAME |
VARCHAR2(240) |
Name of a child of the child entity. For example, a level attribute is a child of a level, which is a child of a dimension. If the descriptor applies to a cube or dimension, or a child of a cube or dimension, this parameter is NULL. | |
PARAMETER_NAME |
VARCHAR2(30) |
User-defined label for the descriptor. | |
PARAMETER_VALUE |
VARCHAR2(80) |
Value of the descriptor. |
ALL_OLAP2_FACT_LEVEL_USES
shows the joins between dimension tables and fact tables in a star or snowflake schema. For more information, see "Joining Fact Tables with Dimension Tables".
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube. |
DIMENSION_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
NUMBER |
NOT NULL |
Name of the dimension. |
DIMENSION_ALIAS |
VARCHAR2(30) |
Dimension alias (if applicable). | |
HIERARCHY_NAME |
NOT NULL |
Name of the hierarchy. | |
DIM_HIER_COMBO_ID |
NUMBER |
NOT NULL |
ID of the dimension hierarchy combination associated with this fact table. |
LEVEL_NAME |
VARCHAR2(30) |
Name of the level within the hierarchy where the mapping occurs. | |
FACT_TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the fact table. |
FACT_TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the fact table. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the foreign key column in the fact table. |
POSITION |
NUMBER |
Position of this column within a multi-column key. | |
DIMENSION_KEYMAP_TYPE |
VARCHAR2(30) |
NOT NULL |
Type of key mapping for the fact table. Values may be:
|
FOREIGN_KEY_NAME |
VARCHAR2(30) |
Name of the foreign key constraint applied to the foreign key column. Constraints are not used by the CWM2 APIs. |
.
ALL_OLAP2_FACT_TABLE_GID
shows the Grouping ID column for each hierarchy in each fact table. For more information, see "Grouping ID Column".
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the cube. |
CUBE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the cube. |
DIMENSION_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension |
HIERARCHY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the hierarchy. |
DIM_HIER_COMBO_ID |
NUMBER |
NOT NULL |
ID of the dimension-hierarchy association. |
FACT_TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the fact table. |
FACT_TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the fact table. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the GID column. |
ALL_OLAP2_HIER_CUSTOM_SORT
shows the sort order for level columns within hierarchies. Custom sorting information is optional.
Custom sorting information specifies how to sort the members of a hierarchy based on columns in the dimension table. The specific columns in the dimension tables may be the same as the key columns or may be related attribute columns.
Custom sorting can specify that the column be sorted in ascending or descending order, with nulls first or nulls last. Custom sorting can be applied at multiple levels of a dimension.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
HIERARCHY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the hierarchy. |
TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension table. |
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension table. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the column to be sorted. |
POSITION |
NUMBER |
NOT NULL |
Represents the position within a multi-column SORT_POSITION . In most cases, a single column represents SORT_POSITION , and the value of POSITION is 1. |
SORT_POSITION |
NUMBER |
NOT NULL |
Position within the sort order of the level to be sorted. |
SORT_ORDER |
VARCHAR2(4) |
NOT NULL |
Sort order. Can be either Ascending or Descending . |
NULL_ORDER |
VARCHAR2(5) |
NOT NULL |
Where to insert null values in the sort order. Can be either Nulls First or Nulls Last . |
ALL_OLAP2_JOIN_KEY_COLUMN_USES
shows the joins between two levels in a hierarchy. The joins are between dimension tables in a snowflake schema, and between level columns in a star schema.
If the level is mapped to more than one column, each column mapping is represented in a separate row in the view.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
HIERARCHY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the hierarchy. |
CHILD_LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Child level in the hierarchy. |
TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension table. |
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension table. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the child level column in the dimension table. In a star schema, this is the column associated with CHILD_LEVEL_NAME . In a snowflake schema, this is the parent column of CHILD_LEVEL_NAME in the same dimension table. |
POSITION |
NUMBER |
Position of column within the key. Applies to multi-column keys only (where the level is mapped to more than one column). | |
JOIN_KEY_TYPE |
VARCHAR2(30) |
NOT NULL |
The key is of type SNOWFLAKE if the join key is a logical foreign key. The key is of type STAR if the join key refers to a column within the same table. |
ALL_OLAP2_LEVEL_KEY_COL_USES
shows the mapping of each level to a unique key column.
If the level is mapped to more than one column, each column mapping is represented in a separate row in the view.
Column | Data Type | NULL | Description |
---|---|---|---|
OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension. |
DIMENSION_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension. |
HIERARCHY_NAME |
VARCHAR2(30) |
Name of the hierarchy that includes this level. | |
CHILD_LEVEL_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the level. |
TABLE_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the dimension table. |
TABLE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the dimension table. |
COLUMN_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the column that stores CHILD_LEVEL_NAME . |
POSITION |
NUMBER |
Position of the column within the key. Applies to multi-column keys only (where the level is mapped to more than one column). |