Lydia Maksoud

Brovanture Consultant

Oracle Cloud EPM Data Exchange Target Expressions can be used when importing data, to apply conditions to source values before loading to the target Oracle Cloud EPM application. Oracle advise that Target Expressions should be used when possible as they are more efficient than data load mappings, especially for loading large data sets.

To being, setup your integration as usual with a Location, Import Format and Data Load Rule.

Then to apply Target Expressions, go to Data Exchange, open the desired integration, and select Map Dimensions, as below:

For each dimension, use the Options cog to select Edit Target Expressions.

Expand the list to view the options:

The available Target Expressions are as follows:

Copy Source Value

Copies the exact source value to the Target.

This can be used instead of a Like * to * mapping.

Constant

Sets a constant value, regardless of the source value.

This can be used instead of a Like * to “Working” mapping, for example.

Default

Will apply a default value when the Source is NULL, otherwise uses the Source value.

Previously to replicate this functionality, we had to use a SQL script to a map NULL value.

Prefix

Add a prefix to the start of the source value.

This can be used instead of a Like * to PRE* mapping, where PRE is the desired Prefix.

Suffix

Add a suffix to the end of the source value.

This can be used instead of a Like * to *SUFF mapping, where SUFF is the desired Suffix.

Substring

Retrieve a substring from the source value, based on a starting character position and the length of the substring to extract, using format substr(Dimension, position, length)

For example, to extract Location ‘London’ from ‘001London_11’, we can use expression substr(Location, 4, 6)

Replace

Specify a target string replacement for a given source string in the source value, using format replace(Dimension, SourceString, TargetString)

For example, to replace any “L” with “0” in a Location, we can use expression replace(Location, “L”, “0”). So source string “LL7991” would map to target “007991”.

Rtrim

Trim trailing characters from the right side of the source string, using format rtrim(Dimension, TrimChar), where TrimChar is the character to trim from the right.

For example, trim trailing 0’s from Location “LKK0000” to give “LKK” using expression rtrim(Location, “0”).

Ltrim

Same as above but from the left side, using format ltrim(Dimension, TrimChar), where TrimChar is the character to trim from the left.

For example, trim leading 0’s from Location “0000LKK” to give “LKK” using expression ltrim(Location, “0”).

Rpad

Add characters to the right side of the source string, using format rpad(Dimension, length, ParChar), where PadChar is the character to pad with.

For example, add trailing 0’s to Location “LON” to give “LON0000” using expression rpad(Location, 7, “0”.

Lpad

Same as above but adding to the left side, using format lpad(Dimension, length, ParChar), where PadChar is the character to pad with.

For example, add leading 0’s to Location “LON” to give “0000LON” using expression lpad(Location, 7, “0”.

Round

Round data values as they are loaded.

This expression is only available for the Amount dimension.

Conditional

Ability to use an if statement to provide conditions which should return a specified value if true or a different value if false.

Split

Retrieve a section of the source string by splitting the string into sections indicated by a delimiter. Use format split(Dimension, “delimiter”, section), where section is a number indicating which section to retrieve once split.

For example, to retrieve Location “London” from string “000-300-London-1000”, use expression split(Location, “-“, 3)

SQL

Use a SQL statement to map members based on SQL conditions.

This can be used instead of the Like * to SQL method.

For more information, see Oracle help here:

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

For more useful Oracle Cloud EPM and NetSuite ERP blogs posted by my colleagues, see the Brovanture website HERE

Until next time

Lydia