AWS Data in Dataverse with Virtual Table – Part 1
Be brave, be curious, explore new worlds! That’s why I took a look outside of Microsoft. I’m sure it won’t surprise you, but there are other competitors like Amazon and Google that provide cloud offerings.
This is one reason why data integration is so important for modern digital enterprises. For example, users often need information from other systems in their business process to make decisions. For Dataverse it doesn’t matter, where these data exist. That’s because I can use more than 800+ connectors to access my data.
Furthermore, there are virtual tables. I explained in my previous blog (Business Central in Dataverse as Virtual Tables) how easy it is to integrate and access my business data from Business Central in Dataverse. Unlike connectors, I can also use these virtual tables in model driven apps as forms or views.
So let’s be brave and curious! Join my new journey and see how I integrate my external data as virtual tables in Dataverse.
Amazon Web Services
But where is my data? What if my data does not exist in the Microsoft Universe? What if I have a hybrid cloud and I want to include my external data into my Power Platform business process? Let’s say my external data is stored in a DynamoDB in my Amazon AWS Cloud.
An AWS DynamoDB is a fast, flexible NoSQL database service for single-digit millisecond performance at any scale similar to an Azure Cosmos DB to store my data. Nothing special.
The question is more or less: How can I access my data from Dataverse?
Is this a now challenge? Let’s see if I can setup a virtual table for my data source…
A Data Source for my Virtual Tables
Everything starts in my Dataverse solution. My table does not exist in Dataverse. For that reason, I try to add a Table from external data:
A wizard opens and help me to setup a virtual connector provider to connect Dataverse with my external data source. Hmm, I can only choose between SQL Server or SharePoint:
This is not what I have expected. However, I know there must be another option.
Microsoft Dynamics 365 Business Central provide a lot of APIs. Additionally, these APIs are used by the Business Central Virtual Table app, to setup virtual tables in Dataverse. In detail, this created virtual tables uses my Business Central APIs as OData endpoints.
In conclusion, I have these options:
But where is my 3rd option? Where can I setup an OData Endpoint? Where is my Virtual Table Data Source provider?
Yes it does not exist in the modern Dataverse interface and can’t be added yet:
For that reason, I navigate to my Advanced settings:
Then I choose Administration:
And I will find Virtual Entity Data Sources:
Now I should be able to set up a data source for my DynamoDB…
OK, I know there this option does not exist for an AWS DynamoDB. However, I can use an OData v4 Data Provider:
Afterwards, I must enter a Name and an URL for my data provider. Furthermore, I can specify Query Parameters and Header Parameters for my endpoint:
You see, I can create an OData Endpoint to access my AWS DynamoDB data. But I need an additional step. Yes I need an OData endpoint, which provides my AWS DynamoDB data. Let’s go to AWS!
Creating an OData Endpoint in AWS
My external data exist in my Amazon AWS Cloud. To solve this problem and to access my data from Dataverse, I must create an OData Endpoint in AWS.
In detail, my AWS Account contains a DynamoDB with my data. Furthermore, I will create an AWS Serverless Application for my OData API hosted on AWS Lambda Function. Finally I’ll expose my OData API in an AWS API Gateway. Here is my simplified target architecture:
Code Catalyst
For my demo I use Code Catalyst to set up a new project. Furthermore, I start with a blueprint as template:
Here I choose a .NET serverless application based on AWS Lambda:
There are a lot options to create a new project. I use also ASP.NET Core C# for my project:
Additionally, I specify my project parameters and the region, where my AWS stack will be deployed:
Finally, I create my new Code Catalyst project:
Wow, I’m impressed! This template provide everything what I need. First of all, I got my C# project prepared in a Git repository. Additionally, there is also a CI/CD pipeline added for my project:
And finally, AWS Cloud Formation have created my whole AWS Stack with all resources for my OData API endpoint:
In the CloudFormation designer I can also review and adjust my used template. I see all my components, roles and permissions:
As you see, there is currently no DynamoDB included. This is OK for now, because I will exclude the DynamoDB to simplify my example for you. In other words I will focus firstly on the OData endpoint and the integration in Dataverse.
OData Endpoint with ASP.NET Core
I start with a very small data model to simplify my blogpost. For this I use a single class “ProductCategory”. This class will later present an entity or record in my DynamoDB. The respective data model class is this:
public class ProductCategory
{
[Key]
[JsonPropertyName("Id")]
public Guid Id { get; set; }
[JsonPropertyName("Name")]
public string? Name { get; set; }
[JsonPropertyName("Description")]
public string? Description { get; set; }
}I have added also a couple packages in my C# project:
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net6.0</TargetFramework>
<!-- ... -->
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Amazon.Lambda.AspNetCoreServer" Version="7.2.0" />
<PackageReference Include="Microsoft.AspNetCore.OData" Version="8.0.12" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.2" />
<PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
</ItemGroup>
</Project>The package “Microsoft.AspNetCore.OData” provide the needed OData functionality for my endpoint and my controller class. Per example I use attributes to specify the Http routes:
[ApiController]
[ODataAttributeRouting]
[ODataRouteComponent("odata/ProductCategories")]
public class ProductCategoryController : ODataController
{
private readonly ILogger _logger;
public ProductCategoryController(ILogger<ProductCategoryController> logger)
{
_logger = logger;
}
[ApiExplorerSettings(IgnoreApi = false)]
[EnableQuery]
[HttpGet("odata/ProductCategories")]
public async Task<IQueryable<ProductCategory>> Get(ODataQueryOptions options)
{
return new []
{
new ProductCategory {Id = Guid.Parse("10000000-0000-0000-0000-000000000000"), Name = "Category 1", Description = "..." },
new ProductCategory {Id = Guid.Parse("20000000-0000-0000-0000-000000000000"), Name = "Category 2", Description = "..." },
}.AsQueryable();
}
[ApiExplorerSettings(IgnoreApi = false)]
[EnableQuery]
[HttpGet("odata/ProductCategories({key})")]
public async Task<ProductCategory> Get(Guid key)
{
return new ProductCategory { Id = key, Name = "Category 1", Description = "..." };
}
}Furthermore, the package “Microsoft.EntityFrameworkCore” helps me to define my EdmModel in “GetEdmModel“. All needed configuration is done in my Startup class. Furthermore, you will discover a couple commands to setup controllers, swagger, OData functionality, and much more…:
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container
public void ConfigureServices(IServiceCollection services)
{
services.AddRouting();
services.AddODataQueryFilter();
services.AddMvcCore(options => options.EnableEndpointRouting = false);
services.AddControllers()
.AddOData(option => option
.AddRouteComponents("odata", GetEdmModel())
.EnableQueryFeatures()
.Select().Expand().Filter().Count().OrderBy().SkipToken()
);
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "ODataAPI", Version = "v1" });
});
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "ODataAPI v1"));
app.UseDeveloperExceptionPage();
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseODataBatching();
app.UseODataQueryRequest();
app.UseODataRouteDebug("debug");
app.UseEndpoints(endpoints =>
{
endpoints.MapControllerRoute("odata", "odata");
});
}
// Define the EdmModel for Dataverse
private IEdmModel GetEdmModel()
{
var odataBuilder = new Microsoft.OData.ModelBuilder.ODataConventionModelBuilder();
odataBuilder.AddEntityType(typeof(ProductCategory));
odataBuilder.EntitySet<ProductCategory>("ProductCategories");
return odataBuilder.GetEdmModel();
}
}My resulting endpoint provide this API visible in Swagger UI:
Entity Data Model (EDM)
An important part of my my OData service is the Entity Data Model (EDM). This model describe, how Dataverse can use my service endpoints. All magic is done in GetEdmModel in my Startup.cs:
// Define the EdmModel for Dataverse
private IEdmModel GetEdmModel()
{
var odataBuilder = new Microsoft.OData.ModelBuilder.ODataConventionModelBuilder();
odataBuilder.AddEntityType(typeof(ProductCategory));
odataBuilder.EntitySet<ProductCategory>("ProductCategories");
return odataBuilder.GetEdmModel();
}As result, Dataverse can use the $metadata route to get information about my service, its entities and relations. I can also query this information with the following Http call:
GET ...execute-api.eu-west-1.amazonaws.com/prod/odata/$metadata
X-API-Key: 123457890As you see, the result contains all information needed by Dataverse. In detail the metadata describe my entity model. For example, I have created a type ProductCategory which is provided by the entity set ProductCategories:
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0"
xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
<edmx:DataServices>
<Schema Namespace="MMe2k.AWSProductCatalog.Models"
xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityType Name="ProductCategory">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Edm.Guid" Nullable="false" />
<Property Name="Name" Type="Edm.String" />
<Property Name="Description" Type="Edm.String" />
</EntityType>
</Schema>
<Schema Namespace="Default"
xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityContainer Name="Container">
<EntitySet Name="ProductCategories" EntityType="MMe2k.AWSProductCatalog.Models.ProductCategory" />
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>Perfect, I’m ready to add my virtual entity data source in Dataverse!
AWS Virtual Entity Data Sources
First of all, my AWS API Gateway provides base URL for my endpoint:
Furthermore, an API Key is required for my API request methods:
This means, I setup my virtual entity data source as follows:
I found out, my configured virtual entity data source is stored in this Dataverse table:
To summarize, I have created my OData endpoint in AWS and I have configured my virtual entity data source in Dataverse. Now it’s time to bring everything together and add a virtual table for my product categories in Dataverse.
Set up Virtual Tables
Let’s recap, my Entity Data Model describe my OData endpoint for Dataverse. This means, I use this information to setup my virtual tables in Dataverse. Again, all information are provided by the $metadata route:
GET ...execute-api.eu-west-1.amazonaws.com/prod/odata/$metadata
X-API-Key: 123457890Here I have highlighted the entities, entity sets, and property names for you:
Furthermore, there is another route available in my OData endpoint:
GET ...execute-api.eu-west-1.amazonaws.com/Prod/odata/
X-API-Key: 123457890The result of this route is here:
In my next blog post I’ll extend my model and describe, how you can use relations and setup Lookup Fields in Dataverse. But for now, let’s start with setting up my virtual table for my ProductCategories.
Defining my Virtual table
I start by adding a normal table and change my table Type to “Virtual” (step 1). Afterwards, I enter the external name of my entity and its entity set (step 2):
I configure also the external name of my primary column “Name” before I save my new table (step 3):
In the next step I navigate to my table properties. I select under advanced settings my previously created data source “AWS Product Catalog”:
Last but not least, I change also the external name of my key column “AWS Product Category” to “Id” (step 5):
Finally, I see my AWS provided external data in my virtual table designer in Dataverse:
In my last step I can add also my “Description” column as – lets use Rich text:
Using my Virtual Table
I have also created a model driven app in my solution. Here I use the designer to add now my virtual table:
You see my Table AWS Product Category is available as Dataverse Table. Furthermore, the result is immediately visible to me:
I can also customize the layout of my lists and forms to suit my users’ needs. Additionally, my Citizen Developers are now able to create their own apps based on my Virtual Table.
More important for me is now, to support full create, read, update, and delete functionality. This is because my current virtual table is read only:
In other words, I will focus in one of my next blog posts on CRUD operations for my virtual table.
Summary
You have seen, there are more available data sources for virtual tables. In detail, I have used an OData Endpoint to provide my external data from my AWS service in Dataverse.
Furthermore, my OData Service provide an Entity Data Model to describe the data and the available Http routes. All this information are useful to setup an external (virtual tables) in Dataverse.
Finally, I showed how I added my virtual table to my Model Driven App. This is just one way. Other possibilities include using my table in Canvas Apps or Power Automate Flows.
For now, I’ve only added a simple virtual table, but as promised, I’ll continue this blog post.
There are other challenges worth writing about. In detail, I will add another entity to my EDM model. I’ll also show you how to define relationships between entities in EDM. In addition, I complete the functionality of my OData endpoint and will provide full CRUD operations. Finally, I’ll store the data in my AWS DynamoDB.
Stay tuned!