Robert, our new consolidation consultant, wants to:
- Understand the use cases of the Data Mapping tool in SAP S/4HANA Finance.
- Explain the process of Data Mapping.
- Describe the main structures of the CSV Mapping Template file.
Data Mapping using the delivered applications in SAP S/4HANA Finance for group reporting Data Collection is a flexible ETL (Extraction, Transformation, Loading) tool, which includes the ability to:
- Define mappings (source to target)
- Run/schedule mapping jobs and
- Check results in traceability logs.
Use cases for Data Mapping with SAP S/4HANA Finance for group reporting Data Collection:
- Import data from remote systems from CSV source files
- Import data from SAP ECC
- Import data from remote systems with Import API
- Import ACDOCA data from remote systems
- Import additional ACDOCA data for statistical information (for example, aging information of AR/AP)
- Copy missing consolidation units' reported data for non-material consolidation units
- Run step consolidation
- Supplement C/I (Consolidation of Investments) control data
- Calculate annual net income for non-balancing books (for example, rounding differences from local ledger data transfers).
With the Define Data Mapping app, you can define mappings between source dimensions from any external system and target dimensions available in SAP S/4HANA.
The source types you can use are:
- .csv files to transfer data from any system into your SAP S/4HANA system
- ACDOCA data from a remote SAP S/4HANA system or the same SAP S/4HANA system to convert data into your (leading) SAP S/4HANA system
- ACDOCU data within your SAP S/4HANA system.
Transformation rules to convert the source data and get it ready to be imported into SAP S/4HANA Finance for group reporting are set up with the help of delivered Excel mapping files which can be downloaded from the Define Data Mapping application depending on the source type.
Excel template files are provided to help you create your data mapping definitions. A template file guides you through the process of creating your mapping. It includes predefined column names that are required in the mapping.
The target of the data transformation is always table ACDOCU of SAP S/4HANA Finance for group reporting.
With the Run Data Mapping app, you can create mapping jobs to run your mapping and import your datar data imported into the ACDOCU table of SAP S/4HANA Finance for Group Reporting.
In addition to using the delivered templates, you can also create and manage your own templates, the so-called Custom Templates.
Importing a CSV file to ACDOCU is the most general use case. Data is extracted from your source system in a CSV file. Data Mapping will then map, transform and load data in ACDOCU.
The mapping file can't exceed the size of 6 MB.
Once data from a source type "File" is processed by the mapping, its document type is by default set to DT = 0C.
Use the Define Data Mapping app to download your mapping template file.
A mapping file provides the mapping, i. e. transformation between input and output dimensions.
What the Metadata tab shows:
- In the first column, under "I", you'll find the input column names. These are the column names from the CSV source file depending on the template you chose.
- The output column names are in the second column, under "O". These output column names correspond to the ACDOCU columns.
The Mandatory column helps to easily identify which characteristic from the ACDOCU table is mandatory in the mapping file output column.
Some output dimensions have mandatory formats that are expected by the ACDOCU table. These formats are listed in the Format column of the Metadata tab of the template files.
If you have downloaded the Template for File, you have to enter the column names from your CSV source data file in column A of the Metadata tab.
The Column Type Ids on the Metadata tab explain how to deal with the identification of columns.
The following column types exist:
- I = Input column/source column, i.e., a column you want to load and transform.
- O = Output column/destination column, i.e., a column of ACDOCU you want to load.
- IO = Input/Output column. You define rows for mapping in a particular order. Once a transformation from an Input column to an Output column is done, the result of the Output column can be reused as an Input column for a different Output column. So Input/Output columns are used as Input columns in the current mapping. They come from Output columns in previous mappings within the same mapping file.
- T = T Columns are temporary columns in intermediary mapping steps. They represent elements that are neither in the Input source nor in the Target source ACDOCU. They help to define the mapping. T columns are defined as Output columns in mapping and they are used as input in the following mapping step of the same mapping file.
- TN= TN columns are temporary numeric columns. They are not part of the ACDOCU-generated columns. You can use TN columns in your intermediary mapping steps. These columns support numeric operators.
In a Mapping tab, rows and columns form the main structure. The columns represent the dimensions to be converted from source to target. You can create as many mapping sheets as you need. The best way is to copy one mapping sheet into the next one, exchange the source and target dimensions to be converted as well as adapt the name of the copied mapping sheet, in order to see, for which dimension the mapping was set up on this sheet. There is no requirement regarding the name of the sheet.
So when a mapping sheet was created, you first have to select the source fields from your CSV file as I dimension (I stands for Input column dimension) and the mapping target fields of table ACDOCU as O dimension (O stands for Output column dimension).
In the figure above you could for example select "Company_ID" as the Input column dimension (I) opening the Input Help of the drop-down in cell B2.
You could then select "ConsolidationUnit" as the Output column dimension (O) using the Input Help of the drop-down in cell C2.The rows starting from row 4 have then to be used to set up the mapping rules between "Company_ID" and "ConsolidationUnit".
Keep the following in mind when setting up a mapping file:
- Excel formulas on a mapping tab are not supported.
- The same input dimension can be used in several mapping sheets.
- You can use the target dimension of a mapping as a source dimension in another mapping.
- The loading process will consider the order of the Mapping tabs in the workbook to determine the steps, i. e. an input column is valid only if it matches the previous filters.
- You can build a mapping to import data for multiple versions, fiscal years, periods, and combinations thereof in one go.
- Values defined in Global Parameters are completed or overwritten with the ones specified in your mapping file.