Data is possibly one of the world’s most valuable resources at the moment. There are even people arguing about whether data is even more valuable than oil. A resource with all this value should be available to as many people as possible. On the other hand, however, access to data must respect privacy and security for all.
I’m really excited about the new Virtual Connectors that are to be announced by Microsoft (I don’t know when yet) and to hold my anxiety I decided to play around with the preview that is available in the App source and today’s post is all about that.
What is a Virtual Connector Provider
A Virtual Connector Provider (VCP) is an out-of-the-box provider just like the OData v4 Provider that can be used to create virtual tables in the Dataverse, but the VCPs will allow us to use existing connectors to surface the data instead of using an external service such as an OData service.
Virtual Tables created using Virtual Connector Providers work slightly differently than a standard Virtual Table will automatically generate entity catalogs with a list of all the available entities by retrieving the metadata from the external data source and we will be able to create virtual tables from the entity catalog.
By the way, I heard about this in the last Power Cat video where Srinath Kannan talks about Virtual Tables
Once again, this is a PREVIEW feature, so don’t go out there installing it in production just yet!
First thing is to get the app installed and you can do it from the app source here
The installation will ask for some details and will redirect you to your Power Platform admin center so you can choose the environment that you want to install the VCP
Once it’s done you should be able to see the new solution within the maker portal
Setting up the external data source connection
Now we need to create the connection to the external database, you can do that in the Data > Connection menu in the Maker Studio
With the connection established we are now able to create a new Connection Reference, which you can do by going to your Solution > New > Connection Reference (Preview)
We are all set now, let’s go and create the Virtual Table Data Source by navigating to Advanced Setting > Administration > Virtual Entity Data Source we can now see this new option “Virtual Connector Data Provider” which is what was installed in beginning.
You will need to register an application user in your environment to be used by a connector to authenticate in your dataverse (make sure to grant the appropriate access to the user) and you will also need to register a app secret
Once the VCP Data source is created an Entity Catalog is generated automagically for your data source in Dataverse, and you can see them through the Maker Studio or using advanced find
Creating the Virtual Table
When you fetch the Entity Catalog records you will see the list of tables available in your external data source, to create a virtual table in Dataverse open the Entity Catalog record for the table that you want to virtualize and set the field Create or Refresh Entity to Yes, and select what is the Primary field of the Virtual Entity
After saving the Entity Catalog record the virtual table is automatically created along with all the fields available in the table in your external data source. How cool is that?
You can now Create, Read, Update and Delete records in your external data source using Power Platform Data Virtual Connectors
The new Virtual Connector Provider is another step towards the low code movement, before Virtual Connectors if you had an underline data source and needed the data surfaced to and from Dataverse you would need to provide either a service to expose the data to the cloud or a custom provider to interact with the data source, both options require custom development, using Virtual Connectors the responsibility lies with the connectors which are already available (As long as the custom provider supports VCP)
A few things to consider:
- Virtual tables are no longer required to have an associated GUID as a primary key with the virtual connector provider. The provider automatically maps the primary key associated with the external data source when creating the virtual table.
- After setting up the service principle that will be used to authenticate with Dataverse make sure to share the connection created with your whole organization
- If you have multiple environments make sure to set the PowerApps Environment field with the desired environment ID
Excellent stuff! Really look forward to GA. I was following through the steps, i.e. SQL connection, Connection Reference, Virtual Data entity source etc, but got stuck at the Entity Catalog step. The Entity Catalog advanced find returned an error instead of supposedly the list of virtual entities (when trying to view data on the Maker Portal, it said remote server returned an error 404) – I wonder what I missed, and if you can provide some pointers, that’s much appreciated.