Microsoft Tools that Help: How to Create an Entity Relationship Diagram

By - July 2, 2014

“To Do the Job Right You Need the Right Tools!”

The slogan of a favorite blog site of mine, Microsoft Dynamics AX Tools. Working with many Dynamics AX developers, I am amazed that some of the most senior developers know about Microsoft tools available, know the processes, but simply don’t use them or waste time creating their own tools. This blog series Microsoft Tools that Help! is a reintroduction to some of the more common Microsoft developer tools and how they can make our lives easier.

Entity Relationship (ER) Diagrams

At the start of every new project engagement, the client’s IT staff will invariably request an entity relationship (ER) diagram of the whole solution. Dynamics AX contains several thousand tables, making it nearly impossible to construct an ER diagram that mapped every available table. To satisfy a client request for one, a diagram would typically be manually created using Microsoft Visio or generated using the Reverse Engineering Tool – available in Dynamics AX – to create the necessary UML data and object models. While the Reverse Engineering Tool allows us to generate the required output for our data model, it is also incredibly time-consuming and would require the developer to be well-versed with the client’s implementation and core data structure.

Reverse Engineering Tool & Generating the UML Data Model

To construct an ER diagram using the Reverse Engineering Tool we would perform the following steps:

  1. Create a project and add the tables you want to include in the ERD diagram
  2. Right-click the on the project and select Add-Ins
  3. Click Reverse Engineer
  4. In the dialog box, specify the File path and name
  5. Select Visio UML Data Model and click OK
  6. Visio opens (automatically)
  7. Add data model elements to the diagram and format accordingly

It is important to understand the relationship between tables, especially with the new data model in Dynamics AX 2012. To assist developers when extending existing modules, Microsoft introduced the AxErd website. The site hosts entity relationship diagrams for the core tables of the application modules in Microsoft Dynamics AX 2012. You can also list all the foreign key relationships for a table and determine which module a table is in, and which tables are found in a particular module. The AxErd site also offers hundreds of links to support pages that are designed to answer developer questions when customizing a given application module, such as the Accounts payable module, featured below:

accounts payable ERD diagram

Example ER Diagram – Accounts Payable

 

While the Reverse Engineering Tool provide some additional capabilities to generate custom diagrams, the AxErd website should be the first step when entity relationship (ER) diagrams are needed to demonstrate the interrelation of tables within a given module.

Further Reading on MSDN

Receive Posts by Email

Subscribe and receive notifications of new posts by email.