Thursday, September 3, 2015

FDM to FDMEE Migration Utility ( - Part 1

Hi all!

I'm just back from vacation and noticed the log-awaited migration utility has been already released :-)
I'm sure some of you have already gone through it and have your own feedback. Before I analyze and share mine with you I guess there are many questions on the table...

  Is this the most suitable approach for my migration project?
  Where can I download it?
  Does the migration utility all I need?
  Is it easy to use?
  Do I need any additional manual step?
  Is it a next->next utility?
  Is this going to save time in my migration project?
  Which is the ROI of this utility?

The migration utility has been released as patch 21379349 in MOS:
Once you download it, you will see some sql, xml, and guide files:
Ok, so no setup/installation files, that means it is not going to be next->next utility, it looks as something more technical. There are couple of XML files beginning with SCEN which remind me to ODI files. So, as Oracle revealed in some conferences, the migration utility has been implemented as an Oracle Data Integrator (ODI) process. that makes sense if you take into consideration that ODI is the ELT engine of FDMEE.

How will the migration utility executed? the migration process will be executed and monitored with ODI Studio :-)

What can I migrate? setup data (FDM metadata artifacts) and historical data.
All artifacts? NO. We will discuss which ones are included and which are not.

I guess you are thinking now...I don't have any ODI knowledge... then maybe you are not the right person to run the migration :-)

Let's go through the main points about the new migration utility.

Why migration is needed?
As you might know FDM and FDMEE have differences both in technical and functional sides. As migration involves mainly technical artifacts, it is not possible to provide an automated process to upgrade from FDM to FDMEE. That's clear. In deed, as I have already stated in other posts, YOU SHOULD NOT THINK about upgrading but migrating your FDM solution. Why? 90% of the times your application will be a good candidate to be analyzed and reviewed in order to evaluate if a re-design is needed.
What means re-design? To me, it means providing the customer the chance of having a FDMEE application with the best performance, maintenance and scalability balance. How we achieve this? Probably by using new features available (data load rules, multi-dim mappings, SQL mapping scripts, etc.), and of course by being able of detecting "not suitable" decisions made during legacy FDM implementation.

Supported FDM versions for migration
Migrations to FDMEE are not supported.
The following tables summarized supported versions for both FDM standalone and ERPI-FDM combined implementations:

To be taking into consideration

  • In FDM, each application had its own database/schema and shared folder. In FDMEE, all the integration are stored in one database/schema. This unique database stores both FDMEE tables (artifacts, data, maps, etc.) and ODI repository tables (names starting with SNP)
  • Oracle suggests to omit very old data to avoid migrating big volumes of data. Bear in mind that is not just a matter of having 5 years data but large number of POV combinations (location, period, and category)
  • In terms of import format types, main difference is that FDM supports script and adapter types besides fixed and delimited files. The first two has been replaced in FDMEE with new functionality. They will partially migrated with this utility. With "partially" I mean that the utility will create main objects needed. For example, the import formats will be created. You just need to adjust them later accordingly. Scripting is on your to-do list :-)
    • Script import format: in FDM you can import data using an integration script which mainly extracts data from a relational table/view. In FDMEE, this has been replaced with the Open Interface Adapter ( The new Universal Adapter for SQL tables and views will be released with This new functionality will avoid the additional step of populating the open interface table.
    • Adapter import format: they were not very popular due to not being flexible but FDM had some source adapters for EBS and SAP. However, one of the highlighted features of FDMEE is the native integration with ERP Systems. For example, GL balances can be directly imported from E-Business Suite or Fusion GL in a very transparent way. Others like SAP or JDE will do the same by using new functionality called Source Adapter which is basically an integration framework providing customization of the extract process.
  • If you use ERP Integrator (ERPI) in combination with FDM you should know that migrating to FDMEE may incur in duplicated artifacts. As you know, ERPI was the little brother of FDMEE and had overlapping functionality with legacy FDM. For example, you could configure periods in both ERPI and FDM.
  • If you have a new database platform in your new EPM system, the utility may not work. 

How migration works
As stated above, the migration utility is basically an ODI process so the FDM artifacts and data are migrated by executing two ODI scenarios from ODI Studio.

Migrating FDM artifacts
The utility migrates the following legacy FDM objects:
Any other objects are not migrated. Scripts...
In following posts we will go through manual post-migration actions needed.

Duplicate names
FDMEE does not allow duplicate names for key artifacts. In the case you have multiple FDM applications, the migration utility lets you add a prefix to FDM artifacts. In that way, you will avoid duplication issues during migration.
For example, if you have two FDM applications having location US_West, you can prefix location from each application so in FDMEE you would have Prefix1_US_West and Prefix2_US_West.

File delimiters
FDMEE just supports comma (,), pipe (|), exclamation (!), colon (:), semi-colon (;) and tab.
Any other delimiter used in legacy FDM will be converted to comma (,).
Watch out! this change will imply modifications in source files as well.

Migrating FDM data
You can migrate your historical data from FDM to FDMEE. This is something to be evaluated during you really need historical data in FDMEE? which POV combinations?

Is there are any other alternative?
Using "Excel Interface" will speed-up your migration process if you decide not to use the utility. With this new functionality you can export artifacts from legacy FDM, slightly adjust the excel generated, and import it into FDMEE.

Here you have the admin guide in case you want to have a look.

In following posts we will go through the installation and utilization of the utility, including manual steps needed :-\


Saturday, June 20, 2015

Ignoring data in #SQL Scripts

Greetings from Miami :-)

Sometimes we want some source data to be ignored during the export step but being kept in FDMEE.
There can be many reasons that take us to that decision:

  • We may want not to stop the process when unmapped members exist (automap)
  • Invalid data coming from your ERP that you want to import but not to export
  • We just want to use it as audit information
  • ...
How can we achieve this? there is a special value you can set in your mappings as target: IGNORE.

Today I want to provide you with a bit of insight about how ignoring data works, especially when you want to create a SQL mapping script to set IGNORE as target value.

As usually, let's see an example.

I will be direct, I want to ignore GL data for Building account. In order to make it simple, I can create an explicit mapping as follows:
Having the mapping rule as explicit ensures it will be executed at first place before any other rule.
What happens when data is imported? We will see that the target account has been mapped to IGNORE:
Although we can see the data in FDMEE, that line will not be included in the DAT file for my target application.
We all know that FDMEE is really cool for filtering and searching. Actually there is a new feature to show in the grid ignored data only:
Even if you can use Query by Example to filter values in the grid (just type IGNORE in any target column) with this feature you will see all ignored data regardless the dimension you had the mapping for.

That's easy, right? there is not so much to add, or is there?

Is "ignore" the same as "IGNORE"?
Like Saint Thomas said, if I don't see it, I won't believe it.
Target value for ignore mapping is not case sensitive, at least when using SQL Server with no case sensitive collation :-)
In any case, as best practice, I recommend using upper case always.

What's happening in TDATASEG table?
Besides having the corresponding target column as "IGNORE" (ACCOUNTX, ICPX, etc.), there is a flag in TDATASEG which indicates the status of the row. That column is named VALID_FLAG and can have three different values:
Actually this is the column the Show option uses to filter data in the grid.

Can I set target value to IGNORE in a SQL mapping script?
Sure, let's see an example where data must be ignored if amount is less than €5000:
We run our Data Load Rule after we create our mapping but we get an intersection check report (good I enabled it), why? how can this be working properly with the explicit mapping but not with the SQL one?
The report above is basically saying "account IGNORE is an invalid member in HFM" (you would get an export error when loading into HP/Essbase)
Time for troubleshooting, one of my favourist task. First thing I would do is to select Show > Ignored data:
Mmm, no data is shown... was it actually ignored? It seems it wasn't.
What is FDMEE doing when applying the mapping? I always suggest to set log level to 5 and see what's happening there. Best way to learn.
As you can see above, mapping was correctly applied but we don't see anything about the VALID_FLAG column. Actually I would expect this to be set as "I". Let's check in the database:
Hold on, so target account is set to "IGNORE" but valid flag column is set to "Y". How can this happen? We do expect value "I".
I already raised this a bug and luckily this will be fixed in (coming soon).
However, if you want to have SQL mappings to ignore data then you can create your SQL script as follows:
After re-applying the mapping we can see how valid flat column is now set to "I". Good! we have what we wanted!
And that's making it work as expected:
One of the main features of #SQL Scripts is that you can update more than one TDATASEG_T column in the same script (that's the table where mappings happen. So basically SOURCE > TDATASEG_T > Mappings happen > Data moved to TDATASEG). Therefore we have used that capability for our workaround :-)

Quick update: if you want to avoid updating VALID_FLAG in the mapping script, you can have an event script AftProcMap in order to update that column in TDATASEG when any of the target dimensions are "IGNORE".


Wednesday, May 13, 2015

Enclosing double quotes or apostrophes in your data? Don't worry, be happy!

Hi folks,

Is your first contact with FDMEE? or are you coming from
Why I'm asking that? because what we are going to discuss about today is something new in and

One year ago I posted about issues when your source data file had either enclosing double quotes or apostrophe.
First scenario typically happens when text/csv files are generated from Excel or other systems which configured to use "" as text delimiter.
Apostrophes may be used within account descriptions or any other description field extracted from your source system.

We can summarize my post and tell you that:
  • Double quotes were not removed when importing data
  • Apostrophes often made your import fail as single quotes (or apostrophes) are also used as string delimited in Jython
Oracle even published some documents for these issues:
  • FDMEE Script Samples (Doc ID 1613836.1)
  • FDMEE: Unable To Import File With Apostrophe In Account Description (Doc ID 1629889.1)
And now what? well from these two issues are handled by FDMEE (PSU530 and PS4 have mostly same code) so no need to worry about any scripting to parse source fields anymore.
Let's see how it works in

Importing a file with double quotes as text delimiter
Let's say we have the following file generated from an Excel file:
We have created a simple import format and data load rule having assigned the import format:
In order to make this example easier we have pre-selected the source file in DLR. In that way we don't have to select it when importing data:
As you can see we have assigned the import format to the DLR. This is not needed if you have only one import format for your location (file-base loads).
Time to import data. We can either execute the DLR from Data Load Workbench or from DLR page (even from a batch...). Results will be the same:
Data has been imported with no double quotes as text delimiter...and no need of import scripts...good!

Importing a file with apostrophe in description
What about this one?
We have defined a DLR using the same import format so no import scripts being used:
The source file has been also pre-selected. Let's import it!
VoilĂ ! data being imported without issues...and descriptions as well. 

BTW, If you were wondering, any of the source files have been modified, so FDMEE keeps your original file as it is :-)

This enhancement is a really good one we don't need additional import scripts to parse our data.

Finally, if you are upgrading your FDMEE from to PSU530 or PS4, don't forget to remove your scripts for that purpose!

Take care!

Sunday, April 12, 2015


Hi all,

I will be attending and presenting @Collaborate2015.

In case you wish to meet there just let me know!

Update 13th April 
For DRM customers, we knew FDMEE is was going to be integrated with DRM so we can maintain our mappings in DRM and import them into FDMEE (you may have been doing this with custom solutions)
It seems that Oracle is also delivering the integration the other way back...having DRM as target in FDMEE so we will be able to load metadata from our ERP systems into DRM using FDMEE.
To wrap up:
- DRM as a source for mappings import
- DRM as a target for metadata loading 

Also, as FCM is getting more powerful, FDMEE-FCM integration will provide more capabilities to users during the month close.

For SAP integration, we will able to load metadata from SAP BW into our EPM apps. Why no SAP ERP? Basically metadata is too spread for SAP ERP so metadata can come from many different places whereas SAP BW metadata is easier to access. First step for a better future integration.

Internet Explorer 11 is now supported in What about we will have to wait until summer :-)

All this is planned to be released in the next PSUs.

And what about next release? 12.x.x? although not officially confirmed, I would bet all my money on the second option. In that way, we would have EPM aligned with the Oracle application stack. When? 2016


  • Non-numeric data loading into Planning
  • Metadata loading from heterogeneous systems (files...)
  • Mapping review mode
  • Mobile functionality for drill-thru page

Please, not that this information is not officially published yet so do not use for making important decisions :-)

Monday, April 6, 2015

Mayday, Mayday, I cannot see the source columns in drill-through details page!

Often seemingly insignificant things make us waste time more than expected.
From my experience, issue below is one the most common for FDM users getting into FDMEE.
Customers sometimes complain about not being able to see source accounts when they drill-through from EPM applications into FDMEE.
Even if we try to select columns from View option, we don't see them:
Are they gone? maybe under the sofa? don't panic :-)
The reason is that FDMEE takes the column names from your import format so if they are not correctly setup in it you will not be able to select them from the View menu in the drill-through details page:
As a good practice, try to set column names for your import even if they have the same name as the target dimension. You can also use some meaningful name or alias.
Then when you drill-through you will see source columns as expected. Column name will show the name you set in the import format plus the dimension assigned in the import format. Now you have time to fix your next issue and forget about this one.
All this applies also to Excel Trial-Balance templates for both single and multiple periods. Documentation says:
"When you an Excel Trail Balance template, the template can contain one or more periods. When
doing a multiple period load, you need to create a dummy import format that indicates multiple
periods. If just a single data value is in the Excel file, then you don't need an import format"
But what is a dummy import format? empty one? If you leave it empty, then you will experience issue above.
In that situation, create a dummy import format (it won't be used anyway), set all column names to what you want to see in the drill-through details, and assign 1 to field number.


Where was I? What have been doing?

Hey folks!

I have been very busy with my little baby, not sleeping so much, and becoming an expert on diaper changing :-)
Being said that, I hope you all understand why I was not blogging so often.

So now trying to normalize my life, one of the TO-DO bullets is to keep on sharing FDMEE stuff with all the EPM community.

As I always say, the show must go on!!!

Monday, February 9, 2015

FDMEE show must go on!

Hey folks,
just had a quick review of new FDMEE and which features we will be able to enjoy. As always there were some more changes than the one described in the new features document.

Workflow and Setup menus
First think I noticed slightly different was the Workflow menu:
As you can see in the image above, there are three changes:
  • Logic Group, Check Rule Group, and Check Entity Group have been moved to Setup tab > Data Load Setup. This actually makes more sense and it's more aligned with FDM Classic
  • Write Back is gone? has been embedded into Import Format definitions. As we now have to set source and target types in the import format, when we select Source = EPM and Target = ERP, that implies a write back.
  • Excel Interface has been added to Integration Setup menu. This new feature allows us downloading/uploading (exporting/importing) FDMEE tables into Excel.This is something that everybody was expecting. If you come FDM Classic world, I'm sure you missed exporting any item with "Export to Excel" button and then being able to import new ones with "Import XLS" functionality (Tools menu). Now this feature has been improved as it allows you exporting/importing any table...any? we will see.

New source and target systems
In addition to PeopleSoft Commitment Control (which was integrated with PSPB - Public Sector Planning And Budgeting), FDMEE now supports Fusion Budgetary Control. More support for Fusion in FDMEE:
HPCM is now supported as a target application... and can be used in EPM data synchronization rules:
Application Settings: Display Data Export Option "Override All Data"
I was expecting this feature to be only available for Planning/HPCM/Essbase applications but it is also displayed for HFM applications (bug?). It is good to have this option as it will avoid accidental data clearing when loading data into HP/Essbase:
Target Application Options: Global User for HFM
This option was already available in for HP and Essbase. It seems that is also available now for HFM although not documented in the admin guide.

GUI to build our Import Format
Another feature to complete parity with FDM Classic. We can now build our import format using a GUI that helps us to easy identify which fields and where they are placed in our source file:
We just have to select a field, then assign a source and target dimension to it. The field number will be automatically filled by FDMEE.
The new column definition will be added to the import format mappings:
Lock POV
In, we could only lock the POV at target application level. That was affecting all locations:
In, we have two lock options (as FDM Classic)
  • Lock the POV: specific location, period, and category. 
Is Data Load Rule included in the POV locked? or in other words, is POV locked at DLR level so I can lock specific DLRs for specific location, period, and category?
Not, when we lock the POV for location, period, and category, we actually lock it for all DLRs (another nice enhancement request)
And we can now see the lock icon in the POV bar :-)

Also we can now see which POVs are locked. When we select specific POV, it will show Unlock POV option if it is already locked:

  • Lock the POV for all locations: we actually had this feature in, just changed button caption
We still cannot see which POVs are locked to all locations from the Unlock All Locations button so you need to search for them.
Now we have parity with FDM Classic although it could be enhanced :-)

As an administrator I would like to have a menu option called POV Lock Manager where I could select combinations of location, period, category, and data load rule to be locked. We could also select "all locations", "all periods"... or even more advanced, "all locations starting with SAP_"... if somebody from Oracle read this...maybe in :-)

HFM Adapter actions now in Jython
This was more than expected, new rewritten HFM, bye to COM? welcome Java...HFM adapter actions in VBS replaced by Jython ones:
If you are learning Jython, this is good place where review coding:

Import Format for file-based loads
When we create an import format we select both Source and Target Types.

  • Source: ERP or EPM
  • Target: ERP or EPM
Different combinations of these values define the data load rule type:
  • Source = ERP and Target = EPM > Data Load Rule
  • Target = EPM and Source = ERP > Write-back Rule
  • Source = EPM and Target = EPM > Data Synchronization Rule
So if we want to load data from a flat file we have to select ERP and EPM as source and target types respectively. Then we can select File from Source drop-down list (notice that File is the default source system for file-based loads and it's already created with FDMEE installation)

File type: delimited, fixed, and multi-period (same as
File delimiter: same as
Data Load Rule for file-based loads
When creating a DLR for source files can now select the file type including different ones for Multi-Period files:
Data Load Rules: source, target, and custom options
Data Load Rule definition has been restructured so we have 3 types of DLR options:

  • Source Options: these are the old source filters in
  • Target Options: new feature! we can now set target application options at data load rule level. When working with FDM Classic, there were situations where we needed a copy of the target adapter to have different options for different locations. Now we can easily set these options in FDMEE: 

Be aware that if you change an option value in the target application options, the new value will be applied to all Data Load Rules. Then you can override the value for each data load rule. I don't like it.
  • Custom Options: the four integration options we had. It would be great if Oracle enhances these options and allow defining more than 4 and even define option type (check box, drop-down...)
Using a Rule file to load data (Planning and Essbase)
Does it mean we can use our own rule files when loading data to HP and Essbase? In, FDMEE created rule files on the fly when loading data.We could not do anything about this. This feature is not documented so we will have to test

More about import formats
We can select the concatenation character for source segments/chart-fields when our source system is an ERP (very useful for ARM integration):

Fixing mappings Errors on the fly
Validation Errors during mapping process? were you missing this feature in we now have it! new mappings can be added from Validate step when errors are captured:

AIF Logs
I don't remember having this log in previous release...aif-WebApp.log
Maybe my infra colleagues can correct me but it seems that Oracle split the server logs and created a new one for web applications (I saw it also for Planning)

Target Application: refresh members
New option to refresh members have been added to the existing one for refreshing dimensions. So if you have new members in your dimension and want to see them in the list of target members in mappings, you can use this feature:
Adapter for Fusion Cloud
Cloud, cloud, this is something you will be familiar, it seems that Oracle is already testing integration with Fusion Cloud

ODI Studio
I suggest you download ODI from OTN and not the one included in the EPM System download. As last release, there are missing files:
Also, don't worry if you try to install ODI 11g in Windows 2012 Server which is not certified? just click Continue and it will be installed:
Data Synchronization
Say goodbye to customization made to move data between EPM applications. We can now do it out of the box. As stated above, once we have registered our EPM applications in FDMEE, we can create an import format where source and target are EPM:
Then we link source and target dimensions, add new dimension rows, expressions, etc.
As shown above, we can use expressions Fill, FillL, and import scripts :-)

I dont' have a server just a humble VM... so I had to follow what my colleague Henri suggested and decreasMinDataCacheSizeInMB to survive :-)

I stop here for today, Excel Interface, Data Synchronization, changes in JAVA API...the show must go on!