What?
Data virtualization is all about interacting with data without needing to worry too much about the details of data location, format, and data security. Microsoft realizes how important all aspects of data are to people and organizations and have been developing ways to enable virtualization across different products and services. In the Power Platform, it translates to Virtual Tables, Virtual Table Providers, and soon Data Virtual Connectors.
I won’t get too much into definitions here as I believe we are well served with the official documentation and many blog posts, but to give you a little bit of context let’s review the basics.
Virtual Tables
Virtual Tables, previously known as Virtual Entities, in a nutshell, are tables in Microsoft Dataverse that represents data that is outside of it.
A Virtual Table holds the definition of an external table (metadata) and every time the records are requested the platform queries the external source using a Virtual Table Provider to get the most up-to-date state of the requested records.
Virtual Table Provider
A Virtual Table Provider is a component that knows how to connect to your external database and is used by the platform to streamline the data back and forth, by the time I’m writing this post the following providers are installed by default
- OData v4
- Azure Cosmos DB
If your data Isn’t accessible through an OData service or an Azure Cosmos DB you can also develop your own custom virtual table provider and teach the platform how to get your data.
Virtual Tables are not something new in Dataverse, it was actually released back in 2017 and it used to provide read-only capabilities to an external data source, which limited the use of it as you couldn’t fully interact with the external data source. The scenario changed recently when Microsoft announced the support to CRUD operations using virtual tables and they are now back in the game.
How?
Diving into the setup of a virtual table and check some of the common pitfalls we get into when working with it. We will use the Adventure Works sample database as our external data source and I’ve created a basic OData service to surface the data from the database to us so we can focus mainly on the Virtual Tables.
The first thing to do is to create the virtual data source in Settings > Administration > Virtual Entity Data Sources we will use the out-of-the-box OData V4 data provider and add the external data source URL when asked.
Now we are ready to create and map our virtual entity that will use the data provider to streamline the data.
Make sure to use the classic view to create the virtual entity, as it isn’t supported yet in the new one.
Check the virtual entity checkbox and select the data source created and map the external table name and collection name
Differently from a classic entity, when you create a virtual entity, the platform only creates 2 fields for us:
- ID (Primary Key – guid – System Required)
- Name (Single Line of Text – Business Required)
If you get an error that sounds like ExternalName is null or empty for attribute ‘yourattribute’ of entity yourentity. That’s likely because you forgot to map a field to the external data source, the best thing to do is to check the metadata for your OData service, if you service implements the OData v4 standards you can do that simply by adding a $metadata at the end of your service URL:
https://yourodataservice.com/odata/$metadadaYou will get a xml file with the metadata details needed for you to map the virtual entities within Dataverse with the external data source:
Make sure to be using the appropriate datatypes mapping according to the data provider that is being used, in this case OData v4:
The following table lists the OData table Data Model (EDM) data type mappings with Dataverse data types.
| OData Data Type | Dataverse Data Type |
|---|---|
Edm.Boolean | Two Options |
Edm.DateTime | Date and Time |
Edm.DateTimeOffset | Date and Time |
Edm.Decimal | Decimal Number or Currency |
Edm.Double | Floating Point Number |
Edm.Guid | Unique Identifier |
Edm.Int32 | Whole Number |
Edm.Int64 | Whole Number |
Edm.String | Single Line of Text or Multiple Lines of Text |
Dataverse records will always have a globally unique identifier (guid) as a primary key column for its records but that is not necessarily true for other data sources, but when using virtual tables we must ensure that the external table provides a guid for each record, Microsoft Dataverse requires that all tables have an ID attribute, this ID is known as a unique identifier and the value must be a guid. You can only map ID columns to external columns with the Edm.Guid data type. You can’t map an Edm.Int32 data type to a Unique Identifier data type column in Dataverse.
By the way, before even starting mapping the fields of virtual tables, make sure to enable the plugin exceptions logs in Settings > Administration > System Settings > Customization as the platform will log parse, connections, and other errors there.
A very common error to get at some point is the UnexpectedXmlElement : The schema element ‘NavigationProperty’ was not expected in the given context. That’s most likely because the external data table has navigation properties (relationships) with tables that don’t exist in Dataverse.
After mapping the virtual tables and if you OData service supports CRUD operations you can already create, read, update and delete data from your external data.
Limitations of virtual tables
Following are the limitations in virtual tables that must be considered.
- Only organization-owned tables are supported. The security filtering applied to user-owned tables is not supported. Access to the virtual table data can be turned on or off for individual users based on their security role. Field-level security is not supported.
- It must be possible to model the external data as a Dataverse table. This means:
- All tables in the external data source must have an associated GUID primary key.
- All table properties must be represented as Dataverse table columns. You can use simple types representing text, numbers, choices, dates, images, and lookups.
- You must be able to model any table relationships in Dataverse.
- A column on a virtual table cannot be calculated or rollup. Any desired calculations must be done on the external side, possibly within or directed by the data provider.
- Although you can add virtual table columns as a lookup on a grid or other UI views, you cannot filter or sort based on this virtual table lookup column.
- Auditing and change tracking is not supported.
- Charts and dashboards are not supported for virtual tables.
- Virtual tables cannot be enabled for queues.
- Offline caching of values is not supported for virtual tables.
- A virtual table cannot represent an activity and does not support business process flows.
- Once created, a virtual table cannot be changed to be a standard (non-virtual) table. The reverse is also true: a standard table cannot be converted into a virtual table.
What’s is coming?
CRUD using Virtual Tables open up a whole new world from a data perspective but there are still some limitations Imagine if we could use the connectors available in Power Automate to streamline the data back and forth an external data source, Microsoft is actually working on that as you read and it is currently in private preview, Srinath Kannan briefly mentioned about it here and I’m looking forward to having a look at Virtual Data Connectors.
