This week I followed up a conversation with the guys at ETL Solutions (www.etlsolutions.com) that we started at DMM3. ETL (as the name suggests) are a software and services company that specialises in data integration. They have an ETL software product (Transformation Manager) plus a professional services arm and they are developing their presence in the Data Migration marketplace as a specialist consultancy offering.
Before I get on to the meat of this blog I'll have to say something about Transformation Manager. Now I haven't seen the product demonstrated, just talked it through with the ETL chaps but its architecture goes something like this...
It is a graphical tool that uses its own text based Data Description Language to describe complex mappings, data relationships and structures. It has its own design tool, test tool and online debugger and generates Java. Metadata is held in an open repository.
In a beauty contest it aint going to win against the look and feel of the Informatica's of this world but then we come onto the bigger question of complex transformations. This is an issue I've being reflecting on for... oh maybe 20 years or more. For those grey beards, like me, who have seen the rise of SQL and then all sorts of graphical programming tools, there has always been this nagging doubt. If you have studied the history of computing you will be aware that COBOL (COmmon Business Orientated Language) was one of the first "Programmer Killers" a coding system using English language constructs that none technical business people could code in, by-passing the IT crowd.
The same was said of SQL, RPG, Business Objects…the list goes on. And yet, we are still thick with programmers. Why? Well the same unfortunate truth keeps reappearing. Some algorithms are difficult. The logic is complex and thinking that through is hard. It doesn't matter if we try to express them in plain English, structured English, code or truth tables. They are tricky. The various tools that over the years have simplified the production of the majority of code just can’t hack really complex transformations. For that we need to drop out into lower level coding.
Of course Data Migration has complex transformations as its staple diet. Maybe 90% of our coding is straight forward one to one matching with a bit of validation thrown in, but that last 10%? That can be gruesome. Sticking with a single coding system keeps all the code in one place, in one format. This is especially true if that coding system is tuned to transformation and data management.
Add to that the ability to manage complex involuted data structures (think the famous parts explosion) in a controlled fashion and you can start to see the strength of this kind of less glamorous approach.
Of course this trade off between speed of point and click versus dexterity of lower level coding depends on where you are in the complexity continuum. You can certainly see where ETL Solutions came from, with their original niche background in automotive and natural resources (think oil and gas production) systems where ready control of the realisation of complex build hierarchies is at a premium, as is the complex transformation of that realisation from one technology to another. Of course for those for whom the major problem is scale and speed well maybe the other solutions are more appealing.
About the author
John Morris has over 20 years experience in IT as a programmer, business analyst, project manager and data architect. He has spent the last 10 years working exclusively on data migration and system integration projects. John is the author of Practical Data Migration.