FDM / FDMEE Mapping – General Principles

Avatar

By
February 24th, 2016


Let’s discuss the basic fundamental purpose of FDMEE. That is to say mappings. FDMEE has come a long way since its origins, and is at this stage is an ETL-like tool (Extract Transform Load). Mapping is the “T”, Transform, part of this acronym. This is where source system, typically General Ledgers, metadata members are transformed to a destination EPM application dimension member. Think of these as accounts, entities, cost centers, product members, and so on.

For example let’s presume a general ledger source account, 400100, with a description of “Product Revenue”. In an EPM application, the related account is “Sales_External”. Mapping is the process of pointing the source G/L code to Sales_External, thereby transforming it in the FDMEE processes.

In FDMEE there are five mapping types. These are found in the Workflow tab under Data Load and Data Load Mapping

  • Explicit
  • Between
  • In
  • Multi Dimension
  • Like

Explicit is a one to one mapping. G/L account 400100 = Sales_External. Note that there is a box for Change Sign. This is commonly used for G/L source data where a trial balance has negative signs for Revenue and Liability/Equity source accounts. Often in EPM applications these are a positive report view and therefore a sign flip is required.

Between is a continuous range of source members’ maps to a one EPM member. For instance accounts 400000 through 401999 are External Sales, but from 410000 these might be returns, allowances, intercompany sales, or so on. In the Source Value put in the start value, a comma and the end value: “400000,401999”. Note that a Rule Name is required for these.

In mappings are a non-continuous range of source member maps to one EPM member. For instance, perhaps the revenue accounts in the G/L are not in order and 400000, 400100, 400200 and 400500 are External Sales types. These are separated by a comma.

Multi Dimension is a new mapping process for FDMEE, as this was not available in FDM. This enables you to use more than one source column / dimension to map to one source EPM member. For instance, let’s say that in all instances the source Entity and the account 402000 are SalesReturns but for Entity E100 this is rather SalesDiscounts. In FDM an Import Script was often used to resolve this. In FDMEE the Multi Dimension mapping facilitates this without scripting. I have a detailed article that deals with this process.

Like mappings use wildcards to facilitate the mappings. These are very beneficial to keeping the mappings more manageable. For instance, if there were over 100 4##### accounts, where all accounts started with 4 go to Sales_External, you would not necessarily have over 100 Explicit mappings, where instead you could use one Like mapping to handle this. Another item for consideration is the available wildcards available. These are:

*

?

The asterisk/star is for anything! So a “4*” for the source could mean 40, 400, 4111, 4ASX11, 49999999, and so on. Thus * by itself would mean everything. This can be useful if there is a default destination often for a custom / user defined dimension, such as EndingBalance.

The question mark is a single character replacement, so “4?” could mean 40, 41, 42, 4X.

These can be used in tandem, for instance 4??2* means any number starting with 4 and a 4th value of 2 followed by any value / string. In the following example I am saying that all accounts that start with 40 go to Sales_External.

Now you might be saying, “Can I use these mapping types in combination?”. For instance, all of my 401xxx G/L accounts, except for 401800, map to Sales_External but the outlier needs to go to SalesReturns. The answer to this is yes, as there is sequence to the mapping. Explicit happens first, followed by Between, then In, then Multi Dimension, then Like. So if I define the 401800 in the Explicit mapping and then have a default Like mapping for 401*, the Like mapping will NOT overwrite the Explicit mapping, as it already exists. If this is confusing, you could think of this in “reverse”, that is that the Like mapping is overwritten by the Explicit mapping, but that is not really the sequence.

Also within the same mapping type, such as Like, these execute in an alpha-numeric sequence, so in the above the star to star Like mapping happens last in precedence as its name is zdefault. Any mapping prior to that will already exist.

So there you go – an overview of FDMEE mappings.


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

Leave a Reply

Your email address will not be published. Required fields are marked *