It's been a while since my last blog post. I've been very busy with other projects and I managed to get to KScope 2016 in Chicago which was great. I encourage anyone and everyone to go to the KScope conference if you can.
This is a lessons-learned type post where we recently upgrade a FDM application from 11.1.2.2 to 11.1.2.4. The FDM app used the very old FDM EBS adapters (not the ERPi ones) and we hit some challenges along the way.
The version of FDMEE we were using was 11.1.2.4.200 and the target was Planning and HFM.
FDMEE EBS Datasource Initialization
The first issue encountered was when we tried to initialize our EBS source in FDMEE. The ODI configuration was correct but we kept hitting this error message when we ran the initialization:
Error:
ODI-1228: Task SrcSet0 (Loading) fails on the target GENERIC_SQL connection FDMEE_DATA_SERVER_MSSQL.
Caused By: weblogic.jdbc.sqlserverbase.ddc: [FMWGEN][SQLServer JDBC Driver][SQLServer]Cannot insert duplicate key row in object 'dbo.AIF_DIM_MEMBERS_STG_T' with unique index 'AIF_DIM_MEMBERS_STG_T_U1'. The duplicate key value is (X, X, X, X, X, X).
After some head scratching and troubleshooting it became clear that the issue was down to duplicate codes within EBS. There were two entities in EBS which had the same name, one in upper-case and the other in lower-case. This was causing the duplicate key error when FDMEE tried to insert both enitities into the AIF staging tables.
We went down the easy route to resolve this...we renamed the source entity in EBS to make it unique. You can alter the index in the FDMEE tables to enable duplicates but we preferred the simple option
Data Reconciliation (Data was double what it should be)
We were seeing strange issues with data reconciliation, we were sometimes seeing the data values doubling up. This problem was down to a bug with the FDMEE snapshot load. For whatever reason the data being loaded into FDMEE was incorrect when we used snapshot mode. When we used Full-Refresh the data loaded correctly.
FDMEE Scheduler
Oh my god, the scheduler in FDMEE is terrible. You can't seem to edit a schedule and if you did manage to edit it then it doesn't show the new value. The other issue we had was that there was no way to select the load type (snapshot or full-refresh) in the load. The default load type is snapshot and this was no good because of the bug mentioned above whch was giving us double values.
Our strategy was to use a Windows batch script to do the load. You have more control over the load process with the batch script so we could select the load type. We then scheduled the batch script to run at the appropriate time using the Windows scheduler. The down-side of this is that you can't schedule via the workspace but our customer actually prefered using the Windows scheduler as they were familiar with it and had more confidence in it.
We created two scripts, one which set most of the parameters required for the FDMEE batch loader and another which set the relevant periods for the load
The FDMEE batch loader is located here:
D:\Oracle\Middleware\user_projects\FDNHOST1\FinancialDataQuality\loaddata.bat
The loader uses the following syntax:
loaddata.bat %USER% %PASSWORD% %RULE_NAME% %IMPORT_FROM_SOURCE% %EXPORT_TO_TARGET%
%EXPORT_MODE% %EXEC_MODE% %LOAD_FX_RATE% %START_PERIOD_NAME% %END_PERIOD_NAME%
%SYNC_MODE%
Notice the EXEC_MODE parameter, this enabled us to define the load type (Full Refresh).
So we created a simple script to set most of these parameters. This script uses a hard-coded password but you can encrypt the password to make it more secure.
@echo off
REM ################################################################################
REM #### FDMEE Batch Load #####
REM #### Uses FDMEE loaddata batch script to initiate load via command line. #####
REM #### #####
REM #### Syntax is FDMEE_Batch.bat RULE_NAME START_PERIOD_NAME END_PERIOD_NAME #####
REM #### e.g. FDMEE_Batch.bat PLN_UK_DLR_Actual May-16 May-16 #####
REM ################################################################################
set BATCH_LOAD=D:\Oracle\Middleware\user_projects\FDNHOST1\FinancialDataQuality\loaddata.bat
set BATCH_SCRIPT_HOME=D:\Scripts\FDMEE_Batches
set USER=admin
set PASSWORD=XXXXXXXX
set RULE_NAME=%1
set IMPORT_FROM_SOURCE=Y
set EXPORT_TO_TARGET=Y
set EXPORT_MODE=STORE_DATA
set EXEC_MODE=FULLREFRESH
set LOAD_FX_RATE=N
set START_PERIOD_NAME=%2
set END_PERIOD_NAME=%3
set SYNC_MODE=SYNC
cd D:\Oracle\Middleware\user_projects\FDNHOST1\FinancialDataQuality
echo %date% %time% Starting load >> %BATCH_SCRIPT_HOME%\FDMEE_Batch.log
call %BATCH_LOAD% %USER% %PASSWORD% %RULE_NAME% %IMPORT_FROM_SOURCE% %EXPORT_TO_TARGET% %EXPORT_MODE% %EXEC_MODE% %LOAD_FX_RATE% %START_PERIOD_NAME% %END_PERIOD_NAME% %SYNC_MODE% >> %BATCH_SCRIPT_HOME%\FDMEE_Batch.log
echo %date% %time% >> %BATCH_SCRIPT_HOME%\FDMEE_Batch.log
FDMEE Load Performance
The other issue we hit was performance. The loads in FDMEE were taking longer than the equivalent loads in 11.1.2.2 FDM. This seemed puzzling to me as FDMEE is usually much more efficient because it uses ODI and the power of the relational database engine for the transformations. We also noted that the HFM consolidation times were also slower, again very strange as HFM 11.1.2.4 performance is generally a lot quicker than older versions 99% of the time.
Here our problem was down to the relational database. We were using a SQL Server 2012 cluster with AlwaysOn synchronization enabled. It turns out this is very bad for performance because the primary SQL Server node updates the secondary node in real-time. Good for resilience, very bad for performance. We decided to turn the synchronization off and went for a periodic asynchronous update strategy.
FDMEE load times were 100% faster and our HFM consolidation times were 100% faster with SQL Server AlwaysOn synchronization turned off.
Turning on database synchronization causes SQL inserts to be slower, Microsoft even say this themselves:
“Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency”.
https://msdn.microsoft.com/en-us/library/ff877931.aspx?f=255&MSPPError=-2147217396
We weren't the only people encountering this:
https://clusteringformeremortals.com/2012/11/09/how-to-overcome-the-performance-problems-with-sql-server-alwayson-availability-groups-sqlpass/
“The one thing that I learned is that although AlwaysOn certainly has its applications, most of the successful deployments are based on using AlwaysOn in an asynchronous fashion. The reason people avoid the synchronous replication option is that the overhead is too great. “
Conclusion
Whilst this post is outlining the issues we had with FDMEE I have to say that the application is now way better than it was in the old FDM. We managed to streamline many of the processes and it was great to be able to use multi-dimensional maps in FDMEE instead of having to script everything. In fact in this example we went from having 6 custom scripts to zero, yes ZERO scripts (apart from our custom batch script of course!). The load times are also faster than their FDM equivalents.
Thanks for reading and if you've any comments/suggestions then please add below.
