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.
Conclusion
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!
Great post! Thanks Alex. I cannot remember how many times I need to find the right data entity for a specific table. This is definitely helpful.
Hi Alex,
I’m getting ‘BadImageFormatException’ at MetadataSupport.GetDataEntityViewNames(), or it’s stuck at that line if I change the platform to x64. I’m on 10.0.33. Do you know what’s the cause?
Thanks,
Andrew Xu
Andrew,
That error in .NET is normally caused by a missing DLL reference. Looking at the referenced blog post (https://alexdmeyer.com/2022/07/07/how-to-use-a-net-project-within-a-d365fo-solution/) it looks like the .NET project needs to reference the Microsoft.Dynamics.AX.Xpp.Support DLL and have a ‘using’ statement for ‘Microsoft.Dynamics.AX.Xpp’ in your class.
The code listed in the blog post was executed against a 10.0.35 version but should work on any version that contains the MetadataSupport library.
Thanks for your reply, .xpp.suppor dll is referenced and using statement was added. This error is not a compilation error, it’s runtime error. It’s strange, isn’t it?
Andrew,
Can you share the code you are using, including the project references and the using statements for me to troubleshoot further?
using System.Collections.Generic;
using Microsoft.Dynamics.Ax.Xpp;
namespace ClassLibrary1
{
public class Class1
{
public static List GetDataEntityMetadata()
{
List result = new List();
var dataEntityNames = MetadataSupport.GetDataEntityViewNames();
foreach (var dataEntityName in dataEntityNames)
{
result.Add(dataEntityName);
}
return result;
}
}
}
References:
Microsoft.Dynamics.AX.Metadata
Microsoft.Dynamics.AX.Metadata.Core
Microsoft.Dynamics.AX.Server
Microsoft.Dynamics.AX.Xpp.AxShared
Microsoft.Dynamics.AX.Xpp.Support
Andrew,
I created a new project from scratch and was able to get the code working with just the following setup:
Hi! Alex,
In Indian legal entity, there is GST Tax engine which runs Transfer Order, i wanted to know about that entity in which the rate of Tax update by the User so that i can download the file having HSNCODE wise Rate/Value.
Sachin,
Do you know which table/data source (or even the form name) that contains the data you are wanting to export? You should be able to use the Data Entity Metadata Excel document then to determine which data entity this corresponds to.
Hi Alex, Can you Please Guide me How to Resolve this Error.
While generating the Bank Payment Advice Print.
Stack trace: Calling wait or detach before calling run.
No report data table with name BankPaymAdviceVendTmp exists in report schema for data provider BankPaymAdviceVendDP.
Venkatesh,
Not sure this relates to this blog post on data entities but based on the error it looks like the BankPaymAdviceVendTmp table exists for the data provider BankPaymAdviceVendDP.
Hi Alex, Actually this (Vendor Payment advice Report ) development not working in the 192.168.4.170 server (It is Synced with UAT Server)
So that’s way we are Developed remaining Development in the 192.168.4.174 Server (It is Working fine in that, actually we also checked for that)
And Deployed into UAT and in the UAT it is Showing error Like : No report data table with name BankPaymAdviceVendTmp exists in report schema for data provider BankPaymAdviceVendDP.
getting this error while runnign the Payment advice report in UAT
Can you Please Guide me, How to Resolve this Error.
Venkatesh,
I am not sure how this error is tied to the blog post, I would recommend opening a case with Microsoft on this issue if you cannot resolve it.
Great article. Please also mention that we have to add the reference to the assembly ‘Microsoft.Dynamics.AX.MetaData’ and MetaData.Core.
Also, which assembly does DataEntityDataSource belong to?
Thanks.
Syed,
Great points on the assembly references!
The object DataEntityDataSource is a custom object I created to hold the data entity -> data source associations.
Hi!
What is the consequences of reducing DB size in production related to Future transaction and books of accounts. Is their any financial impact if there is transaction are deleted to reduce the DB size to make the system performance better.
Sachin,
I do not have any experience doing this directly but normally the deleting of financial transactions is frowned upon unless there is a business case for it. You would need to weigh the potential performance increase against the business requirements for data retention.