FDMEE treading the middle ground

Well, I recently got a chance to work on FDMEE and I was hooked onto it. For those of you who don’t know, FDMEE stands for Financial Data Management Enterprise Edition which is basically a Hyperion specific ETL tool that allows us to integrate data and metadata from a variety of source systems in Hyperion applications (paraphrasing it out of the documentation here). Now I am more interested in the nuts and bolts that make up any system so let us take a plunge. 

Now, when you install Hyperion EPM and choose FDMEE to be installed, you will observe that Oracle Data Integrator or ODI is auto-selected by default as shown in the below screenshot.

Now, we come to the best part. For those of you who like the gritty stuff that makes up Hyperion, you will realize immediately the irony that exists in the world of Hyperion. Hyperion FDMEE is an extract-transform-load tool that internally uses an extract-load-transform tool, namely ODI, for its operations. 

You don’t have to but my word for it. Oracle documents it that way as well.

This is the high level architecture of FDMEE:-

Let me very honest. ETL and ELT are two warring factions and each swears by their product. While ETL can be extremely powerful, it does weigh heavy on the cost side, both monetary as well as resources. ELT is relatively cheap and leverages the existing architecture, but you will reach a break point where ELT would just not be the right solution anymore. So, how is it that FDMEE manages to tread the middle ground so well?

Well, the answer lies in the architecture and the proper use of tradeoff. It is a good example of losing a battle to win a war. Now, let us look at it objectively.

ELT tools make a fundamental assumption which is true in most of the cases. There will not be major manipulations on data fields or metadata fields and the ones that are there will be pretty basic. Like if X, then I want Y (lookup), if X, Y, Z then I want A (aggregator), if X then 1 else if Y then 2 else 3 (Hash maps). These can be manipulated at the target as well and does not actually have to be done before we load the data to the target. However in the case of FDMEE, my target is not a relational system and this can be either EPM or ERP. And manipulations in such a wide variety of target systems would be problematic simply because of the diverse nature of the target systems. So the simple solution to the problem is to reimagine the entire problem and use the divide-and-conquer algorithm to get an ideal solution.

The solution is as follows:

“Extract from any source, load it into the intermediate location and transform it in the desired target format (ELT strategy). The intermediate location is now my transformed data set and I can load it into any target system (ETL strategy)”

Now, let us head for the show and tell. 

This is a file that I want to load in the system using FDMEE.

 This is the hierarchy in my target application.

 

 

If you see the above two snapshots, PL1150 is there in the source extract, but not there in the outline of the application.

So, now I create a data load rule and define the mappings and stuff. This is the snapshot after the data is validated.

If you observe, the PL1150 is also mapped because for the intermediate target that is correct. This is where the ELT is in play in FDMEE. 

Now if I export it to the target application it will fail as shown in the following log file.

 This is the ETL module in play that pushes the data to the target and checks the integrity in the target.

This is the architectural model of the data flow for FDMEE.

So, to summarize it all up, FDMEE makes use of the pluggable architecture to manage its ETL process easy i.e. I can have data coming in from any source system and load it into an intermediate system to tackle the conformity problem (basically applying standardization to the wide variety of source formats to ensure they are same at some inherent level) and do the data manipulations on the intermediate system using ELT. Then I use the intermediate source system as my extract, transform and load it using ETL. 

Best of both philosophies and somewhere in a utopia, ELT and ETL share a drink together with John Lennon playing Imagine in the background….