What happened to my data?
Why are my totals off?
Why the *@#%! do I have to reload the same set of data AGAIN!?
I’ve heard these questions on many-a-implementation involving FDM data loads to HFM and Essbase. Here’s why.
Re-loading Trial Balance Data – It’s not uncommon to load trial balance data multiple times during the close. After all, it typically takes accountants and controllers a few passes to finalize their month-end financial information.
Supplemental Data – It’s also not uncommon to have information for a single entity come from multiple sources. Consider trial balance data from the general ledger system and statistical data loaded manually via Smart View or data forms.
The above data load circumstances can often lead to data integrity issues. This is where a custom FDM data protection solution can help.
Data protection is about protecting target system data from being overwritten by multiple data loads to the same point of view.
FDM Data Load Options
Both FDM Classic and FDM Enterprise Edition (FDMEE) support two data load methods: Replace and Merge. For illustration, consider this example.
- Statistical data, e.g. headcount and units sold, is loaded once during the close.
- Conversely, during the same month-end cycle trial balance data is loaded several times as the financial information refined and finalized.
Loading Data with Replace Mode
When data is loaded using the standard Replace mode, the data for the specific point of view (POV) – Scenario, Year, Period and Entity – is cleared in HFM before FDM loads the new data set.
Drawback – All data for the entity is erased. Based on our example, the statistical data would be wiped out.
Loading Data with Merge Mode
FDM Merge mode does not issue a POV clear before loading data. If the exported data set contains an intersection of data that already exists in the HFM target application, FDM overwrites that exact intersection.
Drawback – If data is re-classed during the close, e.g. data is moved from PPE_LAND to PPE_BLDG, orphaned data intersections will remain in the system.
So, Merge Mode won’t work for us because of the potential data integrity issues. It seems Replace Mode is our only option but that means we have to resubmit our statistical data every time we reload Actuals. There’s got to be a better way.
There is. Enter the custom FDM SmartReplace solution. FDM SmartReplace only erases re-classed data for entities loaded via the exact FDM point of view – Location, Category, and Period. The following presents a visual narrative of FDM data loading with SmartReplace.
Data Loads with SmartReplace
Load #1a – Trail Balance
Trial balance data is loaded via FDM.
Load #1b – Statistics
Statistical data is loaded using a Smart View template.
HFM now has the following set of data for entity 001_LJ.
Load #2 – Trial Balance Reload
Next, using FDM trial balance data is reloaded for entity 001_LJ. Notice the latest trial balance data set has a new record for OH_Legal_Fees. Additionally, data has been re-classed from OH_Benefits to OH_Wrk_Comp.
If data is loaded with Replace mode the statistical data loaded via Smart View is erased.
If data is loaded using Merge mode, the orphaned record for account OH_Benefits remains in the system.
The FDM SmartReplace solution only clears data loaded via the current location for the point of view (Scenario, Year, Period and Entity). All other data for the point of view data is preserved. Therefore, the system has the most accurate results and doesn’t require users to reload statistical data. Data for OH_Benefits is erased and data for OH_Legal_Fees is added to the system.
SmartReplace for FDM Classic and FDMEE
I’ve implemented custom SmartReplace solutions for years now. Recently, I re-engineered the SmartReplace solution for a client using FDM Enterprise Edition (FDMEE).
Have questions? Would you like to discuss how the SmartReplace solution can benefit your organization? Reach out to me.