Although there are some limitations to Data Management with FCCS/PBCS/EPBCS/ARCS/PCMCS, it is still a great tool to get your data into the cloud, and allow your functional users to map data from the source-to-target.
Current Data Management Cloud Limitations:
- No direct integration to source on-premise GLs (does connect to Fusion Financials Cloud)
- FDMEE on-prem allows direct connections/adapters to source GLs and other data sources, including GL Fusion Cloud so it’s a better hybrid deployment choice at this time
- No Jython or Groovy scripting, including #SCRIPT command in Data Load Mappings
- FDMEE allows Jython and Groovy, giving you more options for integrations and mappings
- No in-product automation, must use EPM Automate batch scripts with Windows scheduler, or get fancy with REST API
- FDMEE has batch scheduling capabilities
Even though there isn’t Jython or Groovy scripting for Cloud Data Management at the time of this blog post, you are still able to create some mappings using SQL. This comes in handy if a column in your data file is Null, or you have other conditions to check. You can also do multi-dimensional lookups within the SQL to achieve the desired mapping.
This feature is available for these mapping types:
To start a #SQL mapping, go to the desired Data Management Location, and then click Data Load Mappings. Find the dimension you’d like to map, and go to the “Like” (or “In” or “Between”) tab for mappings. I’m using “Like” in my example.
Enter the source value mapping, and in the target value, type “#SQL”. This will unlock the scripting feature to allow for SQL mapping, you should see the little pencil icon turn from grey to yellow. Remember you need to add a Rule Name in order to save the mapping and continue.
Click the little yellow pencil icon in the mapping line and a prompt will open.
Write in your SQL mapping code. The fields correspond to the Target System registration that you created.
Note: there is not error checking or testing capabilities within this Scripting tool. You will have to perform your own testing via test data files and see how the data comes through in the Data Load Workbench to verify the results.
SQL Code Example:
CASE WHEN ACCOUNT is NULL THEN ‘No Account’ ELSE ‘ACCT_’||ACCOUNT END
This example will map data lines without an ACCOUNT specified to the “No Account” member, otherwise it will concatenate “ACCT_” to the Account number.
Note, that the “ACCOUNT” piece comes from my Setup Tab > Register > Target Application > Dimension Details. Thus, if you are using a SQL mapping on a custom dimension, you’d replace ACCOUNT with UD3 or UD2, for example. You can reference multiple dimensions in these mappings, as well, so you can do things like:
CASE WHEN UD2 is NULL AND UD3 is NULL THEN ‘No Product’ ELSE ‘PRD104’ END
Which would map anything that does not have a defined UD2 or UD3 value to ‘No Product’ otherwise to PRD104, or you could do ‘PRD’||UD2 if you wanted to concatenate ‘PRD’ to the product value.
As you can see, you can get very specific and almost replace other types of mappings (like Explicit or Multi-Dim mappings) by using the SQL mapping in Data Management. This may play in your favor if you have large amounts of data (SQL mapping is faster). However, remember that this is finance user tool, and although you may like code, some end users may not know how to read SQL as easily as mapping tables. Just food for thought.
Other PBCS mapping tips, which are also applicable to on-prem FDMEE:
- IGNORE — Target mapping will ignore data for all data meeting that criteria
- “?”—include a character from a specific position in the source member or segment within a member – wildcard within a member (can use multiple so 1???? can return anything from 10000 to 19999)
- “#”—Skip or drop a character from the source when creating the target member
- “character”—Include the user defined character on the target “as- is”. Used for prefixing, suffixing or any fixed string or required character. This can be used in conjunction with the special format mask characters.
- “*”—Include all characters from the source segment or source. When “*” is used as the only format mask character in a segment, then the entire segment value is copied from the source. This is the true wildcard character, similar to a search.