Ever since Microsoft introduced the new Capacity model for Dynamics 365, customers have been forced to rethink what to store, how to store, and where to store their data.
While there are many ways to answer those questions, we will stick with using Virtual Tables (entities). Lets get started with virtual tables!
Virtual Tables:
Virtual tables or entities provide us an easy way to integrate data with Dataverse without storing or consuming the capacity of Dataverse.
Recently Microsoft enabled doing Create, Update and Delete operations as well in addition to the Read operation. This opens a lot of avenues in terms of usage and implementation.
Limitations:
For our case, we will assume AdventureWorks sample database, and in this blog post, I will show how easy it is to set up Customers as a Virtual Table (entity) in Dataverse.
As a first step, I have already provisioned the AdventureWorks sample database in Azure. Next, we will be implementing a Custom Virtual Table provider that will support operations.
Read Operation:
Let us get our SQL query in shape first. We want to show the following columns in Dataverse:
- Customer Id
- Title
- First Name
- Last Name
- Company Name
- Email Address
- Phone
That will look like this:
SELECT [CustomerID]
,[Title]
,[FirstName]
,[LastName]
,[CompanyName]
,[EmailAddress]
,[Phone]
,[rowguid]
FROM [SalesLT].[Customer]
Plug-in Setup:
We will define our SQL connection helper class:
This will be used to connect to our Azure SQL database. A better approach would be to utilize Environment Variables with a low privileged/read-only SQL account.
Another approach would be to implement OData provider and return that data via an API call.
Firstly, let us implement the retrieve plug-in:
Finally, let us implement the retrieve multiple plug-in:
A couple of pointers regarding security, an OData API-based approach as the provider where the external system can provide permissions would be desirable. Azure AD can be used to define roles against the application identity, and users can then be assigned those roles.
Conclusion:
And there you have it. With just a simple custom provider plug-in implementation, customers display in Dataverse without consuming any storage capacity. A similar approach can be utilized for other objects, especially those that are purely read-only objects.
