The concept of a virtual table (previously: virtual entity) has existed in the Dataverse platform for quite some time already. The feature was originally introduced before XRM and Power Apps merged. This in turn means that the Connector feature used by Canvas apps and Power Automate flows is an alternative approach for the same core need: how to work with data that’s not physically stored within Dataverse?
Since there are still three different flavours of Power Apps , let’s quickly recap what each of them think about data location:
- Model-driven apps: “I’ll let you work with any business data, as long as it’s stored within Dataverse.”
- Power Apps portals: “I’m essentially an external facing version of Model-driven apps, so I follow the same principle.”
- Canvas apps: “Your data may be in whatever system you want! Just point me to the right API and wrap a Connector around it & we’re sorted.”
The term “Model-driven” refers to the existence of a clearly defined data model, on top of which the visible app UI and background features (security, search etc.) are then generated by the Dataverse platform. You get all those features because a specific set of rules exists on how different types of data are related to one another.
Canvas apps enjoy the freedom of taking some data from source A, another piece of data from source B, mashing them together in a common gallery, stitched together with a few lines of Power Fx code. The downside is that the app maker needs to build many of the generic features that in the Model-driven world would just magically appear within the app module.
The best possible outcome would of course be if Power Apps were able to offer both the freedom of a Connector based Canvas app and the strong relational data management capabilities of Model-driven apps. While we are not quite there yet, some elements of the unified app / platform story are starting to emerge.
Connectors in Model-driven apps
Ultimately Microsoft wants to bring the Canvas and Model-driven app types as close together as possible. This means expanding the capabilities for working with external data sources in Dataverse to cover also the Connector technology. At Build 2021 the session “Dataverse for Developers” introduced the latest updates on what the sources for virtual tables can be:
Previously the options for adding virtual tables to Dataverse was pretty much a pro-dev targeted story. The requirements for OData feeds were such that I don’t think I ever managed to find a sample feed to try out the feature. Same for the custom connectors, which are created via writing your own plugins. Technically they can be built, but if the requirements are similar to that of a traditional data integration approach, then it doesn’t exactly revolutionize the low-code data story of Dataverse.
The new preview for Virtual Connector Provider looks more interesting, though. Supporting out-of-the-box connectivity to SQL Server databases is definitely a scenario that’s closer to the no-code level where I personally prefer to operate on. So, I decided to go and see how far this track can take me in building a Model-driven app that actually works with data not physically stored inside Dataverse.
Even though the documents still say “private preview”, anyone can install the Virtual connectors in Dataverse solution from AppSource today:
There’s a Power CAT Live video on YouTube that introduces the solution. If you’re like me and you prefer consuming written information instead of video walkthroughs, this PDF document will be the place to go for understanding the feature. Inside it you will find this diagram that explains the architecture of how concepts like connectors, data sources, connection references etc. relate to this new Virtual Connector Provider.
Setting up SQL Server tables to expand your Dataverse
I have a demo AdventureWorksLT database deployed in SQL Azure, just like the one used in Microsoft’s feature documentation for virtual connectors. I had already earlier used this demo SQL database as a data source for Power Apps Canvas apps, which meant I had an existing connection available in Power Apps Maker portal. Authentication is done with SQL username/password combo in my connection, but Azure AD authentication would also be an option if you’d rather not have stored credentials within the connection.
After following the step-by-step instructions, including setting up an application user / service principal for the virtual connector provider, I had a brand new table visible in my Dataverse environment: “Entity Catalog for AdventureWorksLT”.
Cool, we have a “table of tables”! I can see all the SQL Server database tables available via this connection. By opening up one of these records, I can specify that I want to create the corresponding SQL table as a Dataverse virtual table.
I picked the Product and Product Category tables from there. (Note: modifying the table properties in the Power Apps UI doesn’t seem to work, so use the legacy web client and Solution Explorer to change things like table name.) After this, the virtual connection provider nicely maps all of the available columns in SQL into a matching Dataverse column, with the correct data type.
I can then do the standard configuration tasks I’d perform for a native Dataverse table, such as adding views and modifying form layouts. Of course there are a number of considerations for virtual tables when it comes to the Power Apps features they support. Still, whatever works here is exactly the same experience from an app maker perspective, whether the table is “real” or virtual.
Building a Model-driven app with virtual tables
I created a small demo app module for testing how the different table types can co-exist and work together. I added a custom table called “Requests” and added it as the child table for both Product and Product Category virtual tables coming from SQL.
Let’s first go and browser the external data from a view. Opening up the Products table, the experience is in practice the same as if I was browsing native Dataverse records. I can create a personal view “products currently sold” that filters out all products with a value in SellEndDate field. I can sort based on the SellStartDate. I can filter to see only products with Color value Black.
This is already pretty darn impressive for someone coming from a Model-driven background. Sure, in the Canvas world I’ve been able to easily point a gallery to a SQL table and view the data, but having all of it available within the pre-generated Model-driven UI is a major step beyond that.
Let’s try out how the native Dataverse table + external SQL Server tables work together on a form. Upon adding a new Request, I’m able to reference the related Product Category and Product tables via the standard lookup, just like everything would be stored in a single system. Behind the scenes, the native Request record will get references stored to the external Product Category and Product tables from SQL.
But wait, there’s more! Did you notice that my Request form actually used the Form Component Control to show an embedded form of the Product table on the right side? Immediately upon populating the lookup field on the left side I see all the details of the selected product, just as if they were regular fields of the current record.
In the above example I’m actually editing the Color field of the chose product with the value “White” before creating my request record. What this means is that within the same save event not only am I creating a new row in the Request table in my Dataverse, I’m also directly updating the data in my SQL Server’s Product table.
That is powerful! No custom code was needed in creating an app UI that talks with multiple different line of business systems in real-time, on the very same form.
From databases to Dataverses
This simple example of simultaneously performing CRUD operations on data stored in different systems via a Power Apps form illustrates the reason why Dataverse needs to be seen as much more than just a database. It’s purpose is to be a value-add layer on top of different data storage systems, making them easy to leverage in your business apps. We already see today with the Dataverse file & image data getting stored in Azure Blob Storage and audit log entries in CosmosDB, alongside the core relational data in Azure SQL.
The Virtual Connector Provider and virtual tables take things one step further. Especially in scenarios where you’d need to reference master data from an external system, there may not be a need to physically replicate it into Dataverse (perhaps you also want to reduce the storage costs). Specifying the virtual presence of such data will however make it appear as if it was part of the platform, thus brining it into both Model-driven apps and Canvas apps in a unified way. Even adding support for Dataverse business events to cover Power Automate is technically possible for virtual tables, although these understandably will require pro-developer involvement to get the external systems in sync with the API.
Behind the scenes, these same concepts for virtual entities / tables are already being used by Microsoft in their first-party app features. By browsing the Data Sources within an environment we can see features like case/contact/activity suggestions listed here, as well as platform capabilities like component layers or non-relational data provider.
Two years ago I wrote a blog post called “The Real Common Data Service Emerges” where I explored the direction where Dataverse (then CDS) was going. Since then we have seen Microsoft make the export of relational business data to a data lake a straightforward process with the built-in Azure Synapse Link for Dataverse. Similarly the import capabilities into Dataverse have expanded as the Dataflows / Power Query support keeps improving. Combine these physical data import/export pipelines with the virtual layers that the connector technology may soon offer for several tabular data sources and we’ve got a highly capable low-code toolkit for business data management needs in the Power Platform.
You need to keep in mind that there are many considerations (read: limitations) for using virtual tables to review before deciding if they are a good fit for your business requirements. Even in building the above demo app there were things that don’t quite work the same way as with real Dataverse tables. For instance, I can’t specify a 1:N relationship between the two virtual tables for Product Categories and Products. Quick Find on the SQL data doesn’t seem to produce any meaningful results. Referencing virtual tables via lookups in a Canvas app seems to not retrieve related data at all times. Not to mention the fact that in two different environments the whole Virtual Connector Provider configuration process got stuck before any SQL tables ever materialized in the Entity Catalog.
So, keep in mind that this is a preview of things to come, rather than production ready functionality to use today.
Update 2021-08-20: the feature has now been officially released in public preview format, with new documentation available. Check out the Docs page “Create virtual tables using the virtual connector provider (preview)” that contains the information previously only available in the aforementioned PDF.
Thanks for an interesting post. Have you tried to connect to F&O? Before it wasnt possible to listen to updates from F&O virrual entity, looking forward to see that coming from MS to make it possible to share for ex on hand table.
Johan, unfortunately I don’t have access to any F&O environment that I could test with virtual tables. I’m sure there are more supported systems coming in the future. Over on LinkedIn, Srinath Kannan from Microsoft said: “We are expanding support for other connectors and love to hear from the community other use cases where virtual tables can add value.” So, commenting over there and connecting with Srinath for feedback on your virtual table customer scenarios could definitely help MS in prioritizing their development backlog.
So glad to read your article.
I have tried the connector you privided.
But the power app tell me I do not have the permission to install your connect.
I am the all permission user in my orgnization.
So I can not use it .
Do you have any advises for that?
Dean, do you mean the solution installation process from AppSource fails? Do you have the Power Platform service admin role in your tenant?
We are receiving an error when the Entity Data Catalog is obtaining the results. What permissions did you assign to the service principal created in Azure AD?
Jukka, This is a great feature and i’m wondering if you know how to make this work with a sql db in Azure. The way I have the DB setup it will not allow for primary keys and the virtual connector seems to require that. I can do everything up to showing the tables but when I attempt to go into the specific row form the error I get each time is related to needing a primary key.
Ben, I would imagine primary keys are a requirement from the Dataverse side to be able to implement this functionality. It was the case already in the very first versions of Virtual Entity feature for Dynamics 365 & OData feeds that you needed to have a valid GUID in the data source system.
Did you get a chance the try the Cosmos DB connector too? If not could you please post a similar article with the exact steps for connecting to a Cosmos DB? Thanks for sharing all this information.
Nithin, I believe the Virtual Connector Provider covered in this article only supports SQL Server today (I’ve heard on-premises also works in addition to Azure SQL). I don’t think there’s a no-code way to acccess data in Cosmos DB, especially since Azure Cosmos DB for DocumentDB API Data Provider on AppSource appears to be broken, based on the reviews. So, you’ll probably need to go down the custom code route to use Cosmos DB data in virtual tables today, which is unfortunately out of scope for my blog topics.
Hi Jukka great article. Unfortunately I couldn’t get it to work. I struggled with creating the service principal – I think some specific instructions for your specific database might help as the linked article is too general for a Azure novice like me! I have gone through all the steps as far as creating the entity catalogue, but when I try and access Data tab it says 404 not found, so I’ve obviously done something wrong. I tried to delete everything and start again but I can’t delete the Entity Catalogue as there is a dependency to a MS plugin. Do you know how to delete the Entity Catalogue?
Do you have any information on how to create a column of type Choice (or MultiSelect Choice)? I am using connection to Azure SQL and can’t find documentation about it.
Vitaly, I have not explored the virtual connector provider any further than the demo described here. There doesn’t seem to have been much activity from MS on this preview feature during the past 6 months, so I’m assuming many real-life scenarios may not be supported by it yet. In a couple of weeks time we should get the Release Plan for 2022 Release Wave 1, so it will be interesting to see if that document will contain any mention of product development investments made on this front.
Is your demo still working?
I can’t get the record created/updated. I followed the documentation, tried different databases (I used the demo from the manual), different environments, different tenants. Does not work.
Here’s a Error
Exception Message: APIM request was not successful : BadRequest : A value must be provided for item.
[b75e9664-c16f-ec11-8943-6045bd88addb: External plug-in implementation]
Идентификатор действия: d40e380d-7523-4725-9881-41efc7f942e9
Vitaly, my demo deployment is still retrieving data from SQL Azure into the Model-driven app UI. However, trying to perform any updates back into SQL via the app gives errors like this:
Exception Message: APIM request was not successful : BadRequest : A value must be provided for item.
[ea6af529-e1be-eb11-bacc-000d3adcdd63: External plug-in implementation]
Activity Id: 02aff85f-675b-4861-9cd8-1e62965a6a2d
Yes, I have same problem. Any thoughts on this?
My thoughts are: it’s a preview and Microsoft doesn’t offer any support for it. If there aren’t consistent updates to such preview features, it may well be a sign of change in direction for the particular area of the platform. Virtual entities is one of those topics that has been under development for long enough that the whole product portfolio around it has shifted. While the Virtual Connector Provider is an interesting example of a configurable experience for external data sources, we can’t tell whether a production ready feature is 3 months away or 3 years away.
Hi, do you now if a SQL connection with Azure AD authentication can be used? I can’t get it to work unfortunately.
Hmm, I’m actually not sure. I had always assumed that an Azure AD based connection to SQL would be Microsoft’s primary target for the modern features, but now when I look at the current documentation for creating virtual tables with virtual connectors, it explicitly says “Select SQL Server Authentication as Authentication Type.” It’s strange if AAD would not be supported, since obviously the virtual connector for SharePoint uses that type of authentication. Yet with preview features like this, you never really can be sure of the supported scope.