OData Query Utility for Dynamics AX 2012

Well I hope everyone is doing well, and finding yourself very productive in this Exciting Week for Microsoft Dynamics. Of course, I'm talking in reference to the now released Microsoft Dynamics AX 2012 R2.

While that for sure deserve's a lot of attention, there is something I wanted to give to the community at large, as my Christmas present if you will to everyone. What I'm talking about is an Open Source Project I created called.: AX-OData Queries



As the above image implies, it's the gift that keeps on giving! That is, this open source project has ready to use, a Utility Wizard, and context action that makes understanding which Queries in a Dynamics AX 2012 instance, are valid candidates to become PowerPivot OData Feed sources!



Further as part of this project, there is a Library component that I started, in which I have placed a customer aging query, in the standard folder location. You can read more, by visiting the project's home page.

Further, important, is the documentation, so the following is a direct link to that.: AX-OData Queries Project Documentation. Make sure to read the Read Me, and then following along from there. This includes from Install, through to How To Use Guide.



The above image is the Dynamics AX 2012 project that currently comprises as v1.0.0 of the OData Query Utility scope. This again, includes a Wizard as well as an Context menu, that lives under the Add-in's section.



Like all projects, this needs revision and refinement. This is an Open Source project, and so I do encourage you to get involved as you can. Help make this better! Further, the changes and impacts of AX 2012 R2 has not be tested against this. So there is plenty of chances to make this a much better set of tools, and queries for common data sets.



As-is, you can download the Utility as an XPO file, and import it into a target Dynamics AX 2012 RTM or Feature Pack instance. Then you can either run the Wizard, or you can execute the Context Menu for a specific single query element. The goal, is to help enable the continued theme of creating a System of Engagement with Microsoft Dynamics AX. This specific will help on the Personal BI landscape, in helping faster enable use of PowerPivot, via OData Feeds.

Well that's all for now! (Early) Merry Christmas! As updates happen, of course I will take the time and let everyone know. Please visit the project, download, test, enjoy, contribute! Till Next Time!

Update: There was a slight feature added this afternoon. One that I thought was important to make the first grid, in which you filter the searching of queries by layers. We have added a new field called "Number of Queries".



Now I admit the speed in which the utilElements table can be read, which is a key to the majority of this, is not at the level I like. This is one of the area's that will be focused on next.
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

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!"