Exploring the Mapping Syntax

Robert, our new consolidation consultant, wants to:

• Understand the semantics of the mapping template
• Learn how to set up a mapping file
• Get into the mapping syntax

The figure, Wording for Mapping File Explanations, shows the wording for mapping file explanations.

Three special row types exist in the delivered mapping templates:

• The column type row
• The level row
• The alias row

Then, the so-called mapping rule rows follow indicated by "0".

In this mapping example, the input column dimension values of "COMPANY_CODE" and "ENTITY_CODE" transform into the output column dimension values of "ConsolidationUnit".

Each row in the example represents a specific mapping rule.

Compared to input column values, dimension values in output columns must be in quotes.

You can use wildcards such as "+" and "*" when setting up a transformation file.

In this mapping example's first row, all values of the input column dimension "ENTITY_CODE" (except when its source file value is empty) combine with the input column dimension value "0001" of "COMPANY_CODE". This combination transforms into output column dimension value "4035" of "ConsolidationUnit".

The figure, Mapping of Input Column Values to Output Column Values: Mapping Possibilities, shows three possible ways of converting input column dimension values into output column dimension values.

When you set up a mapping file, if "#" is explicitly set in the source file, make sure you only select # as the input column dimension value. If the dimension value in the source file is actually empty, set {EMPTY} as the input column dimension value in your mapping file.

If in the ACDOCU table, a dimension value must be empty, select "" as the value of the relevant output column dimension.

These simple mapping examples in the figure, An Example of a Simple Mapping Scenario, show the syntax of how dimension values in a source file can convert to target dimension values.

In this scenario, "Company_ID" in the source file and "ConsolidationUnit" in the target database ACDOCU have the same dimension values. In such a case, a mapping between both dimensions can be easily set up. For the Output column dimension value, enter the input column dimension name "Company_ID" in square brackets. This is described in the mapping examples.

In the structure of the downloaded mapping file, row type 3 (third row) always starts with "Alias".

An alias enables you to do the following:

• Replace technical column dimension names with names that are more explicit.
• Rename target column dimensions from an intermediary mapping that are used as source column dimensions in the next mapping.
• Rename columns that have the same names in both the ACDOCA source table and the ACDOCU target table.

If dimensions such as "CostCenter", "PartnerCostCenter", "ProfitCenter", or "PartnerProfitCenter" are target dimensions in a mapping scenario, the output column dimension "ControllingArea" must also be added. "ControllingArea" is compounded to each of the mentioned output column dimensions.

For the mapping rule rows, you can establish a rule hierarchy, that is, you can define the order in which the mapping rules apply.

A child rule (for example, Level 1) is attached to a parent rule (for example, Level 0). Therefore, the filter of the parent rule (Level 0) must include the selection of the child rule (Level 1). The child rule selection represents a more restricted group of data than covered by the parent rule. When you add a rule of Level 1, it is an exception to the rule of Level 0, which comes before it.

If the mapping run executes, only the data generated by the child rule (Level 1) is kept.

Here are the processed hierarchy steps following the mapping example. The rows belong to the "Flow" dimension in the source file:

1. The first four rows:
   • They match the first parent rule "F0.*". However, do not match the child rules "F00.*" and "F000.*". The system uses the parent rule to generate subitem "915".
   • They do not match the second parent rule {EMPTY}. The system does not generate anything for the second parent rule.
2. The fifth row:
   • It does not match the first parent rule "F0.*". The system does not even look into its two-child rules.
   • The system goes directly to the second parent rule {EMPTY}. {EMPTY} in the source file and {EMPTY} in this second parent rule match. Therefore, the system generates subitem "900".
3. The sixth row:
   • It matches the first parent rule "F0.*". The system looks at the first child rule "F00.*" which also matches and so the system ignores the parent rule "F0.*". It uses the first child rule "F00.*" to generate subitem "920".
   • It does not match the second parent rule {EMPTY}, so the system does not generate anything for the second parent rule.
4. The seventh row:
   • It matches the first parent rule "F0.*. The system looks at the first child rule "F00.*" It also matches, so the system looks at the second child rule "F000.*" which is the child rule of the child rule. It also matches, so the system ignores the first parent rule "F0.*" and the first child rule "F00.*". It uses the child rule of the child rule, which is rule "F000.*" to generate subitem "930".
   • It does not match the second parent rule {EMPTY}, so the system does not generate anything for the second parent rule.

In this mapping example, a conversion logic for the combination of input column dimensions "GC_Unit" and "Company_ID" into target dimension "PartnerConsolidationUnit" is set up.

As shown in the scenario, to filter the input column dimension values from the source file, you can use the following mapping-specific operators: "<>", "|" and ";"

Here is a further syntax example using the mapping-specific operator "|".

The output column dimension values of the output column dimension "Subitem Category" can be filled, depending on account groups as described in the example.

You can deactivate or exclude a mapping rule by using the hash sign (#) in the relevant rule row. When you put this sign in front of the level value, the information of the level still displays. However, when the mapping job runs, the rule does not trigger.

For example, row 5 and row 7 in the mapping example are ignored. In the mapping example, except for those in rule row 5 and 7, the posted data values of the "LocalValue" input column measure copy into the "AmountInLocalCurrency" output column measure depending on their "FinancialStatementItem". The same applies to the measure unit. It is copied from the "Local_Cur_Unit" dimension to the "LocalCurrency" target dimension.

Generally speaking, in one mapping file, you define rows for mapping in a certain order. Once a transformation from an input column to an output column is set up in one mapping sheet, you can reuse the result of this output column as an input column of a different output column in a second mapping sheet of the same mapping file.

In the mapping file above, you can see that for all FSItems except for „111100" and „316000", ProfitCenter „YB900" and ControllingArea „A000" will be written in table ACDOCU when the source file and mapping file are uploaded. The exception is related to the level 1 rule rows on the „Mapping ProfitCenter" tab.

The example uses mapping file input/output columns and text columns. Here is the difference between using an input/output column and a text column: You use text columns in intermediary mapping steps but their values are not written into the ACDOCU table. Input/output columns write their value in their output column into the ACDOCU table.

The mapping example above explains the syntax of functions CLEAN and REPLACE.

With the help of the "REPLACE" function, all Input column dimension values of the "FLOW" dimension are checked, and the first character "F" of these values is replaced by "9" in the Output column dimension values of the "Subitem" dimension. In our scenario, "F15" is therefore transformed into "915".

The exception from this rule is the Input column dimension value "FYB10". Here the character "F" is removed, so the Output column dimension value is transformed into "YB10".

Finally, if the Input dimension value of the "FLOW" Input column dimension is empty in the source file, the "Subitem" Output column dimension value is transformed into "900".

Make sure you stick to the mandatory formats when setting up a mapping file. For example, the output column dimension values of "FiscalPeriod" must contain three digits when you set up the mapping file. In the mapping example, you reach this using the "RIGHT" function.

The figure, Example for the Conversion of a Period Dimension, shows another way you can convert the source format of a period dimension into three digits, complying with the target format needed for "FiscalPeriod" in the ACDOCU table.

The figure, COMPLETE, CONCAT, and FIXED Function Syntax, shows the COMPLETE, CONCAT, and FIXED functions that can be used in mapping files and how to set up their function syntax.

The figure, MID and REPT Function Syntax, shows the MID and REPT functions that can be used in mapping files and how to set up their function syntax.

The figure, SUBSTITUTE and TRIM Function Syntax, shows the SUBSTITUTE and TRIM functions that can be used in mapping files and how to set up their function syntax.