Create virtual table in Dataverse to connect external data source

Sending
User Review
0 (0 votes)

Hello Everyone,

Today I am going to share my experience with virtual tables which can be connected from SQL or Sharepoint to create a virtual table.
Lets gets started.
What is Virtual Table?

Virtual table is an intersect table between two different systems, i.e replica of the data source table(SQL or Sharepoint) in Dataverse.
Previously if you want to connect to external data source, you are supposed to integrate the two systems, like sharepoint or SQL Server data source into the Dataverse.
Now no more coding required to integrate the data source into Dataverse from SQL or Sharepoint, Many thanks to Microsoft.
Note: When a virtual table is created inside the Dataverse from SQL or Sharepoint, the data stored in Dataverse virtual table is not considered towards Dataverse storage even millions of records exists in SQL or Sharepoint.
You can make relationship between virtual table and dataverse tables no issues at all.
Lets see how to create virtual table.
Create a virtual table from either ” Table” or from the “Solution”.

Click on the new table chervon and select New table from external data.

New window will be prompted so make a connection either sharepoint or sql server, in this example i am going to make sharepoint connection, provide your sharepoint url and credential and make a connection if successful a green right tick will appear and then click next.

Now we have made a connection and the next screen will show the sharepoint lists available in the connection and make a selection and click “next” see below screen.

As you can see from the above screenshot its clearly asking for sharepoint list and also check the box for configure table and column names that will be used in Dataverse.
Next Configuration step:
Update the table name and details of the table columns before it is created in Dataverse.

Display name, Plural name, Schema name and Primary key will auto created from the data source.
External columns will be displayed once you are happy with the columns and click next.
Final step is to Review and Finish:

Make sure the external data source and dataverse table are correct, if not you can edit from here, if you are happy then click finish.
Virtual table will be created in Dataverse refer to above screenshot and you can start using this table in reporting or any other business.
I hope this helps.
Malla Reddy(@UK365GUY)
#365BlogPostsin365Days