I’ve written about D365FO data entities in the past and have had the opportunity to use them extensively from a ISV and consulting perspective. While working with them I have found myself at different times wanting to have a centralized source of information surrounding their metadata, the data sources used (both root data source and all data sources), and if there was a way to see dependencies between different data entities. So I created an Excel file with all of this information and I want to share how I created it and the file itself.
Note: All of the code and data below was created on a 10.0.35 version of D365FO
Data Entity Metadata
I first started by getting the metadata for each data entity using the MetadataSupport library I have used in the past. Using this I was able to get the name and label of each data entity as well as its public collection name and entity name. I then wrote this data to a CSV file for easy output.
Data Entity Data Sources
While getting the metadata for each data entity I noticed I could also get the data sources. The initial data source listed for the data entity is what I considered to be the ‘root’ data source. The method at the bottom was created so I could use recursion to get all of the data sources utilized in the data entity. If the lines in red are commented out this code will only export the ‘root data source’ of each data entity. If these lines are uncommented then this code returns all data sources for a data entity.
Data Entity Dependencies
Now that I had all of the data sources for each data entity, I had a thought hit me. What if we could use the AX 2012 ERD I host on my website to find the parent -> child associations of the data sources and therefore the data entities themselves? As it turns out, we can do just that.
First we have to find the correct files that contain this relationship. While navigating the ERD I noticed this data existed in the files that started with ‘Fky’ (stands for foreign key) and had the string ‘ChildParents’ in the file name. In the example below you can see the BankAccountTable is associated to all of the tables on the right (BankConstantSymbol, BankGroup, BankReconciliationMatchRule, etc):
These table associations are what we need to build our data entity dependencies.
I then put all of the files that met this criteria into a single folder:
If we look at the raw HTML of these files they all follow a pattern that we can use to pull out the data we want:
By looking for certain character strings, we can gain access to the data we want in its raw form:
I ended up storing the data entity metadata, data sources, and table relationships all in a SQL instance so I could write queries against this data to generate the data entity dependencies:
The above query is finding the root data source for each data entity, looking to see if an associated data source exists in the ERD, and if there is looks to see if another data entity has that data source listed as its root data source. If it does, then that means that there is a relationship between
The End Result
By using the above actions I was able to create this file: Data Entity Overview
It contains 5 separate tabs which include:
- Data Entity Metadata – shows the name, label, public collection name, and public entity name for each data entity
- Data Entity Root Data Sources – shows the root data source for each data entity
- Data Entity All Data Sources – shows all data sources for each data entity
- Data Entity Assoc Overview – shows the child -> parent relationships between the data entities based on the root data source and the ERD file. The ‘child’ data entity would therefore rely on the ‘parent’ data entity to have been populated prior to creating/updating data on the ‘child’ data entity.
- Data Entity Assoc Detailed – Shows the same data as the ‘Overview’ tab but includes the data source information used to associate the two data entities
Doesn’t Microsoft Already Have This Data Entity Dependency Data?
Kind of… If you look in the Data Management workspace there is a ‘Templates’ section and if you execute the ‘Load Default Templates’ task you will have a bunch of out of the box data management templates that include these dependencies already. But they aren’t really searchable and they don’t show ‘why’ these are dependent on each other.
I hope if you work with data entities you find this Excel file useful, shout out to fellow Microsoft MVP Nathan Clouse for his help on putting this blog post together!