Dataverse Virtual Table Creation Wizard (preview)

If someone of you was at Directions EMEA in Hamburg and attended the “Real world solution architecture with Dynamics 365 Business Central and Power Platform” session I made with Josh Anglesea, when explaining the implementation details of our business case there was a part related to connecting external data sources to our Dataverse environment via Virtual Tables. One of these external sources was a custom application with Azure SQL as backend and in the session I’ve explained how we have used the Dataverse Virtual Connector provider to expose some tables of this Azure SQL Database as native virtual tables on Dataverse.

If you remember, lots of steps was needed to do so:

1. Install the Dataverse Virtual Connector provider (via marketplace) on your Dataverse environment
2. In Power Apps porta, under Dataverse, create a new connection to your Azure SQL Database
3. In the Dataverse environment Advanced Settings, under Administration, create a new Virtual Entity Data Source by selecting the Dataverse Virtual Connector provider from the list of available data provider
4. With the connection reference and the virtual table data source setup, an Entity Catalog is automatically generated. The Entity Catalog is specific to the data source and will list all the tables that are in the respective data source.
5. To create a virtual table, a model driven app must be built for the entity catalog table.
6. Once the app is completed, you can select Publish to complete the app and use it later, or you can select Play to create your virtual table now without publishing the app. All eligible data sets from your data source will be provided in the app view.
7. Select the data set you wish to use from the entity catalog, and then select Edit in the navigation bar.
8. In the provided form set the Create or Refresh Entity column to Yes. Select the Primary Key and Primary Field of the virtual entity by using the dropdown lists to find the columns you want to use.
9. Save the record to create the virtual table.

I avoid to report here the screenshots for all these steps because now there’s a new easier way to do this.

Starting from few days ago, the public preview of the Dataverse Virtual Table Creation Wizard is available. With this new experience, you can create virtual tables from SharePoint and SQL (only from these two sources at the time of writing this post) without any code, and without leaving the maker portal.

To create a new Virtual Table from a SQL Database, now you can do the following. In your Dataverse environment, select Tables and then New table from external data:

Select your data source (SQL Server) and click on Add Connection:

Here add your connection details to your SQL Server Database (you need to install the gateway for accessing your on-premise SQL instance). Then you can map the fields (configure table names and columns details):

That’s it. The Virtual Table is created in your Dataverse environment with just few clicks. More easy than in the past, isn’t it? 🙂

Support for data sources is currently very limited (only Sharepoint and SQL Server) but new data sources will be added soon. This is just a beginning of a new experience.

Benefits of using Virtual Tables in these cases?

• You can integrate your SQL Server-based data directly in Dataverse allowing you to access your data in a single way. A Dataverse table can have relationships with multiple virtual tables from different sources and in this way you can create a consolidated data platform for your Power Platform and D365 applications.
• When using a virtual table you’re using a single API set to retrieve all data as the Dataverse OData APIs for CRUD will be used for both local and virtual tables.