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 21-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 21-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.

Example

See Example 2-1, "Create an OLAP Dimension for the Products Table" and "Example: Mapping a Dimension".

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 21-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.

Example

See Example 2-1, "Create an OLAP Dimension for the Products Table" and "Example: Mapping a Dimension".

MAP_DIMTBL_HIERSORTKEY Procedure

This procedure specifies how to sort the members of a hierarchy within one or more columns of a dimension table.

Custom sorting can be specified for level columns or related attribute columns. Columns can be sorted in ascending or descending order, with nulls first or nulls last. By default, columns are sorted in ascending order and nulls are first.

Custom sorting information is optional. You can define a valid hierarchy without using the MAP_DIMTBL_HIERSORTKEY procedure.

Syntax

MAP_DIMTBL_HIERSORTKEY (
         dimension_owner     IN   VARCHAR2,
         dimension_name      IN   VARCHAR2,
         hierarchy_name      IN   VARCHAR2,
         sortcols            IN   VARCHAR2);

Parameters

Table 21-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.
`sortcols`	A string specifying how to sort the values stored in one or more columns of a dimension table. For each column, the string specifies whether to sort in ascending or descending order, and whether to place nulls first or last. The default order is ascending with nulls first. Specify the columns in the order in which they should be sorted. The string should be enclosed in single quotes, and it should be in the following form. `'TBL:table1_owner.table1_name` `/COL:column1_name` `/ORD:ASC\|DSC/NULL:FIRST\|LAST;` `TBL:table2_owner.table2_name` `/COL:column2_name` `/ORD:ASC\|DSC/NULL:FIRST\|LAST;..........'` NOTE: You do not need to repeat the table name for columns in the same table. You do not need to repeat the column names for a group of columns that share the same sorting attributes.

Example

The GLOBAL.CUSTOMER dimension, based on the table GLOBAL.CUSTOMER_DIM, has two hierarchies: SHIPMENTS_ROLLUP and MARKET_ROLLUP.

The MARKET_ROLLUP hierarchy has four levels: TOTAL_MARKET, MARKET_SEGMENT, ACCOUNT, and SHIP_TO. Each level has a corresponding attribute column containing a short description of the level. The attribute column names are: TOTAL_MARKET_DSC, MARKET_SEGMENT_DSC, ACCOUNT_DSC, and SHIP_TO_DSC.

The following command specifies that all the levels within the MARKET_ROLLUP hierarchy should be sorted in ascending order by description. The three most aggregate levels should be sorted with nulls first; the lowest level, with attribute column SHIP_TO_DSC, should be sorted with nulls last.

>EXECUTE cwm2_olap_table_map.map_dimtbl_hiersortkey
         ('GLOBAL', 'CUSTOMER', 'MARKET_ROLLUP',
          'TBL:GLOBAL.CUSTOMER_DIM/COL:TOTAL_MARKET_DSC/COL:MARKET_SEGMENT_DSC
                                  /COL:ACCOUNT_DSC/ORD:ASC/NULL:FIRST
                                  /COL:SHIP_TO_DSC/ORD:ASC/NULL: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 21-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 21-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,
          dimkeytype           IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 21-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:dim1_name/HIER:hier_name`; `/GID:gid_column/LVL:level_name` `/COL:map_column`; '`DIM:dim2_name/HIER:hier_name`; `/GID:gid_column/LVL:level_name` `/COL:map_column`;................' Note: 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.
`dimkeytype`	This parameter is not currently used.

Example

Example 2-3, "Create an OLAP Cube for the COSTS Fact Table" illustrates the mapping commands for a fact table with a storetype of 'LOWESTLEVEL'.

"Example: Mapping a Cube" illustrates the mapping commands for a fact table with a storetype of 'ET'.

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 21-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:dim1_name/HIER:hier_name`; `/GID:gid_column/LVL:level_name` `/COL:map_column`; '`DIM:dim2_name/HIER:hier_name`; `/GID:gid_column/LVL:level_name` `/COL:map_column`;................' Note: 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.

Example

See Example 2-3, "Create an OLAP Cube for the COSTS Fact Table" and "Example: Mapping a Cube" .

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 21-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 21-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 21-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 21-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 21-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 21-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 21-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.