New Expression Types Available for Processing Data in Oracle Cloud
*Note: This blog post explores new Data Integration capabilities that have been made available over the past few months in Oracle Cloud EPM instances. Service Administrator privileges are required to utilize these options.
Have you ever used “Expressions” to process inbound data in Oracle Hyperion Financial Data Quality Management (FDM) or Financial Data Quality Management Enterprise Edition (FDMEE)? If you are experienced with Oracle Cloud, have you worked with this capability in Oracle Planning and Budgeting as a Cloud Service (PBCS), Oracle Enterprise Planning and Budgeting as a Cloud Service (EPBCS), or a related Oracle Cloud product?
If you have come across this topic previously, or if you are new to data integrations in Oracle Cloud, then this is the blog post for you!
With a recent update to Oracle Cloud EPM products such as PBCS and EPBCS, there are now more options than ever before when it comes to processing inbound data from a different source. In this blog post, we’ll look at these new options. You will see they might be able to save you time in your next data integration setup!
Expressions are used to assist in the processing of imported information. An imported data file needs to have separate, distinct fields to align with dimensionality in the target application – but that said, the values in these fields don’t necessarily need to match with the app dimensionality 100%.
For example, consider the example below wherein we have separate metadata values in our source data import file, each one of which aligns with a member that is already in the application:
However, let’s assume that there is a mis-match between how the source system processed metadata (i.e., dimensionality) vs our PBCS example application.
Let’s say in our source system, the “Account” parameter has four digits, for example in the screenshot above: “1223”. But in our new PBCS app, the “Account” dimension has 7 digits. In our example, we would want source “1223” to correspond with target “1223000”.
How do we handle this mis-match? There are multiple options. we could modify the source system to append the extra digits as necessary. We could setup an IT process to modify our file field values and append the digits after export. Or, we could handle the processing of these source values on the import side (i.e., in PBCS) with import expressions.
Capability in Data Management
For those who are familiar with “Data Load Mappings” in Data Management (“DM”), import expressions can be a more favorable option because oftentimes they can be faster to setup, and can facilitate simple mappings just as easily as “Data Load Mappings”. Furthermore, per Oracle, for “large data sets using import expressions for transformation will improve the data load performance.”
“Source expressions” have existed for some time in Data Management. For example, Expression Types available for a dimension like “Account” include these two:
*Fill allows for input of a key word to transform number formats from source à target, for example: “Fill=Euro To US -> 20.000,000 would turn into 20,000.00
*FillL allows for filling a field value with a leading character of your choosing. For example, a designated value of “111111” would imply “FillL=111111”, and so would allow for account source value “3421” to be converted to “113421”
There are several expressions that are specific for the “Amount” field (i.e., the data field), including:
*DRCRSplit allows for recognition of debits and credits as delineated in fixed format files
*Sign allows for recognition of non-conventional negative sign indicators (i.e., indicators that are not negative signs, parentheses, or “< >”
*Factor allows for multiplication of the source file values by a designated value (For the purpose of scaling. For example, if “Factor=10”, then a source value would be converted from “560” to “5600”)
*NZP allows for the loading of zeros. By default, the ability for Data Management to load zero balances is suppressed, for efficiency reasons. The expression parameter “NZP” removes that suppression
New Capability With Data Exchange
With the 2018Q4 PBCS updates discussed, additional expressions are now available outside of DM, in the “Data Exchange” section of the app. To access these options, go to “Navigator” à “Application” à “Data Exchange”:
On this screen, you will see options to access “Data Integration”, as well as “Data Maps”. We are interested in the “Data Integration” screen, for example:
Assume we are working with sample “Test_Integration”, which has DM Location “DrillTest”. We can click on “Map Dimensions” to visualize our high-level mappings:
After we select our “Import Format” (also from DM) and “Type” (if we are working with a data file), we can modify expressions for a dimension or the “Amount” field.
To do this, simply click on the gear symbol to the right of the particular item and select “Edit Source Expression” or “Edit Target Expression”. For example, for “Account”:
In either of these screens, you will notice new options for Expressions that were unavailable in the DM section we discussed earlier.
Specific New Expressions
For Source Expressions, three new ones are available with the updates mentioned:
*Constant allows for the ability to insert a constant for each record in the target column. For example, say for “Entity” we were only loading every data record to a single US entity, “US_100”. We could use the structure constant(“US_100”) to achieve this.
*Lpad allows for the left side of a source string to be padded with a set of designated characters. A space is used as a default character for padding if no character is designated. For example, say we wanted to pad account value “3221” on the left side with zeros up to 5 characters in length. The logic would look like: lpad(Dimension, 5, “0”), and the output would be 032210
*Rpad allows for the right side of a source string to be padded with a set of designated characters. A space is used as a default character for padding if no particular character is designated. For example, say we wanted to pad account value “3221” on the right side with zeros up to 6 characters in length. The logic would look like: lpad(Dimension, 6, “0”), and the output would be 322100
For Target Expressions, fourteen new ones are available with the updates discussed:
*Note that per Oracle – “Target expression types cannot be applied to an Amount dimension or the Attribute, Currency, and Skip rows.”
*Copy Source allows for the ability to “copy a source value to the target”
*Constant – please
see above in “Source Expressions”
*Default allows for insertion of a default value in the case where the source value is blank
*Prefix allows for the ability to add a prefix to a source value
*Replace allows for the ability to replace character(s) within a source string with a defined value
*Split allows for the ability to split the source value as based on a defined delimiter. The process then returns the requested component. Admins can use this to isolate components of GL strings, for example
*Suffix allows for the ability to add a suffix to the end of a source value
*Substring allows for the ability to return a defined set of characters from within a source string
*Lpad – please see above in “Source Expressions”
*Rpad – please
see above in “Source Expressions”
*Ltrim allows for the ability to trim leading characters from a source value (on the left), as defined by the admin
*Rtrim allows for the ability to trim trailing characters from a source value (on the right), as defined by the admin
*SQL allows for the ability to integrate a SQL expression into the mapping. Possible expressions are those that can be used in a SQL INSERT statement
*Conditional allows for the ability to leverage “if-then-else” logic to return a value (i.e., a target value) as dependent upon the source value being processed
Source and Target expressions are very valuable in Oracle Cloud EPM data integrations because they allow for the ability to apply mappings and transformations “across-the-board”, and can be setup very quickly. Historically, limited expressions were available in Cloud apps such as PBCS, and these were offered only through Data Management. Now, with 2018Q4 updates to PBCS, many more expressions are available along with enhanced data integration capabilities to setup your integration faster and more efficiently.
We hope you enjoyed this KPI blog post and that you return soon!