Architecture CDATA (ODBC) integration, to integrate with Dynamics 365, Microsoft CRM and Salesforce
Many of our customers have many questions on integration and data migration. How can you integrate with Dynamics 365? How can you integrate with Microsoft CRM online? Can you integrate with Salesforce? Can you…?
One of the big challenges in Dynamics 365 for Finance and Operations is that code deliveries are not easy to manage and can take a lot of time before you get it in production. Therefore, code development is not the way to move forward. One of our goals is to help you, as the IT team is to create integrations without the need of writing code! We have built-in support for web services to avoid code development but we see that many IT teams don’t have experience with REST API for example JSON. ODBC is a very commonly used solution for integrations by IT teams and with our partners and with the CDATA solution, we can use that again. This will simplify many integrations and make the integration with Microsoft CRM extremely easy. As a sample: We used it to integrate with Microsoft Field service and in 2 hours we had an integration available to send customers and the linked service objects.
In this blog, I want to explain how CDATA works and the architecture behind it.
The principles – See the world as a database
When we use Dynamics 365 for Finance and Operations or Microsoft Dynamics AX 2012 and we want to integrate with Microsoft CRM, we can use the CDATA ODBC driver to integrate in an easy way. You need to buy the specific driver from CDATA and you need to install the driver. You can use the standard setup to connect, using ODBC as described here: Tutorial: How to Connect From Microsoft Dynamics AX 7 to On-Premise Databases using Connectivity Studio. The driver can be set up to connect to your environment.
Using Connectivity Studio, we can now send data to Microsoft CRM. The ODBC driver from CDATA will transform the ODBC request into the right service calls to use the ODATA or REST API to connect to Microsoft CRM. That is the complexity what it solves and saves a lot of time. The benefits of the ODBC driver using CDATA:
- No difference between on-premise and cloud setup
- Easy setup using ODBC
- Joins, inserts/updates
- Temporary data is stored where you install the ODBC driver (Legal)
- No setup required to store ERP keys in CRM (Saves customizations)
- No extra mapping in integration software like BizTalk, Logic Apps or other CRM integrators
There were some challenges that we had faced with the integration. One of the challenges was that records in an ERP application could have ID fields like a sales ID, customer ID and so on but these fields are not always available in Microsoft CRM so we need to define a link between the GUID in CRM and the record in ERP. You can create additional fields in D365 CRM or you can use our out-of-the-box functionality for external ID’s. When an update is required using the ODBC driver, the internal ID must be specified as key field in our ODBC document.
When a record is exported from Dynamics 365 for Finance and Operations, it will insert a record using the normal ‘insert into a’ command from ODBC. The key is requested after the insert and can be used to fill the transformation table that stores the Record ID (internal) and the ID field from D365 CRM.
In the sample above, we refer to Microsoft Dynamics CRM / D365 but you can buy drivers for many other applications:
and many more as shown here: https://www.cdata.com/odbc/