Extending Microsoft Dataverse with external data sources is a powerful way to integrate disparate systems and services into a unified data platform. Here’s a breakdown of how to approach it, depending on your goals and the systems you’re connecting to.
Ways to Extend Dataverse with External Data Sources
1. Virtual Tables (formerly Virtual Entities)
- Use Case: Real-time access to external data without importing it into Dataverse.
- How it works: Dataverse queries the external source on-demand using an OData v4-compliant service or custom connectors.
- Pros:
- No data duplication.
- Near real-time access.
- Good for read-heavy scenarios.
- Cons:
- Write operations may be limited.
- Requires development of a custom data provider if not using OData.
Tip: Microsoft provides OOTB providers for SharePoint, SQL Server, and more.
2. Custom Connectors (Power Platform)
- Use Case: Integrate APIs that are not natively supported.
- How it works: You define RESTful API calls in a custom connector and use them in Power Automate, Power Apps, or Dataverse plugins.
- Pros:
- Highly customizable.
- Works well in automation scenarios.
- Cons:
- More setup involved.
- Less seamless than virtual tables for large datasets.
3. Azure Synapse Link for Dataverse
- Use Case: For analytics and reporting on large datasets.
- How it works: Syncs Dataverse data to Azure Synapse in near real-time; you can then link it to external data sources in Synapse.
- Pros:
- Ideal for data lakes and analytics.
- Scales well.
- Cons:
- Not designed for operational (transactional) access.
4. Dataflows
- Use Case: Scheduled import of external data into Dataverse.
- How it works: Power Query-based ETL to extract, transform, and load external data.
- Supports: SQL, Excel, SharePoint, web APIs, etc.
- Pros:
- Flexible ETL capabilities.
- Easy to set up in Power Platform UI.
- Cons:
- Not real-time (scheduled).
- Data duplication.
5. Plugins and Custom Code
- Use Case: Complex business logic or integrations that don’t fit out-of-box tools.
- How it works: Write server-side code (C#) that runs on Dataverse events and interacts with external services via APIs or SDKs.
- Pros:
- Full control.
- Can be tailored to any logic.
- Cons:
- Requires .NET development skills.
- Harder to maintain.
Authentication Considerations
When accessing external systems:
- Use Azure AD for Microsoft services.
- Use OAuth2 for modern APIs.
- Store credentials securely (e.g., in Azure Key Vault or using Dataverse secrets).
Example Scenario
Let’s say you want to display real-time inventory from an external SQL Server database inside a Dataverse model-driven app:
- Create a virtual table in Dataverse.
- Use the SQL Server connector or create a custom data provider.
- Map external fields to Dataverse schema.
- Users can view (and possibly update) inventory directly in Dataverse apps.