If you’ve ever spent any time with me, you likely know that I can quote a lot of movies. It’s a weird talent that I have. So as I was thinking about this post I did a quick scan of my mental rolodex of quotes for something to fit the theme of this post. Read on and I’ll share.
Throughout my years of consulting, I have often encountered the misconception that FDM is only meant to load data to Enterprise or HFM. While it is true that the product was originally developed to load data to Enterprise, it has come a long way in the more than 10 years of its life cycle. Today’s FDM can also load HFM, Essbase & Strategic Finance (HSF) as well as create data files for Tax Stream. In fact, with a little creativity and strong understanding of the application, FDM can generate a data file for myriad databases and/or applications. In today’s post, I want to spend a little time discussing the benefits and use cases of FDM to load data to Essbase.
First, let’s take a moment to level set on terminology. You may have noticed that I have not explicitly specified Hyperion Planning as a data target to be loaded by FDM. The underlying data engine of Hyperion Planning is Essbase. While there is a relational component of Planning, the vast majority of Hyperion Planning data is stored in Essbase. So for the sake of this conversation, assume that any reference to Essbase also includes
The most common question/statement I hear when proposing FDM to control the data process with Essbase is:
Why do I need FDM? I’ve been loading data to Essbase for years without it.
Yes, very true. Essbase can natively load data, you don’t even need a load rule. It can very efficiently process hundreds of thousands to tens of millions of records. You can use SQL load rules to pull data directly from a relational source. And finally you can use MAXL to execute various calc scripts before and after the data load.
Well if all of that is true, then why do you need FDM? There are 4 primary reasons why FDM is valuable for loading data to Essbase:
- FDM provides drill through to the source data. With the introduction of ERPi, drill through is potentially possible all the way to the source system.
- FDM is end-user driven with full support for lights out automation.
- FDM can perform complex transformation that simply aren’t possible with load rules.
- FDM includes extensive logging that can be used to support data & process auditing.
Let’s explore each of these in more detail. In my humble opinion, the single biggest value proposition of FDM with Essbase is the ability of an end-user to drill through to FDM (or the source system in the case of ERPi) to view the source data as well as the mapping/transformation that was applied to the data. This drill can be initiated from a Financial Report, Planning Web Form or a Smart View grid. This provides much more clarity into the data transformation & load processes thereby empowering the user community. This empowerment leads us right to the next benefit of FDM.
With native Essbase, data loads are often managed by an administrator or sometimes extended to a small subset of people who have access to the EAS console or a batch file on a network directory. FDM by contrast allows any user with an internet browser (and the appropriate application security) to execute the data integration process.
In addition to simply loading data to Essbase, FDM also has the ability to execute a clear calculation prior to the data load and an aggregation following the data load. While the native data load methodology obviously supports this, it is often controlled by a MAXL script that needs to update various substitution variables and execute various calc scripts. In FDM, the entire integration process including the calculations can be controlled simply by the data that is being processed. The system – to quote Terminator – is “self-aware.” This process is completely transparent to the end-user executing the process.
The mapping capabilities of FDM could be viewed as part of the end-user value proposition First and foremost, FDM allows an end-user to input and maintain the mapping/transformations that needs to be applied to the source data. These maps are maintained within the web interface. But another key benefit (and a reason I list the maps as a separate value add) is that FDM allows for complex transformations including cross dimensional mapping to be applied to source data. Cross dimensional mapping is when the target for one dimension is determined by the source or target of another dimension. An example is based on the cost center of the record determines the account to which data is mapped. This is very common in the manufacturing vertical where salary expense is recognized in gross margin when incurred by a direct labor cost center. Traditional data load methodologies are more limited in their ability to account for these situations. More importantly the logic is often buried within a load rule which makes it harder to audit. This leads us to our final bullet.
Last and anything but least, FDM includes extensive logging. Each time data is processed through FDM and loaded to Essbase the activity is logged. The clear & aggregation calculations that were executed are logged. Any additions, deletions or changes made to the maps are logged. Each of these events captures the date and time the action was performed and the user performing the action. This is incredibly valuable to understanding the data in Essbase application and potentially more importantly, proving to your auditors that the data is accurate.
Ok, so are you sold that FDM is the right tool to help manage your data integration process with Essbase? Before we throw those traditional data integration methods away, let’s briefly discuss some of the times when FDM may not be the right fit for your Essbase application.
First and foremost, traditional/native data integration techniques are almost without fail going to perform faster than FDM. Why? Because FDM is a relational repository into which the data must be loaded and subsequently exported. All of the aforementioned logging is possible because the data is essentially staged in the FDM repository. So the cost of that is an impact on performance. Before declaring that FDM is not the right solution due to this, please consider that performance is difficult to benchmark. It can be a combination of hardware, network and most importantly, application requirements & design. I recently completed an application that is processing roughly 7 million records in approximately 45 minutes. At first blush, this might seem like a rather significant amount of time but when you consider that the client was previously investing 3 man days just to perform the transformations, the results are significant. That said, if your data load process is tens to hundreds of millions of records, FDM is not the right tool.
The second potential negative of is that FDM does not natively load data to ASO Essbase cubes. Please pay close attention to the word natively. FDM functionality can be extended through custom scripts to perform a number of tasks which it does not do “out of the box.” The ability to load an ASO cube is just such an example.
Finally, because FDM is a relational repository, all of the data processed through it is stored. This is generally viewed as a benefit but in some instances, highly confidential data (like salaries loading to a Workforce Planning application) should be tightly controlled. Housing confidential data such as this in yet another database creates another potential point of data compromise. But again, this hurdle can be overcome with a little creative thinking and a touch of custom scripting.
I hope that I have given you an objective view of the benefits as well as pitfalls of using FDM to manage your data integration process with Essbase. And for my Essbase friends who still pose the question – why do I need FDM? I flip though my rolodex of movie quotes and I offer this from one of my favorite comedies Waiting:
….Always gotta be right, with your little quips! We get it, man. You’re edgy and cool. Yeah! You’re the coolest guy at Shenaniganz! WHOOO!….