In This Section:
Overview of Designing the Target Database Grid
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
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.
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:
In the bridge window, navigate to the Target Design Grid tab.
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.
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
The following steps depend on each dimension's transformation type:
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.
Double-click the Source Dimensions field to open the Source Dimensions dialog box.
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.
To create a comma-separated list of Financial Management dimensions, double-click multiple dimension names.
Click OK to add the specified dimensions to the Source Dimensions field in the mapping grid.
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.
Double-click the Target Dimensions field to open the Target Dimensions dialog box.
To rename a target dimension, edit the dimension name in the Add field.
To create a comma-separated list of target dimensions, double-click multiple dimension names.
Click OK to add the specified dimensions to the Target Dimensions field in the mapping grid.
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.)
In the Mapping Table column, you can specify a mapping table for those transformation types that support mapping tables.
Double-click the Mapping Table field to open the Mapping Table dialog box.
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.
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.
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.
(For Dimension Mapping transformation type only) For Prefix, double-click the field and enter a Java regular expression.
See About Prefixes.
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.)
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 Type | Number of Source_Dims | Number of Target_Dims | Member Selection | Mapping Table | Prefix | See |
|---|---|---|---|---|---|---|
| Dimension Creation | 1 | 1 | Supported, based on Financial Management members | Supported | Not supported | Dimension Creation Transformation Type |
| Dimension Mapping | 1 | 1 | Supported, based on Essbase members | Supported | Supported | Dimension Mapping Transformation Type |
| Not in Outline | 1 | None | Required One Financial Management member must be specified. | Not supported | Not supported | Not in Outline Transformation Type |
| New Dimension | None | 1 | Required One Essbase member must be specified. | Not supported | Not supported | New Dimension Transformation Type |
| Join Dimensions | > 1 | 1 | Supported, based on Essbase members | Required | Not supported | Join Dimensions Transformation Type |
| Break Dimension | 1 | > 1 | Supported, based on Financial Management members | Required | Not supported | Break Dimension Transformation Type |
| Matrix Mapping | ≥ 1 | ≥ 1 | Not supported | Required | Not supported | Matrix Mapping Transformation Type |
| Currency | None | 1 | Not supported | Not supported | Not supported | Currency Transformation Type |
| Measure | None | 1 | Essbase target database: required; one member must be specified RDBMS target database: not supported | Not supported | Not supported | Measure 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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Dimension Creation | Custom1 | ESSCustom1 | Golf,Shoes | tableName |
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_MEMBER | TARGET_MEMBER | VISIBLE | ISBASE | GENERATION | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|
| [None] | FM None | 1 | 1 | 0 | 0 | 0 |
| AllProducts | AllProducts | 1 | 0 | 0 | 2 | 0 |
| Balls | Balls | 1 | 0 | 0 | 1 | 0 |
| Golf | Golf | 1 | 0 | 1 | 1 | 1 |
| GolfBalls | GolfBalls | 1 | 1 | 2 | 0 | 1 |
| GolfClubs | GolfClubs | 1 | 1 | 2 | 0 | 1 |
| GolfShoes | GolfShoes | 1 | 1 | 2 | 0 | 1 |
| GolfTees | GolfTees | 1 | 1 | 2 | 0 | 1 |
| Shoes | Shoes | 1 | 0 | 0 | 1 | 1 |
| Tennis | Tennis | 1 | 0 | 1 | 1 | 0 |
| TennisBalls | TennisBalls | 1 | 1 | 2 | 0 | 0 |
| TennisRacquets | TennisRacquets | 1 | 1 | 2 | 0 | 0 |
| TennisShoes | TennisShoes | 1 | 1 | 2 | 0 | 1 |
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_MEMBER | TARGET_MEMBER | VISIBLE | ISBASE | GENERATION | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|
| [None] | FM None | 1 | 1 | 0 | 0 | 0 |
| AllProducts | AllProducts | 1 | 0 | 0 | 2 | 0 |
| Balls | Balls | 1 | 0 | 0 | 1 | 0 |
| Golf | GolfProducts | 1 | 0 | 1 | 1 | 1 |
| GolfBalls | GolfBalls | 1 | 1 | 2 | 0 | 0 |
| GolfClubs | GolfClubs | 1 | 1 | 2 | 0 | 1 |
| GolfShoes | GolfShoes | 1 | 1 | 2 | 0 | 1 |
| GolfTees | GolfTees | 0 | 1 | 2 | 0 | 1 |
| Shoes | ShoesProducts | 1 | 0 | 0 | 1 | 1 |
| Tennis | Tennis | 1 | 0 | 1 | 1 | 0 |
| TennisBalls | TennisBalls | 1 | 1 | 2 | 0 | 0 |
| TennisRacquets | TennisRacquets | 1 | 1 | 2 | 0 | 0 |
| TennisShoes | TennisShoes | 1 | 1 | 2 | 0 | 1 |
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.
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
JuneTable 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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Dimension Mapping | Period | Period | 3Months | tableName |
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_MEMBER | HFM_MEMBER_Period | VISIBLE | ISBASE | GENERATION | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|
| 3Months | 3Months | 1 | 0 | 2 | 3 | 1 |
| HY1 | HY1 | 1 | 0 | 3 | 2 | 1 |
| Q1 | Q1 | 1 | 0 | 4 | 1 | 1 |
| JUL | JUL | 1 | 1 | 5 | 0 | 1 |
| AUG | AUG | 1 | 1 | 5 | 0 | 1 |
| SEP | SEP | 1 | 1 | 5 | 0 | 1 |
| Q2 | Q2 | 1 | 0 | 4 | 1 | 1 |
| OCT | OCT | 1 | 1 | 5 | 0 | 1 |
| NOV | NOV | 1 | 1 | 5 | 0 | 1 |
| DEC | DEC | 1 | 1 | 5 | 0 | 1 |
| HY2 | HY2 | 1 | 0 | 3 | 2 | 1 |
| Q3 | Q3 | 1 | 0 | 4 | 1 | 1 |
| JAN | JAN | 1 | 1 | 5 | 0 | 1 |
| FEB | FEB | 1 | 1 | 5 | 0 | 1 |
| MAR | MAR | 1 | 1 | 5 | 0 | 1 |
| Q4 | Q4 | 1 | 0 | 4 | 1 | 1 |
| APR | APR | 1 | 1 | 5 | 0 | 1 |
| MAY | MAY | 1 | 1 | 5 | 0 | 1 |
| JUN | JUN | 1 | 1 | 5 | 0 | 1 |
| 4Months | 4Months | 1 | 0 | 2 | 2 | 0 |
| T1 | T1 | 1 | 0 | 3 | 1 | 0 |
| T2 | T2 | 1 | 0 | 3 | 1 | 0 |
| T3 | T3 | 1 | 0 | 3 | 1 | 0 |
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_MEMBER | HFM_MEMBER_Period | VISIBLE | ISBASE | GENERATION | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|
| 3Months | Year | 1 | 0 | 2 | 3 | 1 |
| HY1 | HalfYear1 | 1 | 0 | 3 | 2 | 1 |
| Q1 | Quarter1 | 1 | 0 | 4 | 1 | 1 |
| JUL | July | 1 | 1 | 5 | 0 | 1 |
| AUG | August | 1 | 1 | 5 | 0 | 1 |
| SEP | September | 1 | 1 | 5 | 0 | 1 |
| Q2 | Quarter2 | 1 | 0 | 4 | 1 | 1 |
| OCT | October | 1 | 1 | 5 | 0 | 1 |
| NOV | November | 1 | 1 | 5 | 0 | 1 |
| DEC | December | 1 | 1 | 5 | 0 | 1 |
| HY2 | HalfYear2 | 1 | 0 | 3 | 2 | 1 |
| Q3 | Quarter3 | 1 | 0 | 4 | 1 | 1 |
| JAN | January | 1 | 1 | 5 | 0 | 1 |
| FEB | February | 1 | 1 | 5 | 0 | 1 |
| MAR | March | 1 | 1 | 5 | 0 | 1 |
| Q4 | Quarter4 | 1 | 0 | 4 | 1 | 1 |
| APR | April | 1 | 1 | 5 | 0 | 1 |
| MAY | May | 1 | 1 | 5 | 0 | 1 |
| JUN | June | 1 | 1 | 5 | 0 | 1 |
| 4Months | 4Months | 1 | 0 | 2 | 2 | 0 |
| T1 | T1 | 1 | 0 | 3 | 1 | 0 |
| T2 | T2 | 1 | 0 | 3 | 1 | 0 |
| T3 | T3 | 1 | 0 | 3 | 1 | 0 |
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)
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 Type | Source Dimensions | Target Dimensions[1] | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Not in Outline | ICP | [ICP Top] |
See Removing Financial Management Dimensions from the Essbase Outline.
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 Type | Source Dimensions[1] | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| New Dimension | Source | Hfm |
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
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
YTDSEPRepresentation 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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Join Dimensions | Period,View | Time | 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_MEMBER | HFM_MEMBER_Period | HFM_MEMBER_View | ISBASE | GENERATION | DIM_LEVEL | VISIBLE | SELECTED |
|---|---|---|---|---|---|---|---|
| Months | Months | [None] | 0 | 2 | 1 | 1 | 1 |
| JUL | JUL | [None] | 1 | 3 | 0 | 1 | 1 |
| AUG | AUG | [None] | 1 | 3 | 0 | 1 | 1 |
| SEP | SEP | [None] | 1 | 3 | 0 | 1 | 1 |
| YearToDate | YearToDate | [None] | 0 | 2 | 1 | 1 | 1 |
| YTDJUL | YTDJUL | [None] | 1 | 3 | 0 | 1 | 1 |
| YTDAUG | YTDAUG | [None] | 1 | 3 | 0 | 1 | 1 |
| YTDSEP | YTDSEP | [None] | 1 | 3 | 0 | 1 | 1 |
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_MEMBER | HFM_MEMBER_Period | HFM_MEMBER_View | ISBASE | GEN | DIM_LEVEL | VISIBLE | SELECTED |
|---|---|---|---|---|---|---|---|
| Months | Quarter1 | Periodic | 0 | 2 | 1 | 1 | 1 |
| JUL | July | Periodic | 1 | 3 | 0 | 1 | 1 |
| AUG | August | Periodic | 1 | 3 | 0 | 1 | 1 |
| SEP | September | Periodic | 1 | 3 | 0 | 1 | 1 |
| YearToDate | Quarter1 | YTD | 0 | 2 | 1 | 1 | 1 |
| YTDJUL | July | YTD | 1 | 3 | 0 | 1 | 1 |
| YTDAUG | August | YTD | 1 | 3 | 0 | 1 | 1 |
| YTDSEP | September | YTD | 1 | 3 | 0 | 1 | 1 |
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. |
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.
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 1If, 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 6The 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:
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
TennisShoesRepresentation of the existing Essbase BallsDim dimension outline:
BallsDim
BallsDim.Total
BallsDim.None
Balls
GolfBalls
TennisBallsTable 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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Break Dimension | Custom1 | ShoesDim,BallsDim | Shoes,Balls | tableName |
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_MEMBER | TARGET_MEMBER_SHOESDIM | VISIBLE_SHOESDIM | TARGET_MEMBER_BALLSDIM | VISIBLE_BALLSDIM | ISBASE | GEN | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|---|---|
| [None] | [None] | 1 | [None] | 0 | 1 | 0 | 0 | 0 |
| AllProducts | AllProducts | 1 | AllProducts | 0 | 0 | 0 | 2 | 0 |
| Balls | Balls | 1 | Balls | 0 | 0 | 0 | 1 | 1 |
| Golf | Golf | 1 | Golf | 0 | 0 | 1 | 1 | 0 |
| GolfBalls | GolfBalls | 1 | GolfBalls | 0 | 1 | 2 | 0 | 1 |
| GolfClubs | GolfClubs | 1 | GolfClubs | 0 | 1 | 2 | 0 | 0 |
| GolfShoes | GolfShoes | 1 | GolfShoes | 0 | 1 | 2 | 0 | 1 |
| GolfTees | GolfTees | 1 | GolfTees | 0 | 1 | 2 | 0 | 0 |
| Shoes | Shoes | 1 | Shoes | 0 | 0 | 0 | 1 | 1 |
| Tennis | Tennis | 1 | Tennis | 0 | 0 | 1 | 1 | 0 |
| TennisBalls | TennisBalls | 1 | TennisBalls | 0 | 1 | 2 | 0 | 1 |
| TennisRacquets | TennisRacquets | 1 | TennisRacquets | 0 | 1 | 2 | 0 | 0 |
| TennisShoes | TennisShoes | 1 | TennisShoes | 0 | 1 | 2 | 0 | 1 |
| Top | ShoesDim.Top | 1 | BallsDim.Top | 1 | 1 | 1 | 1 | 1 |
| EAL.None | ShoesDim.None | 1 | BallsDim.None | 1 | 1 | 1 | 1 | 1 |
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_MEMBER | TARGET_MEMBER_SHOESDIM | VISIBLE_SHOESDIM | TARGET_MEMBER_BALLSDIM | VISIBLE_BALLSDIM | ISBASE | GEN | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|---|---|
| [None] | [None] | 0 | [None] | 0 | 1 | 0 | 0 | 0 |
| AllProducts | AllProducts | 0 | AllProducts | 0 | 0 | 0 | 2 | 0 |
| Balls | Balls | 0 | Balls | 1 | 0 | 0 | 1 | 1 |
| Golf | Golf | 0 | Golf | 0 | 0 | 1 | 1 | 0 |
| GolfBalls | GolfBalls | 0 | GolfBalls | 1 | 1 | 2 | 0 | 1 |
| GolfClubs | GolfClubs | 0 | GolfClubs | 0 | 1 | 2 | 0 | 0 |
| GolfShoes | GolfShoes | 1 | GolfShoes | 0 | 1 | 2 | 0 | 1 |
| GolfTees | GolfTees | 0 | GolfTees | 0 | 1 | 2 | 0 | 0 |
| Shoes | Shoes | 1 | Shoes | 0 | 0 | 0 | 1 | 1 |
| Tennis | Tennis | 0 | Tennis | 0 | 0 | 1 | 1 | 0 |
| TennisBalls | TennisBalls | 0 | TennisBalls | 1 | 1 | 2 | 0 | 1 |
| TennisRacquets | TennisRacquets | 0 | TennisRacquets | 0 | 1 | 2 | 0 | 0 |
| TennisShoes | TennisShoes | 1 | TennisShoes | 0 | 1 | 2 | 0 | 1 |
| Top | ShoesDim.Total | 1 | BallsDim.Total | 1 | 1 | 1 | 1 | 1 |
| EAL.None | ShoesDim.None | 1 | BallsDim.None | 1 | 1 | 1 | 1 | 1 |
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 19The 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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Break Dimension | Custom1 | Sports,Products | Golf,Tennis | tableName |
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_MEMBER | TARGET_MEMBER_SPORTS | VISIBLE_SPORTS | TARGET_MEMBER_PRODUCTS | VISIBLE_PRODUCTS | ISBASE | GEN | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|---|---|
| [None] | [None] | 1 | [None] | 0 | 1 | 0 | 0 | 0 |
| AllProducts | AllProducts | 1 | AllProducts | 0 | 0 | 0 | 2 | 0 |
| Balls | Balls | 1 | Balls | 0 | 0 | 0 | 1 | 0 |
| Golf | Golf | 1 | Golf | 0 | 0 | 1 | 1 | 1 |
| GolfBalls | GolfBalls | 1 | GolfBalls | 0 | 1 | 2 | 0 | 1 |
| GolfClubs | GolfClubs | 1 | GolfClubs | 0 | 1 | 2 | 0 | 1 |
| GolfShoes | GolfShoes | 1 | GolfShoes | 0 | 1 | 2 | 0 | 1 |
| GolfTees | GolfTees | 1 | GolfTees | 0 | 1 | 2 | 0 | 1 |
| Shoes | Shoes | 1 | Shoes | 0 | 0 | 0 | 1 | 0 |
| Tennis | Tennis | 1 | Tennis | 0 | 0 | 1 | 1 | 1 |
| TennisBalls | TennisBalls | 1 | TennisBalls | 0 | 1 | 2 | 0 | 1 |
| TennisRacquets | TennisRacquets | 1 | TennisRacquets | 0 | 1 | 2 | 0 | 1 |
| TennisShoes | TennisShoes | 1 | TennisShoes | 0 | 1 | 2 | 0 | 1 |
| Top | Sports.Top | 1 | Products.Top | 1 | 1 | 1 | 1 | 1 |
| EAL.None | Sports.None | 1 | Products.None | 1 | 1 | 1 | 1 | 1 |
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_MEMBER | TARGET_MEMBER_SPORTS | VISIBLE_SPORTS | TARGET_MEMBER_PRODUCTS | VISIBLE_PRODUCTS | ISBASE | GEN | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|---|---|
| [None] | [None] | 0 | [None] | 0 | 1 | 0 | 0 | 0 |
| AllProducts | AllProducts | 0 | AllProducts | 0 | 0 | 0 | 2 | 0 |
| Balls | Balls | 0 | Balls | 0 | 0 | 0 | 1 | 0 |
| Golf | Golf | 1 | Golf | 0 | 0 | 1 | 1 | 1 |
| GolfBalls | Golf | 1 | Balls | 1 | 1 | 2 | 0 | 1 |
| GolfClubs | Golf | 1 | Clubs | 1 | 1 | 2 | 0 | 1 |
| GolfShoes | Golf | 1 | Shoes | 1 | 1 | 2 | 0 | 1 |
| GolfTees | Golf | 1 | Tees | 1 | 1 | 2 | 0 | 1 |
| Shoes | Shoes | 0 | Shoes | 0 | 0 | 0 | 1 | 0 |
| Tennis | Tennis | 1 | Tennis | 0 | 0 | 1 | 1 | 1 |
| TennisBalls | Tennis | 1 | Balls | 1 | 1 | 2 | 0 | 1 |
| TennisRacquets | Tennis | 1 | Racquets | 1 | 1 | 2 | 0 | 1 |
| TennisShoes | Tennis | 1 | Shoes | 1 | 1 | 2 | 0 | 1 |
| Top | Sports | 1 | Products | 1 | 1 | 1 | 1 | 1 |
| EAL.None | Sports.None | 0 | Products.None | 0 | 1 | 1 | 1 | 1 |
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 46The 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
Customer5Representation 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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Break Dimension | Custom2 | CUSTOMERS2_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_MEMBER | TARGET_MEMBER_CUSTOMERS2_3 | VISIBLE_CUSTOMERS2_3 | TARGET_MEMBER_CUSTOMERS4_5 | VISIBLE_CUSTOMERS4_5 | SELECTED |
|---|---|---|---|---|---|
| [None] | [None] | 1 | [None] | 0 | 1 |
| AllCustomers | AllCustomers | 1 | AllCustomers | 0 | 1 |
| Customer2 | Customer2 | 1 | Customer2 | 0 | 1 |
| Customer3 | Customer3 | 1 | Customer3 | 0 | 1 |
| Customer4 | Customer4 | 1 | Customer4 | 0 | 1 |
| Customer5 | Customer5 | 1 | Customer5 | 0 | 1 |
| Top | CUSTOMERS2_3.Top | 1 | CUSTOMERS4_5.Top | 1 | 1 |
| EAL.None | CUSTOMERS2_3.None | 1 | CUSTOMERS4_5.None | 1 | 1 |
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_MEMBER | TARGET_MEMBER_CUSTOMERS2_3 | VISIBLE_CUSTOMERS2_3 | TARGET_MEMBER_CUSTOMERS4_5 | VISIBLE_CUSTOMERS4_5 | SELECTED |
|---|---|---|---|---|---|
| [None] | [None] | 0 | [None] | 0 | 1 |
| AllCustomers | AllCustomers | 0 | AllCustomers | 0 | 1 |
| Customer2 | Customer2 | 1 | Customer2 | 0 | 1 |
| Customer3 | Customer3 | 1 | Customer3 | 0 | 1 |
| Customer4 | Customer4 | 0 | Customer4 | 1 | 1 |
| Customer5 | Customer5 | 0 | Customer5 | 1 | 1 |
| Top | CUSTOMERS2_3 | 1 | CUSTOMERS4_5 | 1 | 1 |
| EAL.None | CUSTOMERS2_3.None | 1 | CUSTOMERS4_5.None | 1 | 1 |
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 27The 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_hfmDim | DEST_targetDim |
|---|---|
| source_expression | destination_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 Dimension | Selection Type | Member Selection |
|---|---|---|
| Year | MEMBERS | 2006 |
| Scenario | MEMBERS | Actual |
| Entity | MEMBERS | Connecticut.Stamford |
| Period | MEMBERS | July |
| ICP | MEMBERS | [ICP None] |
| Value | MEMBERS | USD |
| Custom1 | MEMBERS | GolfBalls |
| Custom2 | MEMBERS | Customer2 |
| Custom3 | MEMBERS | [None] |
| Custom4 | MEMBERS | Increases |
| View | MEMBERS | YTD |
| Account | MEMBERS | Sales |
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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Matrix Mapping | Scenario | EALScenario | 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_SCENARIO | DEST_EALSCENARIO |
|---|---|
| Actual | Budget |
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_SCENARIO | DEST_EALSCENARIO |
|---|---|
| Actual | Budget |
| 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 Dimension | Selection Type | Member Selection |
|---|---|---|
| Year | MEMBERS | 2006 |
| Scenario | MEMBERS | Actual |
| Entity | MEMBERS | Connecticut.Stamford |
| Period | BASE | |
| ICP | MEMBERS | [ICP None] |
| Value | MEMBERS | USD |
| Custom1 | MEMBERS | GolfBalls |
| Custom2 | MEMBERS | Customer2 |
| Custom3 | MEMBERS | [None] |
| Custom4 | MEMBERS | Increases |
| View | MEMBERS | Periodic |
| Account | MEMBERS | Sales |
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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Matrix Mapping | Period | ESSPeriod | 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_PERIOD | SRC_ESSPERIOD |
|---|---|
| January | Trimester1 |
| February | Trimester1 |
| March | Trimester1 |
| April | Trimester1 |
| May | Trimester2 |
| June | Trimester2 |
| July | Trimester2 |
| August | Trimester2 |
| September | Trimester3 |
| October | Trimester3 |
| November | Trimester3 |
| December | Trimester3 |
| @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 94For 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.
Table 37 lists the valid expressions for source dimensions:
Table 37. Valid Expressions for Source Dimensions
| Expression | Description |
|---|---|
| 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:
|
Table 38 lists the valid expressions for target dimensions:
Table 38. Valid Expressions for Target Dimensions
| Expression | Description |
|---|---|
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. |
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 Type | Source Dimensions[1] | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
Dimension Creation | Value | ESSValue | [Contribution Total],[Parent Total],<Parent Curr Total>,<Entity Curr Total> | ||
Currency | Currencies |
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.00Example 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 #MISSINGNote: | In creating the Currency dimension in Essbase, Analytics Link does not support retrieving currency rates. |
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 Type Source Dimensions[1] Target Dimensions Member Selection Mapping Table Prefix Measure Measure eal_measure 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 Type Source Dimensions[1] Target Dimensions Member Selection Mapping Table Prefix Measure Measures 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.
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]
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>
...
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.
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 Type | VISIBLE | Target Database | Description |
|---|---|---|---|
Dimension Creation | 0 (FALSE) | Essbase (Transparent Partition) | Analytics Link does not create the member in the Essbase outline. |
Dimension Creation | 0 (FALSE) |
| 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. |
Dimension Creation | 1 (TRUE) + SELECTED=1 (TRUE) |
| 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) |
| 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. |
Dimension Mapping[1] Join Dimensions[1] Break Dimension[1] | 1 (TRUE) + SELECTED=1 (TRUE) |
| 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. |
You can edit VISIBLE property values only in the mapping table.
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_MEMBER | TARGET_MEMBER | VISIBLE | ISBASE | GEN | DIM_LEVEL | SELECTED |
|---|---|---|---|---|---|---|
| [None] | ESSCustom1.FM None | 0 | 1 | 0 | 0 | 1 |
| AllProducts | AllProducts | 1 | 0 | 0 | 2 | 1 |
| Balls | Balls | 0 | 0 | 1 | 1 | 1 |
| Golf | Golf | 0 | 0 | 1 | 1 | 1 |
| GolfBalls | GolfBalls | 1 | 1 | 2 | 0 | 1 |
| GolfClubs | GolfClubs | 0 | 1 | 2 | 0 | 1 |
| GolfShoes | GolfShoes | 0 | 1 | 2 | 0 | 1 |
| GolfTees | GolfTees | 0 | 1 | 2 | 0 | 1 |
| Shoes | Shoes | 0 | 0 | 1 | 1 | 1 |
| Tennis | Tennis | 1 | 0 | 1 | 1 | 1 |
| TennisBalls | TennisBalls | 1 | 1 | 2 | 0 | 1 |
| TennisRacquets | TennisRacquets | 0 | 1 | 2 | 0 | 1 |
| TennisShoes | TennisShoes | 0 | 1 | 2 | 0 | 1 |
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 7As 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]. |
When Analytics Link creates an Essbase dimension, the mapping of Financial Management member names to Essbase dimension names are determined in this order:
Name changes based on the Add Parent to Entity Member database property value of the bridge.
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_MEMBER | ENTITY_PARENT | TARGET_MEMBER | VISIBLE | SELECTED |
|---|---|---|---|---|
| Stamford | Connecticut | Stamford_in_Connecticut | 1 | 1 |
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)
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_MEMBER | HFM_MEMBER | VISIBLE | SELECTED |
|---|---|---|---|
| Ac_A | A | 1 | 1 |
| Ac_B | B | 1 | 1 |
| Ac_B1 | B1 | 1 | 1 |
| Ac_B2 | B2 | 1 | 1 |
| Ac_C | C | 1 | 1 |
| Ac_D | D | 1 | 1 |
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 Type | Source Dimensions | Target Dimensions | Member Selection | Mapping Table | Prefix |
|---|---|---|---|---|---|
| Dimension Mapping | HFMDim1 | ESSDim1 | 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.
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.