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 shown below.

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 26-12  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: LOWEST LEVEL, for a fact table that stores only lowest level data ET, for a fact table that stores embedded totals in addition to lowest level data ROLLED UP, for an embedded total fact table with key columns for all levels
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. Each dimension specification must be in the following form: DIM:dimname/HIER:hiername/GID:columnname/LVL:levelname/COL:columnname; This string must also be specified as an argument to the MAP_FACTTBL_MEASURE procedure.
dimktype	This parameter is not currently used.

Exceptions

Table 26-13  MAP_FACTTBL_LEVELKEY Procedure Exceptions

Exception	Description
no_access_privileges	User does not have the necessary privileges. User must be the dimension owner and have the OLAP_DBA role.
cube_not_found	Cube not found.
fact_table_not_found	Fact table not found.