Integrate Business Central data (Onprem&SaaS) in PowerPlateform through CDS

Already eard about Microsoft CDS ?

Since Business Central V16 + you can synchronise Business central data into the CDS entities on Microsoft cloud, in order to use them with the power plateform tools collection (power automate, virtual agent, power BI …) and it’s also working with Onpremise Business central
NB : it was in preview version state for early V16 and general availability in V17 (see here)

Setup CDS Integration in Business Central

The following Microsoft video explain how to setup CDS integrations jobs in Business central.

Requirements you must know

  • Be system administrator of your Power Plateform environnement
  • Also require another Power plateform user without the system admin role (will be use by the connector to modify CDS records)
  • You can NOT modify the Microsoft default fields and table mapping without development :
    • Contacts
    • Customers
      • Payment Terms
      • Shipping Agent
      • Shipment Method
    • Vendor
    • Currency

Monitor Synchronizations

Once you complete the assisted setup, you should access thoses pages to monitor the CDS integration jobs :

  • “Common Data Integration Service Setup” : Show the CDS connection setup you’ve made and some more informations
  • “Integration Table mappings” : Show the tables mappings, actions allows you to force synchronize, show fields mapping, modify filters and direction. Other fileds aren’t editables

    • See the action “Integration Synch. Job Log” to verify the numbers of records synchronized and failed (the failed counter is a flow field you can click to view more details)
  • “Job Queue entry” to check the background tasks for CDS integration are running correctly. There is one job queue entry for each table mapping so you can modify the recurrence as you want, default is 30 minutes

Check datas in the CDS

Once the integrations tasks are complete, you can go into your Power plateform environnement to check-up the CDS datas. In my case I’ve made a csv export.

Note that you can also view this data directly from business central (search for “Account – Common Data Service” page) but still buggy in the early V16 versions.

How it’s working, technicaly

The Business central CDS integration work exactly the same as the CRM integration system.

Record are synchronized with job queue tasks, following the mappings setup, filters and last modification date.

  • This system lean on Integration ID’s in order to make the link betwen CDS records and Business Central Record (basicaly just a table containing Table ID, BC record ID and CDS record ID)
    Integration ID’s are generated on the fly in business central when you modify or insert record belonging to the mappings. during the assisted setup, the process need to generate the Integration ID’s of existings record in order to synch. them
  • Direction : integration can be in 3 modes : “From integration” (external->BC), “To Integration” (BC->External) and “Bidirectionnal”.
    Beware of the bidirectionnal mode, when this’s selected you can be in trouble when both BC and CDS record are modified betwen 2 synch. In this case the record will be in failed log and put in skipped record buffer
  • Skipped Synch. Records : this buffer can be filled with the problem previously explain or in some case of wrong synch. order of records with tables relations. If you add Sales header & lines tables mapping and a job try to synch. the lines before the header. The link with header Integration ID’s can’t be found and the record is skipped.
    Skipped record can be synchronized later manually, I recommand to create a custom job queue for doing it regulary

Modify the CDS integration mappings

Here come the hard part

As said before, you will be forced to develop an extension in order to add or modify the tables and fields mappings with some particular technical skills
1. Create a new “integration” table (virtual table pointing on the CDS entity) or extend existing standard “integration table” to add fields with AL Table Proxy Generator
2. Extend the mapping tables and fields pages in order to allow users to add/edit the mappings, or through an update mapping button
3. Subscride to events for custom fields mapping/data transformation

See microsoft guideline for more details :

If you already work with the CRM integration project you will not be lost to work with this principle, otherwise  keep in mind you probably go ahead lots of struggles if you plan to use dynamic filters, data transformation or table relations

Leave a Reply

Your email address will not be published. Required fields are marked *