FDM, Essbase and Mapping Tables

I thought I’d do a quick little post on how to use FDM mapping tables to your advantage. To do this, I am taking advantage of the backend SQL tables that support FDM. I was recently given a scenario where I needed to use data mappings to load data into an Essbase reporting application. The environment had Essbase (for reporting), Planning, FDM and HFM. All products were on version 11.1.2.2. FDM (not of the EE persuasion) was used to load data into HFM.

The issue came up when we needed to load trial balance data from a newly acquired business, operating on one chart of accounts, to the head office, operating on a different chart.

To illustrate, a sample record may look like this:

9_1_Mapping

In other words, the source data contained members which did not exist on the Essbase side. So, we needed to map records for these intersections into the correct counterparts on the Essbase side. Now, we could use different kinds of techniques to achieve this mapping, including, but definitely not limited to:

  • “Replace” functionality on load rules
  • SQL mapping tables, among others

But the problem still remains as to how to expose these mappings to users so that they could make additions, make changes or even just see how the target data was sourced? I didn’t want them to have to update tables or rules, but at the same time, wanted a more dynamic way to maintain these mappings.

Since we already had an FDM application feeding HFM, I decided to use this existing application as the repository for the mapping information. Users are used to updating maps for HFM anyways, and this would be no different and honestly, I did not want to create a new application to load into Essbase. I am quite happy with load rules. The architecture I had in mind would look a bit like this:

9_2_Mapping_Architecture

Steps:

  • Set up a “mapping” location not tied to any import format.

9_3_Location

  • Create maps tied to the location above. I created maps for all the dimensions in question.

9_4_Map

 

  • Set up a simple view on the FDM schema to generate the source and target lookups. The schema contains a number of tables, but I only needed 2, TDATAMAP and TPOVPARTITION.

TPOVPARTITION contains the names of the locations and the “key” (PARTITIONKEY column) to join to the TDATAMAP table.

9_5_TPOVPARTITION

TDATAMAP contains all the mappings.

9_6_TDATAMAP

The DIMNAME column can be used to figure out the name of the source dimension. For instance, account and entity dimensions will have explicit names of ACCOUNT and ENTITY respectively. The other dimensions are referred to as UD1, UD2 etc. It isn’t hard to figure out which dimension is which.

The query below gives me the details that I needed for my purposes:

9_7_Results

  • Set up a SQL load rule to load data into Essbase with lookups to the mapping view above. I am not going to go into the details of that exercise. The only other configuration I needed to make this work, was to make sure the Oracle schema for loading data could “see” and query the FDM schema. The DBA helped set this link up.

Conclusion

There are a number of uses I can think of for this type of load mechanism. You could use the mapping tables to further perform drill through from the primary chart of accounts, to the secondary chart. You are also empowering your users to maintain these mappings.

One last note, I haven’t had the opportunity to test this on any version post 11.1.2.2 or on Enterprise Edition.

Thanks to Koti C. for providing the initial input on which FDM tables might be of use.

About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

Leave a Reply

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