How to generate a data model from Power Platform or Dynamics 365 online

Updated: Mar 15, 2021

Before migrating data to Dynamics 365 online, to Power Platform, etc., we really need to know where we are going. We can't just jump in knowing that pitfalls are real and could deley the data migration. Even with the most thoroughly tested tools and procedures, we need to identify the new data model and generate a data model diagram.


Problem : Generate a database diagram in Sql Server Management Studio (SSMS) with Power Platform


As we know, we access the database of our environment or our Dynamics 365 organization with SSMS with your own credentials (username, password), using the Azure Active Directory Method.


And, we get only a read-access mode and it's enough to create SQL statements ("select").


Moreover, we could need an global view of our data and metadata model. However, we cannot actually create a database diagram using SQL Server Database Diagram Tool in SSMS. We only get an specific exception :

Exception from SSMS

So, What is the alternative to create our database diagram ?


What our starting point is (Requirements)



1- Installation of Xrm ToolBox



We can download the latest version : https://www.xrmtoolbox.com/. Once downloaded, extract the content where you wish.

XrmToolBox extracted in a folder

2- Installation of Visual Studio Code


We can download the version we need: https://code.visualstudio.com/Download.

Once downloaded, we can install the extension related to the component "PlantUML" to quickly write a diagram.

Extension : PlantUML in Visual Studio Code

3- Create an account related to Microsoft 365 (previously Office 365), purchase a Power Platform account, purchase a Dynamics 365 online account or finally, try a trial version of Power Platform/Dynamics 365 online (https://dynamics.microsoft.com/).


If you already have a Microsoft 365 account, your licence Microsoft / Office 365 includes an access to a Power Platform. Microsoft provides detailed information about licencing (https://docs.microsoft.com/en-us/power-platform/admin/pricing-billing-skus).

Dynamics 365 Licensing Guide March 2021
Download • 1.84MB

What we need to generate


First, we need to generate a file with "PlantUML" extension.


Secondly, we'll be able to generate a database diagram regarding the Common Data Model (CDM) : https://github.com/microsoft/CDM/blob/master/docs/CDMPoster_a3.pdf.



How we could generate our diagram


Step 1 : Generate your "plantUML" file in xrmToolBox


1- Open the tool "UML Diagram Generator"


2- In "UML Diagram Generator", select the entities, the the attributes and the relationships.

UML Diagram Generator and selection

Be careful !


  • Selection could be done by "Solution" (i.e. Dynamics 365 Sales Professional).

  • Include the "intersect" tables if needed : check the checkbox "Intersect".

  • If you select all attributes and relationships, XrmtoolBox will crash!

  • Select only the mandatory attributes (uniqueidentifier only).

  • Do not select all the entities.

3- Generate the "plantUML" file from the button "Generate" and save the "plantUML" file in a local repository or in an online repository (i.e. Git).

"PlantUML" Functionality in XrmToolBox

Step 2 : Open your "plantUML" file in Visual Studio Code


1- Open the "plantUML" file in the editor

2- The editor will display the code and its syntax

Source code of a "plantuml" file

Step 3 : Preview you diagram in the editor which runs on your desktop


1- Make sure a "previewer" is installed. If not, install "PlantUML Previewer".

2- Right-click in the editor and select "Preview current diagram"

Diagram : Preview in Visual Studio Code

For what purposes


A "plantUML" file is a source code file containing instructions in a program language.


1 - It uses well-formed, well-structured and human-readable code to render our database diagram : https://plantuml.com/en/. The content of the code depends on what we selected in XrmToolbox. The code is nested, meaning that the code is placed inside another element : an opening tag (@startuml) and a closing tag (@enduml). There is a tag "entity", encapsulating our entity and being identified in 2 different ways: a) << standard >> as a system or native entity

b) << custom >> as a custom entity

There is no tags related to the relationships which are nested in the opening and closing tags.

2 - A "plantUML" file is easily editable in Visual Studio Code.


We can add or remove the attributes in the namespace "entity".

Moreover, we can add or remove the relationships between the opening and closing tags.


3 - Obviously, a "plantUML" file is easily deployable in a local or an online repository.


Example : Entities account-lead-leadaddress


Here's an example related to 3 entities : account, lead, leadaddress. If we select the option "Intersect", the search functionality will add "accountleads".

For each entity, we can select the attributes we wish to display. Here, we select only the "UniqueIdentifier" type.

For each entity, we can select the relationships we wish to display. Here, we select all the types : 1-N, N-1 and N-N.

XrmToolBox helps to generate a "plantUML" file : "account-lead-leadaddress.plantuml".

account-lead-leadaddress-plantuml
.pdf
Download PDF • 88KB

Once generated, we can preview the diagram before saving.




45 views0 comments