Creating Mapping Tables in the Dimension Mapping

The following procedure provides step-by-step information on defining mappings while linking dimensions in the Dimension Mapping. The source and destination applications are automatically selected, so the first screen of the Mapping Table Designer is skipped.

  To create a mapping table while mapping dimensions:

  1. In the Dimension Mapping, right-click a link, and select Define Mapping.

    The Mapping Table Designer (Map Data) is displayed.

    Each row represents a mapping rule between the source and destination application dimensions. You double-click a cell to enter values directly into the cells or click a cell and select Pick Member to select a member.

    When entering source values directly into a cell, you can use symbols as wild cards and indicators.

    Table 18. Source Dimension Rule Syntax

    Symbol Usage
    * Matches on one or more characters. For example, Acc* matches Acc100, Acc101, and so on.
    ? Matches on one character. For example, Acc??? matches Acc100 but not Acc1000.
    ~ Matches on a range of characters. For example, Acc100~Acc200 matches on Acc100, Acc101, and so on up to Acc200. If both fields are numeric, the range is evaluated numerically. If either field is alphanumeric, the range is evaluated lexicographically.
    \ Indicates that the next character is interpreted literally. For example, Acc\* matches only and precisely Acc*.
    ^ Indicates a space.

    Required—for leading and trailing spaces.

    Optional—for spaces displayed in the middle of inputs.
    {NULL} Matches on an empty field.

    Destination dimensions use different rule syntax. The source rule syntax defines which intersections match the rule. When a row is passed through the translation engine, each source rule is checked to see if the member fits within the rules defined. If a match is found, the destination rule is applied to that member. The destination rules define the modifications to be made to the members that match the source rules. The rule syntax in destination dimensions accepts the following entries:

    • Member names

    • Source member names in combination with literal strings

    • Contents of wild cards

    Table 19. Destination Dimension Rule Syntax

    Symbol Usage
    [] Destination values which can be concatenated.
    [field#] Transfer value of the indicated input field in its entirety. An input field is identified by its sequence number based on the order of definition for the input field. For example, [1] indicates that the value of the first input is the output. FY[1][2] indicates that the output value is the string FY concatenated with the value of input 1 and the value of input 2, so if input 1 is Years, and input 2 is Months, the result is FYYearMonths.
    [field#,* ] The value of * from the input field. For example, if Output1 is defined as [1,*] and Input1 is defined as 123*, if Input1 = 123456, Output1 = 456.
    [field#,*#] Required format if the referenced * is not the first instance of * in the input.

    # is the position of the * in the input field.

    [1, *1] is equivalent to [1, *].

    For example, if Input3 = *123* and Output1 = abc[3, *2], Input3 = 91234 and Output1 = abc4.
    [field#,?] The value of ? from the input field. For example, if Output1 is defined as [1,?] and Input1 is defined as 123?, if Input1 = 123456, Output1 = 4.
    [field#,?#] Required format if the referenced ? is not the first one in the input.

    # is the position of the ? in the input field.

    [2, ?1] is equivalent to [2, ?].

    For example, if Input1 = 123?, Input2 = 4??, and Output1 = abcd[2, ?2] and Input1 = 1237, and Input2 = 498, Output1 = abcd8.
    \ Indicator that the next character is interpreted literally. A \ is necessary when the [ or ] character is used in an output. In an output, the use of \ within a [] definition is not allowed.
    ^ A space indicator.

    Required—for leading and trailing spaces.

    Optional—for spaces displayed in the middle of inputs.
    {NULL} Value used to output an empty field.

  2. Perform an action:

    If you select Pick Member, the member selector enables you to view and select members within a dimension. You can expand and collapse members within a dimension using the [+] and [-].

    If you cannot find a member, you can search for members in the member selector Pick Member dialog box. See Searching for Members Using the Member Selector.

  3. If necessary, modify the mapping:

    • To insert a row at the end of the table, click Add.

    • To copy a row, select a row, and click Duplicate.

      Duplicate rows are inserted at the end of the table.

    • To delete a row, select multiple rows, and click Remove.

    • To move rows up or down in the table, select the rows, and click Move Up or Move Down.

      The rules are processed in order. When a match occurs, the rule is applied to the member and processing completes (no other rules are applied to that member). In some cases, members may match multiple rules so you can control the order of the rules.

    • To edit a cell, double-click the cell or press [F2].

      Tip:

      You can navigate the table using the [Tab] key.

  4. Click Next to validate the mapping tables.

    The Validate Mappings screen contains a table with mapping rules in each row. Only rule syntax, not member information is validated. Each rule is marked as passed or failed.

  5. Click Validate.

    Tip:

    You can click View All to view all rules or View Exceptions Only to view only rules that do not pass validation. If View All is selected, the failed rule is highlighted in red.

  6. If a rule did not pass validation, click Back to return to the Map Data page, fix the errors, and click Next to re-validate.

  7. Click Next.

    The Mapping Properties screen is displayed, enabling you to provide a name and description for the mapping table. It is helpful to provide a prefix, such as MAP_.

  8. Enter a name and description, and click Finish.

    There are no restrictions for names and descriptions.

    A dotted line indicates that a mapping is defined.

    The mapping also displays in the Data Synchronizer. See Working with the Data Synchronizer.