Connect to External Tables with Dataverse Virtual Tables in Dataverse (Tutorial) 💡

In this tutorial, Brian Knight from Pragmatic Works demonstrates how to connect external data sources, such as SQL Server, SharePoint, and Excel, to Dataverse using Virtual Tables. This method helps save both time and storage capacity by allowing direct access to live data without needing to import it physically into Dataverse.

Understanding the Challenge

When syncing data with Dataverse, traditional methods like importing Excel spreadsheets or using Data Flows often result in data being outdated. For example, if a data flow runs at midnight, the data may become stale by 1 AM. Virtual Tables solve this problem by offering a live connection to external data, providing real-time access without the need for constant synchronization.

Benefits of Virtual Tables

• Real-Time Data Access: Virtual Tables allow you to view live data directly from external systems like SQL Server or SharePoint, ensuring that the information is always up-to-date.
• Save Time and Resources: Since Virtual Tables store only metadata, not actual data, they reduce the storage requirements in Dataverse, saving both time and capacity.
• Simplified Integration: Setting up Virtual Tables is much simpler compared to traditional methods, offering an easier way to connect to external data sources.

Setting Up a Virtual Table

Brian walks through the setup of a Virtual Table using a SQL Server database. Here’s a step-by-step guide:

1. Create a New Solution: Start by creating a new solution within the PowerApps Admin Center.
2. Create a New Table: Once in the solution, create a new table. For this example, Brian uses a table titled “Delete Me.”
3. Connect to External Data Source: Choose “Table from an Existing Data Source” and select SQL Server. Enter the connection details such as server name, username, and password to link Dataverse with the SQL Server database.
4. Configure the Table: After successfully connecting, select a table from the SQL Server database to use as the source. Adjust the table’s metadata as necessary to suit the requirements.
5. Save and Use the Data: The data from SQL Server is now linked in Dataverse. Any changes made to the data in Dataverse will be reflected back to SQL Server in real time.

Using the Virtual Table

Once the Virtual Table is set up, you can use it just like any other Dataverse table. For instance, Brian creates a lookup field to reference the SQL Server table within a different Dataverse table. He also demonstrates how to create an app (e.g., a model-driven app) to interact with the data in real-time.

The key feature here is that all changes (inserts, updates, and deletes) are directly reflected in the underlying SQL Server database, without the need for a sync process.

Limitations and Considerations

• Security: Virtual Tables work based on the permissions assigned to the user in Dataverse. Row-level security does not apply to external data sources.
• Latency: There may be a slight delay when pushing changes back to the external data source, especially with on-premise data.
• Schema Changes: If you make schema changes (such as adding or deleting columns) in the external system, you’ll need to remove and re-create the Virtual Table in Dataverse to reflect those changes.

Conclusion

Dataverse Virtual Tables provide a powerful way to connect external data sources with Dataverse, offering real-time data access and reducing the need for constant data imports or synchronization. By using this method, businesses can save time, storage, and resources while maintaining up-to-date information across systems.