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