Introduction
Dynamics 365 and Microsoft Dataverse applications often need to work with data that is stored outside Dataverse. One option is to copy the external data into Dataverse, but this can introduce data synchronization, storage, and maintenance requirements.
Virtual tables provide another approach. They allow external data to be represented in Dataverse without physically copying the records into the Dataverse database.
This can be useful when users need to view external reference data directly from a Dynamics 365 application.
However, virtual tables have important limitations. They do not provide the same capabilities as standard Dataverse tables, particularly for scenarios involving record ownership, offline access, auditing, and other platform features.
In this article, we will understand virtual tables, create a simple practical scenario, examine their limitations, and discuss when they should and should not be used.
What Are Virtual Tables?
A virtual table is a Dataverse table whose data is stored in an external system rather than in the Dataverse database.
From the user's perspective, the external records can appear within a Dataverse or Dynamics 365 application.
A simplified architecture looks like this:
+----------------------+
| Dynamics 365 User |
+----------+-----------+
|
v
+----------------------+
| Dataverse |
| Virtual Table |
+----------+-----------+
|
v
+----------------------+
| External Data Source |
| |
| ERP / SQL / API |
+----------------------+
When a user requests data through the virtual table, Dataverse obtains the information from the configured external data source.
The actual capabilities depend on the virtual table provider and the external data source being used.
Why Use Virtual Tables?
Virtual tables can be useful when an organization already has data in another system and does not want to maintain another copy in Dataverse.
For example, suppose an organization has product information stored in an external ERP system:
ERP System
|
+---- Product ID
+---- Product Name
+---- Price
+---- Stock
Instead of importing all products into Dataverse, a virtual table can expose the required information to Dynamics 365.
This can help reduce unnecessary data duplication.
Practical Example
Consider a company that uses Dynamics 365 for sales but maintains its product catalog in an external system.
The sales team wants to view the product catalog from Dynamics 365.
The requirement is:
Product data remains in the external system.
Dynamics 365 users can view the products.
The product data does not need to be replicated into Dataverse.
The sales application should be able to display the external information.
A virtual table can be considered for this scenario.
The architecture can be represented as:
Sales Representative
|
v
Dynamics 365
|
v
Product Virtual Table
|
v
External Product System
Step 1: Identify the External Data Source
Before creating a virtual table, identify where the data currently exists.
Examples include:
External SQL databases
ERP systems
Other business applications
Supported external data providers
APIs and other integration mechanisms, depending on the provider
For this example, assume the product information exists in an external system with the following logical structure:
Products
--------------------------------
ProductId
ProductName
Category
Price
The external source remains the system of record.
Step 2: Determine Whether a Virtual Table Is Appropriate
Before configuring the integration, determine whether the business requirement fits the capabilities of a virtual table.
Ask questions such as:
Does the application primarily need to read external data?
Does the external system remain the source of truth?
Is data replication undesirable?
Does the application require record-level security?
Does the application need offline access?
Does the application need Dataverse auditing?
Does the application need ownership?
Does the application need standard Dataverse automation?
If the application requires several unsupported Dataverse capabilities, data replication may be a better architecture.
Step 3: Configure the Virtual Table
The exact configuration steps depend on the selected virtual table provider and external data source.
At a high level, the process involves:
Select a supported virtual table provider.
Configure the connection to the external data source.
Provide the required authentication and connection information.
Select or define the external data.
Create the corresponding virtual table.
Map the external fields to Dataverse columns.
Configure the required relationships and metadata.
Add the table to the required Dynamics 365 application.
The exact provider-specific configuration should be documented separately because the available options can vary.
Step 4: Map External Data
Suppose the external product system contains:
ProductId
ProductName
Category
Price
The virtual table can expose corresponding columns:
External Field | Virtual Table Column |
|---|---|
ProductId | Product ID |
ProductName | Product Name |
Category | Category |
Price | Price |
The virtual table represents the external data, but the underlying records continue to reside in the external system.
Step 5: Display the Virtual Table in Dynamics 365
After configuration, the virtual table can be used in the appropriate model-driven application components.
For example:
Dynamics 365
|
v
Products
|
+---- Product A
+---- Product B
+---- Product C
When the user opens the product data, the application retrieves the information through the virtual table configuration.
Example Output
A user might see the following information in the Dynamics 365 application:
Product ID Product Name Category Price
------------------------------------------------------
P001 Laptop Electronics 65000
P002 Monitor Electronics 18000
P003 Keyboard Accessories 2500
The important point is that these records are represented in Dataverse through the virtual table while the source data remains in the external system.
Read-Only and Write Capabilities
One of the most important considerations is that virtual table capabilities depend on the provider and configuration. It is therefore incorrect to treat every virtual table as universally read-only.
Some virtual table scenarios support interaction with external data, while others provide read-only access.
Before implementation, verify the capabilities of the specific provider being used.
For a read-only reference-data scenario, users may be able to view:
External Product
|
v
Virtual Table
|
v
Dynamics 365 User
but changes to the external data must be performed through the external system or an appropriate integration.
Limitations of Virtual Tables
Virtual tables do not provide all the capabilities available to standard Dataverse tables.
The following limitations should be considered during solution design.
Record-Level Security
Security requirements need special attention.
Standard Dataverse security capabilities do not automatically translate into identical record-level security behavior for external data.
The security model of the external system and the virtual table provider should therefore be evaluated before exposing sensitive data.
For confidential or highly sensitive information, the solution should be designed so that users cannot bypass the intended security model through the external source.
Auditing
Dataverse auditing capabilities should not be assumed to work in exactly the same way as they do for standard Dataverse tables.
If the business requires a complete audit history of changes to external records, the external system may need to provide its own auditing mechanism.
Offline Access
Virtual table data generally depends on connectivity to the external data source.
Therefore, scenarios that require users to access the data while working offline need careful evaluation.
This is particularly important for Dynamics 365 mobile scenarios where offline functionality is a requirement.
Performance Depends on the External System
Virtual table queries introduce a dependency on the external data source.
The overall user experience can be affected by:
External system response time.
Network latency.
Query complexity.
Provider implementation.
External system availability.
Amount of data being requested.
For example:
Dynamics 365
|
| Request
v
Virtual Table
|
| Query
v
External System
|
| Response
v
Virtual Table
|
v
Dynamics 365
If the external system responds slowly, users may experience slow data retrieval in Dynamics 365.
No Local Copy of the Data
One of the primary benefits of virtual tables is also an important architectural consideration.
With traditional data replication:
External System
|
v
Data Synchronization
|
v
Dataverse Copy
Two copies of the data exist.
With a virtual table:
External System
|
v
Virtual Table
|
v
Dynamics 365
The external system can remain the source of truth.
This can reduce synchronization requirements, but it also means that the Dynamics 365 application depends on the availability and performance of the external system.
Security Considerations
Security should be evaluated across the complete data path.
Consider:
Dataverse permissions.
External-system permissions.
Authentication.
Authorization.
Network security.
Sensitive data exposure.
API access.
Logging and monitoring.
Do not assume that configuring permissions in Dynamics 365 automatically provides the same authorization behavior in the external system.
Virtual Tables vs Data Replication
The choice between virtual tables and data replication depends on the application's requirements.
Requirement | Virtual Table | Data Replication |
|---|---|---|
Avoid duplicate data | Good fit | Not the primary goal |
External system remains source of truth | Good fit | Possible |
Local Dataverse copy required | No | Yes |
Offline requirements | Limited suitability | Better suited |
Advanced Dataverse features | May be limited | Better support |
Dependency on external system | High | Lower after synchronization |
External data performance | Depends on source | Local Dataverse queries |
Data synchronization | Not normally required | Required |
When Should You Use Virtual Tables?
Virtual tables are a good option when the primary requirement is to expose external information without maintaining a duplicate copy in Dataverse.
Examples include:
External product catalogs.
Reference data.
External pricing information.
Data that changes frequently in another system.
Scenarios where the external application must remain the source of truth.
For example:
ERP Product Catalog
|
v
Virtual Table
|
v
Dynamics 365 Sales
This allows sales users to access relevant product information without creating another authoritative copy.
When Should You Avoid Virtual Tables?
A different architecture may be more appropriate when the application requires:
Extensive Dataverse automation.
Reliable offline access.
Comprehensive Dataverse auditing.
Complex record-level security requirements.
High-performance local querying.
Extensive Dataverse-specific functionality.
Strong independence from the external system.
In these cases, replicating or synchronizing the required data into Dataverse may provide a better user experience and more predictable platform capabilities.
Best Practices
Use Virtual Tables for Appropriate Data
Reference and lookup data are often better candidates than highly transactional workloads.
Optimize External Queries
Keep queries focused on the data required by the application.
Avoid unnecessarily retrieving large datasets.
Monitor External Dependencies
Monitor the availability and response time of the external system because its performance can directly affect the Dynamics 365 experience.
Evaluate Security Before Implementation
Review both Dataverse and external-system authorization requirements before exposing external data.
Test With Realistic Data
A virtual table that works well with a small test dataset may behave differently with production-scale data.
Test representative query sizes and realistic network conditions.
Document Provider-Specific Limitations
Virtual table capabilities can depend on the selected provider. Document the exact provider, supported operations, authentication model, limitations, and dependencies used by the solution.
Common Mistakes
Treating Virtual Tables Like Standard Dataverse Tables
Virtual tables should not automatically be assumed to support every feature available for native Dataverse tables.
Ignoring External System Performance
The external system becomes part of the application's runtime data path.
Using Virtual Tables for Every Integration
Virtual tables are not a replacement for all integration patterns. Synchronization, APIs, dataflows, or other integration approaches may be more appropriate depending on the requirements.
Ignoring Security at the External Source
The external system still owns the underlying data. Its security model must be considered as part of the overall solution.
Conclusion
Virtual tables provide a useful way to expose external data through Dynamics 365 and Dataverse without necessarily creating a duplicate copy of that data in Dataverse.
In this article, we looked at a practical product-catalog scenario and walked through the general process of identifying an external data source, determining whether a virtual table is appropriate, configuring the integration, mapping external fields, and exposing the data to Dynamics 365 users.
We also examined important considerations such as performance, security, auditing, offline access, and external-system dependencies.
Virtual tables are particularly useful when external data needs to remain in its source system while being accessible from Dynamics 365. However, applications that require extensive Dataverse functionality, offline access, complex security requirements, or high-performance local querying may benefit more from a data replication or synchronization approach.
The correct choice depends on the data source, business requirements, security model, performance expectations, and capabilities required by the Dynamics 365 solution.
Join the conversation! Your thoughts help the community grow.