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

AX 2012 Document Data Sources

AX 2012 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”

Power BI - Connect to ODATA Feed

Power BI – Connect to 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.

ODATA Feed Connection String

ODATA Feed Connection String

 

Credentials

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

ODATA Feed Enter Credentials

ODATA Feed Enter Credentials

 

Select Sources

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

ODATA Feed Select Sources

ODATA Feed Select 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, 🙂

Posted in Document data sources, Dynamics AX 2012 R3, Power BI, Working with Dynamics AX 2012 Tagged with: , , , ,
6 comments on “Connect Microsoft Power BI to AX 2012 – ODATA query service
  1. Thomas Häger says:

    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!

    • Ananth says:

      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.

      • Martin says:

        I had this type of error as long as I used the DNS. When I switched to the IP itself it worked just fine. You can check if it works using the browser – then you know if the communication works.

  2. Thomas Häger says:

    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. Thomas Häger says:

    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!

Leave a Reply

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

*