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.

  1. We have direct access to the database.
  2. We can access data via APIs
  3. We can access data via Exports

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 /accounts
GET /contacts
GET /leads
GET /opportunities
GET /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.

  • Company
  • Person
  • Person_Link
  • Address
  • Address_Link
  • Phone
  • PhoneLink
  • Email
  • EmailLink

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

  • Account (acc_companyid) - only certain Sage CRM integrated systems
  • Address_Link (adli_companyid)
  • Case (case_primarycompanyid)
  • Comm_link (cmli_comm_companyid)
  • Email (emai_companyid)
  • Lead (lead_primarycompanyid)
  • Library (libr_companyid)
  • Marketing (mrkt_companyid)
  • Opportunity (oppo_primarycompanyid)
  • Person_Link (peli_companyid)
  • Person (pers_companyid)
  • Phone (phon_companyid)

The link tables in turn link to other tables

  • Address_Link to the Address table
  • Comm_link to the Communication table which in turn may link to recurrences
  • Person_Link to the Person table

The case and opportunity tables exist in workflows

  • Cases link to CaseProgress
  • Cases link to SolutionLink which in turn link to Solutions
  • Opportunity links to OpportunityHistory
  • Opportunity links to OpportunityProgress

Depending on the features implemented

  • Opportunity links to Quotes which in turn link to QuoteItems
  • Opportunity links to Orders which in turn link to OrderItems

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.

  • Is the existing data is in good shape?
  • Is there any duplicate data and have you made sure that any fields that will be mandatory within Sage CRM are complete?

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

  1. Introduction
    • Starting to explore the options available to a partner when moving a customer from an existing CRM or contact management system to Sage CRM.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.

  7. 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.