---
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 dataset is of type `Query` and the user-defined query string requires database parameters, they can be sent using the parameters option. | `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.

## 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, scroll down to the **Access Tokens** 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.