Under the EPM cloud umbrella, there are 5 products come with data management, including

• Planning and Budgeting Cloud
• Enterprise Planning Cloud
• Financial Consolidation and Close Cloud
• Profitable and Cost Management Cloud
• Account Reconciliation Cloud

Data management allows users to map data and metadata from source to target. The cloud data management sometimes is called the lite version of FDMEE. Because there are 2 major limitations:

• Data management cloud does not support Jython and Groovy scripting. FDMEE does. We have confirmed with Oracle that there are no plans to implement support for Data Management scripting due to the security challenges posed by allowing foreign scripts into a shared cloud environment.

• Data management cloud does not have direct integration to source on-premise GLs. FDMEE supports adapter to source GLs.

Well, even though data management does not support Jython and Groovy, it does support SQL scripting. Being said, we are able to create a mapping using SQL in data management. The SQL scripting is available for the following 3 data load mapping types:

• Between
• In
• Like

 

We can create mapping scripts to designate conditional mapping. This type of mapping enables us to specify source members mapped to script expressions rather than to hard-coded target members.

 

To activate the SQL mapping script, go to Between, In or Like data load mapping. In the Target value field enter #SQL. Then the Script pencil icon will be activated.

 

Once the Script has been activated, the pencil icon will turn to yellow.

 

Click the Script button. A prompt window will open and from there we are able to use SQL script to manage mappings.

 

Unfortunately, this scripting function does not have error checking. We have to use either a third part SQL tool to check the syntax or test a data file when loading data through data management.

 

Example 1

If the account column in the data file has NULL, then we can simply map them to No_Account, otherwise, add prefix “A_” to them and map to related accounts.

 

Example 2

The SQL scripting can replace other mappings, including Explicit, Between, In and Multi Dimension in theory. The following is another example.

 

Data management is a data and metadata integration tool. Extending it with SQL is one of the efficient ways to use it. Hope this post gives you some idea on the SQL scripting in data management cloud. See you next time.