EPM Cloud Data Integration - Dealing with [Parent].[Child]

Another day, another solution to remember for later.

When moving data from one application to another, shared members can sometimes flow through the integration with [Parent].[Child] notation. If loading the child member to the target application and the target does not have the same hierarchy, then this won't work. Or, like I did today, when loading entity elimination data from FCC to Planning, then only the parent entity is needed.

A #FORMAT map can do some of this (and maybe all). #FORMAT can ignore specific positions, replace text, etc. Here's the doc link. But there are limitations to #FORMAT and the need for SQL scripting comes into play.

https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/erpia/format_mask_mapping_for_target_values.html 

With SQL scripting, here's what to do. There are two main SQL functions to use: SUBSTR and REGEXP_INSTR. SUBSTR returns a subset of a string. REGEXP_INSTR searches for a character and can return different things, mainly the position of the character.

In the particular use case shown here, only the Parent portion was needed, so omit the first [ and then omit everything after the first ]. Here's the SQL using the two functions. With SUBSTR, we start with the string in the field ENTITY, start with position 2, and then use REGEXP_INSTR to know how far to go. This function is searching for the first ] and returning its position within the string. That result is the last parameter for the SUBSTR function (how far to go) but we need to subtract 2 from the result. Subtracting 1 since the first character is being omitted and subtracting another 1 as the ]  - the character being searched - isn't needed.

SUBSTR(ENTITY,2,REGEXP_INSTR(ENTITY,'[]]+', 1,1,0)-2)

Since I'm using a wildcard map to process all incoming records, I also wanted to flag members that come through without the [parent].[child] format, so I used SUBSTR again to check the first character. If [ then use the above; otherwise, flag that there is a new entity without [parent].[child] (which would mean the member is not shared) and should be dealt with differently, which could be an explicit map, passthrough, etc.

The ending result looks like this (I also added a prefix to the parent so the result looks like ELIM_parent). The lines starting with -- are comments.

This use case is focusing on isolating the parent. If just the child is needed, change the parameters for SUBSTR and REGEXP_INSTR, respectively, to focus on the end of the record without the last ] on the end and search for the position of the second [ in the string.