While testing Tableau 10.1 with AX 2012 R3 Odata services,
I was unable to connect. Has anyone able to connect with Tableau ?
Basically it doesn’t need Authentication, I tried both the ways.
No luck. Are there any wrinkles here ?
While the same works fine in Power-BI, Below is a Power-BI demonstration to show it successfully connects with AX.
What if you press “Show details”?
It displays following message
What is the version of Odata that comes with AX ? any idea !
In the following help URL, It is mentioned that Tableau connects to OData V2
https://onlinehelp.tableau.com/current/pro/desktop/en-us/examples_odata.html
In 2013 at the bottom of the blog one other person pradeep reported a similar issue connecting Odata with CRM
https://blogs.msdn.microsoft.com/crm/2011/03/02/using-odata-retrieve-in-microsoft-dynamics-crm-2011/
I am thinking this could be either due to the differences in odata version. There is no much help online.
The above msg sent earlier was displayed while ussing with “No authentication”
and with authentication it displays the following msg.
“You do not have permissions from the OData source to access the data. Check with the data provider to obtain or update your permissions.”
Oh, you should have said that before - your picture above is misleading, because it doesn’t belong to the error message.
Your actual error says that the account doesn’t have permissions to access the service. Give it permissions or use another account that already has them.
First off, thanks for looking into this Martin.
Administrator account does have permissions, The first screen shot I was displaying 2 screens IE + Tableau (side by side). It executes fine in IE browser without requiring any authentication which is in the background.
BTW- I tried with different user too no luck.
Now I’m now confused when you get which error.
Anyway, I think that AX 2012 uses OData 1.0.
Sorry, if I have caused some confusion above.
But I am now convinced after reading few blogs that there are certainly some wrinkles in Tableau odata access. Below attached are few references on the similar odata subject.
community.tableau.com/…/143063
kb.blackbaud.com/…/70061
www.zillow.com/…/
community.tableau.com/…/DOC-6407
community.tableau.com/…/156169
community.tableau.com/…/DOC-6184
By inspecting the Logs under
“C:\Users\Administrator\Documents\My Tableau Repository\Logs\log.txt”
Noticing that it forms interactive query arguments internally like (i.e, “$top”) at the end of URI ( see the below log info attached for reference and it clearly says query options cannot be applied). This is not supported in Tableau as per their online documentation too.
onlinehelp.tableau.com/…/help.htm
Coping the content here from the help documentation…
The OData connector supports only extracts, which means that some OData V2 functionality is not supported, for example, interactive query arguments such as $expand or $select.
Log info :
{“ts”:“2017-02-26T08:19:42.430”,“pid”:15732,“tid”:“5120”,“sev”:“warn”,“req”:"-",“sess”:"-",“site”:"{A2D19087-2CCA-4A93-B147-3533F2B6D223}",“user”:"-",“k”:“msg”,“v”:“HTTPRequestor::DoWork::HTTPException 400 localhost:8101/…/ <?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"yes\"?>\r\n<error xmlns=“schemas.microsoft.com/.../metadata”>\r\n \r\n <message xml:lang=“en-US”>Query options $expand, $filter, $orderby, $inlinecount, $skip and $top cannot be applied to the requested resource.\r\n”}
Putting all this info, so others can understand what is going-on in the Tableau desktop background process while it’s trying to connect to odata services. Hope this helps all the other users in-case they hit similar issue.
Had to dig a bit to understand the issue, clearly they could have done a better job to display the same msg in the error prompt, instead logging it inside the log files.
Was beating my head against the wall chasing it.
AX 2012 may be built using Odata V2 version but the protocol used is 1.0.
http://www.odata.org/documentation/odata-version-2-0/uri-conventions/
To find out the version of Odata and protocol version in AX 2012 ?
Here are the steps :
Browse the below URI in IE and check for <edmx:Edmx /> version
//MachineName:8101/DynamicsAx/Services/ODataQueryservice/$metadata
The green section highlighted in the image displays the version info.
For more info on Odata versions and documentation, you can reach here "http://www.odata.org/documentation/"
Here is my Experience/Conclusion :
Tableau Dashboard 10.1 cannot connect to AX 2012 Odata services with protocol 1.0 (DataServiceVersion) and this could well be a problem with other products too that have query options/interactive arguments.
Hard to figure out in the logs that has thousands of lines. So make sure to check the log file thoroughly.
Each software works differently, Finally I was able to resolve the issue.
Resolution :
Basically Tableau requires the feed/collection element name to be explicitly passed to the URI, It doesn’t loop through all the collection/feed’s available in the URI.
Very different from PowerBI and wasn’t expecting this at all.
ex : http://:8101/DynamicsAx/Services/ODataQueryservice/ListofUSStates
I cannot mark it resolved, Since I was one who started this thread. But hope this resolution and discovery would help other users.
Final Conclusion : Tableau Dashboard 10.1 can connect to AX 2012 Odata services and works fine but need to pass on the feed/collection element explicitly to the URI.
Hi Venkat, I’m glad you’ve sorted it out and thank you for letting us known. Although you confirmed my assumption that AX 2012 uses OData 1.0 (mentioned in my third reply), you also found that it’s not a problem if you refer to a concrete entity; it seems that Tableau has a problem only with the list of entities.
Maybe you would get better replies if you asked in a forum about Tableau; you would have to be vert lucky to find somebody familiar with Tableau in an AX forum.
I agree, Probably my search was specific to Dynamics AX Odata, hence didn’t find any answers in their Forum. Thought this is broader Dynamics community and someone might know the answer.
But anyway, I confirm that it works with AX
The trick is not to specify the “root of a service reference in the URI” but pass a specific entity/serviceSet at the end of URI.
So typically if they need multiple entities then multiple datasources needs to be created and added to pages as shown in the sample.
Works with Dynamics CRM
and it works with Dynamics NAV,
Posting this info thinking it might be helpful to other dynamics users.
Hey Venkat,
While I agree you may have gotten some better help on a Tableau forum, I know that there’s lots of companies using Tableau to connect to AX. I, for one, am happy to have these types of discussions and commentary on this forum, because other people in AX may stumble across them, too. Keep them coming!
-Jake
