FDMEE - Dynamic Hierarchy Data Maps

The Business Case:

You need to set up Dynamic Data Maps based on a hierarchy in a given dimension like Cost Center or Entity (i.e. if the level 0 member value is a descendant of a given parent map it, otherwise IGNORE). 

The Problem:

Oracle doesn’t provide any documentation for how to read hierarchies to provide this type of dynamic mapping.  The hierarchies are in FDMEE as you can see from the selection box:

…but, how are you supposed to use the hierarchy in the mapping?

A Solution:

Well, this took a bit of trial and error within the FDMEE repository, but turns out, you can query the fully qualified path in the “AIF_TARGET_APPL_DIMENSIONS” table with a SQL Script in your data maps.  It’s actually quite straight forward with some nested queries.

Navigate to the Data Load Mapping and access the “LIKE” maps for the dimension you need.  Add 2 rows (at a minimum…  You can always add more).  The first one will be the SQL script, and the second one will be a default of “IGNORE”:

The idea here is that if we don’t map it with our script, we don’t want to load it to the Target Application.

Select the Pencil on our “#SQL” mapping line to add the CASE statement:

You’ll notice that we are doing some additional lookups for the Application ID and Dimension_ID in order to sync up with the correct hierarchy. 

I will note, that you will need to be careful with the Upper Level member “Like” characters if you have a lot of shared members.  You can add items there to make sure you are only searching your primary hierarchy.

It’s as simple as that…  Tack on an automated “REFRESH MEMBERS” function, and you are off to the races with your automation and dynamic hierarchy maps.

Hope this is helpful to you and good luck!