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!
Hi,
ReplyDeleteI'm pleased to say this also works in the Cloud!
Many Thanks
Mark
Awesome to know! Thanks Mark!
DeleteBest Regards,
Jonathan
Hi Jonathan,
ReplyDeleteI've been using this for the last few months and it works really well. thanks
I'm now trying to use it on a dimension with shared members and I cant get it work.
I have a Geo dim, with a Primary and a Stat hierarchy, each hierarchy has the same level 0 members, I'm trying to ascertain if the member is in the Primary Hierarchy under a GLOBAL parent then map to a specific account. Do you have any pointers to get this logic to work?
Kind REgards
Stuart
Sorry for the late reply Stuart. Hopefully, you've been able to figure this out since your post... I would suggest changing the "like" command to include the primary global parent AND the additional one like:
DeleteCASE
WHEN (select DISTINCT QUALIFIED_NAME...
...)
like '%GLOBAL_PARENT%UpperLevelParent%' THEN UD1
WHEN (select DISTINCT QUALIFIED_NAME...
...)
like '%Alt_Parent%UpperLevelParent%' THEN xxxx
END
Best Regards,
Jonathan
This article provides a practical solution to a specific FDMEE data-mapping problem where mappings need to respond dynamically to member hierarchies. The approach of querying the fully qualified path from the AIF_TARGET_APPL_DIMENSIONS table and combining the result with a default IGNORE mapping is particularly useful because it allows unmapped members to be excluded from the target application rather than requiring every hierarchy relationship to be maintained manually.
ReplyDeleteThe combination of hierarchy lookups, SQL queries, and data-processing logic makes Data Engineering Training relevant to the techniques described. The article demonstrates how understanding the underlying data structures and writing targeted SQL queries can help build more flexible data-integration processes. The additional lookups for Application ID and Dimension ID also show the importance of matching repository information to the correct application hierarchy.
The database-oriented implementation also connects with Backend Training, particularly because the solution works with application repository data and uses SQL-driven logic to influence how information is processed. The discussion of shared members and primary hierarchies is another useful implementation detail, since seemingly simple pattern matching can produce incorrect mappings when the underlying hierarchy contains multiple paths.
ReplyDelete