I recently had a requirement from a customer to setup direct data integration between Dynamics 365 Sales (CDS) and Finance and Operation (FO). In broad the requirement included:
- to streamline supply chain management across between two systems.
- crucial business-oriented Model driven, and Canvas apps need this information
at shorter intervals.
- stable but no code or at least less code solution was required.
- Can be scheduled and be able to utilize Change tracking to allow incremental export.
I have written this blog in such a way that if you follow the steps you can have smooth installation and integration experience between two systems.
As per requirement, Prospect to cash solution provided by Microsoft comes to rescue us.
Prospect to cash is Power Platform data integration feature provided by Microsoft to synchronize data between Finance and Operation (FO) and Dynamic365 Sales (CDS). It has prebuilt templates for synchronizing accounts, contacts, products, sales quotations, sales orders, and sales invoices among both systems.
The following illustration shows how the data is synchronized between Supply Chain Management and Sales.
It is available from CDS AppSource Download Dynamics 365, Prospect to Cash.
I recommend reading the Microsoft article data integration into Common Data Services for better understanding of different features used in this blog.
WHY (Prospect to Cash)
The reason for using Prospect for cash was the client has CDS as main CRM system and no implementation for Finance and Operation, so the only solution was Power Platform data integration to meet the requirements.
In fact initially what I recommend/propose is Dual write for Prospect to cash which is the definitely the recommended approach for integration between FO and CDS, but in our case the customer has CDS implementations and no Finance and Operations (FO just as external integration), hence Power Platform Data Integration (CDS) was the only best suited option.
Apart from that, Dual write has a very powerful Finance and Operations orchestration feature that supports real time/bi-directional integration between FO and CDS,not only for Sales module but also for Project Service Automation and Field Service modules.
In next blog we will address more about Dual write, so stay tuned. This article is specific to Prospect to cash solution’s integration to CDS.
Requirements Dynamics 365 Sales (CDS)
1. Dynamics 365 CE Sales version 1612 (188.8.131.52) (DB 184.108.40.206) online or a later version.
2. Make sure Change Tracking is enabled for any custom entity/standard entities.
3. Prospect to cash solution for Dynamics 365 Sales, version 220.127.116.11 or a later version.
Download from AppSource Download Dynamics 365, Prospect to Cash and must be installed.
Requirements Finance and Operations (FO)
Full detail can be found here, in short:
1. FO, Enterprise edition 7.3 (December 2017).
2. FO, Enterprise edition (July 2017) with hotfix (KB4045570).
3. FO version 1611 (November 2016) with hotfixes (KB4051266)
Installation Prospect to Cash
After installation of Dynamics 365, Prospect to Cash from appsource you must have the following basic templates available in Dynamics 365 Sales. The templates may vary depending on module/license you have for Sales (Pro/Enterprise). (this is just for demonstration purposes).
More information about all available templates can be found here.
Setting up CDS (sales)
Log into admin PowerApps and select your CDS environment for synchronization.
Next, go to Data > Connections, create two connections, one for CDS and one for FO as shown below.
Log into admin Powerplatform and navigate to Data Integration Tab. You can now create a new Connection set and new Project. For Project, select the template you want synchronization for.
The Integration (project)
Everything is ready and after creating new project, the final project looks like this:
Open the project and you will see a lot of options and I will recommend reading this article, as this will help you in understanding all the concepts mentioned below:
Refresh Entities: Used to refresh entities schema participating in task (both CDS and FO). Advance Query filtering: Opens Power Query editor (Power BI) for advance query. Tasks: Select entities for synchronization between source and destination systems. Scheduling: Provide scheduling facility for tasks also support email notification for errors. Execution history: History about all the executions of task and erros in the integration. Connections: Defines source and destination data sources and integrations key which handles over all integration. You can also update or add your own integration keys.
We have created the following task where source is FO (CustomerV2) and destination is CDS (Accounts) for demonstration purposes and to keep things simple. Now our task has following mappings as shown below:
Please note the task executes in UPSERT mode, means if integration could not find the CustomerAccount in CDS (account number) in CDS, then it will create new account otherwise if it matches then it will update that record and so in.
The last field in above mapping is function (Fn) because this field in FO contains text but the relevant CustomerGroup in CDS (destination) is option set so we must use mapping to convert from text to exact number, as shown below.
After running the integration task, we have following results in CDS coming from FO. (shown as demo data for demonstration).
Let us navigate to connections tab and click “Go to connections set details” link and we will see the Integration Keys used in the synchronization process for task.
Furthermore, as Change Tracking for standard entities in CDS are already enable so this solution enables incremental export. In an incremental export, only records that have changed are exported. Obviously, you can map as many fields as you want and can further define more task as well. Also, there is an Advance Query Filtering feature (Power BI) which you can utilize to add more columns or calculations on the columns as well.
Error Handling (resource)
There are two ways supported for viewing errors for tasks:
- Project list page with Dashboard (and you can drill down dashboard).
- On task page and execution tabs (you can also drill down to specific detail infomation). Every time a data integration project is executed (manually or scheduled), we can view the status of the execution on the project list page.
I recommend to use the schedule email notification alert for any errors after execution.
I hope this will be beneficial for the community and stay tuned for the next article Dual write which is also an amazing concept of data integration between two systems FO and CDS.
The goal of this blog was to illustrate the steps which make the integration process smooth for community along with explaining my experiences.
I hope this would be beneficial to the community!