Day 238 - Migration Status

Finally I got a set of entities that migrated correctly.
It was actually quite simple, once the mapping rules were correctly specified according with the data contents. The main problem is that the source team had, as usual, some difficult to admit that their data is dirty and its quality is actually a lot lower than they expected. In this particular case, it was the zip codes.
Once they admitted that the zip codes needed specific rules because they were dirty, it became an easy migration task.
In the next few days I expect to migrate other entity sets without errors.

The migration performance as an overall is quite slow, even using 5 parallel executions. We got some times and, as already known, the GIS loading procedure is responsible for the biggest migration times.
Usually we perform the ETL cycle within Data Fusion, which means we deliver the data directly to the target database, i. e. usually we would only have the Data Fusion transformation procedure time, but in this particular scenario we have to create a text file which GIS will use to load the data.
Here's an example of the performance times we're having now:

Car Insurance Claims (470.563 records):

• Data Fusion transformation procedure: 51m
• File creation for GIS loading: 2h 10m
• GIS file loading: 238h sequential time (time calculated based on 16h of loading)

Personal Accident Insurance Claims (29.303 records):

• Data Fusion transformation procedure: 1m 51s
• File creation for GIS loading: 1m 40s
• GIS file loading: 2h 17m with 3 parallel processes (5h 9m sequential time)

Entities (682.569 records):

• Data Fusion transformation procedure: 6m 3s
• File creation for GIS loading: 23m
• GIS file loading: 5 h 55m with 5 parallel processes (27h 17m sequential time)

Tonight GIS will be cleaned up and the AS/400 will be tuned for better performance for file reading. In the next few days I'll get some new times, hopefully better.

Day 219 - New Convert Sequence Function

We have a functional requirement that needed our technical expertise to be solved, it was a small challenge but an interesting one.

Data Fusion already has a convert sequence function, which receives a key a returns a sequence.
It is very useful to convert things like keys and identifiers between the old system and the new system.
It works on a very simple basis: the new system key is requested based on the old system key, if it exists it is returned, otherwise it is created based on the addition of 1 to the current maximum value present, and the returned.
Here is an example:

Old Key ACC0005 ACC0008 ACC0017 [...]

New Key 1 2 3 [...]

This is persistent between data migration executions, which means only new keys will be added, and it is quite efficient since it is entirely loaded into memory.

This has suit us for almost a decade. All our previous data migrations never required identifier conversions, or creations, based on anything other than a set of identifiers.
But here a new challenge raised. We still must return the new system key based on the old system key, but the creation of a new system key is no longer based on the old system key.
The creation of the new system key is now based on a subset of the old system key.
Here is how it works:

1. Request the new system key based on the old system key
2. If if exists: return the value
3. If if does not exist:
1. Select the next value from the old system sequence key
2. Add an entry to the conversion table registering the old system key, the old system sequence key and the new system key
3. Return the new system key
   

Here is a very simple example for better understanding.
Consider the old system key to be based on the source system account number (ACC) and historical image (IMG). Consider the sequence being incremented only by the account number. A conversion table would be something like:

Old Key ACC0005 # IMG0001 ACC0005 # IMG0002 ACC0008 # IMG0001 ACC0017 # IMG0001 ACC0017 # IMG0002 ACC0017 # IMG0003 [...]

Old Sequence Key ACC0005 ACC0005 ACC0008 ACC0017 ACC0017 ACC0017 [...]

New Key 1 2 1 1 2 3 [...]

Note that the old system sequence key varies based on a subset of the old system key.

Obviously the above example is purely academic, but the this easy to use mechanism is being used to solve a complex transformation problem and it has became extremely useful.

Day 98 - New Proposal

The project is stopped, but there's still management work to do.
The political battle, with the consequent interruption in July, resulted from the fact that the proposal price was considered too high by the sponsor and our partner.

The project has been planned according with Data Fusion methodology and our know-how, which is over 10 years. I recall that we are data migration specialists, I don't have number but I believe there's not many people who have migrated as many banks as we did nor as faster as we do it. This said, we know what we're doing but obviously the sponsor, and unfortunately the partner, does not.

This said, the proposal has been presented according to what is most convenient to the client.
But this time we had to cut costs and in order to do it, we had to cut one resource. This means we've rethought the planing with one less specialist. But in order to do it, we had to force some constraints:

1. The client must give the functional mapping rules in up to three blocks. The first block must have the minimum required in order to be possible to execute the mapping. The second block must cover at least 75% of the rules and the third block must be complete.
2. There's a dead line to deliver the last block. It depends with the complexity and development time of the area being mapped. It usually must not exceed 4 weeks after the first block delivery, which must be delivered by the end of the initial week of the area being mapped.
   
3. The functional mapping deliveries must be of high quality. This means that there's a maximum number of changes programmed. This threshold has been calculated as 25% of the total number of mapping rules, e.g. a project that has 1000 mapping rules can have up to 250 change requests. If the client requests changes above the maximum number of changes planned, that will cost him extra.

We had to include these two rules because we need to have high quality functional mappings delivered fast and we can't spent much time changing recoding the rules, that's why there's a threshold on the number of changes. There's one less resource, so there must be less work to do.

The main difference from our regular methodology is commodity for the client.
In the usual model, the client can request as many change requests as he wants and we, usually, accept that functional mappings are finished late in the project.
This has obvious advantages for the client in contrast with a limited number of changes and no late deliveries.

I think this is a great example were the client has not been well treated, since the price battle actually was more a problem for our partner than for the sponsor.
But the main goal has been achieved, the proposal is now cheaper, with an obvious cost.

Day 46 - Performance Tests

I know it's Saturday, but a lot of data migrations work is done during the weekends. Specially during the last weeks before the end of the project.

But this Saturday, we were focused on performance tests. We had the AS/400 entirely for us for about 3 hours.
We executed performance tests for the transformation rules engine and for the GIS data loader.

As excepted, the performance increased both with a single process and with multiple processes. for both systems.
In the transformation engine we've executed up to 6 parallel migrations, but the maximum gain was below that, the 4th or 5th, depending on the transformation features, more I/O bound or more CPU bound.

There was not time to perform one interesting test though, running the transformation engine at the same time as the GIS data loader.

Nevertheless, the outcome was what we expected: to have a dedicated Intel machine running Linux for the exclusive usage of the Data Fusion. This will allow GIS data loader to have more resources, since we're not competing.

Here's some nice charts from the tests performed.

Day 14 - First Mapper

I've just set up the Data Fusion IDE and server on my machine. Until we install the server on AS/400, my desktop will have to do the trick. I'm using the JTOpen JDBC driver to connect to AS/400 files via DB2.

In order to create the Data Fusion tables layout automatically, I've set up a simple command line application that connects to AS/400 through the same JTOpen JDBC driver and queries the files/tables metadata, in particular it's structure, the columns name, data type, precision, etc..

I've also implemented some transformation rules to run some tests and everything worked fine.
I'm still waiting for some target specification information from the target team, but I believe that I'll have two mappers ready for some serious testing real soon.

I've also received the production data, which means know I can test my data profiler with real data.

Day 7 - First Mappings

The first mappings have been specified.

I've spent almost all day with the target team and the source teams, one from the main system and another from a secondary system.
It was a productive day, since we were able to map over 90% of both systems, that will migrate into the same target specification. The doubts and problems detected will be easily solved.

As expected, some cleaning will be performed by the trasformation rules. For instance, in some circumstances, the honorific title will have to be automatically inferred from a free form text field.
It will also be necessary to perform duplicate detection and entity fusion before executing the migration.

None of these seems really problematic at this time.

Day 1 - Kick off

The project has been officially kicked off.

There was the traditional meeting where all teams where present and an overview of the project was presented. All teams involved have previous data migration experience, which is good.

The project should be completed around mid October, but I felt that management confidence to comply with the date is not very high. This is not a bad signal as one might think, since management showed that it is aware of some real difficulties. Those difficulties may become technical challenges and may delay the project.
Nevertheless, I do believe that the original due date can be achieved.

There will be two phases. The first phase, the current one, will use a small entity subset in order to test the performance of the ETL and to tune the process.
The second phase will be the continuity of the previous phase, but now including all applications and entities.


The project itself consists of migrating the current insurance application, running on OS/390 mainframe, into the new system, the GIS Non-Life, running on AS/400, all using DB2, SAM and VSAM.
There are some other satellite applications running on SQL Server 2005 on Windows.
The ETL tool will be Data Fusion and it will run on AS/400 under the Qshell.

The ETL process will be implemented in a slightly different way from the usual scenario. Usually the data is extracted from the source database and loaded into the target database. This is not the case in this project.
Eventually, the data will be loaded into the final target database, but there's an intermediate step. The ETL process performed by Data Fusion will deliver the data on flat files that will be later consumed by the GIS loader. The GIS loader will validate and load the data into the final database.

Since our (ETL team) experience tells us that performance on AS/400 may be a problem during the ETL process, there was some discussion over the performance tests to be performed during phase one.

If the performance becomes an issue, the data migration may have to be incremental, instead of one-shot. This will be a problem because the source team cannot identify which data has been changed and needs to be migrated again to refresh the target system. One challenge that has been given to us, is to think in a fast way to identify if a record has changed and needs to be migrated again, just in case of a B plan is required.