Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The CWM2_OLAP_TABLE_MAP
package provides procedures for mapping OLAP metadata entities to columns in your data warehouse dimension tables and fact tables.
This chapter discusses the following topics:
The CWM2_OLAP_TABLE_MAP
package provides procedures for linking OLAP metadata entities to columns in fact tables and dimension tables and for establishing the join relationships between a fact table and its associated dimension tables.
Dimension levels and level attributes are mapped to columns in dimension tables. Typically, they are mapped by hierarchy. Measures are mapped to columns in fact tables.
The join relationship between the fact table and dimension tables may be specified for solved or unsolved data stored in a single fact table, or for solved data stored in a single fact table for each hierarchy combination.
The following statements map the four levels of the STANDARD
hierarchy in the XADEMO.PRODUCT_AW
dimension to columns in the XADEMO_AW_VIEW_PRODUCT
dimension table. A long description attribute is mapped for each level.
execute cwm2_olap_table_map.Map_DimTbl_HierLevel ('XADEMO', 'PRODUCT_AW', 'STANDARD', 'L4', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'L4', 'L3'); execute cwm2_olap_table_map.Map_DimTbl_HierLevelAttr ('XADEMO', 'PRODUCT_AW', 'Long Description', 'STANDARD', 'L4', 'Long Description', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'PROD_STD_LLABEL'); execute cwm2_olap_table_map.Map_DimTbl_HierLevel ('XADEMO', 'PRODUCT_AW', 'STANDARD', 'L3', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'L3', 'L2'); execute cwm2_olap_table_map.Map_DimTbl_HierLevelAttr ('XADEMO', 'PRODUCT_AW', 'Long Description', 'STANDARD', 'L3', 'Long Description', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'PROD_STD_LLABEL'); execute cwm2_olap_table_map.Map_DimTbl_HierLevel ('XADEMO', 'PRODUCT_AW', 'STANDARD', 'L2', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'L2', 'L1'); execute cwm2_olap_table_map.Map_DimTbl_HierLevelAttr ('XADEMO', 'PRODUCT_AW', 'Long Description', 'STANDARD', 'L2', 'Long Description', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'PROD_STD_LLABEL'); execute cwm2_olap_table_map.Map_DimTbl_HierLevel ('XADEMO', 'PRODUCT_AW', 'STANDARD', 'L1', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'L1', null); execute cwm2_olap_table_map.Map_DimTbl_HierLevelAttr ('XADEMO', 'PRODUCT_AW', 'Long Description', 'STANDARD', 'L1', 'Long Description', 'XADEMO', 'XADEMO_AW_VIEW_PRODUCT', 'PROD_STD_LLABEL');
The following statement maps the dimension join keys for a cube named ANALYTIC_CUBE_AW
in the XADEMO
schema. Join key relationships are specified for four dimension/hierarchy combinations:
PRODUCT_AW/STANDARD CHANNEL_AW/STANDARD TIME_AW/YTD GEOGRAPHY_AW/CONSOLIDATED.
The fact table is called XADEMO_AW_SALES_VIEW_4
. It stores lowest level data and embedded totals for all level combinations.
execute cwm2_olap_table_map.Map_FactTbl_LevelKey ('XADEMO', 'ANALYTIC_CUBE_AW','XADEMO', 'XADEMO_AW_SALES_VIEW_4', 'ET', 'DIM:XADEMO.PRODUCT_AW/HIER:STANDARD/GID:PRODUCT_GID/LVL:L4/COL:PRODUCT_ET; DIM:XADEMO.CHANNEL_AW/HIER:STANDARD/GID:CHANNEL_GID/LVL:STANDARD_1/COL:CHANNEL_ET; DIM:XADEMO.TIME_AW/HIER:YTD/GID:TIME_YTD_GID/LVL:L3/COL:TIME_YTD_ET; DIM:XADEMO.GEOGRAPHY_AW/HIER:CONSOLIDATED/GID:GEOG_CONS_GID/LVL:L4/COL:GEOG_CONS_ET;');
The following statement maps the F.SALES_AW
measure to the SALES
column in the fact table.
execute cwm2_olap_table_map.Map_FactTbl_Measure ('XADEMO', 'ANALYTIC_CUBE_AW', 'F.SALES_AW', 'XADEMO', 'XADEMO_AW_SALES_VIEW_4', 'SALES', 'DIM:XADEMO.PRODUCT_AW/HIER:STANDARD/LVL:L4/COL:PRODUCT_ET; DIM:XADEMO.CHANNEL_AW/HIER:STANDARD/LVL:STANDARD_1/COL:CHANNEL_ET; DIM:XADEMO.TIME_AW/HIER:YTD/LVL:L3/COL:TIME_YTD_ET; DIM:XADEMO.GEOGRAPHY_AW/HIER:CONSOLIDATED/LVL:L4/COL:GEOG_CONS_ET;');
Table 18-1 CWM2_OLAP_TABLE_MAP
Subprogram | Description |
---|---|
MAP_DIMTBL_HIERLEVELATTR Procedure |
Maps a hierarchical level attribute to a column in a dimension table. |
MAP_DIMTBL_HIERLEVEL Procedure |
Maps a hierarchical level to one or more columns in a dimension table. |
MAP_DIMTBL_HIERSORTKEY Procedure |
Sorts the members of a hierarchy within a column of a dimension table. |
MAP_DIMTBL_LEVELATTR Procedure |
Maps a non-hierarchical level attribute to a column in a dimension table |
MAP_DIMTBL_LEVEL Procedure |
Maps a non-hierarchical level to one or more columns in a dimension table. |
MAP_FACTTBL_LEVELKEY Procedure |
Maps the dimensions of a cube to a fact table. |
MAP_FACTTBL_MEASURE Procedure |
Maps a measure to a column in a fact table. |
REMOVEMAP_DIMTBL_HIERLEVELATTR Procedure |
Removes the mapping of a hierarchical level attribute from a column in a dimension table. |
REMOVEMAP_DIMTBL_HIERLEVEL Procedure |
Removes the mapping of a hierarchical level from one or more columns in a dimension table. |
REMOVEMAP_DIMTBL_HIERSORTKEY Procedure |
Removes custom sorting criteria associated with columns in a dimension table. |
REMOVEMAP_DIMTBL_LEVELATTR Procedure |
Removes the mapping of a non-hierarchical level attribute from a column in a dimension table. |
REMOVEMAP_DIMTBL_LEVEL Procedure |
Removes the mapping of a non-hierarchical level from one or more columns in a dimension table. |
REMOVEMAP_FACTTBL_LEVELKEY Procedure |
Removes the mapping of a cube's dimensions from a fact table. |
REMOVEMAP_FACTTBL_MEASURE Procedure |
Removes the mapping of a measure from a column in a fact table. |
This procedure maps a level attribute to a column in a dimension table.
The attribute being mapped is associated with a level in the context of a hierarchy.
Syntax
MAP_DIMTBL_HIERLEVELATTR ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, dimension_attribute_name IN VARCHAR2, hierarchy_name IN VARCHAR2, level_name IN VARCHAR2, level_attribute_name IN VARCHAR2, table_owner IN VARCHAR2, table_name IN VARCHAR2, attrcol IN VARCHAR2);
Parameters
Table 18-2 MAP_DIMTBL_HIERLEVELATTR Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
dimension_attribute_name |
Name of the dimension attribute. |
hierarchy_name |
Name of the hierarchy. |
level_name |
Name of the level. |
level_attribute_name |
Name of the level attribute associated with this level. |
table_owner |
Owner of the dimension table. |
table_name |
Name of the dimension table. |
attrcol |
Column in the dimension table to which this level attribute should be mapped. |
This procedure maps a level to one or more columns in a dimension table.
The level being mapped is identified within the context of a hierarchy.
Syntax
MAP_DIMTBL_HIERLEVEL ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, hierarchy_name IN VARCHAR2, level_name IN VARCHAR2, table_owner IN VARCHAR2, table_name IN VARCHAR2, keycol IN VARCHAR2, parentcol IN VARCHAR2 DEFAULT NULL);
Parameters
Table 18-3 MAP_DIMTBL_HIERLEVEL Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
hierarchy_name |
Name of the hierarchy. |
level_name |
Name of the level. |
table_owner |
Owner of the dimension table. |
table_name |
Name of the dimension table. |
keycol |
Column in the dimension table to which this level should be mapped. This column will be the key for this level column in the fact table.
If the level is stored in more than one column, separate the column names with commas. These columns will be the multicolumn key for these level columns in the fact table. |
parentcol |
Column that stores the parent level in the hierarchy. If you do not specify this parameter, the level is the root of the hierarchy. |
This procedure specifies how to sort the members of a hierarchy within a column of a dimension table. The column may be the key column or it may be a related attribute column. Custom sorting can specify that the column be sorted in ascending or descending order, with nulls first or nulls last.
Custom sorting information is optional and can be applied at multiple levels of a dimension.
Syntax
MAP_DIMTBL_HIERSORTKEY ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, hierarchy_name IN VARCHAR2, sortcol IN VARCHAR2);
Parameters
Table 18-4 MAP_DIMTBL_HIERSORTKEY Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
hierarchy_name |
Name of the hierarchy. |
sortcol |
A string specifying how to sort the values stored in a given column of a dimension table. The string specifies the table name, the column name, whether to sort in ascending or descending order, and whether to place nulls first or last.
The string should be enclosed in single quotes, and it should be in the following form.
|
This procedure maps a level attribute to a column in a dimension table.
The attribute being mapped is associated with a level that has no hierarchical context. Typically, this level is the only level defined for this dimension.
Syntax
MAP_DIMTBL_LEVELATTR ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, dimension_attribute_name IN VARCHAR2, level_name IN VARCHAR2, level_attribute_name IN VARCHAR2, table_owner IN VARCHAR2, table_name IN VARCHAR2, attrcol IN VARCHAR2);
Parameters
Table 18-5 MAP_DIMTBL_LEVELATTR Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
dimension_attribute_name |
Name of the dimension attribute. |
level_name |
Name of the level. |
level_attribute_name |
Name of the level attribute associated with this level. |
table_owner |
Owner of the dimension table. |
table_name |
Name of the dimension table. |
attrcol |
Column in the dimension table to which this level attribute should be mapped. |
This procedure maps a level to one or more columns in a dimension table.
The level being mapped has no hierarchical context. Typically, this level is the only level defined for this dimension.
Syntax
MAP_DIMTBL_LEVEL ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, level_name IN VARCHAR2, table_owner IN VARCHAR2, table_name IN VARCHAR2, keycol IN VARCHAR2);
Parameters
Table 18-6 MAP_DIMTBL_LEVEL Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
level_name |
Name of the level. |
table_owner |
Owner of the dimension table. |
table_name |
Name of the dimension table. |
keycol |
Column in the dimension table to which this level should be mapped. This column will be the key for this level column in the fact table.
If the level is stored in more than one column, separate the column names with commas. These columns will be the multicolumn key for these level columns in the fact table. |
This procedure creates the join relationships between a fact table and a set of dimension tables. A join must be specified for each of the dimensions of the cube. Each dimension is joined in the context of one of its hierarchies.
For example, if you had a cube with three dimensions, and each dimension had only one hierarchy, you could fully map the cube with one call to MAP_FACTTBL_LEVELKEY
.
However, if you had a cube with three dimensions, but two of the dimensions each had two hierarchies, you would need to call MAP_FACTTBL_LEVELKEY
four times to fully map the cube. For dimensions Dim1
, Dim2
, and Dim3
, where Dim1
and Dim3
each have two hierarchies, you would specify the following mapping strings in each call to MAP_FACTTBL_LEVELKEY
, as follows.
Dim1_Hier1, Dim2_Hier, Dim3_Hier1 Dim1_Hier1, Dim2_Hier, Dim3_Hier2 Dim1_Hier2, Dim2_Hier, Dim3_Hier1 Dim1_Hier2, Dim2_Hier, Dim3_Hier2
Typically the data for each hierarchy combination would be stored in a separate fact table.
For more information, see"Joining Fact Tables with Dimension Tables" .
Syntax
MAP_FACTTBL_LEVELKEY ( cube_owner IN VARCHAR2, cube_name IN VARCHAR2, facttable_owner IN VARCHAR2, facttable_name IN VARCHAR2, storetype IN VARCHAR2, dimkeymap IN VARCHAR2, dimktype IN VARCHAR2 DEFAULT NULL);
Parameters
Table 18-7 MAP_FACTTBL_LEVELKEY Procedure Parameters
Parameter | Description |
---|---|
cube_owner |
Owner of the cube. |
cube_name |
Name of the cube. |
facttable_owner |
Owner of the fact table. |
facttable_name |
Name of the fact table. |
storetype |
One of the following:
' |
dimkeymap |
A string specifying the mapping for each dimension of the data in the fact table. For each dimension you must specify a hierarchy and the lowest level to be mapped within that hierarchy.
Enclose the string in single quotes, and separate each dimension specification with a semicolon as follows: ' Note that the This string must also be specified as an argument to the |
dimktype |
This parameter is not currently used. |
This procedure maps a measure to a column in a fact table.
Syntax
MAP_FACTTBL_MEASURE ( cube_owner IN VARCHAR2, cube_name IN VARCHAR2, measure_name IN VARCHAR2, facttable_owner IN VARCHAR2, facttable_name IN VARCHAR2, column_name IN VARCHAR2, dimkeymap IN VARCHAR2);
Parameters
Table 18-8 MAP_FACTTBL_MEASURE Procedure Parameters
Parameter | Description |
---|---|
cube_owner |
Owner of the cube. |
cube_name |
Name of the cube. |
measure_name |
Name of the measure to be mapped. |
facttable_owner |
Owner of the fact table. |
facttable_name |
Name of the fact table. |
column_name |
Column in the fact table to which the measure will be mapped. |
dimkeymap |
A string specifying the mapping for each of the measure's dimensions. For each dimension you must specify a hierarchy and the lowest level to be mapped within that hierarchy.
Enclose the string in single quotes, and separate each dimension specification with a semicolon as follows: ' Note that the This string must also be specified as an argument to the |
This procedure removes the relationship between a level attribute and a column in a dimension table. The attribute is identified by the hierarchy that contains its associated level.
Upon successful completion of this procedure, the level attribute is a purely logical metadata entity. It has no data associated with it.
Syntax
REMOVEMAP_DIMTBL_HIERLEVELATTR ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, dimension_attribute_name IN VARCHAR2, hierarchy_name IN VARCHAR2, level_name IN VARCHAR2, level_attribute_name IN VARCHAR2);
Parameters
Table 18-9 REMOVEMAP_DIMTBL_HIERLEVELATTR Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
dimension_attribute_name |
Name of the dimension attribute. |
hierarchy_name |
Name of the hierarchy. |
level_name |
Name of the level. |
level_attribute_name |
Name of the level attribute associated with this level. |
This procedure removes the relationship between a level of a hierarchy and one or more columns in a dimension table.
Upon successful completion of this procedure, the level is a purely logical metadata entity. It has no data associated with it.
Syntax
REMOVEMAP_DIMTBL_HIERLEVEL ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, hierarchy_name IN VARCHAR2, level_name IN VARCHAR2);
Parameters
Table 18-10 REMOVEMAP_DIMTBL_HIERLEVEL Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
hierarchy_name |
Name of the hierarchy. |
level_name |
Name of the level. |
This procedure removes custom sorting criteria associated with columns in a dimension table.
Syntax
REMOVEMAP_DIMTBL_HIERSORTKEY ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, hierarchy_name IN VARCHAR2);
Parameters
Table 18-11 REMOVEMAP_DIMTBL_HIERSORTKEY Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
hierarchy_name |
Name of the hierarchy. |
This procedure removes the relationship between a level attribute and a column in a dimension table.
Upon successful completion of this procedure, the level attribute is a purely logical metadata entity. It has no data associated with it.
Syntax
REMOVEMAP_DIMTBL_LEVELATTR ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, dimension_attribute_name IN VARCHAR2, level_name IN VARCHAR2, level_attribute_name IN VARCHAR2);
Parameters
Table 18-12 REMOVEMAP_DIMTBL_LEVELATTR Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
dimension_attribute_name |
Name of the dimension attribute. |
level_name |
Name of the level. |
level_attribute_name |
Name of the level attribute associated with this level. |
This procedure removes the relationship between a level and one or more columns in a dimension table.
Upon successful completion of this procedure, the level is a purely logical metadata entity. It has no data associated with it.
Syntax
REMOVEMAP_DIMTBL_LEVEL ( dimension_owner IN VARCHAR2, dimension_name IN VARCHAR2, level_name IN VARCHAR2);
Parameters
Table 18-13 REMOVEMAP_DIMTBL_LEVEL Procedure Parameters
Parameter | Description |
---|---|
dimension_owner |
Owner of the dimension. |
dimension_name |
Name of the dimension. |
level_name |
Name of the level. |
This procedure removes the relationship between the key columns in a fact table and the level columns of a dimension hierarchy in a dimension table.
Syntax
REMOVEMAP_FACTTBL_LEVELKEY ( cube_owner IN VARCHAR2, cube_name IN VARCHAR2, facttable_owner IN VARCHAR2, facttable_name IN VARCHAR2 DEFAULT );
Parameters
Table 18-14 REMOVEMAP_FACTTBL_LEVELKEY Procedure Parameters
Parameter | Description |
---|---|
cube_owner |
Owner of the cube. |
cube_name |
Name of the cube. |
facttable_owner |
Owner of the fact table. |
facttable_name |
Name of the fact table. |
This procedure removes the relationship between a measure column in a fact table and a logical measure associated with a cube.
Upon successful completion of this procedure, the measure is a purely logical metadata entity. It has no data associated with it.
Syntax
REMOVEMAP_FACTTBL_MEASURE ( cube_owner IN VARCHAR2, cube_name IN VARCHAR2, measure_name IN VARCHAR2, facttable_owner IN VARCHAR2, facttable_name IN VARCHAR2, column_name IN VARCHAR2, dimkeymap IN VARCHAR2);
Parameters
Table 18-15 REMOVEMAP_FACTTBL_MEASURE Procedure Parameters
Parameter | Description |
---|---|
cube_owner |
Owner of the cube. |
cube_name |
Name of the cube. |
measure_name |
Name of the measure. |
facttable_owner |
Owner of the fact table. |
facttable_name |
Name of the fact table. |
column_name |
Column in the fact table to which the measure is mapped. |
dimkeymap |
A string specifying the mapping for each of the measure's dimensions. For each dimension you must specify a hierarchy and the lowest level to be mapped within that hierarchy.
Enclose the string in single quotes, and separate each dimension specification with a semicolon as follows: ' Note that the This string must also be specified as an argument to the |