I’ve been a bit quiet for the last couple of month.\u00a0 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.\u00a0 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.<\/p>\n
Read on for a story on why it took us longer, how we did, and if it was a success at all.<\/p>\n
<\/p>\n
First of all, a little background.\u00a0 The company that I work for now is in financial services sector.\u00a0 CRM is one of the two vital systems, with trading platform being the second one.\u00a0 All processes are tied into CRM – registrations of new clients, incoming and outgoing money, email tracking, and so on and so forth.\u00a0 The system has been installed about a year ago and has accomodated data for a few thousands of clients.<\/p>\n
Another important bit to know is that CRM is used quite a lot 24 hours a day, 5 days a week.\u00a0 Some data is coming in over the weekends too, but there are no real users who access the system.\u00a0 Also, since the company is working with global clientele, weekend is really between 23:00 of Friday and 23:00 of Sunday.<\/p>\n
By now you probably have this question in your head: “Why migrate?”.\u00a0 What is so wrong with the existing system that it needs to be replaced so fast and so furious?<\/p>\n
The company was preparing for a huge expansion.\u00a0 Firtst of all, there was a take over of another company scheduled later that month.\u00a0 That meant an almost sudden expansion of a few thousands of clients to a few tens of thousands of clients.\u00a0 Secondly, a huge marketing campaign was starting up.\u00a0 Thirdly, a few key processes were about to be change (the way new clients were registered).\u00a0 Fourthly, there was a huge need for automation and extended functionality.<\/p>\n
With all that, there was a very limited in-house expertise for the existing system.\u00a0\u00a0 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.\u00a0 And all the source code was there.<\/p>\n
Another big reason for a change, was realization of how tied in the MS CRM is into company’s network.\u00a0\u00a0 While considering the upgrade of the MS Dynamics CRM to version 4, most of the people were horrified of the upgrade path.\u00a0 In order to upgrade the CRM, we first had to upgrade our MS Exchange server.\u00a0 Which meant that we had to upgrade the operating system of the machine.\u00a0 Which meant that we had to upgrade the hardware of the server.\u00a0 And MS Exchange upgrade was strongly suggesting upgrades of quite a few other bits and pieces of software and hardware.\u00a0 Seemed like a really complicated and dangerous thing to do.<\/p>\n
One thing I wanted to note separately, was how easy it was to sell SugarCRM to the management.\u00a0 An open source stack meant that we were could be in full control of the system.\u00a0 Independent software pieces meant that we could replace and upgrade them separately.\u00a0 Fully web based technology aligned very well with many other company tools.\u00a0 The brief demonstration of SugarCRM got them totally convinced that the system is mature enough and can handle everything the company needs, and more.\u00a0 The fact that SugarCRM could be easily installed on a laptop in just a couple of minutes helped.\u00a0 The nice look and selection of themes in default out of the box installation helped.\u00a0 The selection of modules in out of the box installation helped.\u00a0 And the possibility to install the system with plenty of sample data helped too.\u00a0 Overall, it took me less than two hours to convince the management that the system is good enough.\u00a0 And I wasn’t even trying hard.<\/p>\n
So, what’s with the timing?\u00a0 We were given three weeks.\u00a0 In fact, it was two weeks for migration and one week for user training.\u00a0 We had to create a full replica of the existing system.\u00a0 Migrate all the data and implement all the functionality to support all the company processes.\u00a0 It took us three weeks to do it.\u00a0 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.<\/p>\n
It took the company about three weeks to integrate all the new data.\u00a0 Also, quite a few processes were changed.\u00a0 And the company grew plenty of new staff in all departments.<\/p>\n
When the question of the CRM migration was important once again, we had to face quite a few changes.\u00a0 Much much much more data than we originally tested with.\u00a0 A lot of changes in the ways things were handled.\u00a0 More users.\u00a0 And more automation was needed.\u00a0 The replica of the old CRM was of no value anymore.<\/p>\n
It took us about three more weeks to adjust our installation of SugarCRM to fit the new requirements.\u00a0 Even now that the migration is officially over, we still have quite a few things to fix and improve.\u00a0 But that’s going to be a live process from now on.<\/p>\n
The project was very challanging for the company, the team, and me personally.\u00a0 It was a complex thing technically, politically, and socially.\u00a0 For me personally, it was also quite a new management experience.\u00a0 I’ve been doing some project management before, but never had to deal with such magnitude, and especially in such tight deadlines.\u00a0 The good thing though was that I had to pick my own team.<\/p>\n
Here are a few things that I’ve learned and a few things that I think are important to share.<\/p>\n
Technology-wise, thanks heavens for CSV (file format for comma separated values).\u00a0 This was the backbone of the whole migration process.\u00a0 Here is how it works.\u00a0 MS Dynamics CRM has a very limited exporting functionality.\u00a0 Getting things directly out of the database don’t make sense at all.\u00a0 For example, our specific installation had more than 220 tables, all linked to each other.\u00a0 On top of that there were almost 400 views and something around 60 stored procedures.\u00a0 There was no way that we could analyze these structures in any meaningful period of time.\u00a0 So we did an export with entity by entity method.\u00a0 Gladly, there weren’t that many entities.\u00a0 There is a way to list all entities in tabular form, with columns upon columns of entity values.\u00a0 The only sensible format to save that list was in HTML.<\/p>\n
Because we had huge sets of data, MS CRM couldn’t handle the listing properly.\u00a0 So we had to come up with search criterias to separate the listings into smaller chunks of about 10,000 records each.\u00a0 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.<\/p>\n
The way to handle these HTML files was to load them up in MS Excel.\u00a0 It should also better be a newer version of Excel, that can handle more than 256 columns.\u00a0 MS Excel is wonderful at pasing those HTML tables, but it doesn’t offer much help in saving them in CSV.\u00a0 CSV in MS Excel is weird.\u00a0 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.\u00a0 OpenOffice is wonderful at saving CSV.\u00a0 You can pick and choose delimeters, quoting symbols, etc.\u00a0 The point is to make CSV files which can be parsed by PHP.<\/p>\n
Here are two things to remember.\u00a0 First, older Excel formats can’t work with more than 256 columns.\u00a0 The trick we were using was the sheets.\u00a0 Copy data of the extra columns into another sheet of the same file, and it will be much easier to process later.\u00a0 Second, if you will do several exports of data from MS CRM, remember that columsn won’t necessarily be in the same order.\u00a0 In fact, chances are that columns won’t be in the same order.\u00a0 The way go about it is to use hashes, or, sorry, associative arrays.\u00a0 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.\u00a0 Don’t use indexes for data, or you will have to inspect the order and adjust your SugarCRM importing scripts on every export.<\/p>\n
Another technical issue is the deployment process of SugarCRM.\u00a0 We are still in the mess with this and haven’t solved it properly.\u00a0 We used Subversion for the version control of all the files.\u00a0 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.\u00a0 When this happens, the database is modified, and a few local files are changed also.\u00a0 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.\u00a0 If you have any suggestions for this one, please let me know.<\/p>\n
Something else worth noting, is a very unpleasant behavior of SugarCRM in terms of usage tracking.\u00a0 Surely, there is an option to not send anything, but somehow I find it always sending way too much stuff out.\u00a0 And the worst part is that these things are hidden, and written in a way that makes them hard to find.\u00a0 have a look in include\/utils\/mvc_utils.php<\/em> and in include\/utils\/tracker_utils.hp<\/em> .\u00a0 These are the two ugliest places.\u00a0 There is another one which deals with update notifications, which I think I found, but didn’t have time to cut out yet.\u00a0 I am really surprised to see these in an open source project.\u00a0 And then again, I’m glad to have all the sources and being able to remove this stuff.\u00a0 Who knows what goes on in closed source applications…<\/p>\n In terms of project management, I’ve learned to talk to people a lot.\u00a0 Talking to all the people all the time was the bigger part of my participation in the project actually.\u00a0 I think I’ve spent close to 80% of time just talking.\u00a0 I was talking to the members of the team, trying to figure out the best ways to approach problems.\u00a0 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.\u00a0 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.\u00a0 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.\u00a0 I was also talking to a lot of\u00a0 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.<\/p>\n 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.<\/p>\n Also, talking helped a lot in extending deadlines where needed, and in keeping users calm.\u00a0 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.\u00a0 Sometimes I had to go into technical details explaining why certain things weren’t working or were working different from the previous system.\u00a0 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.<\/p>\n As I said, I did a lot of talking.\u00a0 More than during any other project that I ever handled.\u00a0 Of course, I was blessed with excellent teammates who were handling the technical parts of the project.\u00a0 One person was doing everything and anything about SugarCRM.\u00a0 Another one was working on all the exports, figuring out other systems and getting stuff off of them.\u00a0 He also helped a lot with integration of SugarCRM with the other company tools.\u00a0 Great guys both of them!<\/p>\n Now, this pretty much covers the migration process.\u00a0 But since the SugarCRM saga in our company is just started, expect more stuff posted.\u00a0 In the mean time, if you have any questions regarding any aspect of this migration, feel free to ask in the comments.<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n