Within 220.127.116.11 FDM, users were able to complete full financial statement loads using a workbook with multiple sheets. Unfortunately, when upgrading to 18.104.22.168 FDMEE, the workbooks issued a multitude of errors when attempting to run the FDMEE Import Data step! The answer from Oracle was that the workbook template functionality was fully supported and should work, however they were not able to shed light on the problem. So after much research and trial and error, we were able to find a way to make these workbooks load data into FDMEE and fully tie out.
Let’s look at the workbook:
Tabs numbered 1-6 are loading data, while the general tab is defining period, year, currency, and entity. This workbook is intertwined with formulas and look up tables, so this general tab is very important for the rest of the book. I should also note that this whole work book was protected and what FDMEE actually loads is hidden to the end user. The end user is only able to see drop down menu’s and input tables, while the formulas push the data to the named ranges that FDMEE reads.
FDMEE reads all these work sheet data sets using the named ranges. These named ranges begin with “ups” which is what flags FDMEE that this is the data set to load in. The column headers within the named ranges are important because that is how FDMEE maps each dimension. The import format does not map the columns, the column headers within the named ranges does.
This table outlines the dimension header and which dimension it is being mapped to.
Here is the Balance Sheet tab showing the named ranges for the work book as well as the column headers:
Within this file, there are many long formulas that are being read as values;, and many ‘IF’ statements and ‘VLOOKUPs’ that give the ‘if false’ value to be equal to “ “. These formula results created the error within FDMEE to say that there are no values for (X) period we were loading to. To solve this, we did a Find/Replace for all ,””, to replace with ,”0”,. This changed all of the formulas that said if false <blank> and replaced with if false 0, making FMDEE read a value.
With the value header, we set up a VLOOKUP to the general tab. This will change the header from V to V:MM/DD/YYYY matching the period key from the period mapping within FDMEE. This header says that we are loading the value to that month using the period key value within FDMEE period mappings.
We also found that you need a row between the header and your first row of data. We added a row between the header and the data set and deleted a row between the header and the top of the work sheet. We did this to keep the accounts in line. FDMEE does not start reading the file until the third row of data within the ‘ups’ named range. This will need to be done to each named range of data within all sheets of the workbook. Note that some sheets have multiple named ranges.
Lastly, we needed to add a mapping script within FDMEE accounts to say that if amount is 0, then Ignore. That way we aren’t loading 0’s into the application.
We hope this brief overview of how we resolved some pesky migration errors for FDM to FDMEE excel templates may help point you in the right direction, should you encounter similar issues.
Questions? Comments? Feel free to reach us at:
Courtney Belden, firstname.lastname@example.org
Chad Anderson, email@example.com