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