Sunday, December 20, 2015

FDMEE released (PSU100) - Part 1

We finally got out Xmas present!!!

Planning, HFM, Essbase...all were patched, now it's time for FDMEE.

Folks, FDMEE has been released. As usually this new PSU fixes several bugs and provides some interesting functionality. I'm trying to enjoy my Xmas holiday but I had to find some time to open my present before Santa brought it :-)

After playing with new PSU, I have to say this is a must so I encourage you to install it.

Patch overview: 20648390
At first glance this new PSU shows 6 new functionality (although I'm sure we will discover few more) and fixes about 40 bugs.
New functionality

  • Universal Adapter: provides direct integration to SQL tables or views with no need of populating the Open Interface Adapter table. We can now see supported data sources in the source system and source adapter details. As Open Interface Adapter, this new feature has been implemented using ODI (note that ODI model folder and projects are not automatically imported into the FDMEE repository so you have to import them with ODI Studio)
  • Testing Check Rule Expression: this feature was already available in legacy FDM.
  • Loading Exchange Rates to HFM: FDMEE can be used to load exchange rates into HFM. However when integrating with EBS or PSFT, rates were stored in the staging table for EX rates but not exported to HFM. Now you can have the end-to-end integration for EX rates.
  • Extracting HFM Journals: new parameters and filters for type of journal-
  • Purge Scripts: This is a really good and expected one. Purging run-time artifacts (process and staging tables, inbox/outbox...). Nice!
As you can see, the purge scripts are available as Custom Scripts (stored in EPM_ORACLE_HOME\products\FinancialDataQuality\bin\system)
  • Map Monitor Reportswe now have two new map monitor reports for Location and User. In order to avoid storing big amount of audit information, this feature is optional and can be enabled in System/Application Settings.
As always I will review and analyze each of the functionality and share my feedback with you. Before testing any there are couple of them which sounds quite interesting to me. Others just complete the parity with legacy FDM.

Bugs fixed

  • Unable to sort by location when selecting files from Inbox folder... quite important when managing big volume of locations :-)
  • Support case insensitive in the intersection check report for HFM... I still miss the check report in HTML format as FDM so I'm happy if Oracle fixes bugs but still waiting!
  • Status of process details updated successfully
  • Improve performance of HFM intersection check process... I hope this really implies bug performance as the new architecture using the HFM API had a really bad performance. If this has been fixed then it's time to enable this option back for my customers :-)
  • Accumulate within file...I have seen in several customers duplicate intersections in the export DAT file due to logic accounts having column JOURNALID as blank rather than NULL. 
  • SQL Server deadlocks...this has been a nightmare for customers using MSSQL.
  • Improve HFM load process when "Data Protection" is enabled... What does improve mean? I'm quite excited.
  • Add option to turn Account Descriptions on and off...Not sure when this applies but will find out soon
  • Overriding load method in DLR target options...  When configuring load method at DLR level, new method was not overriding the general method set at target application level.
  • Non-admin user should be allowed to see only their processes... Good to see that we can only see (a good pun) what we are processing
  • ...
Honestly some of the bugs listed can be also seen as new functionality added. I'm happy to see the product is getting better and better. I'm seeing users getting more satisfied and hopefully this is just the beginning.

Installing the patch shouldn't be an issue and follows the common Opatch procedure.
In this patch we are back to pre-requisites:
Oracle JDeveloper (ADF) Patch 21240419. Luckily you don't have to apply this one if you already installed HFM or higher.
Installing the ADF pre-requisite
I don't have JDeveloper in my environment so I could easily follow the instructions in the readme file:
Installing FDMEE
Easier than previous PSUs in
1) Stop the FDMEE Service
2) Run Opatch from EPM_ORACLE_HOME\Opatch
3) Start FDMEE Service
4) Restart the HTTP Server
Remember that some changes will happen during FDMEE server startup after applying the patch. Don't be panic about errors due to objects already existing.

New FDMEE is now installed. It's time to enjoy and play with it.
Main changes in the Database
One of the first things I like to review is how the database is changed. As I always say, understanding the database provides you a better insight about the product itself. Main changes applied are stored in a SQL script placed at
EPM_ORACLE_HOME\OPatch\20648390\files\products\FinancialDataQuality\database\Common\Oracle (or MSSQLServer folder)
Let's have a quick look and see what we can get:
  • New columns for table AIF_ARTIFACT_AUDIT...I bet this is related to new Map Monitor Report...
  • New DRM columns for tables AIF_HS_DIM_MEMBERAIF_HS_DRM_LOADS, and AIF_HS_DRM_LOAD_HIERARCHIES. We already had DB tables for DRM integration but functionality was not available. Is it new functionality in this PSU? Oracle did not list it but it's definitely there 
  • Size for SRCKEY column (source value) is increased to 80 chars in all mapping tables (TDATAMAP, TDATAMAP_T, TDATAMAP_STG)
  • Column CURKEY allows now up to 25 chars. This issue was already addressed in old post.
  • In the same way, DATAVIEW allows 20 chars and JOURNALID 80 chars for TDATASEG and TDATASEG_T tables.
  • Indexes modified in both Oracle (Map table) and SQL Server (to solve issues with CHECK process I guess)

  • New Views to audit artifacts and DRM integration
  • New Query definitions for Map Monitor Reports. You can find them in aif_reports.sql file (EPM_ORACLE_HOME\OPatch\20648390\files\products\FinancialDataQuality\database\Common)

As you can see we will be able to get audit details filtered by actions performed...interesting.
This is all for today... Did you already install it? tested it?

Merry Xmas to everyone and enjoy!


  1. Great, just curious to see the code behind purge scripts so I could implement it for! Keep blogging!

    1. Code is pretty structured and comment.

      I encourage you have a look.

      You can download the patch from


    2. Hi Francisco, I'm no FDMEE table expert yet but staging tables are not purged as far as I can see in the scripts (TDATAMAP_STG, TDATAMAP_T, TDATASEG_T - btw are these temporary/staging tables that can be securely purged?). :

      "Delete from AIF_TEMP where process_id <= %s" %(maxID)
      "Delete from AIF_BATCH_LOAD_AUDIT where batch_id <= %s" %(maxBatchID)
      "Delete from AIF_BATCH_LOAD_AUDIT where batch_id <= 0"
      "Delete from AIF_BATCH_JOBS where batch_id <= %s" %(maxBatchID)
      "Delete from AIF_BATCH_JOBS where batch_id <= 0"
      "Delete from AIF_BAL_RULE_LOAD_PARAMS where loadid <= %s" %(maxID)
      "Delete from AIF_BAL_RULE_LOADS where loadid <= %s" %(maxID)
      "Delete from AIF_PROCESS_STEPS where process_id <= %s" %(maxID)
      "Delete from AIF_PROCESS_PERIODS where process_id <= %s" %(maxID)
      "Delete from AIF_PROCESS_PARAMETERS where process_id <= %s" %(maxID)
      "Delete from AIF_PROCESS_LOGS where process_id <= %s" %(maxID)
      "Delete from AIF_PROCESSES where process_id <= %s" %(maxID) :


  2. Thanks for sharing Francisco as of course the only way I found out the FDMEE PSU .100 for FDMEE was released was from the blogs and not from Oracle Support. We will be testing this patch next week due to the long awaited missing Map Monitor reports.

    I too, received an Xmas gift from Oracle Support as they finally delivered a BI Publisher template for the FDMEE report TB By Current Location By Target Account that would give us the same results we had in FDM Classic. It is still missing formatting FDM Classic had but at least the Source GL Account and GL Center to Target Account are the same sorting that is incorrect in the FDMEE out of the box report and we were so familiar with in FDM Classic.

    Sheryl Festa

  3. Thanks for sharing . The bug was indeed squashed according to the defect log!

    "Data load mappings fail when two * wildcards are used."

  4. Hi,

    Can exchange rates be loaded directly into Exchange rates table in Planning schema via FDMEE



Thanks for feedback!