Create Tables from EDMX file or other API metadata format (automatically create tables from API metadata)

[PaulBrittain]

Hello,

Looking for guidance on creating Dataverse Tables in a simpler way than manually creating tables via the PowerApp Solution UI. For example, a developer may wish to create tables for all (or a subset of) Microsoft Graph, to do this would be time consuming and error prone (since the process is manual). Is there a better way to do this?

Since Microsoft Graph has native OData support, API metadata and Entity Models are easily extracted as an EDMX file, can a developer use this to automatically create tables somehow? A developer could, conceivably, either use a tool to directly consume these definitions to create tables, or they could convert the EDMX entities into another more appropriate format which they could use to create dataverse tables... Is this something that is supported? I have spent some time searching for native functionality to do this or third-party tools... no luck so far.

Any information or guidance appreciated! 

[ChrisPiasecki]

Hi @PaulBrittain,

If you wish to create tables and other Dataverse metadata, this can be done using the Web API, or if you are using .NET framework you can use the SDK provided by the XRM Tooling. If you prefer PowerShell there is also the Microsoft.Xrm.Tooling.CrmConnector.PowerShell module available. 

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/create-update-entity-defin...

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/xrm-tooling/build-windows-client-...

With regards to replicating the entities from the graph API, if you just want a read-only access to the data and maybe relate it to your Dataverse data, you may want to consider using Virtual entities. That way you're not duplicating data between sources. 

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/virtual-entities/get-started-ve

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[PaulBrittain]

Hi @ChrisPiasecki ,

Thank you for your reply 🙂 I will be using Virtual Entities to model the read-only data indeed! And it is my intention to use the Web API to create/modify the Entities (now known as Tables).

What I'm missing is how to take data models in EDMX format (or similar) and create the Tables using the Web API and whether a solution already exists that simplifies this process, by bridging the gap between data model and API query construction.

[PaulBrittain]

Bump for visibility.

[rappen]

Looping in @mikefactorial who might be interested in creating something around this!

[ChrisPiasecki]

Welcome @rappen, didn't know you visited here :). I've been debating whether this could be a potential Xrmtoolbox plugin, and in general looking for ideas to create my first plugin for it. I could take a first crack at something like this. 

[PaulBrittain]

Thanks @rappen ! @ChrisPiasecki 

So the "Get Data" from OData using Power Query almost works, but it creates normal entities and there's no option to create them as Virtual Entities(VE). I tried exporting the solution so that I could manually edit the XML with the intention of configuring the entity(ies) as VEs, then re-importing the solution. However I've been blocked by the customization file's limitation of not supporting the defining of entities, attributes, entity relationships, etc. This is a set back from what I thought could be a straight forward, much faster method of VE creation. It also blocks my related plan to save entity xml definitions locally or in an XML file library so that I could insert entities in future solution customizations.xml files directly. Hopefully I won't run into problems with my backup plan of saving created entities for future use by packaging them together in a solution that can be imported into environments.

So unless I'm missing something, I'm aiming my sights back to a OData service root URL -> Dataverse Web API tool.

Since Power Query did such a good job of extracting the tables from an OData feed, one should first look to leverage Power Query in such a tool if that's possible. See: Power Query OData Feed connector | Microsoft Docs

EDIT: Power Query SDK Power Query SDK - Visual Studio Marketplace

Docs indicate there are no blockers to programmatically creating VEs API considerations of virtual entities (Microsoft Dataverse) - Power Apps | Microsoft Docs

Of course, it would be far better if PowerApps gave you the option of setting the created tables as VEs when creating the new tables for the Power Query transformed data in the first place! 🙂