Using the OLAP Catalog Metadata APIs , 5 of 10

Mapping OLAP Metadata

OLAP 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 CWM2_OLAP_AW_CREATE package may be the data source for OLAP metadata. These views project an image of relational fact tables and dimension tables over an analytic workspace, where the data actually resides.

Mapping to Columns

Each dimension 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).

The CWM2_OLAP_TABLE_MAP package contains the mapping procedures for CWM2 metadata. The MAP_DIMTBL_HIERLEVEL procedure maps a level of a given hierarchy to columns in a dimension table. The MAP_DIMTBL_LEVEL procedure maps a level with no hierarchical context to columns in a dimension table.

Each level attribute maps to a single column in the same table as its associated level. The MAP_DIMTBL_HIERLEVELATTR maps a level attribute of a given hierarchy to a column in a dimension table. The MAP_DIMTBL_LEVELATTR maps a level attribute with no hierarchical context to a column in a dimension table.

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. The MAP_FACTTBL_MEASURE procedure maps a measure to a column in a fact 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 CWM2_OLAP_TABLE_MAP.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 any of the following values:

LOWEST LEVEL (Required in CWM, supported but not required in CWM2).
A single fact table stores unsolved data for all the measures of a cube. 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 (CWM2 only).
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" .
ROLLED UP (CWM2 only).
Same as for ET, but with key columns in the fact table for each level of each dimension hierarchy. The presence of fully populated level keys in the fact table facilitates aggregation at runtime.