Data Integration architecture for Microsoft Dataverse, Dynamics 365, and PowerApps

Data Integration Architecture

As Microsoft have accelerated its investment in Dynamics, Power Platform & Azure the latest years a lot of new integrations options are available. Below you find a quick developer guide on Common Data Service Agile Data Integration Tools and architecture with connectors from cloud to on-premises.

Data Integration architecture for Microsoft Common Data Service

I’ll give you a quick view to some of latest integration offering and include some of the older options that some have forgotten about. Below integration options are what I have seen being used in most recently implementation in forward looking companies, with use of Microsoft Power Platform Low code/No code “integration options”.

If you look for how to do Modern Web UI integration rather than Data integration have a look at the Power Apps Component framework (PCF)

Batch import / data Integration and migration architecture

PowerApps Dataflow it is a self-Serv ETL tool for manual or scheduled batch procession where one can combine multiple data source into one and import into existing entity or have Dataflow generate an Entity that fits the data in first load. https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows,

Batch/Data import/data migration can be done for most projects with Power platform Dataflows, which have connector multiple connectors to fx. SFDC for easy migration and SAP BW (Application service & Message Server) plus SAP HANA database. Dataflow delivers single threated data load with max of 500000 record pr. Load.

If data is on-Premises a PowerApps on premises gateway is needed: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/using-dataflows-with-on-premises-data

If more that 500000 records are needed and more speed in data migration/batch Azure Data service, it the thing to use. https://azure.microsoft.com/en-us/services/data-factory/

API Integration architecture

if API integrations are needed (scheduled or data CRUD triggered) Power Automate or Azure LogicApp could be a for fit for purpose. Power Automate now have SAP ECC connector connector for close to no code integration: https://powerapps.microsoft.com/en-us/blog/introducing-the-sap-erp-connector/ and Azure LogicApps still have it integrate connector to SAP https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-using-sap-connector and other enterprise essential connectors like edifact: https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-enterprise-integration-edifact

RPA / Robotics

Power Automate now have RPA functionality. https://docs.microsoft.com/en-us/power-automate/ui-flows/overview. the RPA functionality covers both automation for web and Windows application… https://flow.microsoft.com/en-us/blog/microsoft-power-automate-adds-robotic-process-automation-in-april/

RPA automations are often used where the system to integrate to do not have an API or where Businesspeople have a challenging time getting IT to prioritize building an automation.

RPA is a shortcut making people more efficient but also a fragile tool as it integrates through the UI.

I often see RPA used where customer have SAP as it is usually expensive to do proper API integration with SAP. You know you need RPA for SAP if your businesspeople need to instruct each other through “email integration” on what to update/create in SAP.

Power Automate have 2 RPA flavors, Attended & Unattended

Attended RPA

Attended RPA plan provides the ability for users to run an RPA bot on their workstation. and includes access to several AI Builder capabilities like forms processing, object detection, prediction, text classification and recognition, and more. Attended RPA can have Human step included.

Unattended RPA

Unattended RPA is the same as Attended it just runs on a service without human view or  or human manual steps

Export and Import data to and from Azure Data lake

Enabling a modern Data & AI approach having a pool of unaggregated data from siloed system combined in a enterprise Azure Data lake ready to unleash AI, Machine learning in your business application of choice, Dynamics 365 & PowerApps and as a added bonus this serves well as an data integrator as well

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/connect-azure-data-lake-storage-for-dataflow

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/export-to-data-lake

Dataverse – Virtual Entities architecture

Use Virtual entities for integration of data coming from OData api and display the data as it was stored within Dataverse (CDS). It is usually used for show data(order, Quotes etc.) from ERP system within Dynamics Customer engagement.

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/virtual-entities/get-started-ve

Here is a details step by step setup guide https://lystavlenhistoric.home.blog/2017/09/08/virtual-entities/

Here are a working example of a custom Virtual Entities provider for other CDS environments https://github.com/bguidinger/Xrm.DataProviders

Microsoft have a prebuild solution for Dynamics 365 Finance and Operations & Business Central for integration them through virtual data source in CDS allowing full CRUD (Create, Read, Update, Delete) operations from CDS and Power Platform. 

Dynamics 365 Finance and Operations CDS virtual entities https://appsource.microsoft.com/en-us/product/dynamics-365/mscrm.finance_and_operations_virtual_entity

Dynamics 365 Business Central CDS virtual entities https://appsource.microsoft.com/en-us/product/dynamics-365/microsoftdynsmb.businesscentral_virtualentity

If you are interested in power platform integration you must also want to know the other components of the power platform. Read this Power Platform fundamentals certification guide which will help you do exactly that.

If you want to know some of the behind-the-scenes of the Power Platforms Dataverse setup and history read this https://devblogs.microsoft.com/azure-sql/running-1m-databases-on-azure-sql-for-a-large-saas-provider-microsoft-dynamics-365-and-power-platform/