Common Data Service – Data integrator - Basics

Overview

Common data service (CDS) is an application platform. It provides a place for other applications like Dynamics 365 for Sales (simply called CRM here) & Dynamics 365 for Finance and Operations (simply called AX here) to export / import data to/from.

Data integrator (DI) is a tool built on CDS platform. It provides a place to manage the data integration between two systems. It has the option to define multiple system connections, define projects for integrations, define task within those projects, define mappings between entities, and provision for execution & monitoring of integration.

Please note that DI is not a data migration tool. It currently has limitations on importing data into CRM and will NOT scale up for heavy incoming load.

Disclaimer: The information mentioned in the blog is based upon my understanding of the tool at the time of writing of the blog. Use it to familiarize yourself with the tool but do NOT use it as a formal document or advice for production environment. No warranties attached.

Licensing

At least one power app P2 license is required to model data initially.

At least one P1 license is required to connect the applications to CDS.

However, these licenses are already part of Dynamics product licenses. So please check on what’s included in your existing licenses before purchasing any new license.

Pre-requisites

Required licenses (as described above)

Access to Dynamics 365 for Sales instance

Access to Dynamics 365 for Finance & operations instance

Access to power app portal with optional access to power query.

Quick walk-through

Apply prospect to cash solution to CRM. This is required to get the initial templates in the DI. Download the solution from customer source. Run the exe file and finish setup by providing required details. 

https://mbs.microsoft.com/customersource/Global/365Enterprise/downloads/product-releases/MD365FNOPENTProspectToCash

Login the power app portal. https://powerapps.microsoft.com/

Click Data > Connections on the left menu bar. Create two connections one each for AX & CRM.

Go to Power app admin center (https://admin.powerapps.com/)

Click on ‘Data Integration’ on left menu bar. Click on ‘Connection sets’  and click on ‘New connection set’ on the right side. Give a name to the connection set. Choose both AX & CRM connections in first & second connection. The connections which you have previously created in power apps will show up in dropdown here. In the ‘Organizations’ section provide the ‘AX company’ for AX side of connection and ‘Business unit’ on CRM side of connection. Data will transfer between the given AX company and business unit in this connection set. Click Create.

Once connection set is created, click on it and go to ‘Integration key’. Here you need to define the natural key for each entity with which CRM GUID need to be replaced with.

After creation of connection set, whenever you publish an entity from CRM, you need to come here and set the integration key. (If primary key is changed)

Back on DI, click on ‘New project’ to start a new data integration project. Provide a name to the project and select a template. These templates will come from the ‘Prospect to cash’ solution previously deployed. Currently it is mandatory to select a template. Once the project is created you can delete the template tasks and add your own tasks. While selecting the template make sure the direction of data flow is correct e.g. if you want to send data from AX to CRM, select a template similar to that.

Next, select the connection set previously created, the organizations between which data needs to be flown and provide confirmation.

On project creation, click its name to open it. You’ll find the default task(s) available as provided in the template. You can choose to use it or delete it. Add your own task from ‘Add task’. Each task is a mapping between two entities of AX & CRM and tells the system to flow data between them.

Provide a name to the task, select both AX and CRM entities and Organizations. Then click Create.

Once task is created, you can create/edit mappings between entity fields by clicking on arrow symbol (→) Here you can define mapping between fields and define simple transforms. Click on equal symbol (=) to provide transforms. 

More complex transforms can be done via power query feature which require special white-listing of your project from Microsoft at the moment.

After all tasks are defined, you can click on ‘Run project’ to run all the tasks immediately.

If you want the recurring execution of the tasks, click on ‘Scheduling’ and provide the recurrence pattern.

Once the project is run, click on ‘Execution history’ to see execution details. The errors can be drilled down into the history lines.

That’s it for this blog. This basic information should be good enough to get you started on DI.

There are advanced topics like ‘Change tracking & incremental update’, ‘Advance transformations & filtering’, Sync patterns, Performance tuning, Dashboards & troubleshooting which I’ll try to address in upcoming blogs.