Data Integration Between CE and F&O


This has been a really hot topic lately, especially with the addition of new Dual write features and the CDS coming with Dynamics 365 for Finance and Operations. I have given webinars on this topic a handful of times and presented this topic at various user group conferences around the world as well and it has always been a topic of interest. So, I figured, it was time to go ahead and make a blog post about it.
What is the Data Integrator

We have all seen this picture before. But what is missing are a few key circles.
  1. First we should add a circle for Dynamics 365 for Talent to both sides.
  2. It is possible to have two destinations but this diagram does not depict that scenario.
  3. The last thing you can't really see from the picture is the Power Query options to connect to 20+ other sources. This includes things like SQL Databases, Sales Force (Heaven Forbid you don't use Microsoft Dynamics 365 for your CRM :)), and Excel Spreadsheet just to name a few.
In a nutshell, Data Integrator is part of the PowerApps admin experience. You can access it by going to admin.powerapps.com and clicking on Data integration.
Data Integration Components
Next up, let's quickly review the data integration components. What my picture (and many others don't tell you is that you have to start with connection sets). 

  • Connection sets - Which environment to want to be the source and which environment should be the destination.
  • Projects - A set of one or more mappings for one or more entities. These are typically a logical grouping for a functional feature/form in the user interface.
  • Integration keys - This is where you tell the system which field is the "key" or index for a particular entity.
  • Mappings - This is where the magic happens. You tell the system exactly which field from your source points to which field in the destination. The good news is the templates usually have most of this figured out for you.
  • Schedule - The last component is where you tell the system when and how often to run. By default there is no schedule for a new project so you have to run it manually or create a schedule.
Microsoft Templates
Microsoft has so kindly created a number of different templates to support four major processes.
Unspoken Tips and Tricks
Some notes for you that you will want to consider:
  • Make sure to install the solutions for each template before attempting to set it up.
  • Consider using the advanced query option on your projects to "filter" down the results. This can allow you to do more complex mapping scenarios where you need an if this, then that type of mapping. Here is a link about the advanced queries since I don't cover it in my webinar. https://docs.microsoft.com/en-us/power-platform/admin/data-integrator#advanced-data-transformation-and-filtering
  • You will want to consider your business processes. The templates are only designed to work in a single direction. If you require multi-directional - consider using Dual Write instead.
  • If you require real-time integration, consider using Dual Write instead. You can schedule a job to run every one minute but the reality is the job takes more than one minute to run. You may also see performance issues with very high volumes if trying to run that often.
  • There is not solution/packaging option yet for Data Integration projects. So you cannot move them from one environment to another or share them with other users in your organization. For now that means, make sure to create them with a service account, and create templates to easily "copy" your setup from one environment to another. The only thing you will have to change is the connection set you want to point to.
Hopefully you have found this information to be helpful. If you would like to see my full presentation, it is available on the Dynamics 365 User Group site here.
You will need to be a member of the User Group to watch -- But membership is free and you will get so much benefit, so join now, if you have not already.
P.S. I will plan to write another article on the Dual Write features soon. As well as my Flowing with the Common Data Service series.

Comments