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 outbound connector to connect your Crisp data to additional systems, such as an on-premise SQL server


Resources

Data Model documentation for each connector can be found at the following location, after logging into the Crisp Platform: https://platform.gocrisp.com/knowledge-base/models

 

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 at the top right of the screen, select Outbound Connectors.
Main_Menu_Outbound_Connectors.png

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. 

Crisp_Data_Platform_OData_API_001.png

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

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

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