This is the sixth 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 will consider how data integrity can be maintained as the data is migrated. We want to be able to ensure no orphaned or widowed records are created as the data is moved from the legacy system to Sage CRM.
We have explored previously in this series that our options for accessing the data of the original CRM system boil down to 3 possibilities.
And whatever form we can access the source data it may only have structures that are functionally equivalent to Sage CRM's structure.
It is important to remember that within Sage CRM the term Entity, although it can mean just the physical table, actually means the larger scale business object that contains all information a user needs to understand that thing. To be useful the entity Company must include all the persons connected to that company, their names and addresses, phone and emails.
The data sources we may wish to migrate from another CRM system to Sage CRM represent real and conceptual entities. A Company is an example of a concrete Entity as are Persons; a conceptual entity would be an Opportunity or a Case. Sage CRM doesn't make any distinction in the features it provides for either conceptual or concrete Entities. For example, when processing the data of a conceptual Opportunity entity a user will have very similar features available as to when that user is finally able to record a concrete Order against the Opportunity.
A legacy CRM system may only have endpoints that approximate to Sage CRM structures.
GET /accountsGET /contactsGET /leadsGET /opportunitiesGET /users
We, therefore, have to consider that when data is imported into Sage CRM from another system it would typically need to be transformed to match the Sage CRM data model. For example, an entity representing a customer would need to be split over multiple tables such as company, person, person_link, address, address_link, phone and email.
The correct import or migration of relational data will be key. Each of these tables will be related to each other through the use of primary and foreign key values. This is the stuff that binds everything together.
Data Integrity in Application Data
One of the great strengths of Sage CRM is its openness. Its data model is documented and published and available to members of the Developer Program.
In some CRM systems, the relationships between the different entities may be fixed or defined within the database. This is not the case within Sage CRM for the application tables.
This was a conscious decision made by the original designers of Sage CRM. The application is not a static application system but is a very flexible and customizable engine to create any kind of business process automation where entities and fields can be added at run-time in an ad-hoc manner from within the application.
Most records within Sage CRM exist in complexes. For example, a company will have child records as you can see from the section of the data model below.
The correct import or migration of relational data will be key. This is the stuff that binds everything together. You can have an initial load of companies, contacts, leads and address, and even 'notes' through the basic data import mechanism but once we start thinking about activities like opportunities, cases, communications, custom entities etc and then the relationships that bind everything together become the priority.
You will need to make sure that the data demonstrates integrity - that the existing primary keys are in place and unique. You need to make sure that referential integrity is clear. That is both that child records where necessary have valid foreign key information and that parental records have whatever necessary child records associated with them. You do not want to import addresses that are not associated with companies and you do not want to import companies without addresses.
You, therefore, need to think about the mapping of the data from the legacy CRM system to the new Sage CRM database. For example when thinking about addresses, phone or email's then the link tables will need to be populated.
You will have to decide the way in which the existing systems relationships will be maintained. So this may require you creating new fields in the target Sage CRM database to hold the old primary key and old foreign information within the source system so that after importing into the database the relationships can be re-established by running update SQL statements on the Sage CRM foreign keys based on the 'old' foreign values.
The image above shows just the tables used in the definition of the Company Entity within Sage CRM.
In a default Sage CRM instance, the following base relationships will exist.
A company record is directly referenced in the following 'Child' tables
The link tables in turn link to other tables
The case and opportunity tables exist in workflows
Depending on the features implemented
It is good news that Sage CRM uses standard SQL Server Identity which means when it comes to importing or migrating data we will have a much easier time of it.
But however, you decide to migrate the data from the legacy CRM system, data cleansing is the big first job that needs to be done.
The final article in the series will consider the 3rd Party options and partner expertise available to customers and consider the different migration tools and services that can be used.
Sage CRM 2019 R2: Migrating to Sage CRM