Introduction to Virtual Tables
Virtual Tables in Microsoft Dataverse are an advanced feature that enables the inclusion of data from external sources directly in the Dataverse interface. Unlike standard tables, the data is not physically imported into the database but remains in the original source, while maintaining full integration with the data model and model-driven apps. This approach is ideal when replication of data is unnecessary or restricted for compliance or performance reasons.
The mechanism relies on a configuration that defines the data provider, metadata structure, and access rules. Users can view and interact with data as if it were native to Dataverse, but CRUD operations are executed through the external provider.
Architecture of Virtual Tables
Each Virtual Table includes the following fundamental components:
- Data Provider Plug-in: the connector that manages communication between Dataverse and the external source.
- Metadata Storage: metadata and configuration of the table stored within Dataverse.
- External Data Source: the remote source hosting the actual data, such as an SQL database or an OData service.
Virtual Tables support a limited number of data types, and certain features like auditing or offline mode are not available. However, they offer an elegant solution for real-time integration scenarios.
Available Providers and Configuration
The official providers supported by Dataverse include:
- OData 4.0 provider: natively available, allows connections to any OData-compliant endpoint.
- Azure Cosmos DB provider: available on Microsoft AppSource.
- Virtual connectors: a recent solution enabling the use of Power Platform connectors such as SQL Server, Excel Online (Business), and SharePoint.
- Custom provider: for proprietary APIs, custom plug-ins can be developed.
To create a Virtual Table, you must first configure the data source in Dataverse, then define the virtual table by specifying metadata and columns. Once published, the table appears as any other entity, fully integrable into apps and dashboards.
Limitations and Considerations
Virtual Tables do not support advanced features such as auditing, column-level security, or offline synchronization. They can only be organization-owned. Proper planning of data mapping and provider compatibility is essential to ensure functionality.
The Dataverse TDS Endpoint
The TDS (Tabular Data Stream) endpoint is a feature that exposes Dataverse data in SQL format, enabling connections with tools like Microsoft SQL Server Management Studio (SSMS) or Power BI. This endpoint is read-only and ideal for analysis and reporting.
Developers can execute standard SQL queries, design complex views, and obtain real-time data without exports or intermediate syncs. It is a powerful tool for analysts and data engineers integrating Dataverse into ETL or BI workflows.
For more information, see the official Microsoft documentation: Virtual tables using connectors.
Benefits of the TDS Endpoint
- Instant access to Dataverse data through standard SQL.
- Compatibility with Microsoft tools such as SSMS and Power BI.
- No data replication, reducing cost and complexity.
- Seamless integration with analytical and reporting pipelines.
Use Cases and Integration
Virtual Tables and TDS can be used together to build hybrid integration and analysis solutions. For example, operational data can remain in an external database accessible via Virtual Tables, while the TDS endpoint can be used to analyze consolidated Dataverse data. This ensures scalability and consistency across enterprise systems.
A practical example is the integration of Dataverse with Azure Synapse Analytics or Power BI, where the TDS endpoint acts as a direct bridge for data reading, eliminating complex ETL pipelines. Combined with Apache Spark, it enables advanced distributed analysis and transformations.
Design Best Practices
- Plan your virtual data structure, deciding which tables remain external and which are physical in Dataverse.
- Ensure the chosen provider supports the required data types.
- Avoid using Virtual Tables for large datasets or that require auditing features.
- Use the TDS endpoint for reporting and analytical queries, not for write operations.
By following these guidelines, organizations can achieve a flexible and high-performing data platform without compromising data security or consistency.