Thursday, November 20, 2014

FDM - Mapping Tables

Mapping Tables map source members to target members in FDM applications. There are 5 types of mappings.
  • Explicit Mappings
  • Between Mappings
  • In Mappings
  • Like Mapping
  • Conditional Mapping 
1. Explicit Mappings: one to one mapping. For example, source account 1100 will map to target account 10010101

Tips: You can define the Target Account as "IGNORE", then FDM will ignore all the data records with the source account defined in this rule in the source file. (e.g. records with 1300-101 source account will be ignore when load data to the target system.) You can do that in other types of mappings as well.

2. Between Mappings: many to one mapping. For example, source account in the range 1503~1591 will map to target account 15010101

 3. In Mappings: many to one mapping. For example, source account 1503, 1510 and 1515 will map to target account 15010101


 4. Like Mapping: many to one/many mapping. For example, all the source account start with 1190 will map to account 10010201


You can also define the same wildcard characters (* and ?) in both source and target members so that it will be considered to be automap. For example,


 5. Conditional Mapping: many to many mapping. You can define this type of mapping in Between, In or Like mappings.

When add a new line of the mapping, input "#Script" in the Target field. And then click "Zoom" in the Script field.


You can use a VB script to define a conditional mapping.


varValues(14) is one of the preset variables that are available for use in conditional script expressions, you can find the meaning of the variables in the FDM admin guide.


This ICP mapping means, if the target account equals "InterCoAP" then return ICP as "[Michigan]". In other cases, return ICP as "[ICP None]". The conditional mapping will be as below,


If a source value is used with multiple map types, FDM processes the maps in the following order:
  1. Explicit
  2. Between
  3. In
  4. Like
Explicit maps override all other map types. Between maps override In and Like maps. In maps override Like maps.

By default, FDM maps are sorted alphabetically, from within the Rule Name column. To enable FDM to sort and process maps by numeric value, you enable map sequencing. If a source account is valid within multiple maps of one mapping type, the source account is assigned to


For the dimensions order, FDM processes in the following order by default,
  1. Account
  2. Entity
  3. ICP
  4. Custom1...Custom20
/*------------------------------------------------------------------------------------------------------------------*/

Mapping Tables can be export and import. I don't like to use the MapLoader, instead, I use Import XLS to import mapping tables.

First, we can export the mapping table to Excel by clicking a button.


After the exporting, you can open the Excel file.


You can find there two rows are hidden in this Excel, unhide these rows.


You can find the hidden information as below. Actually, it's the table name and the field names of each column. Don't try to edit these two rows.


In the Excel upper left corner, it's the name box. You can select a name called upsTDATAMAP in this file.


It's the range definition which will be used for import in the later.


You can try to add a row in the end of the existing records to define a new mapping.


Then you need to navigate to Name Manager under the menu Formulas.


You can find the original range of upsTDATAMAP here.


Update the range to cover the new added row.


Then you can find the upsTDATAMAP's range has been updated as below.


Return to FDM application, click the menu Tools and then Import XLS.


Select the Excel file we updated before.


Click Import


Shows the following information.


You can find the record can be added successfully.


Actually, you can use this method to import any other objects that can be exported from the FDM application. (e.g. Control Tables, Import Formats...)

15 comments:

  1. Hi Martin

    As i am new to FDM we need to update the mappings to include a script such that all customers in the field which do NOT start with a “C” are mapped to NO_CUSTOMER.

    Please help

    Thanks in Advance

    ReplyDelete
  2. Hi Zeeshan, I assume you are using FDM Classic, because FDMEE will be another story. In you case, you may create a mapping rule with target as "#Script", in the Script filed, you can input - If Left(varValues(19), 1)<>"C" Then Result = "NO_CUSTOMER"

    I don't know which custom dimension is for customers, for varValues(19), it stands for Source UD1. You can search FDM admin guide for the usage of varValues, or search Conditional Mapping Example

    ReplyDelete
  3. Hi Martin - Several of our entities did not fill in a local account description when the mapping was first established. We are wanting to update our existing mapping and have them include the description. However, when we update that field in the table and import it back in, the description is not added. They have also had this issue with updates to mapping. Do you know what might be causing this issue? Thank you for any guidance you can provide!

    ReplyDelete
    Replies
    1. I should note we are on FDM classic still.

      Delete
    2. Hi, I think you can try to add the description manually first and then export to Excel. After that I think you will know the formatting about how to import the description via Excel.

      Delete
  4. Hi Martin.. I am using FDM not FMDMEE.. and i have a parent location with two other locations using it.. What i am observing is if i update a member in the maps of one of the child location(not the parent) it is being inherited to the another child too. I do not want this to happen... Can it be done?

    ReplyDelete
  5. Hi in my understanding of the parent location is, all the children will share the same mapping with the parent. When you change any of the children or the parent, it will affect the others as well. You can check whether the parent's mapping is changed as well.

    ReplyDelete
    Replies
    1. Hi Martin.. i have a parent x, y and z use x as the parent mapping, both y and z have two dimensions for mapping a and b, so what i want is a should be sourced from x and i want to maintain mapping of dimension b separately in y and z.
      is that possible? i think i may be confusion you but i have nowhere else to go.. many thanks for your time.

      Delete
    2. Hi, I think I understand your requirement a little bit. But because the parent location doesn't allow you maintain the mappings separately. How about you can think about to maintain the mapping of dimension b in x which is a full collection including y and z. But the condition is there is no overlap of the y and z mappings or you can think a "Smart" way to make it clear. In this way y and z can still share a mapping.

      Delete
  6. Hi Martin. Great guideline. It helped me a lot. Is there also a way to delete a large number of maps, but not all of them, the same way? (Export to Excel -> manipulate data -> import xls) Thanks a lot! Regards, Christian

    ReplyDelete
    Replies
    1. Hi, I tried it several times. Adding of new maps is no problem, but deleting does not work at all. :-(
      Do I have to consider something special? Thanks a lot!

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi, we are using FDMEE and I am trying to add a Multi Dimension mapping with one of our Accounts using Explicit mapping for AMORT.Data. However, it appears that FDMEE wants to change it to AM"OR"T.Data. Is there a way around the system recognizing the "OR" within the member name?

    ReplyDelete