Understanding Virtual Tables in Microsoft Dataverse
Microsoft Dataverse
Sep 10, 2023 8:27 PM

Understanding Virtual Tables in Microsoft Dataverse

by HubSite 365 about Dhruvin Shah [MVP]

Microsoft MVP (Business Application & Data Platform) | Microsoft Certified Trainer (MCT) | Microsoft SharePoint & Power Platform Practice Lead | Power BI Specialist | Blogger | YouTuber | Trainer

External YouTube Channel
Citizen Developer

Microsoft DataverseM365 Hot News

Unlock seamless integration of external systems with Microsoft Dataverses virtual tables - without data replication or custom coding.

A Deep Dive into Virtual Tables in Microsoft Dataverse

The YouTube video authored by Dhruvin Shah [MVP] comprehensively discusses the concept and implementation of Virtual Tables within Microsoft Dataverse. The documentation outlines the integration of data housed in external systems, which is efficiently portrayed as tables in Dataverse. This achievement is possible without any replication of data and cancels out the need for custom coding.

The adoption of Virtual Tables allows phasing out the previous methods of incorporating external data using client-side and server-side tactics. With these traditional methods, one needed customized code that came with it several limitations. These limitations included imperfect integration, data duplication, and a significant dedication of development resources to customize the methods. For administrators and system customize-rs, the employment of Virtual Tables reduces the complexities they previously faced with administration and configuration.

Microsoft Dataverse treats Virtual Tables as merely a table definition within its platform. These tables lack any related physical tables created for records within the Dataverse database. Instead, when a record is necessitated, its state is dynamically fetched from the allied external system. Each Virtual Table type is linked to a Virtual Table data provider and probably some configuration detail from a related Virtual Table data source.

Virtual Tables: The Implementation and Mapping

At the outset, a Virtual Table is defined like a conventional custom table. The table, columns, and relationships are specified for the new Virtual Table type. The next step involves linking the Virtual Table to a data provider who would manage the data operations. The custom table type and its columns have to be mapped to the respective data in the external data source. For instance, a Virtual Table might symbolize a row in an external relational database, and each of its columns might coincide with a column in that row.

One can create or edit virtual tables which contain data from an external data source. However, there are certain considerations that need to be followed. Only organization-owned tables are supported. Field-level security is not supported. Moreover, the data from the external source as a Dataverse table. This means all tables in the external data source must have an associated GUID primary key and all table properties must be echoed as Dataverse table columns.

The Limitations

Despite the innovation and convenience offered by Virtual Tables, they come with limitations as well. Auditing is not supported. Virtual Tables do not support search functionality, charts, and dashboards. The offline caching of values is not supported, nor they can characterize an activity and support business process flows. Once created, a Virtual Table can never be transformed into a standard (non-virtual) table and vice versa.

General Understanding of Microsoft Dataverse

Microsoft Dataverse (formerly referred to as Common Data Service) is a cloud-based data platform that is a part of the Microsoft Power Platform. Its main purpose is to assist organizations in the storage, management, and access to data from a broad range of sources. It facilitates users to create custom data entities, establish relationships between entities, and share data with one another.

Dataverse is typically combined with other Power Platform tools, such as PowerApps and Power BI, to build custom business applications and dashboards. As a flexible and scalable platform, it has the ability to support a wide range of business scenarios and workflows.

Microsoft Dataverse - Understanding Virtual Tables in Microsoft Dataverse

Learn about Virtual Table in Dataverse

Virtual tables are a significant advancement in the realm of data integration. They allow for the representation of data from external systems as tables within the Microsoft cloud data platform known as Microsoft Dataverse, facilitating their manipulation without the need for data replication or custom coding. This dynamic system poses a streamlined solution to previous practices that involved complex client-side and server-side approaches.

To understand the functioning of virtual tables better, it is critical to first understand the definition of these entities within the context of the Dataverse platform. In essence, a virtual table functions as a representation of a table on the platform, without the creation of associated physical tables for records within the database. During runtime, as and when a record is needed, its state is recovered in a dynamic manner from the associated external system.

Each type of virtual table is associated with a virtual table data provider and occasionally, configuration information from a virtual table data source. For instance, an OData v4 provider is a standard installation with the service and supports various operations such as create, read (either retrieve single or retrieve multiple), update, and delete. Moreover, an Azure Cosmos DB provider is accessible from AppSource.

If an existing data provider cannot cater to your external data source, a custom virtual table data provider can be developed. They are used in cases where standard data providers are not available. These can cover a full range of CRUD operations.

The creation and mapping of virtual tables bear similarity to the defining of a custom table: the table, columns, and relationships for the new virtual table type are specified. What sets the process apart is the subsequent connection of the virtual table to a data provider to manage data operations. The custom table type and its columns must be mapped to the corresponding data in the external data source.

However, just like every other technological tool, virtual tables come with their fair share of limitations. These limitations primarily pertain to the modeling of external data, the functionality of virtual tables, regulations about changing the type of tables once created, and virtual tables- specific restrictions.

To dive deeper into the subject, watching the video “Virtual Table in Dataverse.” could be a good starting point. The step-by-step process of creating a Virtual table in Dataverse is also discussed in the video. Remember, knowledge about the Microsoft Dataverse is of paramount importance if you wish to extend its functionality via virtual tables. Hence, taking up courses on platforms like PowerApps or Power BI, which often collaborate with this tool, can be a smart move.

For an even more practical approach, you can also try creating and editing virtual tables that contain data from an external data source or experiment with connecting External Data Sources as Virtual tables in Dataverse. This practical approach will provide a hands-on experience of how things work and more knowledge about Azure SQL and Dataverse.

More links on about Virtual Table in Dataverse

Get started with virtual tables (entities) (Microsoft Dataverse)
Jan 27, 2023 — A virtual table is a definition of a table in the Dataverse platform without the associated physical tables for records created in the ...
Create and edit virtual tables with Microsoft Dataverse
Oct 20, 2022 — A virtual table is a custom table in Microsoft Dataverse that has columns containing data from an external data source. Virtual tables ...

Keywords

Dataverse Virtual Table, Virtual Table Dataverse, Microsoft Dataverse, PowerApps Virtual Table, Microsoft PowerApps Dataverse, Table Management in Dataverse, Cloud Virtual Table, Dataverse Table Configuration, Advanced Tables Dataverse, Dynamics 365 Virtual Table