Connect Microsoft Power BI to AX 2012 – ODATA query service

We all know Dynamics 365 for operations offers us a complete new entity store from where we may last but not least consume data for evaluation purposes. Inspired from that idea I started to do some research for similar opportunities within Dynamics AX 2012.

Indeed I’ve been able gather the Information I wanted and I’d like to share that with you as I’m sure some of you are still looking for something like that. Let’s see how it works.

 

Prepare AX ODATA query Service

You’ll find anything you need in: Organization Administration/Setup/Document management/Document data sources

In the document data sources just add a new line

  • select a module entry (it has no impact, just helps you to organize yourselves)
  • Chose Data source type “Query reference”
  • Chose the query which you’d like to consume

The advantage here is, that you may select queries instead of pure tables, These queries usually contain more information than the pure tables which makes it often more easy to consume as you don’t have to tie everything together using the Rec-IDs. I’m sure you’ll be able to find anything you need.

 

Connect Power BI to the service

In Power BI Desktop, just click “Get Data” and “ODATA Feed”

ODATA Feed Connection String

In the next window we enter the path which we will need for connection to our Dynamics AX 2012 installation.

  • Use http://<AosIpAddress>:<WSDLPort>/DynamicsAx/Services/OdataQueryService/
  • in my example it looks like: http://10.1.32.130:8101/DynamicsAx/Services/OdataQueryService

From my experience it is now very important to use the IP address instead of the host name.

Credentials

Now we need to enter the credentials. If your AD allows to – just use your actual user credentials setting.

Select Sources

As we can see we may now chose from the previously enabled Document data sources.

 

Consume and enjoy.

As we can see, it is pretty easy to consume data in Power BI from AX 2012. This way of consuming data brings at least two advantages

  • Access to data is controlled by AX role setup of the user who is being used when it comes to enter the credentials
  •  Many connections in the data model are already prepared and inherit more data information compared to the plain tables. So we can save a lot of time when gathering data for evaluation purposes.

 

If you face any problems don’t hesitate to comment, 🙂

8 thoughts on “Connect Microsoft Power BI to AX 2012 – ODATA query service”

  1. This is a great approach to bring the Query data into Power BI Desktop. Unfortunately I am stuck when establishing the OData feed. I get a 500 internal server error. I guess that is to general for you to have an idea of the issue, but any thoughts on the reason would be very appreciated!

    1. I think you might be facing the same problem I had. Try to check the server you are using. It might not be the server you are exposing your ports on. A good way to check would be go to admin-> Service and application integration framework -> inbound ports and look for the WSDL url and grab the server information from there. Let me know if it helps.

  2. Many thanks for the input! I tried looking further with your directions but I find the same WDSL URL also when tracking it down as you proposed Ananth. Maybe there is a firewall or something stopping me. I will ask our datacenter for some additional guidance. Please let me know if you have any other ideas. Btw – testing the access in the browser was also not successful. Thanks again!

  3. Yes I tried the expclicit IP address and not the DNS, but still no luck. I tried it both in my own environment and the remote desktop Environment where the AX instance is accessed through single signon. Still no luck. Thansk for the advice. I hope I can figure it out when I reach through to our datacenter people.

  4. I made a small progress in this that might give some furhter clues to the matter. When I enter the path in the browser in the Remote Desktop Environment I now get a screen with an error message saying; “The server encountered an error processing the request. The exception message is ‘Object reference not set to an instance of an object.’. See server logs for more details.” Then follows a long exception stack trace.
    Any furhter ideas based on this additional input?
    Many thanks for your guidance!

    1. Hi, I finally got this to work. The issue turned out to be some “dead end” queries that were enabled under data sources… However it only works for quite simple queries. If I have more than one source table for a query it tends to throw the “500 internal error”. I can probably work around this until some point, but I much rather have it solved. Any ideas? Thanks!

  5. I finally had a break through in this. The reason I got the error message was that some of the enabled queries were not valid (dropped out in a recent uplift). With invalid queries enabled this was the consequence. Now most of the queries can be accessed through Poer BI but there are still some queries not working. I’m trying to figure out what makes the difference…

Leave a Reply

Your email address will not be published. Required fields are marked *