Tales From The Script: OData

One of the major improvements made in Dynamics AX 2012 R2 is with regards to OData. For the non-developers, the Open Data Protocol (OData) is an underlying technology protocol that makes it possible for Personal BI concepts to be used with MS Dynamics AX.

OData is an open source standard created by Microsoft that enables easy access of data by people and applications over the Internet. For a quick and simple overview of OData, check here.

Dynamics AX 2012 R2 features a number of improvements especially in Document Data Sources. To access Document Data Sources, click Organization Administration > Setup > Document Management > Document Data Sources.

One of the changes in Document Data Sources made in Dynamics 2012 R2 is the ability to create custom query elements. There is also an Edit button that can be used to edit the custom queries. The button is initially disabled, but becomes enabled after you select and register a query for target custom filtering.

With regards to OData, the ability to edit queries is the most significant improvement in Dynamics AX 2012 R2. The improvement enables anyone that is an Administrator, even if they are not developers, to filter queries beyond what is necessarily defined in their specific designs.

For example, an Administrator can select a custom query option and have a target query element to create customer filtering options for. Just like is the case with most query options on grids and forms, an Administrator can select and give specific values for query execution.

On setting custom query options, the Edit Query button becomes enabled for the custom query elements within the Document Data Sources form. Moreover, after setting the query filtering, the elements in the form can be renamed to be more descriptive of the filter being applied.

With this in place, it’s now possible to use the ODataQueryService to create Personal BI artifacts through tools like PowerView and PowerPivot. The ODataQueryService is available as part of every Microsoft Dynamics Application Object Server (AOS). When you run a query, the result will be a valid OData query element output in either PowerView or PowerPivot, whichever you choose.

However, even with the above change, it’s worth noting that many of the requirements for designing a valid OData feed have not changed. 

OData of Death: Queries Failing to Show as OData Feeds

OData feeds power PowerPivot and PowerView Personal BI artifacts. Sometimes, you may activate a query through ODataQueryService but nothing happens. What can be the cause of this?

When a query fails to show up as an OData feed, the output is usually registered as ODataofDeath. There are a number of reasons why an activated query may fail to show up as a valid OData feed. The failure is usually as a result of the query not meeting the specific criteria required for it to be considered a valid OData feed.

There are four reasons why the ODataofDeath may occur::

i) With valid OData feeds, only queries that support “Value-based paging” are executed. Therefore, if the FetchMode property value for any AOT query is “1:N”, there will be an error.

ii) The data in an OData query response must have a unique primary key. Therefore, AOT queries with View data sources are not valid and will not be displayed when ODataQueryService is run.

iii) OData protocol create and update operations must be supported for a query to be considered a valid OData feed.

iv) OData protocol filters must be supported for a query to be considered a valid OData feed.

When you get an ODataofDeath result, you need to look at the design of your query and determine which of the above aspects apply to it.

For instance, if the FetchMode is 1:N, you will need to change it to 1:1 and deactivate the query. The query will then have to be compiled and restored to refresh the metadata. Apart from this, the Elements and Dictionary caches have to be refreshed before the query is activated for use via ODataQueryService.

If the resulting data is not exactly as you would like, you can use PowerPivot to combine it for analysis.