Open Data Protocol (OData)#

Estimated time to read: 8 minutes

This document will provide a quick guide for the configuration of an OData endpoint to easily retrieve MES data that can be used for advanced analysis in external tools.

Overview#

Open Data Protocol (OData) is an "open" application-level protocol for interacting with data via RESTful APIs. It supports the description of data models, and the editing and querying of data according to those models, in a simple and standard way.

OData is a widely accepted standard that is supported by Analytics tools such as Microsoft Excel, Microsoft Power BI, Tableau, QlikView among many others. To provide easy interoperability, and provide quick and simple access to MES data, which can then be used for any sort of user advanced analysis (and dashboards), starting on version 8.0, all MES Data is exposed via an OData endpoint.

There are two ways to access OData endpoints:

• You can configure an OData endpoint in the MES and assign it to the user defined query in the Queries section, which is located in the Low Code menu for easy access.
• With Data Platform you can access all Data Sets in MES from CDM, ODS, and DWH via OData endpoints.

It is worth mentioning that, besides the endpoint URL, you are required to generate an access token which will then be used for authentication. For that, you should open your User page and go to the Access Tokens section:

Select the Create button on the top ribbon, fill in a meaningful name (important in case you need to invalidate the token in future), set an expiration date, select Create and then copy the new token:

Step 1: Access to MES data via OData#

As already mentioned, there are two ways to access OData endpoints:

• Via the MES Queries section, which is located in the Low Code menu for easy access.
• Via the Data Platform built-in OData endpoints.

OData using MES Queries#

To adopt OData for the MES Queries tool, if the required data is not already covered by an existing MES Query, you should create a new one in the MES. For more information, see the Queries section of the User Guide.

You can select the Endpoint button on the top ribbon in either the main Queries page or a specific Query page and get the specific endpoint for the OData API:

In either situation, a new wizard will open with an option to copy the endpoint URL, which you will use when configuring access on the external tool:

OData using Data Platform#

This is a native feature of the Data Platform that allows you to access all Data Sets in MES from CDM, ODS, and DWH via OData endpoints. Thus, all Data Sets in MES can be accessed from the following endpoint: https://<host url>/datamanager/odata.

The Data Sets are organized into several folders namely:

• Apps
• CDM
• ODS
• DWH
• IoTEvents
• UserDefined

For more information on Data Manager API, see the following documentation: https://<host url>/datamanager/swagger/index.html

Step 2: External Tool Configuration#

Using Microsoft Excel as an example, and to open the OData dataset, the steps are as follows:

1. Use the Get Data functionality from the Data menu, as shown in the image below, and select the From OData Feed option in the From Other Sources submenu:

   

2. Paste the OData endpoint into the URL field and select OK. The application should redirect you to the OData feed window:

   

   Info

   If this redirection does not happen or if the system throws an error, see the Credentials Cleanup section.

   

3. Select the Basic option on the left side panel and paste your Access Token in the Password field and select Connect (you can leave the User name field empty):

   

4. If the access token is valid, the Navigator View should open and provide a list of all the available Queries:

   

5. A sneak preview of the output data for each query is shown, allowing you to select the queries that should be imported. After selecting Load, the data will be imported:

   

Support for MES Queries with parameters#

Parameterization of MES Queries increases the complexity of integration by OData since additional steps are required to correctly configure the OData feed.

On parameterized MES Queries, parameters are seen in the Excel navigator as $VarName. In the example below, the MES Query GetAreaByFacility has a parameter named $Area_Facility:

As expected, a query that includes a mandatory parameter without a default value defined will return no data. We will walk though this example to illustrate how to properly configure the OData feed. Additional functionalities such as filtering, ordering, and paging are also supported when using the advanced query options.

First, you need to know what sort of parameter $Area_Facility represents. In this case we can use the MES UI to try to run the same MES Query:

It is important to notice that although an entity filter option will be presented in the MES UI, the parameter itself will pass the Id of the Facility to the Query engine. This, however, is not a rule and depends on how the MES Query is defined.

For this example, the target Facility for which you want to retrieve existing Areas is called Cookie Factory. At this moment, the Id of the Cookie Factory Facility most likely is still unknown but can be consulted in the MES UI by simply opening the target Facility and copying from the Info section:

Now that the parameter value is known, you can select the GetAreasByFacility entity in the Navigator view and select Transform Data:

The Power Query editor should open, and you should select the Advanced Editor button:

Now you should replace the existing code by:

OData.Feed("<ODataURL>/<QueryName>?$filter=$VarName Eq 'value'", null, [ Implementation="2.0"])

Using the current example this line would be:

OData.Feed("<http://VM-PROD-CI.cmf.criticalmanufacturing.com:10082/api/odata/GetAreaByFacility>?$filter=$Area_Facility Eq '2006291529520000006'", null, [ Implementation="2.0"])

Select Done and you should be able to see the expected results:

In case of error, check whether the parameter is numeric or string. If it is numeric, you should set the filter as:

$filter=$Area_Facility Eq 2006291529520000006” instead of “$filter=$Area_Facility Eq '2006291529520000006'

You can also apply additional conditions like filtering, sorting and pagination. Except for OData Functions, all other conditions should be supported.

Filtering#

Filtering by complex expression with AND / OR and selecting “Name” (Id is returned as null). Expression:

$filter=$Area_Facility Eq '2006291529520000006' AND Name ge 'Cookie' AND (Id Eq 1907180027250000002 OR Id Ne 1907180027250000002 )&$select=Name:

Ordering#

Ordering by Name ascending, Id descending and retrieving the top 5 rows after skipping 5 rows. Expression:

$filter=$Area_Facility Eq '2006291529520000006'&$orderby=Name asc, Id desc&$top=5&$Skip=5:

Credentials Cleanup#

If the system hangs when connecting to the OData feed, clearing the database credentials should solve the problem:

Use the Get Data functionality from the Data menu, as shown in the image above, and select the Data Source Settings option. In the following screen, select the endpoint causing the issue and select Clear permissions. Select Delete in the next dialog to confirm the operation and repeat the connection configuration: