18 CWM2_OLAP_TABLE_MAP

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:

Understanding OLAP Metadata Mapping
Example: Mapping a Dimension
Example: Mapping a Cube
Summary of CWM2_OLAP_TABLE_MAP Subprograms

18.1 Understanding OLAP Metadata Mapping

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.

See Also:

"Mapping OLAP Metadata".

18.2 Example: Mapping a Dimension

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

18.3 Example: Mapping a Cube

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

Summary of CWM2_OLAP_TABLE_MAP Subprograms

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.

MAP_DIMTBL_HIERLEVELATTR Procedure

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.

MAP_DIMTBL_HIERLEVEL Procedure

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.

MAP_DIMTBL_HIERSORTKEY Procedure

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. `'TBL:``tableowner.tablename``/COL:``columnname` `/ORD:ASC\|DSC/NULL:FIRST\|LAST;`'

MAP_DIMTBL_LEVELATTR Procedure

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.

MAP_DIMTBL_LEVEL Procedure

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.

MAP_FACTTBL_LEVELKEY Procedure

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: `'LOWESTLEVEL`', for a fact table that stores only lowest level data '`ET`', for a fact table that stores embedded totals for all level combinations in addition to lowest level data
`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: '`DIM:``dimname1``/HIER:``hiername1``/GID:``gid_columnname1``/LVL:``levelname1` `/COL:``map_columnname1`; `DIM:``dimname2``/HIER:``hiername2``/GID:``gid_columnname2``/LVL:``levelname2` `/COL:``map_columnname2`;...........' Note that the `GID` clause of the mapping string is only applicable to embedded totals. If you specify '`LOWESTLEVEL`' for the storetype argument, do not include a `GID` clause in the mapping string. This string must also be specified as an argument to the `MAP_FACTTBL_MEASURE` procedure.
`dimktype`	This parameter is not currently used.

MAP_FACTTBL_MEASURE Procedure

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: '`DIM:``dimname1``/HIER:``hiername1``/GID:``gid_columnname1``/LVL:``levelname1` `/COL:``map_columnname1`; `DIM:``dimname2``/HIER:``hiername2``/GID:``gid_columnname2``/LVL:``levelname2` `/COL:``map_columnname2`;...........' Note that the `GID` clause of the mapping string is only applicable to embedded totals. If you specify '`LOWESTLEVEL`' for the storetype argument, do not include a `GID` clause in the mapping string. This string must also be specified as an argument to the `MAP_FACTTBL_LEVELKEY` procedure.

REMOVEMAP_DIMTBL_HIERLEVELATTR Procedure

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.

REMOVEMAP_DIMTBL_HIERLEVEL Procedure

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.

REMOVEMAP_DIMTBL_HIERSORTKEY Procedure

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.

REMOVEMAP_DIMTBL_LEVELATTR Procedure

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.

REMOVEMAP_DIMTBL_LEVEL Procedure

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.

REMOVEMAP_FACTTBL_LEVELKEY Procedure

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.

REMOVEMAP_FACTTBL_MEASURE Procedure

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: '`DIM:``dimname1``/HIER:``hiername1``/GID:``gid_columnname1``/LVL:``levelname1` `/COL:``map_columnname1`; `DIM:``dimname2``/HIER:``hiername2``/GID:``gid_columnname2``/LVL:``levelname2` `/COL:``map_columnname2`;...........' Note that the `GID` clause of the mapping string is only applicable to embedded totals. If the measure contained only detail data and was mapped with a storage type of '`LOWESTLEVEL`', do not include a `GID` clause in the mapping string. This string must also be specified as an argument to the `MAP_FACTTBL_MEASURE` and `MAP_FACTTBL_LEVELKEY` procedures.