FDM Mapping – Part III

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:

Type Example 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
 
This entry was posted in Out of the Box Functionality and tagged , , , , . Bookmark the permalink.

7 Responses to FDM Mapping – Part III

  1. itguy says:

    Tony,

    Appreciate all the great information out here. I have a question regarding the order of processing for the LIKE mapping type. Basic question is if I have multiple LIKE maps that could match a source key does it process the comparison from left to right and get the most explicit one to match first?

    Scenario is the source data is passed in a format like acct-subacct-costctr and we are trying to use LIKE maps to simplify the process without getting into too many explicit maps

    So if I have following whereclausevalues
    Map 1 : LIKE 5*-742B-* Target = 742
    Map 2: LIKE 54*-742B-* Target = 999

    With a source key of 5455-742B-x can I safely assume it will use Map 2 instead of Map 1? Any dangers to watch out for here?

    Thanks,
    ITGuy

    • Tony Scalese says:

      All non explicit maps process alphanumerically based on the mapping rule name. So if you need to have map 1 evaluated before map 2, then your rule names should begin with something like A (for 54*) and B (for 5*). These are just examples, any naming convention will work.

      The other option is sequencing maps where the Location “Sequence Map” option can be activated. I think that this is a terrible idea and strongly recommend against it for all of my customers.

  2. Pingback: [BLOCKED BY STBV] Import Scripts – Not a Love Story | The FDM Information Source

  3. Clint says:

    Hi Tony,

    In this situation I am using FDM to load data to HFM… What would you do if you wanted to do a cross-dimensional map based on the parent of a target account. For example, If the TargetKey for the account is a decendant of ‘OperatingIncome’ then map C1_SourceKey ‘Snickers’ to C1_TargetKey ‘Candy’ Else map C1_SourceKey ‘Snickers’ to C1_TargetKey ‘[None]’.

    Hopefully this is not too specific for the purposes of this BLOG.

    Thank you,
    Clint

    • Tony Scalese says:

      Clint,

      If I take a step back, it sounds to me like you are saying that if the HFM account an income statement account, then map it to a C1 that provides a further breakout of the income/expense. But Balance Sheet accounts a break out is not applicable – i.e., [None].

      In this case, and some of this depends on your HFM account dimension, I read the HFM metadata settings and conditionally map based on the account type. Much faster response but like any solution, there are a number of variables to consider – metadata setup in HFM, drill through impacts, performance (if not properly written.

      I’ve done this for several clients and it works well. But again, each application is unique so this approach might need slight refinement to work for your needs.

  4. Julien says:

    Hi Tony,

    Do you think cross-dimensional mapping will perform better by using the ERPi with a flat file source adapter ?

    Thanks for this blog, it is always a source of relevant information.

    Julien

    • Tony Scalese says:

      Julien,

      Cross dimensional maps are agnostic to the data source. It doesn’t matter if the data is coming from a flat file, ERPi or directly from a relational.

      Tony

Leave a Reply