……..by Paul Rutter, Head of Product Management at Preact.
As any IT manager will tell you, integrating CRM projects with external data sources can be one of the biggest pain points of an installation.
Creating a single customer view across all touch points when linking your CRM with websites, ERP systems, SQL databases and other data sources is crucial, but sometimes difficult to get right.
Paul Rutter, Head of Product Management at Preact, who are specialists in implementing and supporting CRM solutions, has come up with 12 top tips and points to consider to help you manage this complex part of your CRM project and overcome some of the common challenges that crop up.
Preact is a top-ranked Microsoft provider supporting businesses through Microsoft Dynamics 365 solutions built on the power platform. This year the company was awarded the Microsoft Business Applications Inner Circle award for the fourth year running.
- Integration vs Migration
Let’s start by defining what we mean by integration and how this differs from migration.
A migration is a process to move data from one system to another. A migration may be run multiple times during a project timeline to conduct trial migrations, but this will be one-off process to extract, transform and load data into a new system. A migration can be handled from a series of spreadsheets or require a more sophisticated solution to address anomalies before moving data between systems.
An integration is the bringing together of your CRM system with a third-party application for continuing benefit. This is an ongoing process, allowing data to pass between these systems which removes duplicated data entry and allows routine workflows to be efficiently handled as data seamlessly flows between these.
An integration could potentially also be used to handle a migration whereby data will flow into CRM and then periodically refresh. But data inconsistencies often require dedicated migration tools to first identify and then resolve these issues.
- Types of Integration
How two systems are joined together will be dependent on an organisation’s requirements and what needs to be achieved. Specifically, why is this data needed in the destination system?
Often there is a need to interact with data to update this within the context of a business process. In these scenarios, it’s possible to develop an integration that will write data into Dataverse so it can be edited through Dynamics 365 or another model-driven app.
However, if users just need to view this data, the integration solution will differ.
In these instances, an integration can be implemented that will expose data in the target system on a read-only basis. For example, a Power BI report could be embedded in a record which queries the external data source and filters the result down to the context of the current record.
- How Virtual Tables can simplify integrations
Virtual tables (also known as virtual entities) are a relatively new approach for completing an integration by representing data from external system as tables in Microsoft Dataverse, without data replication and often without needing custom coding.
Virtual tables can create, read, update, and delete operations which offers the prospect of simplified set-up and reduced ongoing integration admin. This can replace client-side or server-side methods to integrate external data which rely on customised coding and may present limitations.
- What is the business process across these systems?
Before any integration can be built, it needs to be clearly understood by all parties.
Think about the traditional sales process. A lead comes in and is converted to an Account, Contact and Opportunity. Multiple quotes are raised with a pricelist with varying combinations of products and units of measure.
Once the deal is won, an Order with lines, products, units of measure is created and passed to an ERP / accounts system for fulfilment. But this isn’t the end of the process because the order must be fulfilled and an Invoice should appear in CRM so that the sales team, who don’t have ERP access, can see if the customer has paid.
This is an example of the main source of integration complexity, and it’s important to note that complexity isn’t related to how many tables, fields or records are involved. Complexity occurs when combining different data models of two, or more, systems to ensure that the correct data is accurately mapped between these in a timely manner to support the business process.
- Which system is master and what direction does data flow?
In distributed computing we refer to the separation of concerns – essentially, each system should be responsible for a discreet piece of functionality or logic – we should never replicate this logic across multiple systems and expect the same answer. The same can be applied to integrating data. Ultimately, one system should “own” a record such as a contact.
We’ve seen numerous examples over the years where organisations held different version of a contact record in CRM, another in their marketing system, in ERP and in multiple spreadsheets…
To ensure a single source of truth, control must be enforced around which systems can update the central “master” representation of each record. For a contact record, this will usually be the CRM system and for products, an ERP system will typically be the master, where product and pricing detail is passed from ERP to CRM. Depending on the precise process these values may be set as Read Only in CRM.
A one-way or uni-directional integration is relatively straightforward to manage, but greater complexity will creep in where two-way (bi-directional) integration is needed.
For example, if a record is updated in system A, the change will need to be passed to system B, and vice versa. But what happens if two users change the same record in each system at the same time? Which one should win? This may seem an unlikely, but these scenarios do occur, and a robust integration must plan for these contingencies.
The level of complexity will increase further if more than two systems are being integrated, for example a website, order management system and CRM. Each will have a concept of a customer which must be kept up to date so data must be shared correctly across these.
- Understanding Data & Assessing Data Quality
Poor quality data is frequent barrier which should be addressed before any integration is implemented. Ultimately, data management will always remain the ownership and responsibility of the customer because no other organisation will know the data as well as you.
It’s important to include the following as part of your reviews and checks:
Unique data keys – a unique property must be established for each record. A Globally Unique Identifier (GUID) is best, but any “primary Key” will suffice which can even be made up of compound keys. When a record needs to be updated, the unique identifier is used to define the target.
- Duplicate records – these are frequently seen in the context of the multiple versions of the “same” contact or account. But if each has their own unique key, which is correct? This is another instance where data ownership will always rest with the client.
- What are the data types and lengths – the underlying schema and format of data in the various systems can often be a frequent source of errors. For example, if there is an attempt to pass a decimal number to Integer field, or a date/time entry being captured as text. Or, putting a 200-character string into a 100-character length field. All of these scenarios can potentially cause problems.
- Do mandatory fields have a value? – Are there any NULL fields in one system that we need to use in a business process in another system? If so, how will these be handled?
- How will data be filtered? – There may only be a need to integrate just the most recent x months of data from a relational database, but wider consideration should be made of the relationships involved so referential integrity can be preserved. An integrated record may be dependent on another record that falls outside the defined time frame, so in these situations what should be done? For example, should the related record be brought across, even though its outside the specified window, or do we just leave that field as null?
- Comparing data models
On the face of it, different systems may have the same entities, but the way these are used in each may vary greatly.
Going back to the sales process example again, an ERP system would normally be expected to look after “product catalogue” that will include Products (Items), Units of Measure, Price Lists. These would be synchronised from ERP to CRM in one direction. CRM also has the concept of Products, Units of Measure and Price Lists but these are used in a different way.
The Dynamics 365 Sales product catalogue is built on a model, whereby, if you want to sell a customer a product, the product must exist on the pricelist associated with the order. For customers who have special pricing, you create a price list for that customer to includes every product that you want to sell them at the revised pricing. Across multiple products, this can become a significant admin overhead.
Often, ERP systems take a different approach. You may have a price list with products, or “Items” on but, if you agree special pricing for a customer, you configure that one product with a special price against the customer.
As a result, unlike Dynamics 365 Sales, you don’t need to add multiple products into a list just to set a special price on one item. When you create an Order in the ERP system, at the point of adding the product to the order, the system looks through the default pricelist AND any special pricing a customer may have agreed and selects the best price for the customer.
- How frequently does the data need to be updated?
This is another crucial factor in determining how complex the integration will be. If the data needs to be available in the other system in real-time, a form of orchestration will need to be built to push data to the target system. Real-time integrations prompt additional questions, and inevitably result in greater complexity, around error handling, retry logic and what is presented to a user.
Generally, most integrated business processes can tolerate some delay in the system. If a salesperson places an order in CRM, does it really need to be in ERP a second later? To simplify the complexity involved in this process, will that second really make any operational difference? If not, what will be an acceptable time for this entry to be picked up in the other system? A minute’s delay? Or 30 minutes?
The frequency that an integration will run depends entirely on the business processes tolerance. For example, new order integration processes may be set to run every 10 mins, but sync processes to add new products maybe only needed once each day.
The tolerance in this process will determine which integration model is adopted:
- A Push Model – sends data from source to target and receives an acknowledgement before continuing with the transaction.
- A Pull Model – polls the source system periodically and passes the records to the target.
- How is change identified?
When dealing with a “Pull Model”, identification of change in a system is crucial. That’s because we want to avoid synchronising every record each time the integration runs. This will be wasted effort and unnecessary cloud consumption if items haven’t changed. Also, depending on the size of data, a complete sync process may take several hours but business rules may dictate that data should never be more than an hour old in both systems.
Some examples of net change pattern identifications include:
- Last modified timestamp – Each record is date/time stamped with the last time it was modified. The system can then be polled to query any records that have changed since this process last ran.
- Status Flag – A status flag can be added to the records so that when the integration runs, only the records in a status of new or updated are requested. For example: N = new record, U = updated record and P = processed.
- Change tracking – Dynamics 365 has the concept of change tracking for entities and when enabled this allows tokens to be used that will retrieve the changes since the last time you checked.
- Particular attention will need to be given to record deletions in these processes. Change tracking can be used if the source table is Dynamics 365, but for other systems once data is deleted, it’s gone so a separate approach will need to be agreed, such as a Shadow Table.
- How will an external system be connected?
All third-party systems have different ways of integrating with CRM, but not every system has an API we can talk to.
Most modern systems do have some form of API that we can connect, but this will be limited to what the creator of the third-party system has exposed via their API. The majority of API’s that Preact encounter reflect common industry standards, such as: Odata, custom Rest based or SOAP. But there is nothing to stop a developer writing their own way of talking to their system.
Direct database access may be an available option, provided an appropriate database driver is accessible and data is only being read in the source system. Create, update and delete operations will not ordinarily be directly written to the database unless we have complete agreement and acknowledgement of the risks from the vendor/client.
Other ways to pass data between systems can include message queues and service buses that will loosely couple discrete systems, but these would normally only be used for larger scale integrations involving multiple systems.
- Error Handling
At some point, an integration will fail and often this will be caused by bad data. If a process fails because of a transient error, can we retry? If so, how many times should this be done before we give up. If a failure occurs, who should be notified? What actions should be taken to correct the issue and potentially retry?
These are further considerations that will define an integration process and its accompanying documentation.
- Requirement led integrations
In developing integrations, it’s important to consider an acceptable level of risk, with the choice of technology a secondary consideration.
For example, building an integration in Power Automate is effective for some requirements, but this isn’t recommended for handling any integrations where a highly fault tolerant technology is needed. As the analogy goes, you shouldn’t knock a screw in with a hammer, you could technically do it, but it’s not the right tool for the job. The same applies for integrations.
To find out more about Preact, visit https://www.preact.co.uk/
Founded in 1993, Preact is a leading specialist in implementing and supporting CRM solutions. As a top-ranked Microsoft provider, Maidenhead-based Preact supports small and medium sized businesses in advancing their digital transformation through Microsoft Dynamics 365 and solutions built on the Power Platform. The company holds multiple Microsoft Gold Certifications and has been awarded the elite Inner Circle status for Microsoft Business Applications each year since 2018. Preact’s solutions are trusted by more than 10,000 Dynamics 365 users.