Moving on
November 22nd, 2006 by Peter Scott
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.
November 23rd, 2006 at 1:54 am
I believe that the overwhelming advantage that a rebuild has over an initial design is that the nature and frequency of user queries is much better understood. This can allow significant optimisations in a new design.
November 23rd, 2006 at 7:30 am
I’d say it doesn’t just apply to rebuilt systems – sometimes you just learn from past design “mistakes” and you have to make a decision as to whether to just do new things in the “newly discovered” design pattern or you go the extra mile and change all the existing stuff in the system to use the new design pattern.
David makes a good point that over time, the understanding of your users and their usage improves and you can make more informed decisions for the future of your system…I’d say that applies not just in terms of your users and their queries but also in a whole host of other areas such as backup and recovery, ETL processing and security.
November 23rd, 2006 at 7:50 am
There is always a point where you think “it would have been better to this instead of that”. Getting the opportunity to something about is harder for people working in consultancies
November 23rd, 2006 at 9:07 am
Couldnt agree more with the above:
Redesign and Rebuild is the most healthy option. Gives you the opportunity to step back, think, and Improve.
Technology-churn offers this opportunity to IT all the time.
Explain to business-sponsor that a “migrate” project is often a lot of Effort to just stay in place. Whereas a rebuild is a fresh-start.
And dont they also replace their phones, laptops and middle-managers every year…. ? My phone holds more data then my 1992 datawarehouse (it was called differently then).
Think Soaring Phoenix in stead of rowing upstream. (OTT? sorry, too much coffee…)
November 23rd, 2006 at 10:53 am
Thanks Piet
I doubt that any of my migrations have not been a redesign at heart – I know it is the best way :-)