As I was thinking about potential topics for this post, I realized that I’ve never spent any time covering one of the most fundamental pieces of functionality of the application – Mapping. Since mapping is such an integral part of the application, this will be a multi-part series.
Let’s take a moment to define mapping. If you already understand what mapping is feel free to skip to the next paragraph. Some in the industry call FDM a data transformation tool but I think that sells the product a little short. I prefer to think of FDM as an ETL-lite (Extract, Transform, Load) application. Mapping is the T in this acronym; the process of transforming the source system member name/code into the target EPM application dimension member. For example, I have a general ledger account code of 10001 and its description is Cash – Bank of America Deposits. In HFM, my input level account member is Cash. Mapping is the process of transforming the G/L account code of 10001 to Cash. One final note, sometimes you will hear the mapping process referred to as translating the G/L code. I am careful to avoid this term because it can easily be confused with currency translation which is something I fundamentally oppose trying to do in FDM (that’s a post for another time).
Ok, now we’re level set on terminology. Let’s talk about the mapping capabilities of FDM. FDM has 4 mapping types:
- Explicit: 1 to 1 mapping; G/L account 10001 = HFM account Cash
- Between: A continuous range of members maps to a single EPM member; G/L accounts 10001-10099 = HFM account Cash
- In: A non-continuous range of members maps to a single EPM member; G/L accounts 10001, 10005, 10099 = HFM account Cash
- Like: A wildcard mapping; G/L accounts that begin with 100 = HFM account Cash
The transformation of each EPM dimension – with the exception of scenario, year & period (and value if loading to HFM) which are controlled by the FDM point of view – is managed as an FDM “mappable” dimension. Examples of mappable dimensions are Account & Entity. Each mappable dimension can use a combination of the mapping types outlined above. As such, it is important to understand the processing order of each of these mapping types and how FDM applies the transformation.
When maps are processed, each dimension is processed individually in a defined order. As an individual dimension is being processed, the mapping types are also processed in a defined order. Explicit maps are applied first. If an applicable mapping is found, the transformation is applied and FDM continues to the next record. If an Explicit map is not found, the Between maps are evaluated. This process continues for the In and Like mapping types. If no applicable map is found, the FDM continues on to the next record. This process continues for the each of the active mappable dimensions. At the Validate workflow step, a comprehensive report of all unmapped members across all dimensions is presented to the user.
Understanding this process order is paramount to being able to design efficient and easy to maintain maps. Let’s continue with our previous examples. Let’s assume all G/L accounts that begin with 100 map to HFM account Cash. A like map 100* would easily accommodate this. However, what happens when there is a G/L account 10053 should map to HFM Account Short Term Investments? With a situation like this, I have sometimes been challenged by a client (who has not read this post) that we can’t use the like map because of this exception. But we know that this isn’t true. Because explicit maps are applied first, a like map can be used that maps all G/L accounts beginning with 100 to HFM account Cash as long as an explicit map for G/L account 10053 is defined that maps to HFM account Short Term Investments. This is part of the science but also the art of designing FDM mappings.
Now that we’ve discussed the processing order of the mapping types, we can move onto the processing order of the dimensions. By default, FDM processes dimensions in the following order:
This order can be modified but the great majority of implementation utilize this order.
Ok Tony, I understand why you told me about map type processing order but why do I care about the dimension processing order? Here’s why, FDM can map one dimension based on the source or target value from a different dimension. This is called cross dimensional mapping. Understanding dimension processing order is critical to using this functionality properly. For example, FDM can map the entity dimension based on the G/L source dimension or the mapped HFM dimension value. However, if you attempted to map an account based on the mapped Custom1 dimension value, it would never work (with the default processing order) because the Custom1 map has not yet been processed.
I’ve taken some time to educate you on 2 fundamentals of mapping. In the next installment, I will delve deeper into processing order and discuss the impact of map design on performance.