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.

FCC - Using Entities that are Shared in Insertion Rules

When working with insertion rules, sometimes there is a need to calculate something only for specific entities. But, if that entity is shared - in other words, exists in multiple hierarchies or has multiple owners - there can be problems. Here's how to deal with this common problem.

The need most arises with IF statements. The code usually looks something like this:

IF(@ISMBR("331"))

   do some calcs

ENDIF;

This code will pass validation and deploy successfully, but when trying to consolidate, an error is generated.

Clicking the link and drilling into the error message will show this:

Unable to resolve duplicate member name ["331"] on line ____

The problem is the rule doesn't know whether to run for [ParentA].[331], [ParentB].[331], etc. One workaround would be to write the IF statement like this:

IF(@ISMBR("[ParentA].[331]"))

But if the entity gets moved, then that creates another problem.

The solution is to use the @EQUAL function. @EQUAL searches the outline for the specified string within a specified dimension. The result looks like this:

IF(@ISMBR(@EQUAL("331","Entity")))

If an additional entity existed like 331abc that should not be included, then add an additional condition to the IF statement to exclude that entity.

FCC Constant Currency

An occasional requirement for an FCC implementation is to translate data at multiple currency rates. There's always actuals at the actual rates, but then you can translate actuals at last year's rate, actuals at the forecast rate, actuals at next year's budget rate, etc. These different translations provide the data to show whether operations performed well against the comparative data or did the exchange rate just work in their favor.

There are a couple of different ways to set this up. The first question is whether to use a scenario or a custom dimension to hold the translation versions. If the requirement includes the balance sheet, is used occasionally, or the customs are already used, then one or more scenarios is the recommended direction. In the case I'm showing here none of those apply, so this example will use a custom dimension. The requirement is for income statement only, the output is used extensively during close and subsequent reporting, and a custom dimension is available.

After creating additional members in the custom dimension to hold the results, the next step is to create a custom translation rule (as shown below). The top half of the rule shows where to source the exchange rates. In this case, the rates are sourced from scenario Actual for the prior year. The "current period" is specified, so Dec 23 actuals will translate at the Dec 22 exchange rates, Nov 23 at Nov 22, etc. The second half of the screen controls the source and destination members. The left side shows the source data: no constant rate (custom), descendant accounts of net income, and only applies to the actual scenario. On the right hand side is any redirection (target) members. In this case, the results are stored in LastYrRate (so No ConstantRate is translated and then stored in LastYrRate). The account and scenario dimensions do not need redirection, so those fields are left blank so that those members simply pass through.

Once the rule is created it just needs to be deployed to be in effect.

So that will take care of the translations. But if the analysis will include operations from the total company's currency, what to do there? Example: if total company is in US Dollars and there are US Dollar entities that roll up to the total with only US Dollar parents, then there is no translation. As there is no translation, the above translation rule will not include that data. This can be addressed by adding an insertion rule to simply copy the data over from No ConstantRate to LastYrRate. Note that the FIX statement should specify level 0 members of the intercompany and other custom dimensions.

One note on the @ISUDA function used. IsUSD is a UDA that is applied to the level 0 entities that have the "home" currency. Apparently there is a function that can read the entity currency directly but it isn't visible as of now in Calculation Manager. Oracle has filed an internal SR to expose the function which will remove the requirement to tag the entities with a separate UDA like this.

The custom translation and insertion rule will run with each consolidation. After consolidating, the users can simply switch their POV in reports, Smart View, etc. between the custom members No ConstantRate (for actual numbers) and LastYrRate (for actual numbers at last year's rates).

Data Exchange - DRCRSplit for Delimited Files

Recently had to solve a problem during an implementation and as I've done before, I'm adding the solution here so I can find it again.

An incoming trial balance file is delimited and has two columns for the amount: a debit column and a credit column. Each record will have a value in one of these but not both. If the file was fixed format instead of delimited, there is a built-in import format expression called DRCRSplit that will deal with the two columns. With the function, you specify the midpoint of the overall character count but with delimited files you don't have a consistent midpoint character count, so it doesn't work with delimited files.

Like maybe some of you reading this, I did a Google search on this topic and found multiple Oracle forum posts, CloudCustomerConnect posts, etc. asking the same question over several years but didn't find an easy solution. There was a posted answer that combined the DRCRSplit function with the Column function, but as pointed out by someone else that solution causes data problems. So, below is what I did. There may be a more elegant way of solving this problem, but the solution worked for me.

I mapped the debit and credit balance columns to separate attribute fields and for the regular amount field, I mapped the account field to it. Each record needs a numeric amount; otherwise, it will get dropped on the import. I'm thinking the year could be used as the placeholder amount if it were available on each record. I'm guessing that one of the amount fields would probably work in conjunction with the no zero suppress expression.

[Editor note: if the right sidebar is covering part of the picture, click it to see the full detail]

Next, I used a SQL map on the entity to (a) pass the entity through and (b) to populate the amount. Below, the entity portion isn't shown. The AMOUNTX field (the target amount) is populated from the debit column if it is not equal to zero and from the credit column if the debit is equal to zero. Anything placed in an attribute field is stored as a string, so the TO_NUMBER function converts the string back to a number. If there was a need to deal with quotes, spaces, commas, etc. in the number as thousands separators or decimals, that could be addressed with either a format on TO_NUMBER or using one of the REGEXP functions.

I attached this SQL to the entity dimension mapping. If the entity dimension needs more mapping and the SQL gets in the way of using the other mapping types (explicits, etc.) then attach the SQL to another dimension, like Data Source. Any of them will work, so the SQL should be placed somewhere inconspicuous.