Access Virtual tables from Microsoft Dataverse with SQL Server

Hello! Welcome to a new post in the best Data blog. In today’s post we will learn how to access virtual tables in Microsoft Dataverse.

But first… let’s make a brief introduction about what we should know about Microsoft Dataverse.

How Microsoft Dataverse works

Microsoft Dataverse allows us to securely store and manage data used by business applications.

Dataverse data is stored in a set of tables, and it includes a basic set of standard tables that encompasses typical scenarios, but you can also create organization-specific custom tables and populate them with data using Power Query.

App creators use Power Apps to build rich apps that use this data. Additionally, it provides a tabular data stream (TDS) endpoint that emulates an SQL data connection.

The SQL connection provides read-only access to the data in the target Dataverse environment table, allowing you to run SQL queries against the Dataverse data tables.

The Dataverse endpoint SQL connection uses the Dataverse security model for data access. Data can be obtained for all tables in Dataverse that a user has access to. As data to comment that only Azure Active Directory authentication is supported.

As we can see, there are three types of tables that indicate how the table entered the environment:

Standard: The Accounts, Business Units, Contacts, Tasks, and Users tables are examples of standard tables in Dataverse. These are tables that are imported as part of a managed solution and are accessible from the SQL connection.

From Activity: An activity can be considered any action from which an entry can be created in a calendar. An activity can have time dimensions that help determine when the action has occurred or when it will take place.
Virtuals: A virtual table is a custom Dataverse table that has columns that contain data from an external data source. Virtual tables appear in the application to users as normal table rows, but contain data that is taken from an external database dynamically at run time, such as Azure SQL Database.

Log in into SQL Server connection in SQL Server Management Studio

Below is an example of how to log in to the SQL connection in SSMS. We notice that the server name is the URL of the organization.

How can we access virtual tables?

Virtual tables are those that allow you to integrate data from external data sources through a simple representation of those, without data replication. The solutions, applications, flows, etc. they can use virtual tables as if they were native Dataverse tables.

In the following image we can see how the tables are displayed from Power Apps:

The virtual tables are not accessible from the SQL connection, in order to access them and process them in SQL we are going to make use of the Azure Synapse Link for Dataverse, which connects with Azure Synapse Analytics to allow you to obtain almost real-time information about your data. Microsoft Dataverse.

With seamless integration between Dataverse and Azure Synapse Analytics, Azure Synapse Link allows us to run analytics, business intelligence, and machine learning scenarios on your data.

In this way, the accessibility to our tables would be as shown in the following image:

We demo access any table in SQL Server language:

Azure Synapse allows you to quickly analyze your data.

Azure Synapse Analytics is an unlimited analytics service that brings together data integration, enterprise data warehousing, and big data analytics.

You can query data on your own terms as we just saw, using serverless or dedicated resources and at scale.

It also allows you to build analytics solutions with the Apache Spark engine. With Azure Synapse a wide range of possibilities open up to you, you can ingest, explore, prepare, manage and deliver data for immediate AI and machine learning needs, all from a single service.

If you liked it, you may be interested in our latest posts:

Change Data Capture in SQL Server

Private Endpoint between Azure Data Factory and Azure Database

Discover SQL Server 2022 Query Store Hints

If you want us to help you improve the performance of your SQL Server databases, contact us.

Know More