Admit it, you’ve been waiting with bated breath for the next post on FDM mapping. I know, I understand, this is exciting stuff. In all seriousness though, properly designed maps are critical to your application – for end-user maintenance, data quality and performance. The latter is the focus of this posting.
Over the course of my last 2 postings (Part I and Part II), I introduced you to the 4 mapping types and the idea of cross dimensional mappings. Let’s take a moment to look at the common mapping scenarios and the processing expense:
|Explicit||GL Account 10001 = HFM Account Cash||Low|
|Between (Simple)||GL Account 10001-10099 = HFM Account Cash||Low|
|In (Simple)||GL Account 10001, 10005, 10009 = HFM Account Cash||Low|
|Like Pass-through (* to *)||GL Account = HFM Account||Low|
|Like One Sided *||GL Account 100* = HFM Account Cash||Low|
|Like One Sided ?||GL Account 100?? = HFM Account Cash||Low-Medium|
|Cross Dimensional (using Between, In or Like)||When HFM Custom1 (Function) is COGS, HFM Account is Overhead||High-Very High|
Wait a minute Tony you have been touting cross dimensional maps over the past 2 parts of this series and now you are telling me that they can negatively impact performance? What gives? Yes, cross dimensional maps can be a performance killer. I have seen many applications where someone created inefficient maps and a single dimension would take more than 20 minutes to process the mapping. But, a well-defined/scoped cross dimensional map can perform efficiently (albeit with some processing/time cost) that is worth the efficiency for the end-user maintenance and/or data quality variables of the FDM application.
Let’s spend a moment walking through the Cross dimensional example. In the above example, the HFM account mapping is going to be defined by the HFM Custom1 member*. A poor performing Like map might look something like this:
Why is this an inefficient map? The scope of the map is far too broad. This map will run for every single account (Rule Definition = *) in the data. So if you have a data file with 100 records/lines, the data set will need to be loaded into memory, evaluated and then unloaded. This process is repeated 100 times – just to process the Account maps.
Ok, I think I understand, but how do I fix it. Great question and that is where the art of mapping starts to come into the equation. There is no hard and fast rule how to fix this but I’ll share these options with you:
- Use well-defined cross-dimensional maps and use them sparingly
- Concatenate source dimensions/fields to avoid the need for cross dimensional maps
- Leverage event scripts to conditionally set the source member which needs to be mapped
Notice that import scripts are absent from my list. This is not an oversight. My reasons for this are many and could likely take on another post/series in application design/best practices. For now, I am going to hold my virtual tongue and simple say, we’ll talk about this later.
Let’s take a quick look at how the above map could be better defined to have less of an impact on performance. This example actually pulls from my days in a manufacturing company. If you analyze what the map is trying to do, when the account is associated with a cost of goods (COGS) department, it is being mapped to an account called overhead. For all other departments, the account is being mapped to salaries. From a business perspective, this example is classic for manufacturing companies. Labor expense in manufacturing locations is often factored into the cost of good while non manufacturing related activities (Finance, IT, Sales, Marketing, R&D) is recognized as operating expense. Usually there are a handful of “salaries/labor” accounts in the general ledger.
So this cross dimensional map could change the Rule definition from star (*) to 5010* assuming that any account that begins with 5010 is a salary/labor expense account. Now we may have only 2-3 records of the original 100 for which this cross dimension map needs to be applied. The result is a far faster mapping process. The above change (science) is fairly straight forward but the real challenge is understanding the accounting/business rules as well as knowing the right balance of the available mapping methods (art) to apply when creating/maintaining an application.
As I stated in my first post, well designed maps – and in turn, a well performing application – are part art and part science. While these various posts will certainly help you understand the impacts of some of your mapping decisions (the science), the art portion of the equation is not something that can be conveyed via a few blog posts. I encourage you to continue to experiment with different mapping techniques and learn the art of a well designed map. This concludes the mapping series and I hope you found it valuable. I welcome questions and comments that could spur an encore posting.
* Extra points if you can tell me the potential issue with the above mapping definition