Using Target Expressions in EPM Data Exchange

Target Expressions can be used when importing data, to apply conditions to source values before loading to the target 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.

 

 

 

 

 

 

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.

 

Comments

  1. All the Benefits of Hiring Payroll Processing Companies in Mumbai

    The pandemic showed us that even with the world under lockdown there were ways to function and have businesses running. People discovered the advantages that could be gained by the ‘work from home’ systems that had to be adopted after the lockdown. One of the elements that also drove the work is the trend of outsourcing jobs to experts that do them best. Among the many jobs that were outsourced, one of the most common was to hire Payroll outsourcing services in Mumbai. These are services provided by experts that help in ensuring that the payroll calculation and disbursal for your employees is done with complete precision and timeliness.

    ReplyDelete
  2. Your blog article is outstanding; it is informative; in fact, I write on a variety of issues, including app development technology. Please read and give your thoughts on content related to mobile app development company for businesses.

    ReplyDelete
  3. Interesting post! I got to learn a lot about supply chain and Data Management Solution platforms.

    ReplyDelete

Post a Comment

Popular posts from this blog

Executing Smart View Retrievals using VBA

Loading Actuals from Fusion ERP Cloud to PBCS

Loading multi-period row data files using Data Management