Integrations often pose the greatest complexity in CRM projects. This post shares the key considerations, common challenges, and practical strategies for overcoming these hurdles.

To simplify your CRM project, it’s recommended to consolidate data using Microsoft’s common data service, Dataverse. This enables easy access across various Microsoft Business Applications, including Dynamics 365, Power Apps, Outlook, and MS Teams.

You may also want your Dynamics CRM system to connect with external data sources, such as ERP systems, websites, SQL databases and others. In these scenarios, integration is  crucial to achieve a unified customer view across all touchpoints and align business processes.

1. Integration vs Migration: Understanding the Difference.

Firstly, let’s clarify the concepts of integration and migration.

Migration refers to the process of transferring data from one system to another. It may involve multiple iterations throughout a project timeline, including trial migrations.

Data migration is a one-time activity that involves extracting, transforming, and loading data into a new system. Depending on data complexity, migration can be executed using spreadsheets or may require a more sophisticated solution to address anomalies before transferring data between systems.

Integration involves linking your CRM system with a third-party application. It establishes a continuous data flow between these systems, eliminating the need for duplicated data entry and enabling seamless handling of routine workflows.

In some cases, integration could support a migration, where data is initially transferred into CRM and periodically refreshed. However, addressing data inconsistencies often requires dedicated migration tools to identify and resolve these issues before the integration can occur.

2. Types of Integration: Tailoring the Approach to Your Needs.

The method of connecting two systems will depend on the specific requirements of your organisation and the desired outcomes. Determining why the data is needed in the destination system is crucial.

In many cases, there will be a requirement to interact with the data and update it within the context of a business process. For these scenarios, we can develop an integration solution that writes the data into Dataverse, enabling it to be edited through Dynamics 365.

However, the integration approach will vary if users only need to view the data without making any modifications. We can implement integrations that provide read-only access to the data in the target system. For example, a Power BI report could be embedded within a record, querying an external data source and filtering results to match the record context. This enables users to view relevant data without extensive editing capabilities seamlessly.

3. How Virtual Tables can simplify integrations.

Virtual tables (virtual entities) are a relatively new approach for completing an integration by representing data from an external system as tables in Microsoft Dataverse. This is achieved without data replication and often without needing custom coding.

Virtual tables can create, read, update, and delete operations, offering 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.

4. Understanding the Business Process Across Systems.

Before starting with an integration, it is essential to understand the process involved clearly. This is where ANS can work with you to map out each step and identify the specific requirements.

Let’s consider the traditional sales process as an example. It typically starts with a lead, converted into an account, contact, and opportunity. Multiple quotes are generated, each with a price list containing various product combinations and units of measure. Once the deal is won, an order is created, including lines, products, and units of measurement. This order is then passed on to an ERP or accounts system for fulfilment.

However, the process continues. The order must be fulfilled, and an invoice should appear in CRM to enable the sales team, who may need access to the ERP system, to track the customer’s payment status.

This example highlights the primary source of integration complexity. It’s important to note that the number of tables, fields, or records involved does not solely determine complexity. Complexity occurs when combining different data models of two or more systems to ensure that the correct data is accurately mapped between these.

5. Which system is master, and in what direction does data flow?

One system should be designated as the “owner” of a particular record, such as a contact.

We’ve encountered numerous instances where organisations maintained different versions of contact records in CRM, marketing systems, ERP and spreadsheets.

To establish a single source of truth, it is crucial to enforce control over which systems can update the central “master” representation of each record.

Typically, CRM is the master for contact records. ERP will serve as the master for products, handling the transfer of product and pricing details to CRM. Depending on the specific process, these values may be set as “Read Only” in Dynamics 365 CRM.

Managing a one-way integration is relatively straightforward, but complexities arise when a two-way (bi-directional) integration is required.

For instance, if a record is updated in system A, the change must be synchronised with system B, and vice versa. But what happens if two users simultaneously modify the same record in both systems? Which change should take precedence? While this may seem unlikely, such scenarios do occur, and a robust integration must account for these contingencies.

Furthermore, complexity arises when integrating more than two systems, such as a website, order management system, and CRM. Each system maintains its concept of a customer. As a result, it’s essential to ensure accurate and timely data sharing to keep customer information up to date.

6. Understanding Data & Assessing Data Quality.

Poor quality data is a frequent barrier which should be addressed before any integration is implemented. Data management will always remain the responsibility of the customer. That’s because no other organisation will know the data as well as you.

As part of our data review and checks, we will look at the following:

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 defines 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 its 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 the Integer field, a date/time entry is captured as text, or putting a 200-character string into a 100-character length field. These scenarios will cause problems and will be carefully considered as part of our design mapping between systems.

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 months of data from a relational database, but broader consideration should be made of the relationships involved to preserve referential integrity. An integrated record may depend on another record that falls outside the defined time frame, so what should be done in these situations? For example, should the related record be brought across, even though it’s outside the specified window, or do we leave that field as null?

7. Comparing data models for integration.

On the face of it, different systems may have the same entities, but how they are used in each may vary significantly.

Returning to the sales process example, an ERP system would generally be expected to look after a “product catalogue”. This would include Products (Items), Units of Measure, and Price Lists. These will be synchronised from ERP to CRM in one direction. CRM also has the concept of Products, Units of Measure and Price Lists, which are used differently.

The Dynamics 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 with special pricing, a price list will include every product you want to sell 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”, but if you agree to 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 to a list just to set a special price on one item.

When you create an Order in the ERP system, when adding the product to the order, the system looks through the default pricelist AND any special pricing to select the best price for the customer.

8. Determining the data update frequency.

This is another crucial factor in determining how complex an integration will be. If real-time data is needed, a form of orchestration must be built to push this to the target system. Real-time integrations 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 need to be in ERP a second later? To simplify the complexity involved in this process, will that second make any operational difference? If not, what will be a good 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 process tolerance. As an example, an order integration process may be set to run every 10 mins, but sync processes to add new products may only be 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 the transaction.
• A Pull Model – polls the source system periodically and passes the records to the target.

9. 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. If items haven’t changed, this will be a wasted effort and unnecessary cloud consumption. Also, depending on the data size, 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 with 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 different approach will need to be agreed upon, such as a Shadow Table.

10. How will an external system be connected?

All third-party systems have different ways of integrating with CRM, but only some systems have 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 APIs that we encounter reflect common industry standards, such as Odata, custom Rest based or SOAP. But there is nothing to stop developers from writing their way of talking to their system.

Direct database access may be an option, provided an appropriate database driver is accessible, and data is only being read in the source system. Create, update and delete operations will only ordinarily be directly written to the database if 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 typically only be used for larger-scale integrations involving multiple systems.

We would fully document this process regardless of how external systems are connected.

11. Error Handling.

At some point, integration will fail, and often this will be caused by insufficient data. If a process fails because of a transient error, can we retry? How often should this be done before we give up? When a failure occurs, who should be notified? What actions should be taken to correct the issue and potentially retry?

These considerations will define an integration process and its accompanying documentation.

12. Requirement-led integrations.

In developing integrations, we will be led by the requirements for what is sought and the acceptable level of risk to a client – our choice of technology will always be secondary.

For example, building integration in Power Automate is adequate 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 to integrations.

Need more information?

Whether you’re looking to build out more integrations for your Dynamics 365 environment or you currently have a CRM integration that isn’t working for you, please do reach out to us.