Moving on

There comes a stage in the life of a data warehouse when it just needs to move on. Sometimes it just outgrows its space, sometimes the underlying technology is in need of a refresh and just sometimes a rethink of the use of resources leads to a consolidation of systems.

Over recent years I have probably worked on as many migrations projects as I have brand new data warehouses . Migrations have always been as challenging as there is already a performance target in place; the users have used a live system that returns result x from query y in 10 seconds, the new system has to perform as well as the old, and is almost certainly required to be better. In addition to user experience, the migrated data warehouse almost certainly needs to have improved system management capabilities too, who would thank you for putting in a data warehouse design that boost query performance by 85% but can not be recovered in an acceptable time when something breaks.

Moving between operating systems or up database versions has its own challenges, but the biggest challenge is moving from database to database. Vendors have migration tools on their websites but read the fine print and although they can build the structure (or at least describe how it would look in the new database) they can not do too much with the code stored in the database. Traditionally the two approaches are translate the syntax and rebuild as is, or find a bi-lingual (in SQL dialects) expert who can translate the spirit of the code. I know which I think gives the best results. I suppose it is a bit like comparing a computer translation of Beowulf with Seamus Heaney's.