How to do Integration Approach for Dynamics 365 F&O Data Using Dataverse, Azure, and Informatica

For Dynamics 365 Finance & Operations (F&O) data, Dataverse and Azure services provide the primary, supported integration patterns. Informatica can then consume data exposed through these Microsoft layers.

1. Accessing F&O data through Dataverse

Dataverse is the preferred platform for integrating Dynamics 365 apps on Azure. For F&O:

• F&O apps are built on cloud SQL schemas optimized for transactional processing.
• Integration into Dataverse is done via predefined denormalized endpoints or data entities.
• The recommended service for tightly coupled, near real-time integration between F&O and Dataverse is dual-write.

Key points:

• The Data Integrator service can integrate data between F&O apps and Dataverse, but the recommendation is to start using dual-write for bidirectional, near real-time sync.
• With dual-write enabled, any data change in F&O writes to Dataverse, and any data change in Dataverse writes back to F&O, giving an integrated experience and making F&O data available in Dataverse tables for downstream consumption.

Prerequisites conceptually:

• F&O and Dataverse environments connected and configured for dual-write.
• Relevant F&O entities mapped to Dataverse tables via dual-write or Data Integrator projects.

Once synchronized, Dataverse becomes the primary source for consuming F&O data for many integration and reporting scenarios.

2. Running SQL-like queries on Dataverse

Dataverse itself exposes a modern REST-based API and is typically accessed via:

• Dataverse Web API / Organization service.
• Power Platform tools (Power Automate, Power Apps, Power BI) using Dataverse connectors.

For SQL-style access and analytics over Dataverse (including F&O data synchronized into it), the recommended pattern is Azure Synapse Link for Dataverse:

• Azure Synapse Link for Dataverse continuously exports Dataverse data (including F&O entities and tables) into Azure Synapse Analytics and Azure Data Lake Storage Gen2.
• Data is stored in Common Data Model format and, for F&O data, by default in Parquet Delta Lake format for faster query response times.
• From Synapse / Data Lake, data can be surfaced into Azure SQL Database, SQL Managed Instance, or SQL Server, where T‑SQL can be used for SQL-like querying.

This pattern effectively enables SQL querying over Dataverse/F&O data without direct SQL access to the transactional F&O database.

3. Recommended methods to connect using Azure services or Informatica

Supported, recommended architecture for near real-time or continuous data access:

a) Operational integration and low-code orchestration

• Use Dataverse as the integration hub for F&O via dual-write.
• Use Power Automate or Azure Logic Apps with Dataverse connectors for low-code/no-code integration flows.
  • These tools can react to Dataverse events and move data to other systems.

b) Analytics and reporting / SQL access

• Use Azure Synapse Link for Dataverse to continuously replicate F&O data (via Dataverse) into:
  • Azure Synapse Analytics (serverless or dedicated pools).
  • Azure Data Lake Storage Gen2.
• From there, use:
  • Synapse Pipelines to move data into Azure SQL, SQL Managed Instance, or SQL Server.
  • Power BI for analytics over Synapse or SQL.

This provides:

• Continuous replication of standard and custom F&O entities and tables.
• Support for create, update, delete (CUD) transactions.
• High availability and disaster recovery for analytics workloads.

c) Informatica integration

While Informatica is not described directly in the context, the supported pattern is to connect Informatica to:

• Azure SQL Database / SQL Managed Instance / SQL Server that receive data from Synapse Link pipelines.
• Or to Azure Data Lake Storage Gen2 / Synapse as a data source, using Informatica’s native connectors.

In other words, Informatica should consume data from Azure data services that are fed by Synapse Link for Dataverse, rather than connecting directly to F&O’s transactional store.

4. Best practices and considerations

When choosing the integration pattern:

• Custom code vs low-code:
  • Prefer low-code/no-code via Power Automate, Logic Apps, and Dataverse connectors where possible.
  • Use custom code against Dataverse APIs (Organization service or Web API) only when needed for complex scenarios.
• Security and compliance:
  • Use managed identities and secure configurations for Azure services.
  • Ensure data handling complies with organizational security policies, especially for sensitive F&O data.
• Scalability and performance:
  • For high-volume or high-frequency scenarios, use Azure services like Service Bus and Synapse Link to scale integrations.
  • For real-time or near real-time needs, dual-write plus Dataverse-based triggers (Power Automate/Logic Apps) or event-driven Azure components is preferred.
  • For analytics, rely on Synapse Link’s continuous replication and Parquet/Delta formats for performance.

5. Summary architecture for the stated goal

For direct or near real-time data access for reporting and integration:

1. F&O → Dataverse
   • Use dual-write (recommended) or Data Integrator to synchronize required F&O entities into Dataverse.
2. Dataverse → Azure
   • Use Azure Synapse Link for Dataverse to continuously export Dataverse (including F&O) data into Synapse and Data Lake.
3. Azure → Consumers (Reporting/Integration)
   • For SQL-like access: expose data via Azure SQL / SQL MI / SQL Server fed from Synapse.
   • For BI: use Power BI on top of Synapse or Azure SQL.
   • For Informatica: connect to Azure SQL, Synapse, or Data Lake as sources.

This approach aligns with the supported Microsoft patterns, centralizes integration on Dataverse and Synapse Link, and provides near real-time, scalable access to F&O data.

References:

• TechTalk: Integration patterns for Dataverse
• Select the ideal platform for your Dynamics 365 apps integration
• Integrate data into Microsoft Dataverse
• Choose finance and operations data in Azure Synapse Link for Dataverse
• TechTalk: Cloud data warehousing for Dynamics 365 data with Azure Synapse Link for Dataverse