AX 2012, PowerPivot and a Dash of OData Feeds

Sometime back, I wrote about the importance of Microsoft PowerPivot as well as Open Data Protocol (OData) for Microsoft Dynamics AX 2012. All of those pointed to the fact of what AX 2012 would be able to do with such enabling technologies.

Well now finally we have the launch of Microsoft Dynamics AX 2012, and it's time to start cooking up some self-service BI with PowerPivot and OData feeds!

First it's very important to understand what it means for having data published as OData feeds, coming out of Microsoft Dynamics AX 2012. This starts with a query object within AX, as shown below.:



This is the basis for an OData feed being published, as it's similar the base for Document Services in AX 2012. This is were we stop though, and go a different path from Doucment services, for enabling these feeds. From here, we need to take and go into the Application Workspace, and navigate to: Organization Administration > Setup >Document Management > Document Data soruces.



From here, we need to take and add a new entry. If we do not do this, then nothing will show as active in our list of possible OData feeds. In order to create go to File and then new to create a new document. Once you have the screen up, you should then select the data source type of Query, and select the query we created from the drop down. After that we click "Activate".



At this point, AX system service called ODataQueryService will be what publishes our new query document source we just setup. To test this out, you can open up a IE window, and type the following (Assumes your on the AOS): http://localhost:8101/DynamicsAx/Services/ODataQueryService/

When you do this, and Atom list of queries, that you have security rights too, will appear to work with. That's it for enabling a query to be published as an OData feed. We can start working with this right away yes?



Well, like all great baking stories there are little things that you need to understand so the final product comes out tasty and delicious!

So say, and in the real world this will be the case, that you have Excel and PowerPivot installed on a seperate machines from your AOS. Well if this is the case, and you wanted to work with your published OData feeds from the seperate machine, you might think, ok so instead of using localHost I would use the actual server name.

Wrong! This will cause a 400 Bad Request to be generated, and the reason is the implementation of OData does not allow the use of named servers. You have to use the IP Address of the server in which the ODataQueryService is being published from. So the correct format, to access this from a remote server, or just really separate server than that which the AOS is running is as follows: http://xxx.xxx.xxx.xxx:8101/DynamicsAx/Services/ODataQueryService/

Now of course, any good cook knows that you replace the xxx with the actual values of the ip address itself, however for good measure I point that out to you here.

Doing this, we are actually able to take and see the WSDL, after logging into the box when prompted for our AD creds. You do have to login the first time with your AD creds, however after that you should be fine.



Now that we have our WSDL showing up, we can launch into PowerPivot, and click to add Feed data sources.



Make sure to take and enter into the URI the same format as you did before, and click test connection. This should come back ready for your use, and hit next. In doing so we should see our new query document data source from within AX 2012, being published and therefore able to be consumed as an OData feed.



Now that we have this, we can work with our Query document data sources from within PowerPivot to create some wonderful self-service BI goods! It's pretty powerful stuff to be able to do this, and this adhere's to the security model within AX. Therefore if you have rights to a query then you will see it. Otherwise, you will not see it.

Well that's all for now, I hope you have enjoyed the cooking theme use throughout this post. Till next time!



"Visit the Dynamics AX Community Page today!"