FDMEE Migration - Less Than Ideal
So, if you are familiar with the Oracle EPM Suite (Hyperion) you know that most products over the years have had issues with migration. Any migration is usually accompanied by some headaches. The suite provides LCM to migrate most content, HFM has various forms of copying data and metadata, and Essbase has both LCM and the EAS Wizard. There are multiple ways to move metadata and data. Standing in its own unique position is Financial Data Quality Management Enterprise Edition (FDMEE).
In order to migrate content across versions (in other words to upgrade from 11.1.x to 11.2.x) you are supposed to use an Oracle provided SSIS (SQL) or Data Pump (Oracle) script to migrate from the source DB over to the target DB, with the target DB having already been configured for your 11.2.x version. There is fine print in the documents that state the source environment should be on 11.1.2.4.220 or above prior to the migration. The start of this process can be found here.
The following will focus on this process with a SQL FDMEE DB, but the same applies for Oracle DBs. If you look at step 5, it states:
Check for any errors. Fix any issues in the source and repeat steps 3 and 4 in sequence as needed.
Now, this is where things get interesting. What is most likely to occur is you will find various tables do not copy over when initially executing the SSIS script. My experience is that this is due to duplicate records in the TDATAMAP and other similar TDATA tables. Apparently, each combination of PARTITIONKEY, SRCKEY, DIMNAME, and TDATAMAPTYPE should be unique. From a logical perspective, that makes sense. But if they have to be unique then why are they in the tables to begin with? I wish I had an answer to that, but I do not.
Additionally, the utility as provided offers no built in logging functionality, so you need to scroll around in a console window in order to see what an error looks like. It looks as follows, for enquiring minds:
What does all of this mean? Well those of you familiar with FDMEE know that client FDMEE databases and their accompanying table sets can be large. You may also know that many clients want to keep this history of data and transactions for audit purposes. So, in my example the client had a relatively small 30GB database, and over 300,000 records in the TDATAMAP alone. Running the utility to upgrade it takes approximately 90 minutes (or longer depending on the size of the app), and for it fail on each individual duplicate is problematic.
To resolve, this and not run the utility potentially dozens of times, some SQL scripting can be your friend. In this example I will focus on the TDATAMAP table, where the source of most issues seems to reside. In order to do that, a look at the errors shows that the SSIS script fails to update a combination of PARTITIONKEY, SRCKEY and DIMNAME. So the next step is to determine how many duplicates of this type there are. Which can be done as follows:
This will tell you how many duplicates you have, and which combinations they occur in. In the case of my runs, the uniqueness only seems to apply to the FDMEE required dimensions, but your mileage may vary. Looking at the results from the query above you may see something like:
In this case, there are two duplicate records for 1264201300000000 and ENTITY (a required dimension) at Partition Key 30. We can now search those, and remove one of the offending rows, which will each have unique DATAKEYS. In my case, I removed the oldest DATAKEY. Alternatively, you could go back to the FDMEE source and individually remove/resolve these and go back through the whole migration process. I used SQL to first isolate the rows:
This returned two unique rows with different DATAKEYS and I simply removed one of them as follows:
Doing the above sort will then allow you to remove duplicates prior to executing the migration utility, or at least after a first failed run. Whether you chose to resolve the duplicates in FDMEE (probably the Oracle preferred method) or via SQL is up to your judgement. Either way, the above at least lets you know how many duplicates you are potentially dealing with, and gives some information to resolve them. If there are issues in other tables, you could perform similar SQL searches, leveraging their column structure to also isolate and resolve similar issues.
Lastly, it is important to note that Oracle Support has explicitly told me that they do not support migrating FDMEE from 11.1.2.x to 11.2.x via LCM. So, while I know that works, and I have done it for customers when the above has failed. You could encounter issues. Following the LCM path would mean a loss of all historical FDMEE data though, and that can be an issue for customers.
With the above stated, it is best to be prepared to have some struggles with FDMEE migrations. I was able to speak with Oracle Development and they have agreed to add validation and better logging to the FDMEE migration process, I do not have a time/version for when that will be updated, but it is being tracked as a BUG for future improvements.