Using Target Expressions in Oracle Cloud EPM Data Exchange
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:
For more useful Oracle Cloud EPM and NetSuite ERP blogs posted by my colleagues, see the Brovanture website HERE
Until next time
Lydia
+44 (0)1483 685450
contact us