Sunday, November 29, 2015

FDM to FDMEE Migration Utility ( - Part 4

Hi there,

in last post we saw how the migration utility was showing "false" green ticks. My target application was created but no artifacts were migrated.

Today I will go through the ODI process so we can understand what is going on.

ODI variables
As most of the FDMEE scenarios, one of the first step is to refresh the variables that are going to be used in the process. In the migration utility we also have some variable which values may be key for troubleshooting and understand how all this work.

Variable: p_src_con_opr (Step 4)
This is the concatenation operator based on the JDBC driver we setup in the ODI topology for the source database hosting our legacy FDM application:
As my source database is SQL Server, my concatenation operator is plus sign '+'.
Same applies to variable p_tgt_con_opr which stores the concatenation operator for the target database hosting FDMEE.

Variable: p_prefix (Step 6)
This variable stores the value passed when executing the ODI scenario. In step 6 the value of this variable is trimmed to remove left white spaces. It's weird that no RTRIM is applied as that would also cause potential issues:

Variable: p_fdmee_application_name (Step 7)
This variable stores the target application name in FDMEE. For Essbase, it will create an application with name APP-DB. For Planning and HFM, application name will be the one passed during scenario execution:

Variable: p_file_source_system_id (Step 8)
This variable stores the source system id of our source system "File" in FDMEE.
As can see if takes the minimum Id. Why? You could have created different source systems with type "File" (although is not a common practice). Then the new import format migrated for source files will be configured with the original source system with type "File" that FDMEE has.
In case this is not what you want, you would have to change the Id of the source system you want to use in order to make it the minimum (probably by assigning value zero to it)

Variable: p_global_adapter_key (Step 9)
As you may know we could have multiple adapters in legacy FDM. One of them was set as the Global Adapter in the FDM application settings. This variable retrieve the FDM global adapter. This value will be used to migrate global periods and categories into FDMEE:

Variable: p_app_adapters_subquery (Step 10)
The migration utility uses a SQL sub-query in many different extract queries. This sub-query is stored in a variable:
In my FDM application, I have two adapters, an old version of the HFM adapter which was upgraded to a newer one FM11X-G6-C:

Variable: p_tdst_attr_column_type (Step 11)
In the case you use the HFM adapter for ICT module (inter-company transaction), this variable will store the attribute column types for FDMEE TDATASEG table. As you may know, loading data into ICT module is now done by using attribute columns defined in the target HFM application:
That's not my case so the variable stores "DEFAULT" column type: 
Quick note: I think there is a but in this code. As can see it checks HFM adapter fdmFM11XG6B twice. Maybe the wanted to say when the target adapter was either fdmFM11XG6A or fdmFM11XG6B or fdmFM11XG6C.

Printing variables (Step 14)
Key variables are logged to the migration utility log:
 If something went wrong with the migration utility, I suggest you start by checking these variables:

Initialize migration utility classes
If you are curious and you really want to see what's done in every step, then you can check Step 13. There you will see that the core process code has been built using Jython :-)

Create target application in FDMEE
Now that we are familiar with ODI...let's see how the target application CONSMINDEV has been created in FDMEE.
Steps 15 to 23 shows what ODI does to create the target application:
It's remarkable that the target application has not to exist in the system in order to create. This process assumes the target application already exists. In my case, actually it did not exist when the migration utility was launched. And consequently, the migration utility is not checking if you have access to the application so you can register. Bear in mind that the migration is done through the database layer so no security is applied at application layer.

Create Target Application (Step 15)
This process basically performs some checks in both FDM and FDMEE databases in order to validate the application.
The code to create the application is in function createTargetApp. If you have any issues creating the application you can always get the code from step 13 (Init Process)
If you review the code, you will notice that application is created as "CLASSIC". There are couple of notes in the migration utility guide regarding this topic:

Create Target Application Options (Step 21)
This process creates the target application options with default values. This means the migration utility does not migrate them from FDM Classic adapter options.
You can see in the SQL code to insert the options how default values are taken from AIF_LOOKUP_VALUES table:
For HFM applications, the lookup type 19 refers to all valid options. In FDMEE, target application options are very similar to target adapter options in legacy FDM. There are just few differences like Sticky Server option which is now taken from the EPM registry if applies:
In FDM, options were stored in table tCtrlOption. As you can see below in my source FDM application, the target adapter for the HFM application had option "Intersection Validation Report" set to Yes (OptionSwitch = 1)
The default value for this option in FDMEE is No (ATTR2 = 0):
Consequently, it is highly recommend to review the target application options after they are migrated:
Create Target Application Dimensions (Steps 22 and 23)
Once target application options have been created, the application dimensions are created in the target application. This procedure is performed in three steps:

1. Extract dimensions from FDM target adapter
Dimension details are stored in FDM table TINTDIMENSIONDEF:
One of my concerns was about default dimension names being used in FDM adapter. It's very common that you find prefix FM in Account, Entity, Scenario and Year dimensions when migrating FDM apps. It would be great if the utility removes the prefix when used. I say that because I already got feedback from migrations having issues because of this. As the dimension names in the target HFM application do not match dimension names in FDMEE, you will get connection issues between FDMEE and HFM :-(
So you either change in FDM before migration or (late?) you change in the FDMEE database after migration.
I haven't tried but you could also refresh Metadata from target application in FDMEE. This should update the dimension names and also update the custom dimension names if you are using dimension aliases in HFM.
2. Update target application dimensions for special configurations (HFM application with ICT dimensions, HP multi-currency enabled, etc.) 
Different checks and actions are done in
The code to create the application is in function updateAppDimensions. If you have any issues with dimensions you can always get the code from step 13 (Init Process)

3. Update dimension IDs for HFM target applications

Ok folks, we are close!
In the next post I will show you why artifacts were not migrated :-)

Did you already use the utility? any feedback?

Take care!