Mapping Table Template Reference

You can create mapping tables in the Data Store yourself, rather than use the mapping table templates that Analytics Link creates from the Target Design Grid tab.

Analytics Link uses the SQL query examples provided in this appendix to create mapping table templates. These queries are in Oracle 11g Server format.

See About Dimension Member Mapping Tables

The following query creates a mapping table template for mapping one Financial Management source dimension to one target dimension:

CREATE TABLE "<EAL_DATA_STORE_DB>"."<MAPPING_TABLE>"
 (
 "HFM_MEMBER" NVARCHAR2(80) Not Nullable, 
 "TARGET_MEMBER" NVARCHAR2(80) Not Nullable, 
 "VISIBLE" NUMBER(38,0) Not Nullable, 
 "ISBASE" NUMBER(38,0) Nullable, 
 "GENERATION" NUMBER(38,0) Nullable, 
 "DIM_LEVEL" NUMBER(38,0) Nullable, 
 "SELECTED" NUMBER(38,0) Nullable
 );

Representation of the mapping table columns:

Mapping table rules for the Dimension Creation transformation type: Member selection is based on the Financial Management source dimension. By default, if a Financial Management member is not included in the mapping table, Analytics Link considers VISIBLE is set to 1 (TRUE) and the member is included in the target database. The member name is the same as it is in Financial Management.

Analytics Link implements the LAST duplicate row policy only on rows for which SELECTED is set to 1 (TRUE). The uniqueness of a row is identified by the HFM_MEMBER and TARGET_MEMBER columns. See About Duplicate Data Row Consolidation Operators.

See Dimension Creation Transformation Type.

The following query creates a mapping table template for mapping one Essbase source dimension to one Financial Management target dimension:

CREATE TABLE "<EAL_DATA_STORE_DB>"."<MAPPING_TABLE>"
 (
 "HFM_MEMBER_<DIM>" NVARCHAR2(80) Not Nullable, 
 "TARGET_MEMBER" NVARCHAR2(80) Not Nullable, 
 "VISIBLE" NUMBER(38,0) Not Nullable, 
 "ISBASE" NUMBER(38,0) Nullable, 
 "GENERATION" NUMBER(38,0) Nullable, 
 "DIM_LEVEL" NUMBER(38,0) Nullable, 
 "SELECTED" NUMBER(38,0) Nullable
 );

For HFM_MEMBER_<DIM>, you must substitute the name of the Financial Management target dimension.

Representation of the mapping table columns:

Mapping table rules for the Dimension Mapping transformation type: Member selection is based on the Essbase target dimension. Therefore, if an Essbase member is not displayed in the TARGET_MEMBER column, Analytics Link considers VISIBLE is set to 0 (FALSE) for that member and Analytics Link does not add the “eal” UDA to the member name. See About User-Defined Attributes (UDAs).

Analytics Link implements the LAST duplicate row policy only on rows for which SELECTED is set to 1 (TRUE). The uniqueness of a row is identified by the TARGET_MEMBER and HFM_MEMBER_DIM columns. See About Duplicate Data Row Consolidation Operators.

See Dimension Mapping Transformation Type.

The following query creates a mapping table template for mapping multiple Financial Management source dimensions to one Essbase target dimension:

CREATE TABLE "<EAL_DATA_STORE_DB>"."<MAPPING_TABLE>"
 (
 "TARGET_MEMBER" NVARCHAR2(80) Not Nullable, 
 "HFM_MEMBER_<DIM1>" NVARCHAR2(80) Not Nullable, 
 ...
 "HFM_MEMBER_<DIMN>" NVARCHAR2(80) Not Nullable, 
 "ISBASE" NUMBER(38,0) Nullable, 
 "GENERATION" NUMBER(38,0) Nullable, 
 "DIM_LEVEL" NUMBER(38,0) Nullable, 
 "VISIBLE" NUMBER(38,0) Not Nullable, 
 "SELECTED" NUMBER(38,0) Nullable
 );

For HFM_MEMBER_<DIM1> through HFM_MEMBER_<DIMN>, you must substitute the names of the Financial Management source dimensions.

Representation of the mapping table columns:

Mapping table rules for the Join Dimensions transformation type: Member selection is based on the Essbase target dimension. Therefore, if an Essbase member is not displayed in the TARGET_MEMBER column, Analytics Link considers VISIBLE is set to 0 (FALSE) for that member and Analytics Link does not add the “eal” UDA to the member name. See About User-Defined Attributes (UDAs).

Analytics Link implements the LAST duplicate row policy only on rows for which SELECTED is set to 1 (TRUE). The uniqueness of a row is identified by the TARGET_MEMBER and HFM_MEMBER_DIM1 to HFM_MEMBER_DIMn columns. See About Duplicate Data Row Consolidation Operators.

See Join Dimensions Transformation Type.

The following query creates a mapping table template for mapping one Financial Management source dimension to multiple Essbase target dimensions:

CREATE TABLE "<EAL_DATA_STORE_DB>"."<MAPPING_TABLE>"
 (
 "HFM_MEMBER" NVARCHAR2(80) Not Nullable, 
 "TARGET_MEMBER_<DIM1>" NVARCHAR2(80) Not Nullable, 
 "VISIBLE_<DIM1>" NUMBER(38,0) Not Nullable, 
 
 "TARGET_MEMBER_<DIMN>" NVARCHAR2(80) Not Nullable, 
 "VISIBLE_<DIMN>" NUMBER(38,0) Not Nullable, 
 "ISBASE" NUMBER(38,0) Nullable, 
 "GENERATION" NUMBER(38,0) Nullable, 
 "DIM_LEVEL" NUMBER(38,0) Nullable, 
 "SELECTED" NUMBER(38,0) Nullable
 );

Representation of the mapping table columns:

Mapping table rules for the Break Dimension transformation type:

See Break Dimension Transformation Type.

The following query creates a mapping table template for mapping intersections that are defined by n number of Financial Management source dimension to intersections that are defined by m number of Essbase target dimensions.

CREAT TABLE "<EAL_DATA_STORE_DB>"."<MAPPING_TABLE>"
 (
 SRC_<HFM_Dimension1>  NCLOB not null,
...
 SRC_<HFM_DimensionN>  NCLOB not null,
 DEST_<Destination_dimension1/column1> NCLOB not null,
   
 DEST_<Destination_dimensionM/columnM> NCLOB not null,
 )

Representation of the mapping table columns:

If the Financial Management Entity dimension is a source dimension, the table includes a column, named SRC_PARENT, for the Entity Parent value. Representation of the mapping table columns:

See Matrix Mapping Transformation Type.