Using Dataverse virtual tables

Dataverse Virtual Tables (previously known as Virtual Entities) are a feature in Microsoft Dataverse that allows you to access and display data from external data sources in the same way as native Dataverse tables. Unlike standard Dataverse tables, virtual tables do not store data locally within Dataverse. Instead, they provide a virtualized view of the external data, allowing users to interact with it through the same interface used for native Dataverse tables, without requiring data duplication.

Virtual tables are especially useful when you need to integrate external systems with Dataverse but do not want to move or replicate the external data. For example, you can create virtual tables that pull data from on-premises databases, third-party cloud systems, or other external data sources.

1. Overview of Dataverse Virtual Tables

Dataverse Virtual Tables serve as an abstraction layer that connects your Dataverse environment to external data sources, making it appear as if the data resides natively in Dataverse.

Key Benefits:

No Data Replication: You don’t need to copy external data into Dataverse; it remains in its original location.
Unified User Experience: Users can interact with external data in the same way they interact with standard Dataverse tables.
Integration with Power Apps, Power Automate, and Power BI: Virtual tables can be used seamlessly within Power Apps, Power Automate workflows, and Power BI reports, just like any other Dataverse data.
External Data Source Flexibility: You can connect various data sources, such as SQL databases, SharePoint lists, web services, and more.

2. Prerequisites for Using Virtual Tables

Before you can use virtual tables, you need to meet the following prerequisites:

Power Apps or Dataverse environment: Ensure you have a Dataverse environment in which you want to set up virtual tables.
Data Source: An external data source (e.g., SQL Server, OData web service, or another supported system) that contains the data you want to integrate.
Data Provider: You need to configure and connect to the external data source via a Data Provider in Dataverse. This is a connector that allows Dataverse to access and retrieve data from external systems.
Premium Licensing: Virtual tables are a feature included in premium licensing, so ensure you have the appropriate license to use them.

3. Setting Up Virtual Tables in Dataverse

Follow these steps to create and configure virtual tables in Dataverse:

Step 1: Set Up the Data Provider

To connect to an external data source, you first need to set up a Data Provider in Dataverse.

Go to Power Platform Admin Center.
Navigate to Environments and select the environment where you want to set up the virtual table.
Under Data, select Virtual Tables.
Select Data Providers and click New.
Choose the type of data provider based on your external data source (e.g., SQL Server, OData, Web API).
Enter the necessary connection details (such as connection string, authentication credentials, etc.).
Test the connection to ensure it’s configured correctly.

Step 2: Define the External Data Source (Entity)

Once the data provider is set up, you can define an external data entity that will represent the external data source.

In Power Apps, go to the Dataverse section.
Select Virtual Tables and click New Virtual Table.
Enter a name for the virtual table and select the appropriate Data Provider.
Specify the entity you want to connect to in the external system (e.g., a SQL table, an OData service, or a web API endpoint).
Define the schema for the external entity, including field types, relationships, and keys. You may need to map the fields from the external data source to Dataverse-compatible field types.
Save the virtual table configuration.

Step 3: Map External Data to Dataverse

After defining the external data entity, you need to map the fields from the external system to the virtual table. This is crucial because Dataverse will interpret the external data based on this mapping.

Define the field mappings between the external data source and Dataverse.
Map the columns in the external data source to the corresponding fields in the virtual table schema.
Configure additional attributes, such as primary keys and foreign keys, to ensure proper data relationships.
Save and test the configuration to ensure that the data is accessible.

Step 4: Create Relationships with Other Dataverse Tables

You can create relationships between virtual tables and native Dataverse tables, allowing you to integrate the external data into your app or workflows.

In Power Apps, navigate to the Dataverse section and select Tables.
Select a virtual table and choose Relationships to add related entities.
Create one-to-many, many-to-one, or many-to-many relationships as needed.
Define the relationship details and ensure the data integrates well with the other entities in Dataverse.

Step 5: Use the Virtual Table in Power Apps, Power Automate, and Power BI

Once the virtual table is configured, it behaves just like a native Dataverse table. You can:

Use the virtual table in Power Apps: Display the data in forms, views, and galleries, just like any other Dataverse table.
Automate workflows with Power Automate: Trigger workflows based on changes to the virtual table, such as when data is created or updated.
Visualize data in Power BI: Use the virtual table as a data source in Power BI for reporting and analysis.

4. Key Features of Virtual Tables

Real-Time Data Access: Virtual tables provide real-time access to data stored externally without replication or synchronization delays.
Standardized CRUD Operations: You can create, retrieve, update, and delete records (CRUD) in virtual tables just like any other Dataverse table. However, the data is not physically stored in Dataverse but is instead fetched from the external system.
Business Rules and Logic: Virtual tables support the same business rules, workflows, and automation logic that can be applied to native Dataverse tables, enabling you to apply consistent business logic across your apps.
Supports Relationships: You can create relationships between virtual tables and native Dataverse tables, allowing for data to flow and be referenced across systems.
Security and Permissions: Virtual tables support the same security and permissions model as native Dataverse tables, ensuring that data access is controlled.

5. Use Cases for Virtual Tables

External CRM Systems: Integrate data from an external CRM system (e.g., Salesforce) into Dataverse for reporting, analytics, and app integration.
ERP Integration: Bring external ERP data (e.g., from SAP or Oracle) into Dataverse for enhanced operational reporting or cross-system workflows.
On-Premises Data: Access and use data from on-premises SQL servers or legacy systems without needing to migrate the data into Dataverse.
Cloud Data Sources: Integrate with cloud-based data services like SharePoint, Azure SQL, or other SaaS platforms.

6. Best Practices for Virtual Tables

Optimize Performance: Since virtual tables fetch data in real-time from external systems, ensure that the external data source is optimized for performance. Poor performance in the external system can impact the user experience in Dataverse.
Error Handling: Implement error handling mechanisms for scenarios where the external data source is unavailable or returns unexpected data.
Security: Ensure secure connections between Dataverse and the external data source. Use authentication mechanisms like OAuth, API keys, or custom connectors as appropriate.
Data Consistency: Since data is not stored in Dataverse, ensure that the external system maintains data integrity and consistency to prevent issues when the data is accessed via virtual tables.