Cross reference between AX tables/columns and DIXF Data Entities? (Migrating ETL from reading AX tables directly to Data Entities)

(0) Share

Report

Posted on by Community Member

I have some legacy ETL that is populating a data warehouse from AX that I would like to change to use DIXF Data Entities.

Something I'm not quite understanding is how, for given tables/columns referenced in the ETL code, how one would know which corresponding Data Entity (if any) would contain the data from that core table?

For example, a table referenced in my ETL is [PURCHLINE].

Now, based on experience and common sense, the corresponding Data Entity is going to be [Purchase Order Lines], but is there a method of finding this association without using experience and common sense? Or in other words, is there somewhere in the AX metadata (some sort of a system table, for example, something comparable to INFORMATION_SCHEMA.COLUMNS in SQL) where I can extract *all* relations between core AX tables and their corresponding Data Entities, hopefully including both column and table names?

*This post is locked for comments

I have the same question (0)

All responses (18)

Answers (2)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You could easily find it in the AOT within Visual Studio's Application Explorer.

AOT > Data Entities > PurchPurchaseOrderLineEntity > Metadata > DataSources > PurchLine

Since source code is in XML, you could use XML parser tools to query and extract the relations yourself. Here is an idea based on what Peter Villadsen has shown us at the Technical Conference couple of years back:

https://community.dynamics.com/ax/b/dynamicsnavax/archive/2016/03/18/dynamics-ax-7-searching-using-basex

The tricky piece is when a data entity uses another entity as its' source, so you'd have to traverse down multiple levels.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The part I'm not seeing is how you go from PURCHLINE to PurchPurchaseOrderLineEntity without a reliance upon prior knowledge.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Once you extract the information by the above suggested method, you could filter by table name. If you know that Purchase order lines are PurchLine table, then you could see what entities are using that table as its' datasource. The name of the entity has a close resemblance to its' source.

You could also extract the label properties of tables to match it against the datasource tables set on the entity, with which you could match the "descriptive names" of entities with actual tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

> Once you extract the information by the above suggested method

But, unless I'm misunderstanding you, this methodology (of answering the question) requires the answer to the question as it's starting point.

If I was to rephrase the question as: "for a given table <x>, how would one find the corresponding Data Entity", what would the steps be to answering *that* question?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

"then you could see what entities are using that table as its' datasource."

This would be useful, is there a way to do that?

Looking in:

AOT > Data Model > Tables PurchLine

.... I am not seeing a way. If a Data Entities collection was added as a new node here, that would be a pretty reasonable solution, but I don't see anything related to entities there?

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

So if you go to AOT > Data Model > Tables PurchLine -> right click -> Find references. It will give you list of usage in the output window of VS. There you should be able to find records like

Same information you can get from DYNAMICSXREFDB database tables if you prefer SQL

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

For some reason, mine doesn't find any references.

SQL is definitely preferred for me, I had looked at DYNAMICSXREFDB yesterday but nothing in there caught my eye. I lookled at:

select top 100 * FROM [DBVersion]
select top 100 * FROM [Providers]
select top 100 * FROM [Modules]
select top 100 * FROM [Names]
select top 100 * FROM [References]

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

Probably you have not build them, its easy to fix. Go to Dynamics 365 - > Build models menu in VS, select all models and on the Options tab tick "Build cross reference data". It will take 30m -1h and populate these tables and you need to do this only once and rebuild them for specific model if you do some changes.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Am running that now and will report back with success or failure, many thanks for the advice.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

This approach looks to have some promise, I wonder if it works in all scenarios though (it presumes a VIEW underlies a data entity, the exceptions to this I'd think would be rather small):

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where table_name = 'PURCHLINE' and view_name like '%ENTITY'

VIEW_NAME

--------------------

PURCHPURCHASEORDERLINECHARGEENTITY

PURCHPURCHASEORDERLINEENTITY

RETAILBUYERSPUSHLINEENTITY

RETAILCROSSDOCKLINEENTITY

Another approach is to explicitly look for " FROM <whatever_table_you're_looking_for" in the definition itself:

SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), name, type, type_desc

FROM sys.objects

WHERE OBJECT_DEFINITION([object_id]) LIKE '% FROM PURCHLINE %' and name like '%ENTITY'

In this case it returns exactly the object I am looking for, not sure how reliably one can expect it to be the very first table referenced in the FROM statement though.

name

-----------------------------------------

PURCHPURCHASEORDERLINEENTITY

Was this reply helpful? Yes No