Crisp Data Platform OData API – Crisp Knowledge Base

Introduction

The Crisp Data Platform OData API allows you to retrieve your Distributor or Retailer Data from the Crisp Data Warehouse. The API adheres to OData Version 4 protocol. Crisp uses the OData API to connect to Excel, Power BI, and Tableau, but you can also create a generic Odata API connector to connect your Crisp data to additional systems, such as an on-premise SQL server.

Resources

Data documentation for each connector can be found here: Crisp Data Catalog.

Authentication

The OData API is secured via Basic Authentication. It utilizes an Authorization Header that contains the word Basic followed by a space and a base-64 encoded string {username}:{password}. For example, to authenticate as user "demo" with password "p@55w0rd" the client would send:

Authorization: Basic ZGVtbzpwQDU1dzByZA==

See https://en.wikipedia.org/wiki/Basic_access_authentication for more information about Basic Authentication.

To retrieve your authentication username and password:

1. From the main menu on the left side of the screen, select Connectors.

2. If you want to see Odata API info for a specific connector, select the connector tile (e.g., Power BI) that you want to see details for.

3. If you want to see details for a generic OData API connector, select the OData API connector tile.

A details screen appears where you can view the OData API endpoint URL, username, and password used to authenticate.

Endpoints

You can also find Endpoints on the destination connector properties page shown above.

https://api.gocrisp.com/odata/accounts/{account_id}/connector_configurations/{connector_configuration_id}/v1/feed.svc

The base endpoint will return a list of datasets available for the configured connector. From the list, you can then append the name of the dataset to get the actual data:

https://api.gocrisp.com/odata/accounts/{account_id}/connector_configurations/{connector_configuration_id}/v1/feed.svc/{dataset}

All request methods are GET

For example:

GET https://api.gocrisp.com/odata/accounts/999/connector_configurations/1/v1/feed.svc

RESPONSE
{
"@odata.context": "https://api.gocrisp.com/odata/accounts/999/connector_configurations/1/v1/feed.svc/$metadata",
"value": [
{
"name": "Analysis_Unfi_Store_Attrition_Events",
"url": "Analysis_Unfi_Store_Attrition_Events"
},
...
{
"name": "Unfi_Weekly_Supplier_Service_Level",
"url": "Unfi_Weekly_Supplier_Service_Level"
}
]}

To access the Analysis_Unfi_Store_Attrition_Events dataset, make the following call:

GET https://api.gocrisp.com/odata/accounts/{account_id}/connector_configurations/{connector_configuration_id}/v1/feed.svc/Analysis_Unfi_Store_Attrition_Events

Queries*

URL Option	Syntax	Description
Get Available Datasets	{baseURL}	Returns a list of available datasets
Get Metadata	{baseURL}/$metadata	Returns a list of datasets and their meta-data. This call returns XML instead of JSON Append ?$format=json to get metadata in JSON format
Get Specific Dataset	{baseURL}/{dataset name}	Returns detailed payload for specified dataset
Select	{baseURL}/{dataset name}?$select={comma separated field list}	Returns detailed payload for specified dataset and specific selected fields
Filter	{baseURL}/{dataset name}?$filter={query filter}	Returns specific data as per filter value for dataset. OData standards should be followed for operator types
Count	{baseURL}/{dataset name}?$count=true	Returns a record count for the given query and filter parameters

* Please refer to Endpoints to retrieve the correct {baseURL}

Filter Supported Operators

The query endpoints support filtering by supplying a $filter query parameter to the endpoint URL.

URL Option	Syntax
Equals	$filter={field name} eq {field value}
Not Equals	$filter={field name} ne {field value}
Greater Than	$filter={field name} gt {value }
Greater Than or Equal	$filter={field name} ge {value}
Less Than	$filter={field name} lt {value}
Less Than or Equal	$filter={field name} le {value}
Logical And	$filter={field name} le {value} and {field name} eq {value}
Logical Or	$filter={field name} le {value} or {field name} eq {value}
Logical Not	$filter=not ({field name} eq {value})

Not that all query parameters must be URL encoded. For example a filter for PeriodStartDate would be encoded as:

https://api.gocrisp.com/odata/accounts/999/connector_configurations/0/v1/feed.svc/Unfi_Weekly_Manufacturer_Chargeback_Extract?%24filter%3DPeriodStartDate%20eq%202020-01-01

Notes and Examples

All date fields are formatted as YYYY-MM-DD
For String values, enclose the parameters in single quotes, ex. 'value'
See https://www.odata.org/documentation/odata-version-2-0/uri-conventions/ for more information

Time Deltas and Incremental Loads

A combination of the ‘AND’ operation and the 'GT' and 'LE' operations can be used for incremental loads. For example, if we last fetched data on 2021-01-01 and a week later we want to fetch all data that has been updated since we last fetched data, we would need to request data where the start date is greater than or equal to 2021-01-01 and less than current time:

GET {baseURL}/{dataset name}?%24filter={start date parameter}%20gt%202020-01-01%20and%20{start date parameter}%20le%20{now}

API Output Rules

Output will be formatted in JSON. The one exception to this is the $metadata query option, which returns XML by default. To retrieve $metadata in json format, append the $format=json query parameter.
The output for a dataset will only contain data for the fields requested in the call. If no fields are requested, all fields in the requested dataset will be displayed.
Pagination:
- To set a desired page size, send the following header with the request:
  - ```
  prefer: odata.maxpagesize={integer value}
```
- In case the requested maxpagesize is bigger than the server allowed maxpagesize, then the request will return the max rows set by the server, which is 10000 rows per page.
- If page size is not provided, the system defaults it to 10000
- Next Page: The URL for the next batch of data (if available) is provided at the bottom of the payload as:
  - ```
  @odata.nextLink
```