FDM to FDMEE Migration Utility (11.1.2.4) - Part 2

Did you already have the chance to play with the new migration utility?

In my previous post I described the basics about this new ODI built-in tool which is supposed to speed-up your migration process by moving your legacy FDM artifacts to FDMEE through the database layer.

Today, I will go through the installation steps needed to perform the migration of one classic FDM application running in SQL Server 2008 into new FDMEE 11.1.2.4 running on SQL Server 2012.
The application has two target adapters for HFM. Actually is an old version which was synchronized the the new version of the adapter but kept in the FDM application.

Prerequisites

What do I need to start my migration?

• a running version of FDM - not necessarily, actually I have restored a SQL server database for a FDM application in the same database server that FDMEE uses. Reason? well I just have one virtual machine in my laptop :-) what about you? the migration is done through the database layer so I don't see any problem having them in the database server. However if you also migrate your database platform (ex: from Oracle to SQL Server), you may want to keep it in your old database server. Backup of the original database is always recommended as the migration utility creates some object in it.     
• ODI Studio 11.1.1.7- don't try to install ODI Studio from EPM installer, it fails. I still keep the full ODI 11.1.1.7 installer. I think Oracle just keeps last 11g release which is 11.1.1.9. If you don't have the original installer files, I would suggest you raise a SR with oracle so they can provide you with the links to download it.
• FDMEE 11.1.2.4.x - got it!

As expected with this kind of automation tools there are many restrictions, some of them due to its functionality, others due to FDMEE's functionality. For this reason I encourage you read carefully the migration guide. It may avoid headache for you :-)

For example, the utility guide shows the following note:

You can read above "FDMEE does not support parent locations across different target applications".

What do they mean with this? Is that you cannot have one parent location for two children locations each of them pointing to a different target application having or not the same dimensionality?
Let's have a quick look:

I can create one parent location for two children pointing to different target applications: 

- Location LOC_PARENT_GLOBAL has an import format pointing to an HFM application

- Location LOC_CHILD_A has an import format pointing to an HFM application

- Location LOC_CHILD_B has an import format pointing to a Planning application

But what about if I want to maintain mappings for Planning dimensions?

There is no way of showing Planning dimensions as parent location points to the HFM application.

I will be able to map planning dimensions only if LOC_CHILD_B has a parent pointing to Planning, which as I said, makes totally sense:

To me, it does not make sense that FDMEE allows you having this configuration so, as an enhancement, it would be great if you could only select then parent locations having the same target application as the children one you are configuring.

Step 1: installing the patch

Let's start with the easy ones:

1) Create a working directory

2) Download path 21379349 (Migration Utility)

 3) Unzip the patch to your working directory

Step 2: installing DB schema

Before moving forward, please call your DBA colleague if your don't feel comfortable with SQL.

We need to run some SQL scripts coming with the patch for both FDMEE and FDM schema (if you work with Oracle) or database (if you work with SQL Server).

These scripts will create SQL temporary objects used by the migration process.

Update the FDMEE Schema/Database

1) Logon to the FDMEE database using a tool like SQL Management Studio

2) Run appropriate SQL script. The one for SQL Server in my case:

Command is completed successfully:

What is this script doing?

- Create table AIF_FDMC_UPGRADE_KEYS

- Add columns prefixed by FDMC to tables:

      

   AIF_TARGET_APPL_DIMENSIONS

   TPOVPARTITION

   TDATAMAP

   TDATAMAPSEG

   TLOGPROCESS

   TDATASEG

   TDATASEG_T

-    Alter attribute columns in TDATASEG and TDATASEG_T to support ICT module integration.

Update the FDM Schema/Database

1) Logon to the FDM database using a tool like SQL Management Studio

2) Run SQL script aif_fdmc_create_common.sql (valid for Oracle/SQL Server)

We get error below when running the script in SQL Mgmt Studio:

The code above is valid for Oracle. We have replaced the semi-colon between the two CREATE VIEW statements by reserved word GO:

What is this script doing? It basically creates two Views:

- View TDATASEG_ALL (union of of all TDATASEGX) tables

- View TDATAMAPSEG_ALL (union of all TDATAMAPSEGX tables)

Note: bear in mind that you will need to adjust this script in the case you use more than 50 data segments in legacy FDM (probably when having more than 250 locations)

Install and configure the ODI objects

In ODI Studio:

1) Configure topology for FDMC Source

    - Logical schema: FDMC_SOURCE

    - Data server: FDMC_SOURCE

      JDBC connection

    - Physical server for FDM database: FDMC_SOURCE

   - Context FDMC_CMFDMDEV

You can use GLOBAL context or create a new one. I will create a new one FDMC_CMFDMDEV as a duplicate of existing one GLOBAL. It is recommended to use one context for each FDM application to be migrated:

After updating logical schema for FDM and FDMEE:

2) Import ODI Scenarios in INSERT_UPDATE mode:

     - Scenario FDMC_EXTRACT_DATA_001

     - Scenario FDMC_EXTRACT_SETUP_OO1

To import the ODI scenarios:

Select them from the working directory:

Note: click Yes in all warnings

Besides SQL objects needed for the migration, we now have our ODI topology configured:

• Physical/Logical architecture for our legacy FDM database
• The two scenarios which perform the migration for both Setup (artifacts) and Historical data.

Next stop... executing the migration! and maybe troubleshooting...or maybe not :-)