Getting the Most Out of FDMEE Using SQL

Dwight Davis

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.

Lookup 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.

For Example

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.

Final Thoughts
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.

Dwight Davis

About Dwight Davis

Dwight Davis is an expert Enterprise Performance Management Solutions Architect with over 20 years of experience focusing on all phases of data integration development/maintenance for financial data and metadata. Dwight has held multiple industry and consulting roles specifically focused on Financial Data Quality Manager (FDM & FDMEE) and Data Relationship Manager (DRM).

4 comments on “Getting the Most Out of FDMEE Using SQL”

  1. Sai Nikesh Thadakamalla says:

    Hello Davis,

    What does the TopDown keyword in the script editor mean? Does it mean all the source values get mapped to Topdown member and also the amounts for all those get rounded off by 2 digits

    1. Sai Nikesh Thadakamalla says:

      Or may be its just the Lookup dimension name?

    2. Dwight Davis says:

      Thanks for the questions Sai. In the example, I am using the Custom 5 dimension, and I need tell FDMEE what to do with Custom 5 before the SQL is executed. That is the reason for having ‘TopDown’ before the actual SQL. With the SQL, I am saying that I want all the source amounts to be divided by 1000, and returned with 2 decimal places.

  2. Sai Nikesh Thadakamalla says:

    Appreciate your reply Davis!

    Basically TopDown keyword is just used as a Dummy place holder before the SQL is executed. It has nothing to do with the script right?

Leave a Reply

Your email address will not be published. Required fields are marked *