How to integration Power Apps 's Data to custom Server from CDS

[Cristars]

       I built a model-driven apps using common data services , i used the  CDS  entities , relations ,views  and so on . But i can't find a official way  to integration the Apps's Data to the Custom SQL Server . 

       we have many on-primes systems in the organization's domain , so i need a solution to synchronize data , include pull  and push the domain's system data  . 

Thanks a lot  !! 

[jlindstrom]

Model-driven apps don't natively use the connectors that canvas apps use, but there are options.

The biggest thing, before you integrate your on premise data into CDS is make a plan. if you have 10 on premise systems, likely they have similar data in them. You probably have things like contact data in multiple systems. having 10 different two-way integrations is a recipe for a lot of complications and potential data corruption.

Whenever I do an integration with multiple legacy systems, I want to do a single data integration rather than multiple if possible. create a data warehouse or use a data lake and bring your data together--deduplicate if there are multiple copies of the same record. if you have contact data in multiple systems, you need to set rules about which wins in the case there are multiple.

so what are your integration options? Depends on what you want to do with the data. Is it actionable data, meaning the user must make updates to it, relate activities and notes to it, etc, or is it reference data that is mainly read only?

If it is actionable data, you will want to integrate the data into the CDS. You can use Azure Data Factory, SSIS, etc and create a two-way data integration. I recommend the Kingswaysoft (www.kingswaysoft.com) adapter for SSIS.

If it doesn't need to be two-way, you can use data flows https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows

these are standard one-way integration processes  that use PowerQuery.

Another option, if the data is related to primary records in CDS that you might want to consider is embedded canvas apps. this lets you embed a canvas app in model-driven forms. https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/embed-canvas-app-in-form

For example, if you have customer data in another system, you could embed a canvas app on the account form using the SQL server connector and display data from SQL server in your model driven app.

Another option, if you have customer data in multiple places, is to use Customer Insights. https://dynamics.microsoft.com/en-us/ai/customer-insights/. Customer Insights is a Customer Data Platform (CDP) that can bring  data from multiple systems together and is integrated with CDs and the power platform.

[Cristars]

Thank you for your reply ,   but  how to export the business data  from  Common Data Service  to  our  DMZ Server . i wan't to use the webapi  , beacause  it's a little complicated. 

[jlindstrom · Accepted solution]

So the azure data factory/ssis option with kingswaysoft that I mentioned earlier can also be used to take data that is new/changed from cds to your dmz server.

kingswaysoft uses the web api and makes it simpler than custom development. kingswaysoft is a good choice if you want to have batch integration where on a time interval records that have been created or changed are copied to the DMZ server.

if the integration is more near real time, use power automate or its developer cousin Azure Logic Apps.

and if you want to integrate two cds environments together you can use a cds integration process https://docs.microsoft.com/en-us/power-platform/admin/data-integrator