Welcome back Ghouls & Ghost, where you find yourself once again traveling into the strange unknown of
Freaky Tech Friday!. Today I have an especially scary tale, for those of you brave enough to dare listen. Be fore warned, as this story goes deep into the unknown, down into the deeps of the
ODataQueryService for AX 2012.
That's right, you are now listening to the tale of
OData of Death! This tale, like most of its sort, starts off in a typical fashion. A BI or X++ Developer is working on a Query in AX 2012, minding their own business, when they activate there work, go to see the ODataQueryService and
.... Nothing!.
What horrible cause could this be? Why will the query not show up as a valid OData feed?
OData of Death has struck again, in another AX 2012 instance. In fact there are four deadly sins of query's and the ODataQueryService in general, that control the fate of our much needed hero, known as Personal BI!
These deadly sins I refer to are.:
- OData protocol filters are not supported
- OData protocol create and update operations are not supported
- Each record in an OData query response must have a unique primary key. AOT queries with View data sources are therefore not OData query candidates and will not be displayed in an OData metadata request
- Only queries that support “Value-based paging” are executed. AOT queries whose FetchMode property value is “1:N” will generate an error message in the Windows Event Log
There we have it, and so our Query has fallen to one of these deadly sins. Which one could it be? Since we know that the first are not the cause of the sudden death to our work, it has to be one of the last two. In looking at our query design, we did not use a view.:
Then we must look at the very last sin, the one that can really change how we think about designing queries. That's right it was a
FetchMode death! In looking at our query design, we have on the VendCertification data source, a FetchMode of 1:n.
Now to resurrect the dead, and bring forth our query to the living - we need to change the FetchMode from 1:n, back to 1:1. Then Deactivate the query in the Document Data Sources. Make sure to compile, and restore the query so the meta data is refreshed, and further refresh the Dictionary and Elements caches. At this point the ritual for reviving our query is complete. We can activate it once more, and see that our query now appears for use, via the ODataQueryService.
With this however, comes at a price. The data is not exactly as we would like it to be represented.
What to do?! There is hope on the horizon, a changing of the wind soon - as it has been foretold that the second coming of AX 2012, in the form or
AX 2012 R2 will bring with it redemption for some of these deadly sins.
Further you must think in terms of the desired usage and consumption of your data via OData Query feeds, and use the magic found in the sacred
PowerPivot offering to join sources within it's mighty ability.
Well that's all for this weeks
Freaky Tech Friday! I hope you avoid the deadly sins, and stay clear of the
OData of Death scenario's! Come back next week, if you dare, for another frighten tale on
Freaky Tech Friday!Till Next Time!
Follow Me @:
"Visit the Dynamics AX Community Page today!"
Labels: AX 2012, Dynamics AX 2012, Freaky Tech Friday's, Halloween, Microsoft, OData, OData Feed, ODataQueryService, PowerPivot, Query
