Migrating MS Dynamics CRM to SugarCRM

I’ve been a bit quiet for the last couple of month.  That’s because I was leading an ambicious project at my new job – migration of a Microsoft Dynamics CRM version 3 to SugarCRM Community Edition version 5.0.0.  There were only three people involved, non of us could afford to work full time on the project, and we only had three weeks to do it.

Read on for a story on why it took us longer, how we did, and if it was a success at all.

First of all, a little background.  The company that I work for now is in financial services sector.  CRM is one of the two vital systems, with trading platform being the second one.  All processes are tied into CRM – registrations of new clients, incoming and outgoing money, email tracking, and so on and so forth.  The system has been installed about a year ago and has accomodated data for a few thousands of clients.

Another important bit to know is that CRM is used quite a lot 24 hours a day, 5 days a week.  Some data is coming in over the weekends too, but there are no real users who access the system.  Also, since the company is working with global clientele, weekend is really between 23:00 of Friday and 23:00 of Sunday.

By now you probably have this question in your head: “Why migrate?”.  What is so wrong with the existing system that it needs to be replaced so fast and so furious?

The company was preparing for a huge expansion.  Firtst of all, there was a take over of another company scheduled later that month.  That meant an almost sudden expansion of a few thousands of clients to a few tens of thousands of clients.  Secondly, a huge marketing campaign was starting up.  Thirdly, a few key processes were about to be change (the way new clients were registered).  Fourthly, there was a huge need for automation and extended functionality.

With all that, there was a very limited in-house expertise for the existing system.   SugarCRM had this nice advantage of being implemented with LAMP stack (Linux, Apache, MySQL, PHP), which was way more familiar to most of the IT people in the company.  And all the source code was there.

Another big reason for a change, was realization of how tied in the MS CRM is into company’s network.   While considering the upgrade of the MS Dynamics CRM to version 4, most of the people were horrified of the upgrade path.  In order to upgrade the CRM, we first had to upgrade our MS Exchange server.  Which meant that we had to upgrade the operating system of the machine.  Which meant that we had to upgrade the hardware of the server.  And MS Exchange upgrade was strongly suggesting upgrades of quite a few other bits and pieces of software and hardware.  Seemed like a really complicated and dangerous thing to do.

One thing I wanted to note separately, was how easy it was to sell SugarCRM to the management.  An open source stack meant that we were could be in full control of the system.  Independent software pieces meant that we could replace and upgrade them separately.  Fully web based technology aligned very well with many other company tools.  The brief demonstration of SugarCRM got them totally convinced that the system is mature enough and can handle everything the company needs, and more.  The fact that SugarCRM could be easily installed on a laptop in just a couple of minutes helped.  The nice look and selection of themes in default out of the box installation helped.  The selection of modules in out of the box installation helped.  And the possibility to install the system with plenty of sample data helped too.  Overall, it took me less than two hours to convince the management that the system is good enough.  And I wasn’t even trying hard.

So, what’s with the timing?  We were given three weeks.  In fact, it was two weeks for migration and one week for user training.  We had to create a full replica of the existing system.  Migrate all the data and implement all the functionality to support all the company processes.  It took us three weeks to do it.  Because we had no time to train the users, and because the company was on the doorstep of a large take over, we had to suspend the CRM migration efforts.

It took the company about three weeks to integrate all the new data.  Also, quite a few processes were changed.  And the company grew plenty of new staff in all departments.

When the question of the CRM migration was important once again, we had to face quite a few changes.  Much much much more data than we originally tested with.  A lot of changes in the ways things were handled.  More users.  And more automation was needed.  The replica of the old CRM was of no value anymore.

It took us about three more weeks to adjust our installation of SugarCRM to fit the new requirements.  Even now that the migration is officially over, we still have quite a few things to fix and improve.  But that’s going to be a live process from now on.

The project was very challanging for the company, the team, and me personally.  It was a complex thing technically, politically, and socially.  For me personally, it was also quite a new management experience.  I’ve been doing some project management before, but never had to deal with such magnitude, and especially in such tight deadlines.  The good thing though was that I had to pick my own team.

Here are a few things that I’ve learned and a few things that I think are important to share.

Technology-wise, thanks heavens for CSV (file format for comma separated values).  This was the backbone of the whole migration process.  Here is how it works.  MS Dynamics CRM has a very limited exporting functionality.  Getting things directly out of the database don’t make sense at all.  For example, our specific installation had more than 220 tables, all linked to each other.  On top of that there were almost 400 views and something around 60 stored procedures.  There was no way that we could analyze these structures in any meaningful period of time.  So we did an export with entity by entity method.  Gladly, there weren’t that many entities.  There is a way to list all entities in tabular form, with columns upon columns of entity values.  The only sensible format to save that list was in HTML.

Because we had huge sets of data, MS CRM couldn’t handle the listing properly.  So we had to come up with search criterias to separate the listings into smaller chunks of about 10,000 records each.  Now, I’ll give you a second to imagine an HTML file with a table, which has around 10,000 rows and anywhere from 100 to 500 columns.

The way to handle these HTML files was to load them up in MS Excel.  It should also better be a newer version of Excel, that can handle more than 256 columns.  MS Excel is wonderful at pasing those HTML tables, but it doesn’t offer much help in saving them in CSV.  CSV in MS Excel is weird.  So, what we ended up doing was using MS Excel to convert HTML tables into older version of Excel, so that we could open them up in OpenOffice.  OpenOffice is wonderful at saving CSV.  You can pick and choose delimeters, quoting symbols, etc.  The point is to make CSV files which can be parsed by PHP.

Here are two things to remember.  First, older Excel formats can’t work with more than 256 columns.  The trick we were using was the sheets.  Copy data of the extra columns into another sheet of the same file, and it will be much easier to process later.  Second, if you will do several exports of data from MS CRM, remember that columsn won’t necessarily be in the same order.  In fact, chances are that columns won’t be in the same order.  The way go about it is to use hashes, or, sorry, associative arrays.  Take the first row, of data, which has the column titles, build an index array out of it, and then go through data rows, assigning values to appropriate keys.  Don’t use indexes for data, or you will have to inspect the order and adjust your SugarCRM importing scripts on every export.

Another technical issue is the deployment process of SugarCRM.  We are still in the mess with this and haven’t solved it properly.  We used Subversion for the version control of all the files.  But it’s not enough for SugarCRM, since some of the modules are bulit with its own Studio, and loaded with its own Module Loader.  When this happens, the database is modified, and a few local files are changed also.  We sort of have it working now, with plenty of magic and strict procedure involved, but it’s far from perfect, requires too much manual labour, and is very prone to problems still.  If you have any suggestions for this one, please let me know.

Something else worth noting, is a very unpleasant behavior of SugarCRM in terms of usage tracking.  Surely, there is an option to not send anything, but somehow I find it always sending way too much stuff out.  And the worst part is that these things are hidden, and written in a way that makes them hard to find.  have a look in include/utils/mvc_utils.php and in include/utils/tracker_utils.hp .  These are the two ugliest places.  There is another one which deals with update notifications, which I think I found, but didn’t have time to cut out yet.  I am really surprised to see these in an open source project.  And then again, I’m glad to have all the sources and being able to remove this stuff.  Who knows what goes on in closed source applications…

In terms of project management, I’ve learned to talk to people a lot.  Talking to all the people all the time was the bigger part of my participation in the project actually.  I think I’ve spent close to 80% of time just talking.  I was talking to the members of the team, trying to figure out the best ways to approach problems.  I was talking to people who were supporting MS CRM installation, trying to get as much as possible from them regarding the details of the running system.  I was talking to each user and each user group trying to see what they were using, how they were using it, and what they wanted and needed from the new system.  I was talking to the management a lot, trying to figure out where the company was going and what would be the changes in the near, intermediate and far away future.  I was also talking to a lot of  people outside of the company, trying to see how similar problems were solved there, how they managed projects of this magnitude, how they were dealing with their users, developers, and managers.

I know that many projects take a lot of talking, but in this one we had practically no documentation of the existing system and process, and we had very tight deadlines, so talking ended up being a very effecient way of getting information.

Also, talking helped a lot in extending deadlines where needed, and in keeping users calm.  Somehow, people are taking delays and system limitations very much easier if they know why those limitations are in and when there are problems will be solved.  Sometimes I had to go into technical details explaining why certain things weren’t working or were working different from the previous system.  Sometimes I had do a totally non-technical talk showing to people which other departments exist in the company and how they interact, and how things are prioritiezed and why.

As I said, I did a lot of talking.  More than during any other project that I ever handled.  Of course, I was blessed with excellent teammates who were handling the technical parts of the project.  One person was doing everything and anything about SugarCRM.  Another one was working on all the exports, figuring out other systems and getting stuff off of them.  He also helped a lot with integration of SugarCRM with the other company tools.  Great guys both of them!

Now, this pretty much covers the migration process.  But since the SugarCRM saga in our company is just started, expect more stuff posted.  In the mean time, if you have any questions regarding any aspect of this migration, feel free to ask in the comments.