“The trouble with the world is not that people know too little; it’s that they know so many things that just aren’t so.” (Mark Twain) And a case in point is ‘Transforming’ data is not the same as ‘Mapping’ data. Sure, you’ve heard from LMS sales people that ‘Migration from your legacy system to their shiny new system is easy due their data load wizard’ and yes, these wizards do help by cleverly mapping a data element from your old LMS to the new LMS regardless of the naming convention. But, and it’s a very big but – what they don’t do is ‘transform’ the data so that it is in the right format and that the correct reference tables are populated with the correct data. Clear? Hmmm!

Ok let’s take a simple example such as date format. There are so many formats a date can take, the most obvious being the difference between the UK (DD/MM/YYYY) and the US (MM/DD/YYYY). I am sure you can see that migrating US formatted date data field into a UK formatted date data field or vice versa will have disastrous consequences if not converted beforehand. The same is true for say Time, where in one system it may be held in actual hours, minutes and seconds and in the other it’s held in hours only and decimal units i.e. 14 hrs 15 mins 12 secs in one system equals 14.2520 hours in the other. Again, if the formats are not ‘transformed’ (aka converted), then you’re going to get some fairly inaccurate and potentially wacky reactions and reporting.  

Reference tables also form part of the transformation process where for example in one system, a description reference is held as a 3-digit field and in the other system it’s held as a 4-digit field. Unless you apply consistent logic to relate the 3-digit field to the 4-digit field, then there is the potential to corrupt the migrated data record with the wrong description.

Now, I’m going to sound like a broken record, but this problem is magnified with different and large data sets which means automate where possible. In case you had not realised, that’s a plug for MDLman. Accurate transformation is key to the process to ensure the data to be migrated is in the right format and obeys and conforms to any reference data in the new system. Clearly, the more you can automate this part of the process, the faster and more accurately you can transform the data, but sometimes there are fields that need manual intervention such as truncated fields. For example if a free form description field in the legacy LMS is 40 characters long but only 25 characters long in the new LMS, then the data needs to be truncated to 25 characters or it won’t load – but it still needs to make sense. Therefore, the field requires manual review and editing to ensure sense is maintained. In general, attempting transformation without an automation tool or a logical process is a tall order and so this is an area that you either dedicate a lot of time and effort to or outsource to a specialist to help you. So make sure this is in the Statement of Work and budgeted for. That was my second plug for us!

With Transformation covered it feels like we are nearing the end of the process, but there are a few more steps in the process to negotiate to ensure the migration is complete and a success. I’ll cover these in my next blog….

Next blog in the Link

Previous blog in the Link