Mapping OLAP Catalog Metadata

OLAP Catalog metadata mapping is the process of establishing the links between logical metadata entities and the physical locations where the data is stored. Dimension levels and level attributes map to columns in dimension tables. Measures map to columns in fact tables. The mapping process also specifies the join relationships between a fact table and its associated dimension tables.

Note:

The dimension tables and fact tables may be implemented as views. For example, the views you can generate using the DBMS_AWM package may be the data source for OLAP Catalog metadata. For more information, see "Overview".

Mapping to Columns

The CWM2_OLAP_TABLE_MAP package contains the mapping procedures for CWM2 metadata. Dimension levels, level attributes, and measures can be mapped within the context of a hierarchy or with no hierarchical context.

Mapping Dimensions

Each level maps to one or more columns in a dimension table. All the columns of a multicolumn level must be mapped within the same table. All the levels of a dimension may be mapped to columns in the same table (a traditional star schema), or the levels may be mapped to columns in separate tables (snowflake schema).

Each level attribute maps to a single column in the same table as its associated level.

Mapping Measures

Each measure maps to a single column in a fact table. All the measures mapped within the same fact table must share the same dimensionality.

When more than one hierarchical context is possible within a cube (at least one of the cube's dimensions has multiple hierarchies), each combination of hierarchies may be mapped to a separate fact table. In this case, each table must have columns for each of the cube's measures, and the measure columns must appear in the same order in each table.

Joining Fact Tables with Dimension Tables

Once you have mapped the levels, level attributes, and measures, you can specify the mapping of logical foreign key columns in the fact table to level key columns in dimension tables.

The MAP_FACTTBL_LEVELKEY procedure defines the join relationships between a cube and its dimensions. This procedure takes as input: the cube name, the fact table name, a mapping string, and a storage type indicator specifying how data is stored in the fact table.

The storage type indicator can have either of the following values:

'LOWESTLEVEL' A single fact table stores unsolved data for all the measures of a cube (star schema). If any of the cube's dimensions have more than one hierarchy, they must all have the same lowest level. Each foreign key column in the fact table maps to a level key column in a dimension table.
'ET' Fact tables store completely solved data (with embedded totals) for specific hierarchies of the cube's dimensions. Typically, the data for each combination of hierarchies is stored in a separate fact table. Each fact table must have the same columns. Multiple hierarchies in dimensions do not have to share the same lowest level.

An embedded total key and a grouping ID key (GID) in the fact table map to corresponding columns that identify a dimension hierarchy in a solved dimension table. The ET key identifies the lowest level value present in a row. The GID identifies the hierarchy level associated with each row. For more information, see "Grouping ID Column" . For more information on mapping the key relationships between fact tables and dimension tables, see "MAP_FACTTBL_LEVELKEY Procedure".

When the fact table and dimension tables are joined with a storage type of LOWESTLEVEL, the cube's hierarchies have a solved_code of 'UNSOLVED LEVEL-BASED'.

When the fact tables and dimension tables are joined with a storage type of ET, the cube's hierarchies have a solved_code of 'SOLVED LEVEL-BASED'.

See "SET_SOLVED_CODE Procedure".