Configuring Virtual Tables in Microsoft Dataverse

In the modern enterprise landscape, data is rarely confined to a single system. Organizations often find themselves managing a sprawl of information across SQL databases, legacy ERP systems, third-party APIs, and cloud storage solutions like Azure Cosmos DB. Historically, the solution to this fragmentation was to build complex synchronization pipelines—often referred to as ETL (Extract, Transform, Load) processes—to move data from these external sources into Microsoft Dataverse. While effective, synchronization introduces latency, increases storage costs, and creates the risk of data inconsistency.

Virtual tables offer a different approach. Instead of moving the data, a virtual table allows Dataverse to "read" and "write" to an external source in real-time. To the end-user or a Power App, a virtual table looks and behaves almost exactly like a standard Dataverse table. However, the data remains in its original location. This lesson explores how to configure virtual tables, the different providers available, and the best practices for implementing them in a production environment.

Understanding the Virtual Table Architecture

At its core, a virtual table is a definition in the Dataverse metadata store that points to an external data source. When a user requests data from a virtual table—perhaps by opening a view in a Model-driven app—Dataverse does not look in its own SQL storage. Instead, it passes that request to a "Virtual Table Provider." This provider acts as a translator, converting the Dataverse query into a format the external source understands (like a SQL query or an OData request) and then converting the results back into a format Dataverse can display.

This architecture provides a "live" connection. If a record is updated in the external SQL database, that change is immediately visible in the Power App. Conversely, if the virtual table is configured for updates, a change made in the Power App is pushed back to the external source instantly. This eliminates the "stale data" problem common with scheduled synchronization.

The Components of a Virtual Table

To successfully configure a virtual table, you must understand three primary components:

1. The External Data Source: This is where the actual data lives (e.g., an Azure SQL database or a web API).
2. The Virtual Table Provider: This is the logic that handles the communication. Microsoft provides several "out-of-the-box" providers, but developers can also write custom ones using C#.
3. The Connection Reference: This defines the credentials and endpoint details used to access the external source securely.

Callout: Virtual Tables vs. Data Integration

It is important to distinguish between data virtualization and data integration. Integration (using tools like Power Query or Azure Data Factory) physically copies data into Dataverse. This is best for high-performance reporting and when you need to use Dataverse features like auditing or row-level security that the external source doesn't support. Virtualization is best when you need real-time access, have massive datasets that would be too expensive to store in Dataverse, or must comply with data residency laws that prevent moving data out of a specific region.

Types of Virtual Table Providers

Microsoft offers multiple ways to create virtual tables, depending on your technical expertise and the nature of the data source.

1. Virtual Connector Provider (Recommended)

This is the most modern and accessible method. It utilizes the existing library of Power Platform connectors (like SQL Server, Excel Online, or SharePoint). This provider significantly simplifies the setup process by offering a wizard-based experience in the Power Apps Maker Portal. It handles much of the boilerplate mapping automatically.

2. OData v4 Provider

If your external data is exposed via an OData v4 web service, you can use this native provider. OData is a standardized protocol for building and consuming RESTful APIs. This is a common choice for connecting to modern ERP systems like SAP or Microsoft Dynamics 365 Business Central (when it’s not already integrated).

3. Azure Cosmos DB Provider

Specifically designed for Azure Cosmos DB (using the SQL API), this provider allows you to surface non-relational JSON documents as structured tables in Dataverse. This is particularly useful for high-scale applications that generate large volumes of telemetry or log data.

4. Custom Providers

For unique scenarios where no standard connector exists—such as a proprietary legacy API—developers can write a custom Virtual Table Provider using the Dataverse Plug-in framework. This requires writing C# code to handle the Retrieve, RetrieveMultiple, Create, Update, and Delete messages.

Step-by-Step: Configuring a Virtual Table via SQL Connector

The most common real-world scenario is surfacing data from an Azure SQL database. Let’s walk through the process of setting this up using the Virtual Connector Provider.

Step 1: Prepare the External Source

Before touching Dataverse, ensure your external SQL table has a primary key. Dataverse requires every record to have a unique identifier. Ideally, this should be a GUID (uniqueidentifier in SQL), but the connector can also map to BigInt or Integer keys by creating a mapping.

Step 2: Create the Connection

1. Navigate to the Power Apps Maker Portal.
2. Go to Connections (under the "More" or "Data" tab).
3. Select New Connection and search for SQL Server.
4. Provide the server name, database name, and authentication credentials (e.g., SQL Authentication or Azure AD Integrated).

Step 3: Configure the Virtual Table

1. In the Maker Portal, go to Tables.
2. Click the dropdown on the New Table button and select New table from external data.
3. Select the SQL connection you created in the previous step.
4. Choose the specific SQL table you want to virtualize.
5. Configure Table Settings: You will be asked to define the "External Name" (the name in SQL) and the "Display Name" (what users see in Dataverse).
6. Map the Primary Key: Select the column that serves as the unique ID. If it is not a GUID, Dataverse will handle the translation internally, but you must specify which column it is.
7. Finish: Dataverse will generate the metadata. This process may take a minute as it creates the columns and relationships in the background.

Note: When you create a virtual table, Dataverse creates a "shadow" version of the schema. It does not create any physical storage for the rows. If you delete the virtual table in Dataverse, the data in your SQL database remains untouched; only the "window" into that data is removed.

Mapping Fields and Data Types

One of the most critical aspects of virtual table configuration is field mapping. Dataverse has its own set of data types, which may not always align perfectly with the external source.

Data Type Compatibility

When the wizard runs, it attempts to guess the best mapping. However, you should manually verify these:

• Strings: SQL Varchar or Nvarchar maps to Dataverse Text or Text Area.
• Numbers: SQL Int maps to Dataverse Whole Number. SQL Decimal or Float maps to Dataverse Decimal.
• Booleans: SQL Bit maps to Dataverse Yes/No.
• Dates: SQL DateTime maps to Dataverse Date and Time. Be mindful of time zone conversions, as Dataverse often expects UTC.

External Names

Every column in a virtual table has two names: the Logical Name (used by Dataverse) and the External Name (the actual column name in the source). If your SQL column is named Cust_Primary_Phone_Number, you might want the Dataverse logical name to be new_phone. Accurate mapping of the External Name is the most common reason virtual tables fail to load data. If there is a typo in the External Name, the provider will return an error stating the column could not be found.

Advanced Scenario: Custom Virtual Table Providers

Sometimes, a standard connector isn't enough. Perhaps you need to aggregate data from multiple API endpoints before displaying it in a single table. In this case, you must write a custom provider.

A custom provider is a .NET assembly (a Plug-in) that is registered on specific messages. Unlike standard plug-ins that trigger after data is saved to the database, these plug-ins trigger instead of the standard Dataverse data operations.

Example: Custom RetrieveMultiple Implementation

When a user views a list of records, Dataverse sends a RetrieveMultiple message. Your code must intercept this, call the external API, and convert the JSON response into an EntityCollection.

public class MyVirtualTableProvider : IPlugin
{
    public void Execute(IServiceProvider serviceProvider)
    {
        var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
        
        // Check if this is the RetrieveMultiple message
        if (context.MessageName == "RetrieveMultiple")
        {
            // 1. Extract the query from the context
            QueryExpression query = (QueryExpression)context.InputParameters["Query"];

            // 2. Call your external API (e.g., using HttpClient)
            string apiResponse = CallExternalApi(query);

            // 3. Parse the response and create Entity objects
            EntityCollection results = ParseApiResponse(apiResponse);

            // 4. Set the output parameter to return the data to Dataverse
            context.OutputParameters["BusinessEntityCollection"] = results;
        }
    }

    private string CallExternalApi(QueryExpression query)
    {
        // Logic to translate QueryExpression to a REST API URL
        // and execute the web request goes here.
        return "{ 'id': '1', 'name': 'Sample Record' }";
    }

    private EntityCollection ParseApiResponse(string json)
    {
        EntityCollection collection = new EntityCollection();
        // Logic to convert JSON to Entity objects goes here.
        Entity record = new Entity("new_virtualtable");
        record["new_virtualtableid"] = Guid.NewGuid();
        record["new_name"] = "Sample Record from API";
        collection.Entities.Add(record);
        
        return collection;
    }
}

Registering the Custom Provider

Once the code is written, you use the Plugin Registration Tool to:

1. Register the Assembly.
2. Register a Data Provider. This tells Dataverse which assembly handles the data operations.
3. Create a Data Source record in the Dataverse environment that points to your provider.
4. Finally, create the Virtual Table and associate it with that Data Source.

Callout: The Importance of GUIDs

Dataverse is built on the assumption that every record has a unique GUID. If your external source uses an integer (like an Identity column in SQL), your custom provider or the Virtual Connector must "spoof" a GUID. The Virtual Connector does this by deterministically hashing the integer ID into a GUID format. If you are writing a custom provider, you must ensure that the same external record always returns the same GUID, or features like record linking and deep-linking will break.

Relationships and Virtual Tables

One of the most powerful features of virtual tables is their ability to interact with native Dataverse tables. You can create relationships between them, but there are specific rules you must follow.

Many-to-One (N:1) Relationships

You can create a lookup field on a Native Table that points to a Virtual Table. For example, you could have a native "Work Order" table with a lookup to a virtual "External Asset" table stored in a maintenance database. This allows you to associate your Dataverse processes with external data seamlessly.

One-to-Many (1:N) Relationships

Creating a relationship where the virtual table is the "parent" and the native table is the "child" is also supported. However, keep in mind that since the parent data isn't in Dataverse, you cannot use features like "Cascading Delete." If a record is deleted in the external SQL database, Dataverse has no way of knowing it should delete the related "child" records in its own database.

Virtual to Virtual Relationships

You can also relate two virtual tables to each other, provided they both reside in the same external data source. This is common when virtualizing an entire schema (e.g., Orders and Order Lines from an ERP).

Performance Considerations and Best Practices

Because virtual tables rely on real-time communication with external systems, performance is a major consideration. A poorly configured virtual table can make a Model-driven app feel sluggish or cause it to time out.

1. Optimize the Source

Dataverse will pass filters (queries) down to the external source whenever possible. If a user filters a view by "Created On" date, Dataverse sends that filter to SQL. If the SQL column isn't indexed, the query will be slow. Always ensure that the columns used for filtering or sorting in Power Apps are indexed in the source database.

2. Limit the Column Count

Avoid including "heavy" columns in your virtual table definition if they aren't needed. For example, if your SQL table has a MAX varchar column containing large logs or XML data, don't map it to Dataverse unless it's strictly necessary for the application. Every mapped column adds overhead to the data retrieval.

3. Avoid N+1 Query Problems

When displaying virtual table data in a subgrid on a form, Dataverse is generally efficient. However, if you write a custom Plug-in or Power Automate flow that loops through virtual records and performs a lookup for each one, you can quickly hit API limits or cause performance bottlenecks. Try to use batch operations or RetrieveMultiple with specific filters instead.

4. Security Nuances

This is a critical point: Dataverse Security Roles do not automatically apply to the external data source. If a user has "Read" access to the virtual table in Dataverse, they can see all records the Connection Reference can see. If you need row-level security (e.g., User A should only see their own records), you must implement that security either in the external source (using SQL Row-Level Security) or via complex logic in a custom provider.

Warning: No Auditing or Change Tracking

Standard Dataverse features like "Audit Logs" and "Change Tracking" do not work with virtual tables. Because Dataverse doesn't own the data, it cannot track changes made directly in the external system. If you require a history of who changed what, you must implement auditing within the external database itself.

Comparison: Standard Tables vs. Virtual Tables

Common Pitfalls and How to Avoid Them

Even experienced architects run into trouble with virtual tables. Here are the most common mistakes and how to navigate them.

1. The "Record Not Found" Error

This often happens when the Primary Key mapping is incorrect. If you are using the SQL Connector and your Primary Key is a BigInt, but you told Dataverse it was a String, the retrieval will fail. Always double-check that the data types in the "External Name" mapping match the source exactly.

2. Timeouts in the UI

Dataverse has a standard timeout for plug-in execution (usually 2 minutes). If your external API or SQL database takes 30 seconds to respond, the user experience will be poor, even if it doesn't technically "time out." If the external source is slow, consider using a data integration (ETL) approach instead of virtualization.

3. Missing GUIDs in Custom Providers

If you are writing a custom provider and you generate a new Guid() every time a record is retrieved, you will break the platform. Dataverse uses the GUID to maintain the "identity" of the record. If the GUID changes every time the page refreshes, you won't be able to link to that record or use it in a lookup. You must create a consistent mapping between the external ID and the Dataverse GUID.

4. Authentication Failures

Virtual tables often use a "Service Account" via the Connection Reference. If that account's password expires or its permissions are revoked in the external system, the virtual table will stop working for all users immediately. Use managed identities or service principals where possible to avoid password expiration issues.

Quick Reference: Troubleshooting Checklist

If your virtual table isn't displaying data, work through this checklist:

• Connectivity: Can you reach the external endpoint from the internet? (Dataverse is a cloud service and must be able to "see" the source).
• Permissions: Does the account used in the Connection Reference have SELECT (and UPDATE/INSERT if applicable) permissions on the external table?
• Metadata: Does the External Name of the table and every column match the source exactly (including case sensitivity in some databases)?
• Primary Key: Is the Primary Key column identified correctly, and does every row in the source have a value in that column?
• Plugin Registration: If using a custom provider, is the plug-in registered on the correct message and is the Data Source record pointing to the correct assembly?

Summary and Key Takeaways

Virtual tables are a powerful tool for creating a unified data experience without the overhead of data synchronization. They allow you to treat external data as if it lived inside Dataverse, enabling the use of Model-driven apps, Power Automate, and the Power Pages portal on top of legacy or external systems.

However, they are not a "silver bullet." The decision to use a virtual table must be weighed against performance requirements, security needs, and the limitations of the virtual table framework.

Key Takeaways:

1. Live Access: Virtual tables provide a real-time window into external data, ensuring that users always see the most current information without waiting for sync cycles.
2. Storage Efficiency: Because data remains in the source, virtual tables do not consume valuable Dataverse database capacity, making them ideal for massive datasets.
3. Provider Variety: You can choose from easy-to-use Virtual Connectors for SQL and SharePoint, or build highly complex Custom Providers for specialized APIs.
4. Mapping is Crucial: Success depends on the accurate mapping of External Names and Data Types. Most errors stem from mismatches between the Dataverse metadata and the external schema.
5. Performance Responsibility: The performance of a virtual table is only as good as the performance of the external source. Indexing and query optimization in the source system are mandatory.
6. Security Gap: Remember that Dataverse security roles do not automatically filter the external data. Access is governed by the connection credentials, so additional security design may be required.
7. Feature Trade-offs: Be aware of what you lose when going virtual—specifically auditing, change tracking, roll-up fields, and offline support. If these features are mission-critical, an ETL integration is the better choice.