Data Warehousing

Over the years I've worked on many conversion projects, from one operating system to another, from one BI or ETL tool to another, but the conversions I've done the most that's a tough challenge for many is converting from one database to another.

The premise of SQL was to make an easy to use portable language to access data. So why is it perceived by many that converting from one database to another is a huge challenge? Because they underestimate the work involved by not knowing who uses the data and how and rushing it into production with improper QA.

There are many tools to help with conversions and the first place to look is with the database vendor you are migrating to. Many database companies have migration toolkits to make this task easier. Infobright has several free tools in it's download section of their website called ICE Breaker to make it easier to migrate from Microsoft SQL Server and Oracle for example.

These tools typically 

1) generate the DDL (create table statements) so you quickly have a working schema

2) export the data into a flat files

3) import the flat files into the schema on the new database.

The premise of these tools is to get you up and running as fast as possible. But it's the next steps are what has helped make my conversions a success:

1) evaluate who in the company is using the data and how. You can do this by contacting all department managers and asking them, preferably in a face to face meeting. I know this sounds daunting, but without their support, success is unlikely. All it takes is one department telling you in the 11th hour that it's their crunch time and they haven't had time to convert their MS Access reports and your conversion is on hold for months. You will be surprised in all the clever ways people access a data warehouse with MS Office without IT's knowledge.

2) setup user training on how to switch their applications from the old database to the new. Setup training for smaller groups from throughout the company with multiple classes as most departments can't afford to lose too many of their staff at once.

3) coordinate with desktop support to have them include new ODBC drivers and any client software needed so that they can push this to every desktop that needs it well in advance.

4) update the ETL process to keep the new database updated. This can be as simple as pointing process to a new ETL tool connector, or as difficult as editing shell scripts. This is a good time to evaluate how your ETL process is working for you and make it better.

5) update the BI tools to point to the new database.

6) there's nothing that will reduce the trust in your ability to deliver if your conversion produces wrong results in queries and reports. This is why it's very important to setup a QA process early in the conversion to make sure reports balance and that data is correct. The biggest mistake I see is waiting until the end of the project to start this.

7) use the professional services group from the new database vendor to help kick start the project and then come back in the later part of the project to help fine tune everything. Nobody knows the database product or has access to support better than a consultant that works for the vendor. Spending weeks fumbling trying to learn things on your own just wastes time and adds to project costs.

Having a working database as quickly as possible, even with stale data to do the above tasks in parallel will allow the conversion to be successful in the shortest amount of time with the least impact to the business.