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 :
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.
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.
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.
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).
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
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"
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.
