In my last post, I spend a bit of time explaining some of the fundamentals of mapping. In this post I’ll delve a little deeper into one of the most powerful transformation capabilities of FDM – cross dimensional mapping. Cross dimensional mapping is the concept of using 2 or more dimensions to determine the mapped result for a single dimension. For example, the general ledger cost center and account determine the Custom1 functional area.
Let’s take a moment to dive a little deeper into this example. The first logical question might be, why would I use a cross dimensional map in this example, why not just concatenate those source dimensions together and use a series of wildcard maps to determine the appropriate target EPM Custom1/function? This is a valid question and in some instances may adequately address the your needs. We’ll take a moment to flow this through. We’re a manufacturing organization. In our manufacturing locations, the salary expense of the cost centers is recognized as a cost of goods sold function where in our sales offices, this expense can be admin or selling functions. In this case, I could concatenate the cost center with the G/L account and use wildcard maps to determine the function. For example:
- CC5400-Acct501010 –> COGS
- CC1000-Acct501010 –> Finance
This approach means that every combination of cost center and account needs to be accounted leading to a potential geometric increase of possible “source” members which need to be mapped. While this might be applicable and/or needed, there’s also a chance that this combined “source” value is only needed for a few data intersections. In this instance, a cross dimensional map may be far better suited. Let’s take a look at how a cross dimensional map might be of use.
Imagine we have a series of accounts that capture severance expense. These accounts can incur charges in any cost center – including those normally mapped to finance, IT, marketing, research & development, et cetera. However, when any of these cost centers incurs severance expense, the function should be mapped to function “one time costs.” Now we could combine cost center and account together as our source value and design a series of explicit and wildcard maps to flow data to the correct function based on the combination. Or, you could use a cross dimensional map that evaluates the account and maps to the “one time costs” function when the applicable accounts are associated with the data record.
At this point, you might challenge, what’s the real difference in these 2 approaches. To that I would not argue and actually from a performance perspective, the former may perform better – in this specific example. But consider this, while FDM can use a cross dimensional map to transform based on 2 source dimension values (G/L account, cost center), it can also map based on the target/mapped value of one or more dimensions. Imagine that there a 10-12 severance accounts in the general ledger. Certainly you could add maps that account for each of these and update them every time there is a new account added to the G/L. But what if you could instead interrogate the target account of the record to determine if it was “Severance” and map to the appropriate “one time costs” function? Well, FDM cross dimensional maps allow you to do just that.
FDM can use the source or target member on the record to determine the target member for the current dimension being processed. Now here is the big, giant asterisks – the dimension from which you would want to use the target dimension needs to have already been processed. This is the concept (processing order) that I introduced in the prior post. As a reminder, by default, dimensions process in the following order:
So if I need to map the account based on the target custom1 (ex: Function), I could not do this without modifying the processing order of the dimensions. However, I could map the Custom1 based on the mapped Account.
At this point, you might be thinking – Awesome, I’m in, let’s do this, cross dimensional maps are super cool. Tell me how to do them. My pleasure. Adding a cross dimensional map requires a wildcard (Between, In, Like) type map. Once a map has been created, in the target field, enter the text #Script. This keyword will instruct FDM to utilize the logic entered in the Script field.
The script field is then used to perform the look-up of the other system/dimensional values. Based on the value returned, the current dimension target is set. An example script is below:strSrcAcct = varValues(13) Select Case strSrcAcct
Case “509995”, “509999” ‘Severance
Result = “One Time Costs”
In this example, the source account of the record is retrieved using the varValues(13) reference. When the source account is 509995 or 509999, the function is mapped to “One Time Costs”. The use of the keyword Result is what is used to set the target/mapped member. The script can utilize a variety of VBScript functions/methods to conditionally determine the mapped result. In this example, I utilized the Select Case statement to limit the scope of this map execution. And that’s it, your first cross dimensional map!
The varValues references are built-in application values. The below table highlights commonly used varValues:
|Location ID (POV)||varValues(1)|
|Category ID (POV)||varValues(2)|
|Period Key (POV)||varValues(3)|
* Each additional dimension (ICP, Custom1-20) will increment by 1 for the source and target values.
Since cross dimensional maps can only be used in wildcard maps, it is important to remember that any explicit maps that exist (or will added) will take precedent over a cross dimensional map. Additionally, cross dimensional maps have the potential to have a significant impact on mapping performance. In my next post, I will delve into the performance pitfalls of cross dimensional maps and offer some tips to help you avoid the dreaded poorly performing maps.