---
pdfexport: true
alias: tutorials-odataaccess
timetoread: true
tutorial: full
description: "Access data sets via the OData protocol using Data Platform"
---

# OData Access to Data Sets

**OData** (Open Data Protocol) is an open standard protocol that enables simple and standardized access to data. **Data Platform** allows accessing the data in all data sets using this protocol.

## Overview

In this tutorial we describe how **OData** clients can easily access data in CM MES data sets stored in **Data Platform**.

## Endpoints

Through **Data Platform**, all data sets can be accessed using the **OData** V4 protocol.

If you navigate to the **Data Sets** page in the **Data Platform** section, you can see that the data sets are organized in 6 root folders:

![Data Sets](../../images/odata_access_01.png)

Each folder contains different types of data sets:

- **Apps** - folder for data sets created and used by MES Apps.
- **CDM** - folder for system data sets containing the **Canonical Data Model** (CDM) data.
- **DWH** - folder for data warehouse data sets containing aggregated data.
- **IotEvents** - folder for data sets associated with iot events.
- **ODS** - folder for data sets containing MES ODS data.
- **UserDefined** - folder for data sets manually created by CM MES users.

Each of these folders can be accessed independently via **OData**. For example, to list all available data sets in the **CDM** root folder, the OData client should use one of these endpoints:

- Service Document - `https://<hostname>/datamanager/odata/CDM`
- Metadata - `https://<hostname>/datamanager/odata/CDM/$metadata`

To access the data of data set `MaterialMovement` in the **CDM** root folder:

`https://<hostname>/datamanager/odata/CDM/MaterialMovement`

To access the data of data set ResourceLayout in the Resource folder (which is inside the CDM root folder):

`https://<hostname>/datamanager/odata/CDM/Resource.ResourceLayout`

Thus, to access data sets that are not in root folders, the path to the data set should be built using the dot separator.

## Query Options

The standard OData query options are supported, plus a few extensions:

| Query Option | Description | Example |
|-|-|-|
| **$select** | Pick the columns to get. | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$select=Enterprise_Name,Facility_Name,Area_Name,Step_Name,Material_Name,InProcessPrimaryQty` |
| **$filter** | Filter the data to get. |`https://<hostname>/datamanager/odata/CDM/MaterialMovement?$filter=contains(Material_Name,'Cookie') and Step_Name eq 'Mixing'` |
| **$orderby** | Columns to order the data. | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$orderby=Material_Name asc,Step_Name desc` |
| **$top** | Number of rows to get. If `$top` is not set, Data Manager will return up to the default maximum number of rows defined by the `DATAMANAGER_DEFAULT_MAX_ROWS` environment variable (set to 1000 by default). | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$top=50` |
| **$skip** | Number of rows to skip before returning the result. | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$skip=50` |
| **$count** | Add the total number of rows in the unfiltered data set to the reply. | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$count=true` |
| **$apply** | Group and/or aggregate data. | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$select=Area_Name,Material_Name,TotalTrackIns,TotalTrackOuts&$apply=groupby((Area_Name,Material_Name),aggregate(TrackInCount with sum as TotalTrackIns,TrackOutCount with sum as TotalTrackOuts))` |
| **$query** | If the query needs to be sent via POST instead of a GET request (for example, the query is too long to be sent with a GET request), the `$query` option should be used, and the actual query sent as plain text in the body of the POST request. | `https://<hostname>/datamanager/odata/CDM/MaterialMovement?$query`, POST request body in plain text: `$select=Step_Name,Material_Name&$filter=Material_Name eq 'Cookie01'` |
| **parameters** | If the data set is of type `Query` and the user-defined query string requires database parameters, they can be sent using the parameters option. For more information, see [[tutorials-odataaccess#data-set-parameters|Data Set Parameters]]. | `https://<hostname>/datamanager/odata/UserDefined/MyDataset?parameters=var1=123,var2='example'` |

!!! note
    All the options run server-side, so all OData queries should be as specific as possible to obtain the best performance.

## Data Set Parameters

A data set of type `Query` can declare database parameters in its SQL SELECT statement, using the syntax `{<name>: <datatype>}`. For more information, see [[user-guide-create-data-set]].

To assign values to those parameters, add the `parameters` query option to the OData request and separate each parameter with a comma:

`https://<hostname>/datamanager/odata/UserDefined/MyDataset?parameters=var1=123,var2='example'`

A parameter holds either a flat value or an array of values. Enclose array values in square brackets and separate them with commas:

`https://<hostname>/datamanager/odata/UserDefined/MyDataset?parameters=var1=[1,2,3],var2=['example','sample']`

### Parameter Conversion

**Data Platform** converts the value of each parameter into the value that ClickHouse receives when it runs the query. The table below lists every valid pattern and the value it is converted to.

| Parameter Type | Value Sent | Value in ClickHouse |
|-|-|-|
| **Valued flat variable** | `var='xpto'` | `'xpto'` |
| **Non-valued flat variable** | `var=` | `null` |
| **Empty flat variable** | `var=''` | `''` |
| **Null flat variable** | `var=null` | `null` |
| **Valued array variable** | `var=[null]` | `[null]` |
| **Valued array variable** | `var=[1,'xpto']` | `[1,'xpto']` |
| **Valued array variable** | `var=[1,'xpto',null]` | `[1,'xpto',null]` |
| **Non-valued array variable** | `var=[,]` | `[null,null]` |
| **Empty array variable** | `var=[]` | `[]` |

Table 1: Conversion of OData parameter values.

An empty value and a null value are distinct. Sending `var=''` gives ClickHouse an empty string, while sending `var=` gives ClickHouse a null. The same applies to arrays, where `var=[]` gives an empty array and `var=[,]` gives an array of two nulls.

!!! warning "Breaking change in 12.0.0"
    An empty array variable is converted to an empty array. In earlier versions, `var=[]` was converted to `[null]`, an array with a single null element.

    A query that expects an array with at least one element no longer gets one, and arrays built from different parameters can end up with different lengths, which makes ClickHouse fail the query. Review every report, dashboard, and OData client that sends an empty array. To keep the previous behavior, send `var=[null]`.

## Accessing Data Sets With an OData Client

Any OData client can connect to **Data Platform**. However, the client will need an **Access Token** to retrieve the data.

To create an **Access Token**, go to your User page, select the **Access Tokens** sub-section, and create a new **Access Token**:

![Access Tokens](../../images/odata_access_02.png)

Save the token, so that it can be used in the OData Client to access the data:

![Access Tokens](../../images/odata_access_03.png)

### Data Sets in Excel

To retrieve data sets in **Excel** go to **Data > Get Data > From Other Sources > From OData Feed**:

![Access Tokens](../../images/odata_access_04.png)

Type the URL for the root folder you want to access, or an OData query to access a specific data set with query options, and select **OK**:

![Excel OData url](../../images/odata_access_05.png)

An authentication window will pop-up. Select **Basic**, paste your **Access Token** in the **Password** field and select **Connect** (you can leave the **User name** field empty):

![Excel OData authentication](../../images/odata_access_06.png)

You should now have access to the data in the data set, or to the list of data sets if you gave it an URL to a root folder (in which case you can pick the data set from the list). The data is now available to be processed and refreshed in **Excel**:

![Excel OData data](../../images/odata_access_07.png)

**Excel** caches the access tokens. Thus, if you are having trouble accessing the data (for example, your token expired) you should go to **Get Data > Data Source Settings** and select **Clear Permissions**, select the permissions you want to clear and **Delete** them:

![Excel OData clear permissions](../../images/odata_access_08.png)

Next time you try to connect to the data set, **Excel** will ask for the token again.

### Data Sets in Jupyter Notebook

There are several ways to access **OData** data sets in **Jupyter Notebook**. For example, with a regular HTTP request with an OData query:

![Excel OData clear permissions](../../images/odata_access_09.png)

Just make sure to use **Basic Authentication** in the request, and setting your **Access Token** as the password.