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 was
Running for the door
I 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.
- We have direct access to the database.
- We can access data via APIs
- We can access data via Exports
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
- Starting to explore the options available to a partner when moving a customer from an existing CRM or contact management system to Sage CRM.
- What are the typical types of older system? What is the typical database size and complexity?
- Partners should understand the size of the problem and the opportunity. Getting a sense of typical system can help with planning especially when they may be several customers that need to be migrated and it is desirable to standardise the approach.
- What are the options for extracting the data from a legacy CRM system?
- This could range from a flat-file download to only API access.
- What do we need to migrate?
- Any project needs to consider its scope with 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.
- What are the data differences (structure, terminology etc) between the legacy system and a Sage CRM database?
- We need to look at the differences in data structures and objects to understand the work that needs to be carried out to all the migration to succeed.
- How will we maintain data integrity?
- How will you maintain the integrity of data especially referential integrity as the data moves from one database to another? We want to be able to ensure that all communications, opportunities, notes etc all remain the children of the correct company and person entities and that no orphaned or widowed records are created.
- What 3rd Party options and partner expertise is available?
- This last article will look at the different migration tools and services that are available within the partner community.