Designing the Target Database Grid

In This Section:

Overview of Designing the Target Database Grid

About Transformation Types

About Dimension Member Selection

About Dimension Member Selection for the Value Dimension

About Dimension Member Mapping Tables

About the VISIBLE Mapping Property

About Making Intermediate Members Invisible

Member Naming Considerations when Add Parent to Entity Member is Set to On

About Prefixes

About User-Defined Attributes (UDAs)

The information in this chapter applies to Essbase and RDBMS target databases. Financial Management data examples are based on the Simple Demo Financial Management application.

Overview of Designing the Target Database Grid

The Target Design Grid tab in the bridge window enables you to modify the structure of Financial Management data in the target database. Financial Management data is converted, or transformed, in the target database based on a dimension's transformation type. The Source Dimensions column contains the list of Financial Management dimensions that are mapped to the dimensions listed in the Target Dimensions column. If the target database is Essbase, the Financial Management dimensions are mapped to Essbase dimensions. If the target database is an RDBMS database, the Financial Management dimensions are mapped to columns in relational tables in the RDBMS database.

  To design the target database grid:

  1. In the bridge window, navigate to the Target Design Grid tab.

  2. Under Mapping Grid, for each of the 12 Financial Management dimensions in the list, double-click the field in the Transformation Type column, and select a transformation type.

    See About Transformation Types.

  3. Optional: To add new dimensions to the target grid, navigate to the last row in the grid (which is empty) and select one of the following transformation types:

    • New Dimension

    • Currency Dimension

    • Measure Dimension

  4. The following steps depend on each dimension's transformation type:

    1. In the Source Dimensions column, you can change the Financial Management dimension or, more typically, create a comma-separated list of Financial Management dimensions for those transformation types that can have multiple Financial Management dimensions.

      1. Double-click the Source Dimensions field to open the Source Dimensions dialog box.

      2. To change the source dimension, delete the dimension name in the Add field and then double-click a dimension name to add it to the Add field.

      3. To create a comma-separated list of Financial Management dimensions, double-click multiple dimension names.

      4. Click OK to add the specified dimensions to the Source Dimensions field in the mapping grid.

    2. In the Target Dimensions column, you can change the name of the dimension as it appears in the target database or create a comma-separated list of dimensions for those transformation types that can have multiple target dimensions.

      1. Double-click the Target Dimensions field to open the Target Dimensions dialog box.

      2. To rename a target dimension, edit the dimension name in the Add field.

      3. To create a comma-separated list of target dimensions, double-click multiple dimension names.

      4. Click OK to add the specified dimensions to the Target Dimensions field in the mapping grid.

    3. In the Member Selection column, enter the name of the members that you want displayed in the target database. (The descendants of the specified members are also included in the target database.)

      1. Double-click the Member Selection field to open the Member List dialog box.

      2. Enter a dimension member name or a comma-separated list of member names.

      3. Click OK to add the specified members to the Member Selection field in the mapping grid.

      See About Dimension Member Selection.

    4. In the Mapping Table column, you can specify a mapping table for those transformation types that support mapping tables.

      1. Double-click the Mapping Table field to open the Mapping Table dialog box.

      2. Enter the Mapping Table Name and click Recreate.

        Note:

        If the mapping table exists, Analytics Link recreates the table and fills it with default values.

      3. After the mapping table is created, click OK.

        Analytics Link creates the mapping table template in the Data Store using default values, which you need to modify.

      4. In the Data Store, modify the mapping table data as needed and commit the changes to the Data Store.

      Note:

      Instead of having Analytics Link create a mapping table template, you can create a mapping table directly in the Data Store. Consult this documentation for the mapping table structure for each transformation type. If you created the mapping table directly in the Data Store, in the Mapping Table dialog box, enter the Mapping Table Name and then click OK.

      See About Dimension Member Mapping Tables.

    5. (For Dimension Mapping transformation type only) For Prefix, double-click the field and enter a Java regular expression.

      See About Prefixes.

  5. Validate the grid.

    See Validating Target Grids.

  6. Click Save Grid to update the outline definition.

To delete a row in the mapping grid, right-click the row and select Delete Row.

To reset the mapping grid to the default values, click Reset Grid.

To change the order in which dimensions are displayed in an Essbase outline, use the Row Up and Row Down buttons. The order in which dimensions are displayed can impact how members are named. (These buttons do not apply when the bridge target is an RDBMS database.)

About Transformation Types

Transformation types determine how Financial Management dimensions are represented in the target database and enable you to add non-Financial Management dimensions and members to the target database.

The number of source and target dimensions, and whether member selection, mapping table, and prefix are supported, depend on the transformation type. Table 7 summarizes the attributes of each transformation type. These attributes are discussed in detail in this chapter.

Table 7. Summary of Transformation Type Attributes

Transformation TypeNumber of Source_DimsNumber of Target_DimsMember SelectionMapping TablePrefixSee
Dimension Creation11Supported, based on Financial Management membersSupportedNot supportedDimension Creation Transformation Type
Dimension Mapping11Supported, based on Essbase membersSupportedSupportedDimension Mapping Transformation Type
Not in Outline1None

Required

One Financial Management member must be specified.

Not supportedNot supportedNot in Outline Transformation Type
New DimensionNone1

Required

One Essbase member must be specified.

Not supportedNot supportedNew Dimension Transformation Type
Join Dimensions> 11Supported, based on Essbase membersRequiredNot supportedJoin Dimensions Transformation Type
Break Dimension1> 1Supported, based on Financial Management membersRequiredNot supportedBreak Dimension Transformation Type
Matrix Mapping

1

1

Not supportedRequiredNot supportedMatrix Mapping Transformation Type
CurrencyNone1Not supportedNot supportedNot supportedCurrency Transformation Type
MeasureNone1

Essbase target database: required; one member must be specified

RDBMS target database: not supported

Not supportedNot supportedMeasure Transformation Type

Dimension Creation Transformation Type

The Dimension Creation transformation type uses one Financial Management dimension as the source to create one dimension in the target database. This transformation type replicates the Financial Management dimension as it appears in the Financial Management application.

The Dimension Creation transformation type supports:

  • Member selection, based on Financial Management members

  • Member name mapping

The same Financial Management dimension cannot be used in Dimension Creation and Break Dimension transformation types within the same bridge.

In this example, assume that you want to create the Essbase ESSCustom1 dimension from the Financial Management Custom1 dimension, and you only want to include the Golf and Shoes members, and their descendants.

Representation of all members in the Financial Management Custom1 dimension:

Custom1
   [None]
   AllProducts
      Golf
         GolfBalls
         GolfShoes
         GolfTees
         GolfClubs
      Tennis
         TennisBalls
         TennisShoes
         TennisRacquets
   Balls
      GolfBalls
      TennisBalls
   Shoes
      GolfShoes
      TennisShoes

Table 8 represents the information that you must provide on the Target Design Grid tab:

Table 8. Target Design Grid tab: Dimension Creation Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Dimension CreationCustom1ESSCustom1Golf,ShoestableName 

Table 9, Analytics Link Mapping Table Template: Dimension Creation Transformation Type shows the Analytics Link-created mapping table template. Analytics Link fills the HFM_MEMBER column with the names of all members in the Financial Management Custom1 dimension (the member selection specification is ignored). The TARGET_MEMBER column is filled with the same names from the HFM_MEMBER column. SELECTED is set to 1 (TRUE) only for Golf and Shoes, and their descendants (such as GolfBalls and GolfShoes); the other members are set to 0 (FALSE).

Table 9. Analytics Link Mapping Table Template: Dimension Creation Transformation Type

HFM_MEMBERTARGET_MEMBERVISIBLEISBASEGENERATIONDIM_LEVELSELECTED
[None]FM None11000
AllProductsAllProducts10020
BallsBalls10010
GolfGolf10111
GolfBallsGolfBalls11201
GolfClubsGolfClubs11201
GolfShoesGolfShoes11201
GolfTeesGolfTees11201
ShoesShoes10011
TennisTennis10110
TennisBallsTennisBalls11200
TennisRacquetsTennisRacquets11200
TennisShoesTennisShoes11201

Assume that you do not want GolfTees, which is a descendant of Golf, represented in the Essbase outline. Set VISIBLE to 0 (FALSE) for GolfTees. Also, assume that you want to change the names of Golf and Shoes to GolfProducts and ShoesProducts, respectively in the Essbase outline. These changes are shown in Table 10.

Table 10. User-Updated Mapping Table Template: Dimension Creation Transformation Type

HFM_MEMBERTARGET_MEMBERVISIBLEISBASEGENERATIONDIM_LEVELSELECTED
[None]FM None11000
AllProductsAllProducts10020
BallsBalls10010
GolfGolfProducts10111
GolfBallsGolfBalls11200
GolfClubsGolfClubs11201
GolfShoesGolfShoes11201
GolfTeesGolfTees01201
ShoesShoesProducts10011
TennisTennis10110
TennisBallsTennisBalls11200
TennisRacquetsTennisRacquets11200
TennisShoesTennisShoes11201

For the Dimension Creation transformation type, members for which VISIBLE and SELECTED are set to 1 (TRUE) are created in the Essbase outline and are tagged with the “eal” UDA. See About User-Defined Attributes (UDAs).

Representation of the Essbase ESSCustom1 dimension outline:

ESSCustom1
   GolfProducts   (Never Share) (UDAS: eal)
      GolfBalls   (Never Share) (UDAS: eal)
      GolfShoes   (Never Share) (UDAS: eal)
      GolfClubs    (Never Share) (UDAS: eal)
   ShoesProducts   (Never Share) (UDAS: eal)
      GolfShoes   (Shared Member)
      TennisShoes   (Never Share) (UDAS: eal)

If, before creating the target database, the dimension exists in the target database, Analytics Link removes the existing dimension and recreates it based on the target grid definition.

Dimension Mapping Transformation Type

The Dimension Mapping transformation type maps members in one Essbase dimension to members in one Financial Management dimension. The Essbase dimension must already exist in the Essbase outline.

The Dimension Mapping transformation type supports:

  • Member selection, based on the Essbase dimension

  • Member name mapping

  • Prefix (see About Prefixes)

The same Financial Management dimension cannot be used in Dimension Mapping and Break Dimension transformation types within the same bridge.

In this example, assume that you created a Period dimension in an Essbase outline and you want to map its members to the members in the Financial Management Period dimension.

Representation of the existing Essbase Period dimension outline:

Period
   3Months
      HY1
         Q1
            JUL
            AUG
            SEP
         Q2
            OCT
            NOV
            DEC
      HY2
         Q3
            JAN
            FEB
            MAR
         Q4
            APR
            MAY
            JUN
   4Months   (Dymanic Calc)
      T1   (Dymanic Calc)   [Formula: ="JUL"+"AUG"+"SEP"+"OCT";]
         JUL   (Shared Member)
         AUG   (Shared Member)
         SEP   (Shared Member)
         OCT   (Shared Member)
      T2   (Dymanic Calc)   [Formula: ="NOV"+"DEC"+"JAN"+"FEB";]
         NOV   (Shared Member)
         DEC   (Shared Member)
         JAN   (Shared Member)
         FEB   (Shared Member)
      T3   (Dymanic Calc)   [Formula: ="MAR"+"APR"+"MAY"+"JUN";]
         MAR   (Shared Member)
         APR   (Shared Member)
         MAY   (Shared Member)
         JUN   (Shared Member)

Representation of the Financial Management Period dimension:

Period
   HalfYear1
      Quarter1
         July
         August
         September
      Quarter2
         October
         November
         December
   HalfYear2
      Quarter3
         January
         February
         March
      Quarter4
         April
         May
         June

Table 11 represents the information that you must provide on the Target Design Grid tab:

Table 11. Target Design Grid tab: Dimension Mapping Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Dimension MappingPeriodPeriod3MonthstableName 

Table 12, Analytics Link Mapping Table Template: Dimension Mapping Transformation Type shows an Analytics Link-created mapping table template. Analytics Link fills the TARGET_MEMBER column with the member names from the Essbase Period dimension. The HFM_MEMBER_Period column is filled with the same names from the TARGET_MEMBER column. SELECTED is set to 1 (TRUE) for 3Months and its descendants; the other members are set to 0 (FALSE).

Table 12. Analytics Link Mapping Table Template: Dimension Mapping Transformation Type

TARGET_MEMBERHFM_MEMBER_PeriodVISIBLEISBASEGENERATIONDIM_LEVELSELECTED
3Months3Months10231
HY1HY110321
Q1Q110411
JULJUL11501
AUGAUG11501
SEPSEP11501
Q2Q210411
OCTOCT11501
NOVNOV11501
DECDEC11501
HY2HY210321
Q3Q310411
JANJAN11501
FEBFEB11501
MARMAR11501
Q4Q410411
APRAPR11501
MAYMAY11501
JUNJUN11501
4Months4Months10220
T1T110310
T2T210310
T3T310310

To map Essbase members to Financial Management members, modify the member names in the HFM_MEMBER_DIM column to match the Financial Management member names. As shown in Table 13, User-Updated Mapping Table Template: Dimension Mapping Transformation Type, Essbase member 3Months, and its descendants, are mapped to Financial Management member names.

Table 13. User-Updated Mapping Table Template: Dimension Mapping Transformation Type

TARGET_MEMBERHFM_MEMBER_PeriodVISIBLEISBASEGENERATIONDIM_LEVELSELECTED
3MonthsYear10231
HY1HalfYear110321
Q1Quarter110411
JULJuly11501
AUGAugust11501
SEPSeptember11501
Q2Quarter210411
OCTOctober11501
NOVNovember11501
DECDecember11501
HY2HalfYear210321
Q3Quarter310411
JANJanuary11501
FEBFebruary11501
MARMarch11501
Q4Quarter410411
APRApril11501
MAYMay11501
JUNJune11501
4Months4Months10220
T1T110310
T2T210310
T3T310310

Members for which VISIBLE and SELECTED are set to 1 (TRUE) are tagged with the “eal” UDA in the Essbase outline. See About User-Defined Attributes (UDAs).

Representation of the Essbase Period dimension outline:

Period
   3Months   (UDAS: eal)
      HY1   (UDAS: eal)
         Q1   (UDAS: eal)
            JUL   (UDAS: eal)
            AUG   (UDAS: eal)
            SEP   (UDAS: eal)
         Q2   (UDAS: eal)
            OCT   (UDAS: eal)
            NOV   (UDAS: eal)
            DEC   (UDAS: eal)
      HY2   (UDAS: eal)
         Q3   (UDAS: eal)
            JAN   (UDAS: eal)
            FEB   (UDAS: eal)
            MAR   (UDAS: eal)
         Q4   (UDAS: eal)
            APR   (UDAS: eal)
            MAY   (UDAS: eal)
            JUN   (UDAS: eal)
   4Months   (Dymanic Calc)
      T1   (Dymanic Calc)   [Formula: ="JUL"+"AUG"+"SEP"+"OCT";]
         JUL   (Shared Member)
         AUG   (Shared Member)
         SEP   (Shared Member)
         OCT   (Shared Member)
      T2   (Dymanic Calc)   [Formula: ="NOV"+"DEC"+"JAN"+"FEB";]
         NOV   (Shared Member)
         DEC   (Shared Member)
         JAN   (Shared Member)
         FEB   (Shared Member)
      T3   (Dymanic Calc)   [Formula: ="MAR"+"APR"+"MAY"+"JUN";]
         MAR   (Shared Member)
         APR   (Shared Member)
         MAY   (Shared Member)
         JUN   (Shared Member)

Not in Outline Transformation Type

Financial Management dimensions that are set to the Not in Outline transformation type are not displayed in the target database.

The Not in Outline transformation type requires member selection, based on the Financial Management dimension. For Analytics Link to return data for Essbase queries, coordinates for all Financial Management dimensions, even dimensions that are not displayed in the Essbase outline, must be known. Therefore, for each Financial Management dimension that is set to the Not in Outline transformation type, you must specify a single Financial Management member that represents the coordinate for that dimension.

For example, assume that you do not want the Financial Management ICP dimension displayed in an Essbase target database and [ICP Top] represents the coordinate for the ICP dimension. Table 14 represents the information that you must provide on the Target Design Grid tab:

Table 14. Target Design Grid tab: Not in Outline Transformation Type

Transformation TypeSource DimensionsTarget Dimensions[1]Member SelectionMapping TablePrefix
Not in OutlineICP [ICP Top]  

1 Because the Financial Management dimension is not displayed in the Essbase outline, the Target Dimension field is not available.

See Removing Financial Management Dimensions from the Essbase Outline.

New Dimension Transformation Type

Use the New Dimension transformation type when the Essbase outline has an existing Essbase dimension that is not represented in the Financial Management application.

The New Dimension transformation type requires member selection, based on the Essbase dimension.

For example, assume that the Essbase outline includes a dimension named Source, in which there are two members: Hfm and NonHfm. For member Hfm, you want the source of the data to be Financial Management data; for member NonHfm, you do not want the source of the data to be Financial Management data.

On the Target Design Grid tab, in an empty row, select the New Dimension transformation type. Table 15 represents the information that you must provide on the Target Design Grid tab:

Table 15. Target Design Grid tab: New Dimension Transformation Type

Transformation TypeSource Dimensions[1]Target DimensionsMember SelectionMapping TablePrefix
New Dimension SourceHfm  

1 Because the Essbase dimension does not exist in the Financial Management application, the Source Dimension field is not available.

In the Essbase outline, member Hfm is tagged with the “eal” UDA and Essbase query results are returned from Data Synchronization Server. Queries on member NonHfm do not involve Data Synchronization Server. See About User-Defined Attributes (UDAs).

Representation of the Essbase Source dimension outline:

Source
   Hfm   (UDAS: eal)
   NonHfm

Join Dimensions Transformation Type

The Join Dimensions transformation type maps members from multiple Financial Management dimensions to one Essbase target dimension. The Essbase dimension must already exist in the Essbase outline. Analytics Link does not create the Essbase dimension.

The Join Dimensions transformation type supports:

  • Member selection, based on the Essbase dimension

  • Member name mapping (required)

The same Financial Management dimension cannot be used in Join Dimensions and Break Dimension transformation types within the same bridge.

In this example, assume that you want to map all of the members in the Time dimension in an Essbase outline to members in the Financial Management Period and View dimensions.

Representation of the Essbase Time dimension outline:

Time
   Months
      JUL
      AUG
      SEP
   YearToDate
      YTDJUL
      YTDAUG
      YTDSEP

Representation of the Financial Management Period dimension:

Period
   HalfYear1
      Quarter1
         July
         August
         September

Representation of the Financial Management View dimension:

View
   Scenario View
   Periodic
   YTD
   HYTD
   QTD

Table 16 represents the information that you must provide on the Target Design Grid tab:

Table 16. Target Design Grid tab: Join Dimensions Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Join DimensionsPeriod,ViewTime tableName 

After defining Financial Management dimensions in the Source Dimensions column, you must delete the rows in the mapping grid that duplicate any of the specified Financial Management dimensions. In this example, the row for the Period dimension was used to define the Join Dimensions transformation type for the Period and View dimensions. Therefore, you must delete the default row for the View dimension.

Table 17, Analytics Link Mapping Table Template: Join Dimensions Transformation Type shows a Analytics Link-created mapping table template. Analytics Link fills the TARGET_MEMBER column with all of the member names from the Essbase dimension. The HFM_MEMBER_Period column is filled with the same names from the TARGET_MEMBER column. The HFM_MEMBER_View column is filled with [None]. For all members, VISIBLE is set to 1 (TRUE). SELECTED is set to 1 (TRUE) for all members because member selection was not limited.

Table 17. Analytics Link Mapping Table Template: Join Dimensions Transformation Type

TARGET_MEMBERHFM_MEMBER_PeriodHFM_MEMBER_ViewISBASEGENERATIONDIM_LEVELVISIBLESELECTED
MonthsMonths[None]02111
JULJUL[None]13011
AUGAUG[None]13011
SEPSEP[None]13011
YearToDateYearToDate[None]02111
YTDJULYTDJUL[None]13011
YTDAUGYTDAUG[None]13011
YTDSEPYTDSEP[None]13011

To map Essbase members to Financial Management members, modify the member names in the HFM_MEMBER_Period and HFM_MEMBER_View columns to match Financial Management members, as shown in Table 18.

Table 18. User-Updated Mapping Table: Join Dimensions Transformation Type

TARGET_MEMBERHFM_MEMBER_PeriodHFM_MEMBER_ViewISBASEGENDIM_LEVELVISIBLESELECTED
MonthsQuarter1Periodic02111
JULJulyPeriodic13011
AUGAugustPeriodic13011
SEPSeptemberPeriodic13011
YearToDateQuarter1YTD02111
YTDJULJulyYTD13011
YTDAUGAugustYTD13011
YTDSEPSeptemberYTD13011

In the Essbase outline, all members for which VISIBLE and SELECTED are set to 1 (TRUE) are tagged with the “eal” UDA. See About User-Defined Attributes (UDAs).

Representation of the Essbase Time dimension outline:

Time
   Months   (UDAS: eal)
      JUL   (UDAS: eal)
      AUG   (UDAS: eal)
      SEP   (UDAS: eal)
   YearToDate   (UDAS: eal)
      YTDJUL   (UDAS: eal)
      YTDAUG   (UDAS: eal)
      YTDSEP   (UDAS: eal)

Caution!

In the Join Dimensions transformation type, you are not allowed to map multiple Essbase members to the same Financial Management dimensions member combination, as shown in Table 19.

Table 19. User-Updated Mapping Table: Join Dimensions Transformation Type

TARGET_MEMBERHFM_MEMBER_PERIODHFM_MEMBER_VIEWISBASEGENDIM_LEVELVISIBLESELECTED
AJulyPeriodic13011
BJulyPeriodic13011

Break Dimension Transformation Type

The Break Dimension transformation type breaks one Financial Management Custom dimension (Custom1, Custom2, Custom3, or Custom4) into multiple Essbase dimensions. The target Essbase dimensions must already exist in the Essbase database outline. The Break Dimension transformation type does not create Essbase dimensions.

In the Data Synchronization Server database, the Financial Management Custom dimension is replaced with new dimensions. These dimensions and their hierarchies are created using the member list from the mapping table and the hierarchical structure of the Financial Management Custom dimension. The number of new dimensions in the Data Synchronization Server database is the same as the number of Essbase target dimensions.

In Data Synchronization Server, Analytics Link adds two members to each new dimension, as shown in Figure 4, New Dimension Hierarchy: Top and EAL.None Members:

  • Top—This member represents the top member of the new dimension

  • EAL.None—This member tells Data Synchronization Server that the new dimension does not participate in queries.

Figure 4. New Dimension Hierarchy: Top and EAL.None Members

This image shows a diagram in which the Top and EAL.None members are specified and the rest of the dimension is unspecified.

In the new dimensions, Analytics Link does not use the aggregation rules (for example, AggrWeight or SwitchSignForFlow) of the source Financial Management dimension. Data Synchronization Server aggregates the new dimensions based only on their hierarchies in the Data Synchronization Server database. Essbase dimension hierarchies are used only for reporting. Therefore, if an Essbase dimension hierarchy does not match the hierarchy in the Data Synchronization Server database, an Essbase query will return unexpected results. For example, assume that the Data Synchronization Server database includes the following hierarchy, in which members A and B are children of member C. The values of the base members are aggregated into the value of the parent member:

DSS Hierarchy   Member Value
   C            6
      A         5
      B         1

If, in an existing Essbase dimension hierarchy, members A and C are children of member B, the member values would not properly represent the Data Synchronization Server hierarchy:

Essbase Hierarchy   Member Value
   B                1
      A             5
      C             6

The Break Dimension transformation type supports:

  • Member selection, based on the Financial Management source dimension

  • Member name mapping (required)

The same Financial Management dimension cannot be used in the following transformation types within the same bridge: Break Dimension and Join Dimensions; Break Dimension and Dimension Creation; and Break Dimension and Dimension Mapping.

See the following break dimension examples:

Breaking a Financial Management Dimension Hierarchy into Multiple Essbase Dimensions

You can break hierarchy branches in a Financial Management dimension into separate Essbase dimensions.

In this example, assume that you want to map the Shoes hierarchy in the Financial Management Custom1 dimension to the Essbase ShoesDim dimension and the Balls hierarchy to the Essbase BallsDim dimension.

Representation of the Financial Management Custom1 hierarchy:

Custom1
   [None]
   AllProducts
      Golf
         GolfBalls
         GolfShoes
         GolfTees
         GolfClubs
      Tennis
         TennisBalls
         TennisShoes
         TennisRacquets
   Balls
      GolfBalls
      TennisBalls
   Shoes
      GolfShoes
      TennisShoes

Representation of the existing Essbase ShoesDim dimension outline:

ShoesDim
   ShoesDim.Total
      ShoesDim.None
      Shoes
         GolfShoes
         TennisShoes

Representation of the existing Essbase BallsDim dimension outline:

BallsDim
   BallsDim.Total
      BallsDim.None
      Balls
         GolfBalls
         TennisBalls

Table 20 represents the information that you must provide on the Target Design Grid tab:

Table 20. Target Design Grid tab: Break Dimension Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Break DimensionCustom1ShoesDim,BallsDimShoes,BallstableName 

Table 21, Analytics Link Mapping Table Template: Break Financial Management Dimension Hierarchy Example shows the Analytics Link-created mapping table template. Based on the member selection specification, SELECTED is set to 1 (TRUE) for Shoes and Balls, and their descendants (such as GolfShoes and GolfBalls, respectively), and Top and EAL.None; the other members are set to 0 (FALSE).

Table 21. Analytics Link Mapping Table Template: Break Financial Management Dimension Hierarchy Example

HFM_MEMBERTARGET_MEMBER_SHOESDIMVISIBLE_SHOESDIMTARGET_MEMBER_BALLSDIMVISIBLE_BALLSDIMISBASEGENDIM_LEVELSELECTED
[None][None]1[None]01000
AllProductsAllProducts1AllProducts00020
BallsBalls1Balls00011
GolfGolf1Golf00110
GolfBallsGolfBalls1GolfBalls01201
GolfClubsGolfClubs1GolfClubs01200
GolfShoesGolfShoes1GolfShoes01201
GolfTeesGolfTees1GolfTees01200
ShoesShoes1Shoes00011
TennisTennis1Tennis00110
TennisBallsTennisBalls1TennisBalls01201
TennisRacquetsTennisRacquets1TennisRacquets01200
TennisShoesTennisShoes1TennisShoes01201
TopShoesDim.Top1BallsDim.Top11111
EAL.NoneShoesDim.None1BallsDim.None11111

Table 22, User-Updated Mapping Table Template: Break Financial Management Dimension Hierarchy Example shows an updated mapping table:

  • In the VISIBLE_SHOESDIM column, VISIBLE is set to 0 (FALSE) for members for which SELECTED is set to 1 (TRUE) but that are not part of ShoesDim (such as Balls and TennisBalls). For the same members, in the VISIBLE_BALLSDIM column, VISIBLE is set to 1 (TRUE). In the visible column for each Essbase dimension, it is not necessary to change the default visible settings for members for which SELECTED is set to 0 (FALSE).

  • For each Essbase dimension, the Top member is renamed to dimName.Total.

Table 22. User-Updated Mapping Table Template: Break Financial Management Dimension Hierarchy Example

HFM_MEMBERTARGET_MEMBER_SHOESDIMVISIBLE_SHOESDIMTARGET_MEMBER_BALLSDIMVISIBLE_BALLSDIMISBASEGENDIM_LEVELSELECTED
[None][None]0[None]01000
AllProductsAllProducts0AllProducts00020
BallsBalls0Balls10011
GolfGolf0Golf00110
GolfBallsGolfBalls0GolfBalls11201
GolfClubsGolfClubs0GolfClubs01200
GolfShoesGolfShoes1GolfShoes01201
GolfTeesGolfTees0GolfTees01200
ShoesShoes1Shoes00011
TennisTennis0Tennis00110
TennisBallsTennisBalls0TennisBalls11201
TennisRacquetsTennisRacquets0TennisRacquets01200
TennisShoesTennisShoes1TennisShoes01201
TopShoesDim.Total1BallsDim.Total11111
EAL.NoneShoesDim.None1BallsDim.None11111

Members for which VISIBLE and SELECTED are set to 1 (TRUE) are tagged with the “eal” UDA in the Essbase outline. See About User-Defined Attributes (UDAs).

Representation of the ShoesDim and BallsDim hierarchies in the Essbase outline:

ShoesDim
   ShoesDim.Total   (UDAS: eal)
      ShoesDim.None   (UDAS: eal)
      Shoes    (UDAS: eal)
         GolfShoes   (UDAS: eal)
         TennisShoes   (UDAS: eal)
BallsDim
   BallsDim.Total   (UDAS: eal)
      BallsDim.None   (UDAS: eal)
      Balls   (UDAS: eal)
         GolfBalls   (UDAS: eal)
         TennisBalls   (UDAS: eal)

Financial Management application member values:

Member         Value
GolfBalls      5
GolfShoes      7
TennisBalls    4
TennisShoes    3
Balls          9 (GolfBalls + TennisBalls)
Shoes          10(GolfShoes + TennisShoes)

Example Essbase report script:

{ WIDTH 25 }
"Actual"
"2006"
"July"
"Sales"
"Customer2"
"ESSCustom3.FM None"
"Increases"
"Stamford"
"FM Entity Currency"
"FM ICP None"
"Periodic"
<ROW ("ShoesDim")
<IDESC "ShoesDim"
<COLUMN ("BallsDim")
<IDESC "BallsDim"
!

Resulting Essbase report:

Actual  2006  July  Sales  Customer2  ESSCustom3.FM None  Increases  Stamford  FM Entity  Currency  FM ICP None  Periodic

                   BallsDim.None GolfBalls TennisBalls Balls    BallsDim.Total BallsDim
                  ============== ========= =========== ====     ============== ========
   ShoesDim.None     #MISSING    5         4           9        9              9
GolfShoes            7           #MISSING  #MISSING    #MISSING 7              7
TennisShoes          3           #MISSING  #MISSING    #MISSING 3              3
   Shoes             10          #MISSING  #MISSING    #MISSING 10             10
      ShoesDim.Total 10          5         4           9        19             19
         ShoesDim    10          5         4           9        19             19

Breaking Base Members in a Financial Management Dimension into Multiple Essbase Dimensions

The Break Dimension transformation type can be used to break the base members in one Financial Management dimension hierarchy into separate Essbase dimensions.

In this example, assume that you want the base members from the Golf and Tennis hierarchies in the Financial Management Custom1 dimension split into two Essbase dimensions.

Representation of the Financial Management Custom1 hierarchy:

Custom1
   [None]
   AllProducts
      Golf
         GolfBalls
         GolfShoes
         GolfTees
         GolfClubs
      Tennis
         TennisBalls
         TennisShoes
         TennisRacquets
   Balls
      GolfBalls
      TennisBalls
   Shoes
      GolfShoes
      TennisShoes

Representation of the existing Essbase Sports dimension outline:

Sports
   Golf
   Tennis

Representation of the existing Essbase Products dimension outline:

Products
   Balls
   Clubs
   Shoes
   Racquets
   Tees

The combination of members from these dimensions will represent one member of the Financial Management dimension. For example, the combination of Products.Balls and Sports.Golf represents the GolfBalls member in the Financial Management Custom1 dimension.

Table 23 represents the information that you must provide on the Target Design Grid tab:

Table 23. Target Design Grid tab: Break Dimension Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Break DimensionCustom1Sports,ProductsGolf,TennistableName 

Table 24, Analytics Link Mapping Table Template: Consolidating Base Members Example shows the Analytics Link-created mapping table template. Based on the member selection specification, SELECTED is set to 1 (TRUE) for Golf and Tennis, and their descendants, and Top and EAL.None; the other members are set to 0 (FALSE).

Table 24. Analytics Link Mapping Table Template: Consolidating Base Members Example

HFM_MEMBERTARGET_MEMBER_SPORTSVISIBLE_SPORTSTARGET_MEMBER_PRODUCTSVISIBLE_PRODUCTSISBASEGENDIM_LEVELSELECTED
[None][None]1[None]01000
AllProductsAllProducts1AllProducts00020
BallsBalls1Balls00010
GolfGolf1Golf00111
GolfBallsGolfBalls1GolfBalls01201
GolfClubsGolfClubs1GolfClubs01201
GolfShoesGolfShoes1GolfShoes01201
GolfTeesGolfTees1GolfTees01201
ShoesShoes1Shoes00010
TennisTennis1Tennis00111
TennisBallsTennisBalls1TennisBalls01201
TennisRacquetsTennisRacquets1TennisRacquets01201
TennisShoesTennisShoes1TennisShoes01201
TopSports.Top1Products.Top11111
EAL.NoneSports.None1Products.None11111

Table 25, User-Updated Mapping Table Template: Consolidating Base Members Example shows an updated mapping table:

  • In the TARGET_MEMBER_SPORTS column, to consolidate the values of the Golf, GolfBalls, GolfShoes, GolfBalls, and GolfShoes members, each member is named Golf. Similarly, to consolidate the values of the Tennis, TennisBalls, TennisRacquets, and TennisShoes members, each member is named Tennis. By default, in the VISIBLE_SPORTS column, the VISIBILITY property for all members that are included in the member selection specification is set to 1 (TRUE). For the Sports.None member, for which SELECTED is set to 1 (TRUE) but that you do not want displayed in the Sports dimension, the VISIBILITY property is set to 0 (FALSE).

  • In the TARGET_MEMBER_PRODUCTS column, base members are renamed according to product groups. For example, GolfBalls and TennisBalls are renamed to Balls, and GolfClubs is renamed to Clubs. By default, in the VISIBLE_PRODUCTS column, the VISIBILITY property for all members is set to 0 (FALSE), except for Top and EAL.None. Therefore, the VISIBILITY property is set to 1 (TRUE) for members that you want included in the Products dimension (such as GolfBalls and Top). For the Products.None member, for which SELECTED is set to 1 (TRUE) but that you do not want displayed in the Products dimension, the VISIBILITY property is set to 0 (FALSE).

Table 25. User-Updated Mapping Table Template: Consolidating Base Members Example

HFM_MEMBERTARGET_MEMBER_SPORTSVISIBLE_SPORTSTARGET_MEMBER_PRODUCTSVISIBLE_PRODUCTSISBASEGENDIM_LEVELSELECTED
[None][None]0[None]01000
AllProductsAllProducts0AllProducts00020
BallsBalls0Balls00010
GolfGolf1Golf00111
GolfBallsGolf1Balls11201
GolfClubsGolf1Clubs11201
GolfShoesGolf1Shoes11201
GolfTeesGolf1Tees11201
ShoesShoes0Shoes00010
TennisTennis1Tennis00111
TennisBallsTennis1Balls11201
TennisRacquetsTennis1Racquets11201
TennisShoesTennis1Shoes11201
TopSports1Products11111
EAL.NoneSports.None0Products.None01111

Members for which VISIBLE and SELECTED are set to 1 (TRUE) are tagged with the “eal” UDA in the Essbase outline. See About User-Defined Attributes (UDAs).

Representation of the Sports and Products dimension hierarchies in the Essbase outline:

Sports   (UDAS: eal)
   Golf   (UDAS: eal)
   Tennis   (UDAS: eal)
Products   (UDAS: eal)
   Balls   (UDAS: eal)
   Clubs   (UDAS: eal)
   Shoes   (UDAS: eal)
   Racquets   (UDAS: eal)
   Tees   (UDAS: eal)

Representation of the Products dimension hierarchy Essbase outline:

Financial Management application member values:

Member           Value
Golf             31
GolfBalls        5
GolfShoes        7
GolfTees         11
GolfClubs        8
Tennis           15
TennisBalls      4
TennisShoes      2
TennisRacquets   9

Example Essbase report script:

{ WIDTH 25 }
"Actual"
"2006"
"July"
"Sales"
"Customer2"
"ESSCustom3.FM None"
"Increases"
"Stamford"
"FM Entity Currency"
"FM ICP None"
"Periodic"
<ROW ("Sports")
<IDESC "Sports"
<COLUMN ("Products")
<IDESC "Products"
!

Resulting Essbase report:

Actual  2006  July  Sales  Customer2  ESSCustom3.FM None  Increases  Stamford  FM Entity  Currency  FM ICP None  Periodic

            Balls   Clubs      Shoes   Tees      Racquets   Products
            =====   =========  =====   ========  ========   ========
Golf        5       8          7       11        #MISSING   31
Tennis      4       #MISSING   2       #MISSING  9          15
   Sports   9       8          9       11        9          46

Breaking Nonbase Members in a Financial Management Dimension into Multiple Essbase Dimensions

The Break Dimension transformation type can be used to break a nonbase member and its children in a Financial Management dimension hierarchy into separate Essbase dimensions.

In this example, assume you want to break the AllCustomers nonbase member and its children in Financial Management Custom2 dimension into two existing Essbase dimensions: CUSTOMERS2_3 and CUSTOMERS4_5. The AllCustomers member does not appear in either Essbase dimension.

Representation of the Financial Management Custom2 dimension:

Custom1
   [None]
   AllCustomers
      Customer2
      Customer3
      Customer4
      Customer5

Representation of the existing Essbase CUSTOMERS2_3 dimension outline:

CUSTOMERS2_3
   CUSTOMERS2_3.None
   Customer2
   Customer3

Representation of the existing Essbase CUSTOMERS4_5 dimension outline:

CUSTOMERS4_5
   CUSTOMERS4_5.None
   Customer4
   Customer5

Table 26 represents the information that you must provide on the Target Design Grid tab:

Table 26. Target Design Grid tab: Break Dimension Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Break DimensionCustom2CUSTOMERS2_3,CUSTOMERS4_5 tableName 

Table 27 shows a representation of a Analytics Link-created mapping table template.

Table 27. Analytics Link Mapping Table Template: Breaking Nonbase Members

HFM_MEMBERTARGET_MEMBER_CUSTOMERS2_3VISIBLE_CUSTOMERS2_3TARGET_MEMBER_CUSTOMERS4_5VISIBLE_CUSTOMERS4_5SELECTED
[None][None]1[None]01
AllCustomersAllCustomers1AllCustomers01
Customer2Customer21Customer201
Customer3Customer31Customer301
Customer4Customer41Customer401
Customer5Customer51Customer501
TopCUSTOMERS2_3.Top1CUSTOMERS4_5.Top11
EAL.NoneCUSTOMERS2_3.None1CUSTOMERS4_5.None11

Table 28, User-Updated Mapping Table Template: Breaking Nonbase Members shows an updated mapping table:

  • In the TARGET_MEMBER_CUSTOMERS2_3 column, Customer2 and Customer3 are consolidated under the Top member, which has been renamed to CUSTOMERS2_3. In the VISIBLE_CUSTOMERS2_3 column, VISIBLE is set to 0 (FALSE) for members that are not part of the CUSTOMERS2_3 dimension.

  • In the TARGET_MEMBER_CUSTOMERS4_5 column, Customer4 and Customer5 are consolidated under the Top member, which has been renamed to CUSTOMERS4_5. In the VISIBLE_CUSTOMERS4_5 column, VISIBLE is set to 1 (TRUE) for members that are part of the CUSTOMERS4_5 dimension.

Table 28. User-Updated Mapping Table Template: Breaking Nonbase Members

HFM_MEMBERTARGET_MEMBER_CUSTOMERS2_3VISIBLE_CUSTOMERS2_3TARGET_MEMBER_CUSTOMERS4_5VISIBLE_CUSTOMERS4_5SELECTED
[None][None]0[None]01
AllCustomersAllCustomers0AllCustomers01
Customer2Customer21Customer201
Customer3Customer31Customer301
Customer4Customer40Customer411
Customer5Customer50Customer511
TopCUSTOMERS2_31CUSTOMERS4_511
EAL.NoneCUSTOMERS2_3.None1CUSTOMERS4_5.None11

Members for which VISIBLE and SELECTED are set to 1 (TRUE) are tagged with the “eal” UDA in the Essbase outline. See About User-Defined Attributes (UDAs).

Representation of the CUSTOMERS2_3 dimension hierarchy in the Essbase outline:

CUSTOMERS2_3   (UDAS: eal)
   CUSTOMERS2_3.None   (UDAS: eal)
   Customer2   (UDAS: eal)
   Customer3   (UDAS: eal)

Representation of the CUSTOMERS4_5 dimension hierarchy in the Essbase outline:

CUSTOMERS4_5   (UDAS: eal)
   CUSTOMERS4_5.None   (UDAS: eal)
   Customer4   (UDAS: eal)
   Customer5   (UDAS: eal)

Financial Management application member values:

Member         Value
AllCustomers   27
Customer2      5
Customer3      7
Customer4      4
Customer5      11

Example Essbase report script:

{ WIDTH 25 }
"Actual"
"2006"
"July"
"Sales"
"GolfBalls"
"ESSCustom3.FM None"
"Increases"
"Stamford"
"FM Entity Currency"
"FM ICP None"
"Periodic"
<ROW ("CUSTOMERS2_3")
<IDESC "CUSTOMERS2_3"
<COLUMN "CUSTOMERS4_5")
<IDESC "CUSTOMERS4_5"
!

Resulting Essbase report:

Actual  2006  July  Sales  GolfBalls  ESSCustom3.FM None  Increases  Stamford  FM Entity  Currency  FM ICP None  Periodic

                     Customers4_5.None   Customer4   Customer5   Customer4_5
                     =================   =========   =========   ===========
Customers2_3.None   #MISSING             4           11          15
Customer2           5                    #MISSING    #MISSING    5
Customer3           7                    #MISSING    #MISSING    7
   Customers2_3     12                   4           11          27

Matrix Mapping Transformation Type

The Matrix Mapping transformation type maps n Financial Management dimensions to m Essbase dimensions using definition mapping expressions (rules), instead of direct member-to-member mapping that is used in other transformation types. (You can, however, use Matrix Mapping to directly map members from the source dimension to the target dimension.)

The Matrix Mapping transformation type is supported only for Essbase replicated partition or RDBMS database bridge targets. You can define more than one Matrix Mapping transformation type in a bridge.

The Matrix Mapping transformation type requires a mapping table to define the matrix mapping rules.

In creating a mapping table template for the Matrix Mapping transformation type, Analytics Link creates columns for each dimension specified on the mapping grid: SRC_hfmDim for each source dimension and DEST_targetDim for each target dimension. When the Financial Management Entity dimension is specified as a source dimension, Analytics Link also creates a column, named SRC_PARENT, for the Entity Parent.

No default data is used to populate the mapping table. You must create rows and define the expressions for transforming the source intersection to the target intersection. Each row in the mapping table represents one rule. Each field in a row must contain an expression. See Table 29.

Table 29. User-Updated Mapping Table Template: Matrix Mapping Transformation Type

SRC_hfmDimDEST_targetDim
source_expressiondestination_expression

For valid expressions, see:

Analytics Link implements matrix mapping rules during the process of extracting the region data set from the Data Synchronization Server database to the target database. Analytics Link starts with the first row in the data set and applies each mapping rule, one at a time, to the data set row. After applying all of the rules on the first row, Analytics Link goes to the next row in the data set, and so on. When the rule source expression results in a match, the corresponding target expression is implemented and a destination cell is created. If the rule source expression does not result in a match, a destination cell is not created.

In the following example, assume that you are extracting the region described in Table 30 from the Data Synchronization Server database into an Essbase database through a replicated partition:

Table 30. Region Definition: Matrix Mapping Example

HFM DimensionSelection TypeMember Selection
YearMEMBERS2006
ScenarioMEMBERSActual
EntityMEMBERSConnecticut.Stamford
PeriodMEMBERSJuly
ICPMEMBERS[ICP None]
ValueMEMBERSUSD
Custom1MEMBERSGolfBalls
Custom2MEMBERSCustomer2
Custom3MEMBERS[None]
Custom4MEMBERSIncreases
ViewMEMBERSYTD
AccountMEMBERSSales

The data set that is extracted from Data Synchronization Server consists of one row of data, defined by the following coordinates:

Actual 2006 July Sales GolfBalls Customer2 [None] Increases Stamford USD [ICP None] YTD

Data for this region in Financial Management:

       Actual
July   76.00

Assume that you want to copy the value of the Actual member in the Financial Management Scenario dimension into the Budget member in the Essbase EALScenario dimension.

Table 31 represents the information that you must provide on the Target Design Grid tab:

Table 31. Target Design Grid tab: Matrix Mapping Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Matrix MappingScenarioEALScenario tableName 

Table 32 shows a representation of a user-updated mapping table, in which there is one rule that maps the value of the Actual member to the Budget member in the destination dimension. The source and destination expressions use member names.

Table 32. User-Updated Mapping Table Template: Matrix Mapping Transformation Type

SRC_SCENARIODEST_EALSCENARIO
ActualBudget

After updating the mapping table, you must synchronize the mapping changes on the Target Design Grid tab and then extract the region on the Extract tab.

Analytics Link implements the one rule in the mapping table on the only row in the data set. The source expression matches the Actual member in the Scenario dimension. Therefore, the corresponding destination expression is implemented. Analytics Link creates a destination cell and copies the value of Actual to the Budget member in the EALScenario dimension. In the extracted data set, the value of Budget is 76 but there is no value for Actual.

Example Essbase report script:

{ WIDTH 12 }
ROW ("SCENARIO")
"Actual"
"Budget"
"2006"
"July"
"Sales"
"GolfBalls"
"Customer2"
"ESSCustom3.FM None"
"Increases"
"Connecticut.Stamford"
"USD"
"FM ICP None"
"YTD"
!

Resulting Essbase report:

Actual  Budget  2006  July  Sales  GolfBalls  Customer2 ESSCustom3.FM None  Increases  Connecticut.Stamford  USD  FM ICP None  YTD

Actual   #MISSING
Budget   76

Now assume that the matrix mapping table has two rules, as shown in Table 33. The second rule maps the value of Actual to a member with the same name in the destination dimension.

Table 33. User-Updated Mapping Table Template: Matrix Mapping Transformation Type

SRC_SCENARIODEST_EALSCENARIO
ActualBudget
Actual=Scenario

For the only row of the data set, Analytics Link implements the first rule. The source dimension expression, member name Actual, results in a match and a new destination cell is created using the destination dimension expression: the member in the destination dimension is named Budget and its value is the same value of the Financial Management Actual member. Then Analytics Link implements the second rule on the row. The source dimension expression again matches the Actual member in the Scenario dimension and a destination cell is created. The member in the destination dimension is also named Actual and its value is the same value of the Financial Management Actual member. In the extracted data set, the values of Actual and Budget are 76.

Resulting Essbase report:

Actual  Budget  2006  July  Sales  GolfBalls  Customer2 ESSCustom3.FM None  Increases  Connecticut.Stamford  USD  FM ICP None  YTD

Actual   76
Budget   76

In the following example, assume that you want to change the hierarchy in the Financial Management Period dimension, which uses Year, HalfYear, Quarter, and Month, into a hierarchy that uses Year and Trimester in the Essbase ESSPeriod dimension.

Representation of the Financial Management Period dimension hierarchy:

Year
   HalfYear1
      Quarter1
         July
         August
         September
      Quarter2
         ...
   HalfYear2
      ...

Representation of the Essbase ESSPeriod dimension:

ESSPeriod
   Year
      Trimester1
      Trimester2
      Trimester3

Assume that you are extracting the region described in Table 34 from the Data Synchronization Server database into Essbase. For the Period dimension, all base members, which are the 12 months of the year, are included in the data set.

Table 34. Region Definition: Matrix Mapping Example

HFM DimensionSelection TypeMember Selection
YearMEMBERS2006
ScenarioMEMBERSActual
EntityMEMBERSConnecticut.Stamford
PeriodBASE 
ICPMEMBERS[ICP None]
ValueMEMBERSUSD
Custom1MEMBERSGolfBalls
Custom2MEMBERSCustomer2
Custom3MEMBERS[None]
Custom4MEMBERSIncreases
ViewMEMBERSPeriodic
AccountMEMBERSSales

The data set that is extracted from Data Synchronization Server consists of 12 rows of data (one row for each Period dimension base member). For example, the following coordinates are for July:

Actual 2006 July Sales GolfBalls Customer2 [None] Increases Stamford USD [ICP None] YTD

Data for this region in Financial Management:

       July  August  September  October  November  December  January  February  March  April  May   June

Sales  7.00  13.00   17.00      6.00     12.00     3.00      5.00     2.00      9.00   1.00   8.00  11.00

To map the member names, you must use a mapping table. Table 35 represents the information that you must provide on the Target Design Grid tab:

Table 35. Target Design Grid tab: Matrix Mapping Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Matrix MappingPeriodESSPeriod tableName 

Table 36 shows a user-updated matrix mapping table, in which there are 13 rules.

Table 36. User-Updated Mapping Table Template: Matrix Mapping Transformation Type

SRC_PERIODSRC_ESSPERIOD
JanuaryTrimester1
FebruaryTrimester1
MarchTrimester1
AprilTrimester1
MayTrimester2
JuneTrimester2
JulyTrimester2
AugustTrimester2
SeptemberTrimester3
OctoberTrimester3
NovemberTrimester3
DecemberTrimester3
@LIKE(%)Year

On the first row of the region data set, Analytics Link implements each rule, one at a time. In this pass, only two of the 13 rules result in matches. In the first match, the source expression, member name January, results in the creation of a destination cell in which the member is named Trimester1 and its value is the value of January. The second match is the source expression, @LIKE(%). A destination cell is created in which the member is named Year and its value is the value of Janaury. The other source expressions do not result in a match on the first data row.

As Analytics Link continues to implement each rule on each row in the data set, duplicate data rows (but with different values) are created for Trimester1, Trimester2, Trimester3, and Year. Assume that you select the SUM duplicate data row option when you extract the region.

Example Essbase report script:

{ WIDTH 12 }
COLUMN ("PERIOD")
<DESC "Period"
"Actual"
"2006"
"Sales"
"GolfBalls"
"Customer2"
"ESSCustom3.FM None"
"Increases"
"Connecticut.Stamford"
"USD"
"FM ICP None"
"Periodic"
!

Resulting Essbase report:

Actual  2006  Sales  GolfBalls  Customer2 ESSCustom3.FM None  Increases  Connecticut.Stamford  FM ICP None  Periodic

           Trimester1   Trimester2   Trimester3   Year
           ==========   ==========   ==========   ====
Periodic   17           39           38           94

For example, the value of Trimester 1, which is 17, is the result of summing the four duplicate Trimester1 data rows that represent the values of January (5), February (2), March (9), and April (1). The value of Year, which is 94, is the result of summing the 12 duplicate Year data rows that represent the values of each month.

Valid Expressions for Source Dimensions

Table 37 lists the valid expressions for source dimensions:

Table 37. Valid Expressions for Source Dimensions

ExpressionDescription
Member name

Matches the specified member name in the source dimension.

Example expression:

Golf

Matches member Golf.

=(comma separated member list)

Matches the source dimension members that are specified in the member list.

Example expression:

=(Golf,Shoes)

Matches members Golf and Shoes.

!=(comma separated member list)

Matches the source dimension members, except those that are specified in the member list.

Example expression:

!=(Golf,Shoes)

Matches all members in the dimension except Golf and Shoes.

@DESCENDANT(string)

Matches the descendants of the specified member in the source dimension. This function excludes the specified member.

Example expression:

@DESCENDANT(Golf)

Matches members GolfBalls, GolfShoes, GolfTees, and GolfClubs, which are descendants of Golf.

@LIKE(expression)

Matches the source dimension member names that match the specified pattern. (The LIKE expression is similar to the SQL LIKE expression.)

Some supported expressions:

  • @LIKE(string%)

    As a trailing-wildcard character, the percentage sign allows matching to a string of any length (including zero length) that match the specified pattern.

    For example, the following expression matches member names in the source dimension that begin with “USD,” such as USD, USD Total, and USD Adjs:

    @LIKE(USD%)

    The following expression matches alias member names in the source dimension starting with “Entity Curr” and that are enclosed in angle brackets <>, such as <Entity Currency>, <Entity Curr Total>, and <Entity Curr Adjs>:

    @LIKE(<Entity Curr%>)
  • @LIKE(string_)

    The underscore (_) allows matching on a single character and specifies the members in the source dimension that match the specified pattern.

    For example, the following expression matches member names with three characters that begin with “US,” such as USD and USA, but does not match USDA:

    @LIKE(US_)
  • @LIKE([[]string])

    To match member names that begin with “[”, such as member names that are enclosed with brackets [], you must enclose the open bracket with a set of brackets.

    For example, the following expression matches the member [Proportion] in the source dimension:

    @LIKE([[]Proportion])

Valid Expressions for Target Dimensions

Table 38 lists the valid expressions for target dimensions:

Table 38. Valid Expressions for Target Dimensions

ExpressionDescription

Member name

Use the specified member name in the target dimension.

Example expression:

SalesRevenue

For example, if the source dimension expression matches Sales, the member in the target dimension is named SalesRevenue.

=SRC_HFM_dimName

The value is taken from the specified dimension of the source cell. For example, if the source dimension expression matches the Sales member, the member in the target dimension is named Sales. If the Entity dimension is the source dimension, to get the value of the SRC_PARENT cell, you use parentId as the name of SRC_HFM_dimName.

Currency Transformation Type

The Currency transformation type, in essence, breaks the Financial Management Value dimension into two Essbase dimensions (Currency and Value), making the reporting process easier and more intuitive to users.

Representation of the Financial Management Value dimension:

Value
   [None]
   [Contribution Total]
   [Contribution Adjs]
   [Contribution]
   [Elimination]
   [Proportion]
   [Parent Total]
   [Parent Adjs]
   [Parent]
   [<Parent Curr Total>
   <Parent Curr Adjs>
   <Parent Currency>
   <Entity Curr Total>
   <Entity Curr Adjs>
   <Entity Currency>
   EURO Total
   EURO Adjs
   EURO
   GBR Total
   GBR Adjs
   GBR
   USD Total
   USD Adjs
   USD

In one of the most common usage scenarios for the Currency transformation type, you must specify only up to the first 15 members of the Financial Management Value dimension in the member selection specification to become the members of the Essbase Value dimension. In Financial Management, these first 15 members do not include an explicit definition of entity currency; rather the entity currency is implicitly defined. For example, in Financial Management, the <Entity Currency> member returns the value of an entity in its local currency (the default currency of the entity) and the <Parent Currency> member returns the value of an entity translated into the currency of its parent entity (the default currency of the entity parent). See About Dimension Member Selection for the Value Dimension.

All currency members (such as USD) from the Financial Management Value dimension become the members of the Essbase Currency dimension. (Member selection is not supported for the Currency dimension transformation type.) In this way, the user always knows the currency of the retrieved data.

You can add only one Currency dimension to the grid.

You must provide a name for the Currency dimension (for example, Currencies) in the Target Dimension field. Table 39 represents the information that you must provide on the Target Design Grid tab for the Value and Currency dimensions:

Table 39. Target Design Grid tab: Currency Transformation Type

Transformation TypeSource Dimensions[1]Target DimensionsMember SelectionMapping TablePrefix

Dimension Creation

Value

ESSValue

[Contribution Total],[Parent Total],<Parent Curr Total>,<Entity Curr Total>

  

Currency

 

Currencies

   

1 Because the Currency dimension does not exist in the Financial Management application, the Source Dimension field is not available.

Representation of the Essbase ESSValue and Currencies dimensions:

ESSValue
   FM Contribution Total   (UDAS: eal)
      FM Contribution Adjs   (UDAS: eal)
      FM Contribution    (UDAS: eal)
         FM Elimination    (UDAS: eal)
         FM Proportion   (UDAS: eal)
   FM Parent Total   (UDAS: eal)
      FM Parent Adjs   (UDAS: eal)
      FM Parent   (UDAS: eal)
   FM Parent Curr Total   (UDAS: eal)
      FM Parent Curr Adjs   (UDAS: eal)
      FM Parent Currency   (UDAS: eal)
   FM Entity Curr Total   (UDAS: eal)
      FM Entity Curr Adjs   (UDAS: eal)
      FM Entity Currency   (UDAS: eal)
Currencies
   EURO   (UDAS: eal)
   GBR   (UDAS: eal)
   USD   (UDAS: eal)

If the Solve Uniqueness Problems database property is set to On, Analytics Link solves duplicate member names in the currency dimension by adding the dimension name; for example, Currencies.USD.

Financial Management application member values:

                      Europe.Italy   Sullivan.Italy
[Contribution]        3.00           6.00
[Parent]              3.00           6.00
<Entity Currency>     3.00           3.00
EURO                  3.00           3.00
GBR
USD                   6.00           6.00

Example Essbase report script:

{ WIDTH 35 }
"Actual"
"2006"
"July"
"Sales"
"GolfBalls"
"Customer2"
"ESSCustom3.FM None"
"Increases"
"FM ICP None"
"Periodic"
<ROW ("ESSValue")
"FM Entity Currency"
"FM Parent"
"FM Contribution"
<COLUMN ("ESSEntity")
"Europe.Italy" "Sullivan.Italy"
"EURO"
!
 "USD"
!
 "GBR"
!

Resulting Essbase report:

Actual   2006   July   Sales   GolfBalls   Customer2   ESSCustom3.FM None   Increases   FM ICP None   Periodic   EURO

                       Europe.Italy      Sullivan.Italy
         ===================================================
FM Entity Currency        3                 3
FM Parent                 3                 #MISSING
FM Contribution           3                 #MISSING

Actual   2006   July   Sales   GolfBalls   Customer2   ESSCustom3.FM None   Increases   FM ICP None   Periodic   USD

                       Europe.Italy      Sullivan.Italy
         ===================================================
FM Entity Currency        #MISSING           #MISSING
FM Parent                 #MISSING           6
FM Contribution           #MISSING           6

Actual   2006   July   Sales   GolfBalls   Customer2   ESSCustom3.FM None   Increases   FM ICP None   Periodic   GBR

                       Europe.Italy      Sullivan.Italy
         ===================================================
FM Entity Currency        #MISSING           #MISSING
FM Parent                 #MISSING           #MISSING
FM Contribution           #MISSING           #MISSING

Note:

In creating the Currency dimension in Essbase, Analytics Link does not support retrieving currency rates.

Measure Transformation Type

The Measure transformation type adds a Measure dimension to the target database.

You can add only one Measure dimension to the grid.

The target database type determines how the Measure transformation type is specified on the Target Design Grid tab:

  • Essbase target database—If you use the Measure transformation type, in the Target Dimension field, you must provide a name for the dimension and, in the Member Selection field, you must specify one member that Analytics Link will create in the Measure dimension. The name of the member must be unique in the Essbase outline: for Essbase members that Analytics Link creates and for Essbase members in already existing Essbase dimensions. Analytics Link does not check for uniqueness on this member (even if the Solve Uniqueness Problems property is set to On).

    In this example, assume that you want to name the Essbase dimension as Measure. Table 40 represents the information that you must provide on the Target Design Grid tab:

    Table 40. Target Design Grid tab: Measure Transformation Type for an Essbase Target Database

    Transformation TypeSource Dimensions[1]Target DimensionsMember SelectionMapping TablePrefix
    Measure Measureeal_measure  

    1 Because the Measure dimension does not exist in the Financial Management application, the Source Dimension field is not available.

    In the Essbase outline, the Measure dimension is tagged as an Accounts dimension. Representation of the Essbase Measure dimension outline:

    Measure   Accounts
       eal_measure (Never Share) (UDAS:eal)
  • RDBMS target database—Analytics Link automatically adds the Measure transformation type to the mapping grid and, by default, names the dimension Measures in the Target Dimension field. You can change the dimension name. Member selection is not supported.

    Table 41 represents the default information on the Target Design Grid tab:

    Table 41. Target Design Grid tab: Measure Transformation Type for an RDBMS Target Database

    Transformation TypeSource Dimensions[1]Target DimensionsMember SelectionMapping TablePrefix
    Measure Measures   

    1 Because the Measure dimension does not exist in the Financial Management application, the Source Dimension field is not available.

    When you extract data to an RDBMS database, Analytics Link creates a mapping table in the Data Store. The name of the Measure dimension (for example, Measures) is used as the column name and the column is populated with data, not member names.

About Dimension Member Selection

This topic discusses member selection for dimensions other than the Value dimension. See About Dimension Member Selection for the Value Dimension.

Through member selection you can limit the Financial Management dimension members that you want to include in the target database or limit the existing Essbase members that will receive Financial Management data. The transformation type of a dimension determines whether:

  • Member selection is supported for the dimension

  • Member selection is based on members in the Financial Management source dimension or the Essbase target dimension.

For information about which transformation types support member selection, see Table 7, Summary of Transformation Type Attributes.

When member selection is specified for the Entity dimension, you must enter member names in the following format:

parentName.memberName

This format ensures that the correct data is retrieved for the top-level members in the Entity dimension, which are specified in the member selection, and the first 12 members of Value dimension, as they appear in the Financial Management application.

When member selection is specified for dimensions that use the New Dimension, Join Dimensions, or Dimension Mapping transformation types, and an Essbase target database is created with the Allow Duplicate Member Names property selected, member names (which are based on the Essbase target dimension) must be entered in a fully qualified format.

Using Fully Qualified Name: A fully qualified member name comprises the duplicate member or alias name and all ancestors up to and including the dimension name. Each name must be enclosed in brackets ([ ]) and separated by a period (.).

Fully-qualified format:

[DimensionMember].[Ancestors...].[DuplicateMember]

For example:

[Market].[East].[State].[New York]
[Market].[East].[City].[New York]

Using Shortcut Qualified Member Name for New Dimension: For the New Dimension transformation type, you must provide the name of an existing Essbase member in the Member Selection column on the Target Design Grid tab. If the Essbase Duplicate member names allowed property is set to TRUE for the Essbase outline that contains this member, you must specify a unique member name in the following format:

[New_Dimension_Name]@[Member_Selection_Name]

Qualifying Members by Differentiating Ancestor: A member name qualified by differentiating ancestor uses the member or alias name and all ancestors up to and including the ancestor that uniquely identifies the duplicate member or alias. The top ancestor in the path will always be a unique member name. Each name must be enclosed in brackets ([ ]) and separated by a period (.). The syntax is as follows:

[DifferentiatingAncestor].[Ancestors...].[DuplicateMember]

For example:

[State].[New York]
[City].[New York]

About Dimension Member Selection for the Value Dimension

When member selection is specified for the Value dimension, enter member names as they appear in Financial Management. In Financial Management, all members in the Value dimension are base-level members, as shown in the following representation of the Value dimension:

Value
...
   <Entity Curr Total>
   <Entity Curr Adj>
   <Entity Currency>
...

Analytics Link creates the Value dimension in an Essbase target database as a multilevel hierarchy, based on the consolidation logic of the Value dimension in Financial Management. For example, assume that you enter <Entity Curr Total> in the Member Selection field. In Financial Management, the value of the <Entity Curr Total> member is the sum of the <Entity Currency> and <Entity Curr Adj> members. Therefore, Analytics Link creates a hierarchy where <Entity Curr Total> is the parent of <Entity Currency> and <Entity Curr Adj>, as shown in the following representation of the Value dimension in an Essbase outline:

ESSValue
..
   <Entity Curr Total>
      <Entity Curr Adj>
      <Entity Currency>
...

About Dimension Member Mapping Tables

Mapping tables map member names among source and target dimensions. Analytics Link creates mapping table templates in the Data Store that is associated with the bridge. Analytics Link creates the template using default values, which you then manually modify in the Data Store tables.

The structure of Analytics Link mapping tables depends on the transformation type of the dimension. See the transformation type topic for specific information about mapping table requirements. For information about which transformation types support mapping tables, see Table 7, Summary of Transformation Type Attributes.

The following information applies to all mapping tables:

  • Analytics Link uses column names to find data; the order of columns is not important.

  • Analytics Link-created mapping table templates include ISBASE, GENERATION, and DIM_LEVEL columns. The source database from which the values in these columns are taken depends on the transformation type. For example, the member selection specification for the Dimension Creation transformation type is based on Financial Management members. For the Join Dimensions transformation type, the member selection specification is based on Essbase members. See the Member Selection column in Table 7, Summary of Transformation Type Attributes.

  • The ISBASE, GENERATION, and DIM_LEVEL columns are created to assist you in making massive data updates to the values in the HFM_MEMBER, TARGET_MEMBER, and VISIBLE columns, using queries instead of manually updating rows. For example, to set the VISIBLE property to 1 (TRUE) for all nonbase members, a query can search the ISBASE column for values of 0.

  • If creating a user-defined mapping table, the ISBASE, GENERATION, and DIM_LEVEL columns are not required.

  • The SELECTED column, which is for presentation purposes only, reflects the member selection specification in the Member Selection column on the Target Design Grid tab. Changing values in the SELECTED column in the mapping table does not affect the members that are used in creating the target database, because Analytics Link only uses the member selection specification from the Target Design Grid tab.

Oracle recommends that you create mapping table templates in Analytics Link; however, you can create mapping tables yourself. See Mapping Table Template Reference.

About the VISIBLE Mapping Property

The VISIBLE property specifies whether a member is related to Analytics Link.

Table 42 describes the relationship among Dimension Creation, Dimension Mapping, Join Dimensions, and Break Dimension transformation types, the value of VISIBLE in a mapping table, and the target database type.

Table 42. VISIBLE Mapping Table Column

Transformation TypeVISIBLETarget DatabaseDescription

Dimension Creation

0 (FALSE)

Essbase (Transparent Partition)

Analytics Link does not create the member in the Essbase outline.

Dimension Creation

0 (FALSE)

  • Essbase (Replicated Partition)

  • RDBMS

Analytics Link does not create the member in the Essbase outline.

When you extract data to the Essbase or RDBMS database, Analytics Link does not copy the member value, even if the member is included in the extract data region.

Dimension Creation

1 (TRUE)

+

SELECTED=1 (TRUE)

Essbase (Transparent Partition)

Analytics Link creates the member with the “eal” UDA in the Essbase outline.

The member value is retrieved from Data Synchronization Server through the Essbase transparent partition.

See About User-Defined Attributes (UDAs).

Dimension Creation

1 (TRUE)

+

SELECTED=1 (TRUE)

  • Essbase (Replicated Partition)

  • RDBMS

Analytics Link creates the member in the Essbase outline.

When you extract data to an Essbase or RDBMS database, Analytics Link copies the member value, if the member is included in the extract data region.

Dimension Mapping[1]

Join Dimensions[1]

Break Dimension[1]

0 (FALSE)

Essbase (Transparent Partition)

Analytics Link does not add the “eal” UDA to the member in the Essbase outline. See About User-Defined Attributes (UDAs).

The member value is not retrieved from Data Synchronization Server through the transparent partition.

Dimension Mapping[1]

Join Dimensions[1]

Break Dimension[1]

0 (FALSE)

  • Essbase (Replicated Partition)

  • RDBMS

When you extract data to the Essbase or RDBMS database, Analytics Link does not copy the member value, even if the member is included in the extract data region.

Dimension Mapping[1]

Join Dimensions[1]

Break Dimension[1]

1 (TRUE)

+

SELECTED=1 (TRUE)

Essbase (Transparent Partition)

Analytics Link adds the “eal” UDA to the member in the Essbase outline.

The member value is retrieved from Data Synchronization Server through the Essbase transparent partition.

See About User-Defined Attributes (UDAs).

Dimension Mapping[1]

Join Dimensions[1]

Break Dimension[1]

1 (TRUE)

+

SELECTED=1 (TRUE)

  • Essbase (Replicated Partition)

  • RDBMS

When you extract data to an Essbase or RDBMS database, Analytics Link copies the member value, if the member is included in the extracted data region.

1 For these transformation types, Analytics Link does not create dimensions in the Essbase outline, as the Essbase dimensions and members already exist.

You can edit VISIBLE property values only in the mapping table.

About Making Intermediate Members Invisible

With the Dimension Creation transformation type, if you make an intermediate Financial Management member invisible, the children of the intermediate member remain visible and become children of the parent of the removed intermediate Financial Management member.

In this example, assume that you want AllProducts to include GolfBalls, Tennis, and TennisBalls, but you do not want the rest of the members included in the Essbase database.

Representation of the Financial Management Custom1 dimension:

Custom1
   [None]
   AllProducts
      Golf
         GolfBalls
         GolfShoes
...
      Tennis
         TennisBalls
         TennisShoes
...
   Balls
      GolfBalls
      TennisBalls
   Shoes
      GolfShoes
      TennisShoes

Financial Management dimension member values:

Member        Value
AllProducts   13
Golf          3
GolfBalls     1
GolfShoes     2
Tennis        10
TennisBalls   7
TennisShoes   3

On the Target Design Grid tab, select the Dimension Creation transformation type for the Custom1 dimension and specify a mapping table. In the Analytics Link-created mapping table, VISIBLE and SELECTED are set to 1 (TRUE) for all members.

Table 43 represents a user-updated mapping table, in which the VISIBLE property is set to 0 (FALSE) for all members except for AllProducts, GolfBalls, Tennis, and TennisBalls, which are the only members that you want included in the Essbase outline.

Table 43. User-Updated Mapping Table Template: Making Intermediate Members Invisible Example

HFM_MEMBERTARGET_MEMBERVISIBLEISBASEGENDIM_LEVELSELECTED
[None]ESSCustom1.FM None01001
AllProductsAllProducts10021
BallsBalls00111
GolfGolf00111
GolfBallsGolfBalls11201
GolfClubsGolfClubs01201
GolfShoesGolfShoes01201
GolfTeesGolfTees01201
ShoesShoes00111
TennisTennis10111
TennisBallsTennisBalls11201
TennisRacquetsTennisRacquets01201
TennisShoesTennisShoes01201

Members for which VISIBLE and SELECTED are set to 1 (TRUE) are tagged with the “eal” UDA in the Essbase outline. See About User-Defined Attributes (UDAs).

Representation of the hierarchy in the Essbase ESSCustom1 dimension:

ESSCustom1
   AllProducts   (UDAS: eal)
      GolfBalls   (UDAS: eal)
      Tennis   (UDAS: eal)
         TennisBalls   (UDAS: eal)

Example Essbase report script:

{ WIDTH 20 }
<ROW ("ESSCustom1")
"Actual"
"2006"
"July"
"Sales"
<IDESC "ESSCustom1"
"Customer2"
"ESSCustom3.FM None"
"Increases"
"Connecticut.Stamford"
"FM Entity Currency"
"FM ICP None"
"Periodic"
!

Resulting Essbase report:

Actual   2006   July   Sales   Customer2   ESSCustom3.FM None   Increases   Connecticut.Stamford   FM Currenty Entity   FM ICP None   Periodic

ESSCustom1             13
   AllProducts         13
      GolfBalls        1
      Tennis           10
         TennisBalls   7

As the Essbase report shows, when you change the VISIBLE property, you can get unexpected values in Essbase (nonbase members in the Essbase dimension are not equal to the sum of their children).

Note:

In the Entity dimension, if the original Entity Parent member is set to invisible (thereby removing the Entity Parent member from the Essbase dimension), another entity becomes the Entity Parent of the Entity member. This substitution may cause unexpected values for Value dimension members that are dependent on the Entity Parent member, such as [Proportion], [Elimination], and [Parent].

Member Naming Considerations when Add Parent to Entity Member is Set to On

When Analytics Link creates an Essbase dimension, the mapping of Financial Management member names to Essbase dimension names are determined in this order:

  1. Name changes based on the Add Parent to Entity Member database property value of the bridge.

  2. Name changes based on mapping table

In this example, assume the Add Parent to Entity database property is set to On. All other database properties are set to Off.

Representation of a hierarchy in the Financial Management Entity dimension:

Entity
   [None]
      UnitedStates
         California
            Sunnyvale
            FosterCity
         Connecticut
            Stamford
 

Table 44 represents a mapping table for the Entity dimension, in which the Stamford member is renamed as Stamford_in_Connecticut in the TARGET_MEMBER column and Connecticut is specified in the ENTITY_PARENT column.

Table 44. Example 1: Mapping Table for Entity Dimension

HFM_MEMBERENTITY_PARENTTARGET_MEMBERVISIBLESELECTED
StamfordConnecticutStamford_in_Connecticut11

According to the order in which Analytics Link names members, name changes that are based on a mapping table overwrite the Add Parent to Entity Member specification. Therefore, Stamford is renamed to Stamford_in_Connecticut, not Connecticut.Stamford.

Representation of the hierarchy in the Essbase ESSEntity dimension:

ESSEntity
   FM None   (UDAS: eal)
      UnitedStates   (UDAS: eal)
         UnitedStates.California   (UDAS: eal)
            California.Sunnyvale   (UDAS: eal)
            California.FosterCity   (UDAS: eal)
         UnitedStates.Connecticut   (UDAS: eal)
            Stamford_in_Connecticut   (UDAS: eal)
 

About Prefixes

In situations where a uniform prefix is used in Essbase member names (for example, to solve member name uniqueness problems), the prefix feature is useful for mapping member names without using a mapping table. The prefix feature is supported only for the Dimension Mapping transformation type.

In this example, assume that the members in an Essbase dimension are similarly named as the members in a Financial Management dimension, except that the Essbase member names include a uniform prefix of “Ac_”.

Representation of an Essbase dimension, ESSDim1:

Ac_A
Ac_B
   Ac_B1
   Ac_B2
Ac_C
Ac_D

Representation of a Financial Management dimension, HFMDim1:

A
B
   B1
   B2
C
D

To map the Financial Management member names to the Essbase member names, you can use a mapping table, as shown in Table 45.

Table 45. Mapping Table Template

TARGET_MEMBERHFM_MEMBERVISIBLESELECTED
Ac_AA11
Ac_BB11
Ac_B1B111
Ac_B2B211
Ac_CC11
Ac_DD11

Because using a mapping table might not be practical when mapping a large number of members, you can specify the prefix as a regular expression to simplify the mapping process. Table 46 represents the information that you must provide on the Target Design Grid tab:

Table 46. Target Design Grid tab: Dimension Mapping Transformation Type

Transformation TypeSource DimensionsTarget DimensionsMember SelectionMapping TablePrefix
Dimension MappingHFMDim1ESSDim1  Ac_

Analytics Link creates internal mapping information by removing the prefix from the Essbase member name. Each Essbase member in the dimension is then mapped to the Financial Management member with the same name. For example, Essbase member Ac_A becomes A and is mapped to Financial Management member A.

If a mapping table exists for the dimension, the prefix feature is:

  • Not implemented on the Essbase dimension members for which mapping is defined in the mapping table

  • Implemented on the Essbase dimension members for which mapping is not defined in the mapping table

If Analytics Link cannot find a matching Financial Management member in the Data Synchronization Server database, the query returns #MISSING for the particular member.

About User-Defined Attributes (UDAs)

In Essbase, a user-defined attribute (UDA) describes a characteristic of the members that are assigned a UDA and is used to return lists of members that have the specified associated UDA.

For an Essbase transparent partition target database, Analytics Link assigns a UDA with the name “eal” to dimension members that Analytics Link creates or that are mapped to Financial Management members. Essbase query results are returned from Data Synchronization Server when member coordinates for all Essbase dimensions have the “eal” UDA.

Members that are assigned the “eal” UDA by dimension transformation type:

  • Dimension Creation, Currency, and Measure transformation types:

    All members Analytics Link creates for which VISIBLE and SELECTED mapping properties are set to 1 (TRUE).

  • Dimension Mapping, Join Dimensions, and Break Dimension transformation types:

    Only those members for which VISIBLE and SELECTED mapping properties are set to 1 (TRUE).

  • New Dimension transformation type:

    Only the selected member from the existing target dimension.

For an Essbase replicated partition target database, the replicated Partition definition is not based on UDAs. Analytics Link copies data into Essbase database cells only when a member in each Essbase dimension is mapped to Financial Management members on the Target Design Grid tab. The Essbase member must be mapped in one of these ways:

  • The Essbase member is created by Analytics Link, using the Dimension Creation, Currency, or Measures transformation types.

  • The Essbase member is mapped to a Financial Management member through a mapping table, using the Dimension Mapping, Join Dimensions, or Break Dimension transformation types.

  • Using the Dimension Mapping transformation type, the Essbase member that is not mapped through a mapping table and has the same name as the Financial Management dimension member.

  • The Essbase member is mapped to a Financial Management member through matrix mapping rules, using the Matrix Mapping transformation type.

  • The Essbase member is mapped to a Financial Management member through the Member Selection column value, using the New Dimension transformation type.