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.
Now we need to enter the credentials. If your AD allows to – just use your actual user credentials setting.
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, 🙂