Data Warehousing

Read the results of some testing an ICE user performed.

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.

OK, so most of you know that I lived in the world of PostgreSQL for several years before moving into the world of Infobright and MySQL. With that in mind, I've been determined to continue to hone my skills around MySQL as it serves as the primary interface on top of our open source columnar data warehousing engine. Yes, I've hacked around with MySQL for many years and was of the first OEM'rs of MySQL when I was at Ascential. I even created a website on the side called www.MyCollegeFAQ.com that is based on MySQL. It is  a free (no advertising) and open site that is used to help high school and college students get answers to their frequent asked questions about getting into college. Always had an affinity towards the MySQL database but other efforts led me to PostgreSQL. One tool that I've found helpful these days is the MySQL Cheat Sheet that is installed on my iPod Touch. It was developed by the folks at Concentric Sky. This handy reference tools is a great companion reference for all the various commands, data types, sql formats, etc. for MySQL. They say that if you study something for 10,000 hours, you become an expert. Let me say, I'm not quite 10,000 hours into MySQL, thus the Cheat Sheet. If you are interested in getting the Cheat Sheet, it is on the Apple App Store. This is not just a reference to a website tuned for Safari on the iPod Touch or iPhone, it is a simple and easy to use application that is a growing dictionary for MySQL reference information that is continually updated. It's 0.99 and well worth it. Enjoy.