By Dwight Davis on March 21st, 2017
When Oracle introduced FDMEE, SQL became an option as a scripting language for mapping. Jython and SQL replaced VB that was used in FDM. Initially, I gravitated towards using Jython but when I realized that you can do more with SQL, I started to explore it as an option. In this blog post, I will walk through a scenario where I used SQL in the mapping tables to do more than just mapping.
FDMEE and ERP Integrations
A scenario I frequently encounter is one where a client has integrated FDMEE with their ERP system, and as a result, they have lost ability to massage data using scripting in an import format. In this particular example, our client was upgrading from FDM to FDMEE and wanted to directly integrate FDMEE with Oracle EBS R12.
In their FDM environment, they were creating flat files from a data warehouse and loading them into FDM. They utilized an import format script on the amount column that would divide the amount by 1000. Since you cannot write a script in the import format when you have a direct integration with an ERP system, an alternative solution was needed.
That solution came in the form of a SQL script.
Specifically, we used a Lookup Dimension (such as UD5) in FDMEE so that we could create a SQL script in a dimension that would not interfere with the mapping for the other dimensions.
After creating the (UD5) lookup dimension, we added a SQL script in a Like mapping record, and the SQL is run on every record that is imported.
Note that a common question that we get after introducing a lookup dimension is: Will data get loaded to the target application using a lookup dimension? The answer is, no. A lookup dimension is strictly used for reference.
Below is an example of a simple SQL script; however this functionality can be used to accomplish more complex SQL activities such as updating a table, updating values in a table, or referencing a table.
FDMEE is a multifaceted integration tool. Extending it with SQL is just one of the ways you can use it. If you have questions about this topic or you would like me to discuss another topic regarding FDMEE, DRM, or Integration please comment below or drop me an email.