---
pdfexport: true
alias: tutorials-opendataprotocol
timetoread: true
tutorial: full
description: "This document guides users on accessing MES data via the Open Data Protocol (OData) endpoint"
---

# Open Data Protocol (OData)

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:

![Generate Access Token](../../../images/odata_4.png)

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:

![Access Token](../../../images/odata_5.png)

!!! warning
    This token will only appear once, so you must store it in a safe location for future use.

### 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 [[user-guide-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:

![Add OData Endpoint from Main Queries page](../../../images/odata_1.png)

![Add OData Endpoint from Specific Query page](../../../images/odata_2.png)

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:

![Endpoint URL](../../../images/odata_3.png)

#### 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

![endpoints](../../../images/dataplatform_odata_datasets_endpoint.png)

!!! info
    Step 2 is identical for both OData approaches. Just choose the OData endpoint you want to connect during basic authentication.

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:

    ![Get Data from OData Feed in Microsoft Excel](../../../images/odata_6.png)

2. Paste the OData endpoint into the **URL** field and select **OK**. The application should redirect you to the **OData feed** window:
  
    ![Microsoft Excel - OData feed URL](../../../images/odata_7.png)

    !!! info
        If this redirection does not happen or if the system throws an error, see the [[tutorials-opendataprotocol#credentials-cleanup|Credentials Cleanup]] section.

    ![Microsoft Excel - OData feed access](../../../images/odata_8.png)

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):

    ![Microsoft Excel - OData feed user name](../../../images/odata_9.png)

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

    ![Microsoft Excel - OData feed queries](../../../images/odata_10.png)

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:

    ![Microsoft Excel - OData feed preview](../../../images/odata_11.png)

## 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`:

![Microsoft Excel - Query parameters](../../../images/odata_12.png)

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:

![Using query parameters in MES UI](../../../images/odata_13.png)

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:

![Checking ID field in MES UI](../../../images/odata_14.png)

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

![Navigator - Transform Data](../../../images/odata_15.png)

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

![Microsoft Excel - Advanced Editor](../../../images/odata_16.png)

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"])`

![Microsoft Excel - Advanced Editor feed](../../../images/odata_17.png)

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

![Microsoft Excel - Advanced Editor results](../../../images/odata_18.png)

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`:

![Microsoft Excel - Advanced Editor results filtering](../../../images/odata_19.png)

### 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`:

![Microsoft Excel - Advanced Editor results ordering](../../../images/odata_20.png)

## Credentials Cleanup

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

![Microsoft Excel - Data Source settings](../../../images/odata_21.png)

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:

![Microsoft Excel - Credentials Cleanup](../../../images/odata_22.png)