Learn the word; ODATA. We will hear a lot of ODATA in the future, because it will change the way we integrate and how we exchange information between AX and other systems. A good starting point is the AX help wiki, that Kuntal Mehta created. I have decided to explore what the ODATA can do, and wanted to write a bit about my journey. Instead of trying to explain all technical details of data entities and how the architecture is, then let us rather just test something J
What you need to test what I’m doing is
- An AX RTW environment deployed from LCS
- Internet explorer
- Good old notepad
Step1: What services is available?
To get all entities available to you use your Site address, and add “/data” at the end.
Then save the file you receive, and open it in notepad. (I have associated *.json with notepad). The file you get looks like this:
Each line here represents a data entity service we can use. The format of this is the JSON format, but that is not important now.
Step2: Show me the customers
In the file you may find that there is an entity/schema named “Customers“. I can therefore just add the “/data/Customers” to my URL
And then I get a JSON file of all the customers;
But this is a bit “cloudy” and I can further filer down what I want. Let’s say I just want to see all customer names. I can then add “/data/Customers/?$select=Name” to my URL
Now it returns a JSON file with only the Name.
If I wanted to add one more column, like the Payment terms, the syntax would look like “/data/Customers/?$select=Name,PaymentTerms“, but this would not work because the comma cannot be used on a URL. I therefore need to replace the comma with %2C, that is the URL representation of comma. For multiple columns I therefore add “/data/Customers/?$select=Name%2CPaymentTerms”
You see some strange “@data.etag”, and here is an explanation. It is for caching.
Step3: Can I read this in Excel?
Yes. Excel can import OData, and format it like we would.
Then fill in the /data URL, select schema, and then select fields.
And then you may read directly into Excel all entities made available in AX RTW, even without the AX connector.
Step5: Show me all !
Sure. Try to add the “/data/$metadata“, and AX return All schemas, fields and relations. It take a long time, but nice to explore.
Step6: Can we use DIXF to import directly from OData feeds ?
This is what I would love to see. But I have not found it yet.
Happy DAX’ing 😉
9 thoughts on “AX RTW – My ODATA and JSON journey – Part I”
Hello Kurt – Thanks for the nice post. I started all the steps, when I stuck to Step 2 where my local VM URL: https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/bankaccounts is giving 404 error. I tried /customers or other entities as well but all of them are giving 404. Would you please help to get past this error? Anybody else?
The entity name is case sensitive. Try “…/data/BankAccounts”
Thanks Peter. Yes. I figured it out. Thanks again.
Unfortunately Dynamics AX does not support CORS, so OData is useless if you are creating a browser/client application unless you proxy all requests through a CORS-capable proxy.
It is a useful post and works well. I’m trying to pull out data from a specific legal entity from one of my AX instance. Do you know the syntax of specifying a legal entity name with OData? My goal is to pull out data from AX (e.g. pull out Customers from Contoso USRT company only) to PowerBI with OData.
The updatable or isPublic property is not visible to edit on any view. Can you help me
What’s your opinion on performance? Would aggregate entities, exposed as OData, be suitable to export millions of lines into a PowerBI file?
So glad MS is now using REST/JSON/ODATA. Looking forward to see how Swagger will play with it. I’m sure it is already possible in VS. Or maybe somebody will develop a plugin.
LikeLiked by 1 person
Nice post Kurt… Keep them flowing in.. Thanks.