Today's post on the OData Query Service is by Principal Software Architect Arthur Greef.

Microsoft Dynamics AX 2012 hosts an OData Query Service that uses the Open Data Protocol (OData) protocol to execute queries that are modeled in the Application Object Tree (AOT). The OData protocol is a HTTP protocol for querying and updating data. Microsoft Dynamics AX 2012 supports data query scenarios (with some limitations listed below), but does not support data update scenarios. The following example describes how to read data from the Currency table. The sample assumes that the Application Object Server (AOS) is installed on a machine named ‘aoshost’.

Set up an AOT query that can be executed by the OData Query Service

  1. Open the Microsoft Dynamics AX 2012 development environment.
    1. Under AOT, click the Queries node, and then create a new query node. Either  (1) Select File > New or (2) Right-click the Queries node, and then select New Query.
    2. Change the name the new query node to “CurrencyQuery”.
    3. Under AOT, click Queries > CurrencyQuery > Data Sources node, and then create a new data source node. Either: (1) SelectFile > New. or (2) Right-click the Data Sources node, and then select New Data Source.
    4. Select the new data source node Currency_1, and then on the Table property in the property sheet, select Currency
  2. Open the Microsoft Dynamics AX 2012 Windows Client.
    1. Click Organization Administration > Setup > Document management > Document data sources.
    2. In the Document data sources form, click New or press CTRL+N to add a new document data source.

                                          i.    No specific value is required in the Module column.

                                        ii.    In the Data source type column, select Query.

                                       iii.    In the Data source name column, select or enter the CurrencyQuery.

                                       iv.    Select the Activated check box to activate the query as a document data source.

                                        v.    Close the Document data sources form.

Using the OData Query Service with Windows Internet Explorer

  1. Open Internet Explorer.
  2. Enter the following URL to return the OData Query Service definition: http://aoshost:8101/DynamicsAx/Services/ODataQueryService/. The service will display the name CurrencyQuery. The Application Object Server (AOS) host name and the services port are provided when installing and configuring each AOS and can be found in the AX32Serv.exe.config file created under the file system folder created for the AOS.
  3. Enter the following URL to return the OData Query Service metadata definition: http://aoshost:8101/DynamicsAx/Services/ODataQueryService/$metadata
  4. Enter the following URL to execute the CurrencyQuery query: http://aoshost:8101/DynamicsAx/Services/ODataQueryService/CurrencyQuery

Note: If Internet Explorer formats the results as a feed, then right-click on the HTML page, and select View source to open the result in Notepad.

 Using the OData Query Service with Microsoft PowerPivot

  1. Install Microsoft Excel.
  2. Install Microsoft PowerPivot.
  3. Open Microsoft Excel.
  4. Click the PowerPivot tab.
  5. On the PowerPivot tab, in the Launch group, click the PowerPivot Window button. A PowerPivot window opens.
  6. On the Home tab, in the Get External Data group, click the From Data Feeds button.
  7. In the Data Feed Url field, enter the following URL:  http://aoshost:8101/DynamicsAx/Services/ODataQueryService/
  8. Click the Next button to view the CurrencyQuery OData data source.
  9. Click the Finish button to import the currency data into PowerPivot.
  10. Click the Close button to view the currency data.

Using the OData Query Service with Microsoft Visual Studio

  1. Install Microsoft Visual Studio.
  2. Create a new Console Application.
  3. Right-click the project References node in the Solution Explorer window, and then select the Add Service Reference… menu item.
  4. Enter the following Url into the Address field and click Go: http://aoshost:8101/DynamicsAx/Services/ODataQueryService/
  5. Click OK to add the service reference to the project.

OData Query Service paging

The OData Query Service returns data in pages that have a length specified in the AX32Serv.exe.config file created under the file system folder created for the AOS. The page size can be modified by editing the configuration file and by restarting the AOS. The default page size is shown in the following XML document fragment.

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

OData Query Service limitations

  • 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

OData Query Service notes

  • You may need to replace the AOS host machine name with an IP address in the OData Query Service Url when accessing OData Query Services remotely.
  • Some applications require a slash mark (/) at the end of the service definition URL. For example: http://aoshost:8101/DynamicsAx/Services/ODataQueryService/
  • Check the Application Windows Log in the Event Viewer if no results are returned from a query. Error messages generated by unsupported queries are written to this log.

 Known Bugs

  • There is a bug in how we check for 1:N fetch mode in Microsoft Dynamics AX 2012 and this results in a windows event log message stating that the query is not supported because it specifies a fetch mode of 1:N. The problem is that the root data source's fetch mode defaults to 1:N and the query validation logic checks and throws an exception for this condition. The root data source fetch mode is not exposed in the AOT so the work around is to export the query as an XPO file and then to edit the query by adding the fetch mode for the root data source as shown in the following example:

     BLOCK #TrvExpTable
      PROPERTIES
        Name                #TrvExpTable
        Table               #TrvExpTable
        UniqueId            #1000
        FetchMode           #1:1
      ENDPROPERTIES

This posting is provided "AS IS" with no warranties, and confers no rights.