This blog post walks you through the process of creating ERD diagrams and unpacking them with information about Dynamics 365 Finance & Operations.
Problem statement
The customer IT team is expecting the implementation team to generate an ERD diagram for custom or standard tables to understand Dynamics 365 FO table relationships.
There are a few situations in the discovery and design phase of Dynamics 365 FO implementation where ERD really simplifies everyone's life.
Why is it so critical?
By creating ERDs, business analysts and users can clarify business terms, understand business domains, and connect concepts to databases.
What is an ERD (Entity Relationship Diagram)?
It describes the relationship between entities or concepts in a data model. It is therefore necessary to distinguish between those two concepts.
An ERD is really about showing logically how concepts relate to one another and what key data elements business users need to know about a concept. The purpose of the tool is to help communicate how information is stored and how the business views relationships.
Using an example, what might be a business concept?
It could be a sales order, and I think it's a good one. Is it possible for a customer to place more than one order, and is it possible for an order to have more than one customer? It is usually not the case. Customers often place multiple orders.
Yes, that's great, but most orders have just one, and only one, customer. That's what a relationship is all about.
As far as data goes—let’s talk about it for just a moment—it isn’t the way most business analysts want to make them—but it’s going to show you, every detail, all the tables in the database, and how those tables are related to one another. It's the same idea I just spoke about: one-to-many, one-to-one, many-to-many.
How do those tables relate to one another, and what are all the fields they contain?
My goal is to walk you through some of the key examples using the visual model we showed you earlier. Let's begin with entities. An entity is a thing, a concept. The box you're looking at is on this model. It's a concept in business jargon. As far as relational databases are concerned, it is the table.
Then, there are the relationships. It's the relationships between the boxes that make them connected. In this diagram, we can see how the entities relate to one another, which is where the real insight lies. A relationship is actually a verb or a numerical relationship that connects two nouns.
Lastly, we have attributes. An entity can have more than one attribute. Attributes provide details about the entity. Information about the concept is provided in detail.
That model helped us get over that intellectual hurdle, and it was a business-focused ERD, as well as an ERD that helped us overcome this huge problem on the project.
Let's jump in and understand the Visual Studio extension & dbdiagram.io tool features and see how it generates DBML.
Navigate to the GitHub repository of Paul.
GitHub link : https://github.com/noakesey/d365fo-entity-schema
As described, this tool is a visual studio extension to generate an entity relation schema using Database Markup Language (DBML)
Post generation of entity relation schema, an online tool such as dbdiagram.io can be used to render the resulting DBML.
Thank you Paul for your valuable contribution to the community.
Steps to follow to generate the ERD
- Download the Visual Studio extension from GitHub
- Install on your Cloud Hosted Dynamics 365 FO VM as per the guidelines
- Navigate to Visual Studio -> Extensions -> Dynamics 365 -> Addins -> Generate entity relation schema
- Input the required table name in the text box
- Click on Add button
- Repeat steps 4 & 5 for an additional table, if required
- Alternatively, you can use Add outward relations or add inward relations options for your scenario.
- Click on Generate DBML
- Table relationship schema will be available in Visual Studio
- Navigate to https://dbdiagram.io
- Remove sample content
- Copy table relationships schema content from Visual Studio and paste the same to the editor of dbdigram.io
Isn't it simple and elegant to generate DBML?
Conclusion
A faster time to implement business processes with correct business entity relationships.
I hope this article was a good read for you. Do share it with your friends and other peers.
Thank you, Shabbir for inspiring me to write this blog and exchange thoughts.
Thank you so much!
