This is the third article in a short series that is intended to explore the options available to a partner when moving a customer from an existing CRM or contact management system to Sage CRM on-premise.
In this article, I want to consider the options for extracting the data from a legacy CRM system. These options could range from coping with a flat-file download to only having API access to a SAAS system.
It is fair to say that software companies don't like the thought of losing a customer. When we come to the point of trying to move data from an old system to a new system we can discover that the designers of the software never really conceived that data would ever need to be extracted. I have come across very old systems that use proprietary data storage or less-than-obvious databases such as Pervasive. These tend to be very old systems but the challenge that they present can be found in newer systems. Sometimes you need to spend time researching a compatible ODBC driver and hope that you can export the data in a useable format.
And newer systems can be perverse. Some market-leading SAAS providers could be accused of deploying hostile design to discourage free and unimpeded access by a customer to their data. When a customer database can be exported it will be in a flat-file Excel or CSV files that are complete but do not correspond to the structure of the data as presented in the user interface. Or you may have to pay to gain access to APIs that give complete control over the data, essentially a type of 'ransom'.
I can find myself singing the words of Messrs Frey, Felder & Henley.
"Last thing I remember, I wasRunning for the doorI had to find the passage back to the place I was before'Relax' said the night man,'We are programmed to receive.You can check out any time you like,But you can never leave!'"
Whatever the limitations of the original legacy system our options will boil down to 3 possibilities.
Each option has its advantages and disadvantages.
Direct Database Access
Being able to access a full database is the best option. And this includes databases whether they are MS SQL Server databases, Access etc. If you can see the database within SQL Server Management Studio as a linked database or via an ODBC connection then 'we are in business'. The challenge, in this case, is understanding the differences in data structure and how tables and columns map from the source system to Sage CRM. But that is a topic for another article.
Access via APIs
It doesn't matter whether the API is REST or SOAP or whatever. We need to be able to extract the business data. If you get the data in XML, JSON or you are only able to get the data in a CSV type file export, it gives you the data and that is the most important thing. The next question is whether the API returns the primary key and foreign key information for that business data as it is queried. If it does then you will then be able to maintain referential integrity as the data is imported into Sage CRM.
Does the source have custom entities? This is data that will need to be mapped to Sage CRM.Is the data controlled by implicit business rules? Are their data ranges and strict selection lists defined for the data? Can you access those rules?
Access via Exports
I think this is potentially the least desirable option.
We may be limited to only accessing pre-built default exports and reports. These may not give us all the data we need and especially because data like primary key values and foreign key values are not typically included in reports or exports then we can end up with little more than a series of flat files that we would have to then have to process.
In my next article, I will consider what we need to migrate. This is because a migration project just like any project needs to consider its scope and time and resources available. For the migration of legacy data to Sage CRM, we need to take quite a cold hard look at what needs to be brought across whether that is Business Data, Documents, Custom Entities and Business Rules.
Sage CRM 2019 R2: Migrating to Sage CRM