Background
About a year ago, Oracle released the much anticipated FDMEE tuning guide. The release of this document is an acknowledgment that while FDMEE has been consistently gaining momentum due to being user-friendly and intuitive, there are some cases where performance does not meet user expectations out-of-the-box. The guide provides some simple and useful tuning guidelines that boost performance during data loads in many situations (specifically loads with larger sets of data). While this guide has proven to be very valuable, a recent discovery related to mapping design appears to be one of the best ways to optimize large data loads via FDMEE.
As many probably know, FDM (FDMEE predecessor) was originally developed as an ETL tool for loading data into HFM. More specifically, it was designed to load summarized periodic GL trial balance data into HFM. Given the nature of these HFM data loads, there typically are not very high record volumes. As FDMEE capabilities expanded to include a wider range of sources and targets, a need arose for much higher data load volumes than were traditionally required. The FDMEE Open Interface Adapter allows the tool to connect to a SQL table, which paves the way for broader use cases and much larger, more customized data loads.
The Challenge
A recent project required the ability to maintain business mappings in a simple user interface (FDMEE), while loading a significantly higher volume of data than a typical GL (detailed daily reporting through the Open Interface Adapter). Here are some stats for the data we were trying to load through FDMEE:
- ~3 million records per day
- Daily data loaded through an Open Interface table to ten dimensions
- Seven dimensions required only a minor transformation or prefix
- The business required the data to be loaded in an hour or less
The initial performance was as follows:
- Data Load from import through export to the target Essbase ASO cube was 2 hours and 15 minutes
- After initial optimization using the tuning guide, the load time was reduced to 1 hour and 45 minutes
- Not bad — about a 20% gain, but still did not meet the requirement of under an hour
- After digging through the level 5 logs, the team found that FDMEE was spending the largest amount of time processing the seven dimensions with simple transformations and prefixes, which took over an hour to complete
Let’s look in depth at the mappings:
The first three mappings in the system require some sort of maintenance, such as the Explicit mappings for Account found below:
The rest of the mappings are either 1:1, All (*) to 1, or All (*) to All (*) and these mappings will never change. Unfortunately in FDMEE, if you wanted to map All (*) data in the source column to All (*) data in the target column, you cannot just leave the dimension blank as follows:
The lack of a mapping as seen above would lead to the following error in the FDMEE logs:
To solve this issue, the obvious solution would be to insert a “Like” mapping that mapped the source as is to the target (as seen below).
Seems perfectly viable, but taking a deeper look at the logs shows that this single mapping took about 6 minutes of processing time. Looking even further, it became obvious that each similar mapping took relatively the same amount of time – about 6 minutes. During those 6 minutes, FDMEE was making updates to back-end system tables related to the mapping process (specifically an UPDATE of the TDATASEG_T temporary table for the specific “UDX” column, for those interested in the details). The key to optimizing this load would be to minimize the effort required for FDMEE to execute these mappings – which were relatively simple and static transformations. Luckily, we identified an approach that could do exactly that!
The Solution
We start by evaluating the dimension mappings. The first dimension we analyze happens to be Currency, which is a very basic set of two Explicit mappings that will never change or require maintenance. As we look to the next several dimensions we see a common theme – there are six static dimension mappings that are never going to change. If we used the traditional mapping approach, we would spend a lot of time processing these all separately and fail to meet our performance objective. However, working with Oracle’s FDMEE product management team we identified a new and interesting technique that allowed us to combine these six static mappings into one and achieve significantly better performance.
The method we used to implement this combined mapping is a SQL statement in a Like mapping. Now, what exactly does that mean? When creating a Like mapping, instead of using the native FDMEE functionality of entering a Source Value and the corresponding Target Value, a “*” can be entered in the Source value and “#SQL” in the Target Value. After doing this, the Pencil in the screenshot below will turn yellow and custom SQL can now be written to map all source data for that dimension to the desired target.
We are attaching this combination mapping to our Currency dimension. The main question then is: how do we map the other dimensions and still keep it inside a single mapping? Well, in the background, this SQL is simply updating back-end FDMEE table structures (the TDATASEG_T table), so we try to see if the SQL statement approach will allow us to update other columns. Our Currency dimension is the column named UD1X. See below for how we mapped the other dimensions – UD2X, UD3X, UD4X, UD7X, UD8X, and UD9X – all within this one SQL statement:
Since these other “UDX” columns are already populated, FDMEE no longer fails in the process when leaving the mapping for those dimensions blank.
The Results
As a result of the SQL statement above, FDMEE now only needs to do one UPDATE for seven dimensions, saving 45 minutes in our processing time (45% time reduction)! This was a HUGE time saver and made FDMEE a viable solution for this high-volume data load. We are now able to map three million records daily within one hour and still have the convenient, user-friendly mapping management functionality that FDMEE provides.
This method of designing mappings is not documented anywhere in the Tuning Guide, but is one of the most effective ways we have found to optimize FDMEE performance.
