ODataQueryService Paging Setting for AX 2012

Today I wanted to spend a little time, in the trenches if you will, with the Microsoft Dynamics AX 2012 ODataQueryService. I've talked a good bit about this, and it's value over the past 12 months for enabling Personal BI conepts through the use of PowerPivot.



Today I wanted to spend time, talking through an issue that you might encounter when actively using OData feeds from AX 2012. Lets first look at the desired need for reporting, specifically targeting the InventTable or Item Master within Dynamics AX.



What you see in the above image, is a query that joins several tables, in order to get a full result for Item data, as an OData Feed. As you can see, we have Invent, Purch & Sales Setup data, Item Group, Model group and the InventTable itself. When trying to execute such a query as an OData feed within Dynamics AX 2012 with default paging settings for the ODataQueryService, you might see your TempDB database grow in an uncontrolled fashion. This was seen with an Dynamics AX 2012 FP CU3 instance, with SQL Server 2012 database.

In looking at this further, it seemed to take place anytime the InventTable was involved within a Query. EcoResProduct data works fine, as well as tables with a lot more records. The behavior that was seen, specifically related to PowerPivot not being able to complete the request for data, and the TempDB taking as much space and it could on the drive that hosted it's .MDF file.

If however, we change the paging file setting in the AOS(es) AX32Serv.exe.config files, key of ODataQueryPageSize, value from 1000 to 5000, then you will see such a problem go away.

       <add key="ODataQueryPageSize" value="1000" />

change to:

       <add key="ODataQueryPageSize" value="5000" />

.

Even with this in mind, when need to keep the goal of what PowerPivot is meant to deliver front and center. Meaning it's better to design queries, that would represent one or two tables for a dataset. These separate queries, then become valid targets for fact tables and dimension tables within the PowerPivot design space. The front & center concept here, is the fact that we are building a cube when working with PowerPivot. So though you can increase the paging size for the ODataQueryService, but keep in mind that the best route might be to break up a larger query with multiple tables, into multiple queries, with less tables per each query.

This setting change was first reported by Microsoft, with the following post.: OData Query Service. Further keep in mind, that you can join the Open Source project I have going, focusing around OData Queries and Utilities for Microsoft Dynamics AX.: AX-OData Queries on CodePlex

That is all for this post, but check back soon as a lot more to come. Till Next time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

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

Snap, Crackle, InfoPath?

Well the release of Snap 2.0 for Dynamics AX was recently released. [ link: Codeplex Snap in program link ] With this release we see some changes. One the move to codeplex from GotDotNet for managing the project, and the
add-on's for the different Snap ins for doing different information worker task. these are still simplistic in nature, but show the power of using this piping for Snap to achieve this type of interaction.

One of the area's I wanted to point out was the Customer Journal Snap-in. This Snap-in is focused around InfoPath forms. Some post back I talked about using InfoPath with Dynamics AX. And here is a great example of doing that, with the Snap pipe line. You could easily use this model to achieve other custom built solutions for Information workers at your clients, Today, that could easily carry into the next version of Dynamics AX. Check out the bit from the site posting below:

"The Customer Journal Snap-in is a handy tool for a Sales representative that helps improve the productivity of sales meetings by enabling the sales rep to view and carry relevant details of a customer (contact info, orders, service requests), which are copied over from Microsoft Dynamics AX 4.0 to a Microsoft Office Infopath 2007 form, to the sales meeting. He can also make changes to the customer's information and take notes using this InfoPath form during the meeting and later synch the changes back to Microsoft Dynamics AX 4.0 server on the click of a
button."

So with this, you have the ability for some information worker processes to work with office 2007, and can be very attractive to Office centered users, that make use of InfoPath or Excel.

Of course we all know the future interface for Dynamics AX is Office and SharePoint, and the futher development of this along with the development for the future releases of Dynamics AX just shows this to be true. The future: Office, SharePoint, .Net, and SQL Server are the core.

Well check back soon as I continue to post!

Find a job at: www.DynamicsAXJobs.com