Understanding OLAP Metadata Mapping

OLAP metadata mapping is the process of establishing the links between logical metadata entities and the physical locations where the data is stored. 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.

Note: The dimension tables and fact tables may be implemented as views. For example, the views you can generate using the CWM2_OLAP_AW_ACCESS package may be a 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 Logical Metadata Entities

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

Each dimension level attribute maps to a single column. Level attributes must be mapped within the same table as their associated levels.

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.

Joining Fact Tables with Dimension Tables

Once you have mapped the levels, 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 mapping between a fact table and its dimension tables may be specified for any of the following configurations:

• LOWEST LEVEL. A single fact table stores lowest level 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.

  This warehouse configuration is required for metadata in the OLAP 1 Catalog. It is supported, but not required, for metadata in the OLAP 2 Catalog.

• EMBEDDED TOTAL. Fact tables store embedded totals and lowest level data for specific hierarchies of the cube's dimensions. Typically, the data for each combination of hierarchies is stored in a separate fact table. 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 maps to corresponding columns that identify a dimension hierarchy in a solved dimension table.

  This warehouse configuration is supported for metadata in the OLAP 2 Catalog only.

• ROLLED UP. This type of warehouse has embedded total fact tables and solved, hierarchical dimension tables as in EMBEDDED TOTAL mode. All mapping to dimension tables is in the context of hierarchies as in EMBEDDED TOTAL mode.

  However, in ROLLED UP mode, there are 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.

  This warehouse configuration is supported for metadata in the OLAP 2 Catalog only.