[badge:OData v4|info]

Overview

The OData v4 API provides a standardized RESTful interface for querying and accessing data from all entities in the system. OData (Open Data Protocol) is an OASIS standard that defines best practices for building and consuming REST APIs.

The base URL for all OData requests is:

`{HOSTNAME}/api/odata/{companyId}`

PowerBI Integration

The OData v4 API can be used directly in PowerBI and other business intelligence tools that support OData feeds. This allows you to create reports and dashboards using your data without custom integrations.

In PowerBI, select **Get Data** → **OData**
Enter the OData URL: `{HOSTNAME}/api/odata/{companyId}`
When prompted for authentication, select **Basic** authentication
Enter username: `api` and password: your API key
Select the entities (tables) you want to import

**API Key Required**

You need to generate an API key before using the OData feed. [navigate:/apiKeys|Generate API key here|key]

Key Endpoints

Supported Query Options

Usage Examples

Basic Filtering

GET /api/odata/{companyId}/products?$filter=price gt 100&$orderby=name&$top=10

Collection Lambda (any/all)

GET /api/odata/{companyId}/products?$filter=reviews/any(r: r/rating gt 4)

Expanding Relations

GET /api/odata/{companyId}/products?$expand=category,supplier&$select=name,price

Aggregation with Grouping

GET /api/odata/{companyId}/products?$apply=groupby((category_id),aggregate($count as count,price with sum as total))

String Functions

GET /api/odata/{companyId}/products?$filter=contains(name,'widget') and startswith(sku,'PRD')

Date/Time Functions

GET /api/odata/{companyId}/orders?$filter=year(orderDate) eq 2024 and month(orderDate) ge 6

Math Functions and Operators

GET /api/odata/{companyId}/products?$filter=round(price) gt 100 and (price mul 0.8) lt 500

Entity References ($ref)

GET /api/odata/{companyId}/products(123)/category/$ref

Returns reference URL to the related category entity.

Code Examples with Authentication

tab: JavaScript
// Using HTTP Basic Authentication
const apiKey = 'your-api-key';
const auth = btoa('api:' + apiKey); // base64 encode

fetch('/api/odata/{companyId}/products?$top=10', {
  headers: {
    'Authorization': 'Basic ' + auth
  }
})
  .then(response => response.json())
  .then(data => console.log(data.value));
---
tab: cURL
# Using HTTP Basic Authentication (-u flag)
curl -u api:YOUR_API_KEY "/api/odata/{companyId}/products?\$filter=price gt 100&\$top=10"

# Or manually specify Authorization header
curl -H "Authorization: Basic $(echo -n 'api:YOUR_API_KEY' | base64)" \
  "/api/odata/{companyId}/products"
---
tab: Python
import requests
from requests.auth import HTTPBasicAuth

api_key = 'your-api-key'
url = '/api/odata/{companyId}/products'

# Using HTTP Basic Authentication
response = requests.get(
    url,
    auth=HTTPBasicAuth('api', api_key),
    params={'$filter': 'price gt 100', '$top': 10}
)

data = response.json()
print(data['value'])
---
tab: PowerShell
$apiKey = "your-api-key"
$credentials = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("api:$apiKey"))

$headers = @{
    "Authorization" = "Basic $credentials"
}

$response = Invoke-RestMethod -Uri "/api/odata/{companyId}/products?`$top=10" -Headers $headers
$response.value

Authentication

OData API endpoints require authentication using one of the following methods:

HTTP Basic Authentication [badge:Recommended for OData|success]

For OData endpoints, use HTTP Basic Authentication with username api and your API key as password:

Authorization: Basic {base64(api:your-api-key)}

Example with curl:

curl -u api:YOUR_API_KEY \
  /api/odata/{companyId}/products

[navigate:/apiKeys|Generate and manage API keys →|key]

Bearer Token (JWT)

Use JWT token obtained from login for UI authentication:

Authorization: Bearer your-jwt-token

API Key Header

Alternative method using custom header (not for OData endpoints):

X-API-Key: your-api-key

Session Cookie

Automatic session-based authentication for browser requests:

Cookie: session=...

**Important**

For OData endpoints (`/api/odata/*`), HTTP Basic Authentication is required. The username must be `api` and the password is your API key. Other authentication methods work for non-OData endpoints.

ETags for Caching

The OData API supports ETags (Entity Tags) for efficient caching. ETags help clients avoid unnecessary data transfers when entities haven’t changed.

How it works:

Server returns an `ETag` header with each response
Client sends `If-None-Match` header with the ETag value on subsequent requests
If entity hasn't changed, server returns **304 Not Modified** (no body)
If entity changed, server returns **200 OK** with updated data and new ETag

Implemented Features

• Service document endpoint
• $metadata endpoint with EDMX generation
• Capability annotations in metadata (FilterRestrictions, SortRestrictions, etc.)
• FilterFunctions annotations (advertises supported filter functions)
• ApplySupported annotations (advertises aggregation capabilities)
• Entity collection queries
• Single entity retrieval by ID
• $count endpoint
• $ref endpoint for navigation properties (get entity references)
• $filter with comparison operators (eq, ne, gt, ge, lt, le)
• $filter with logical operators (and, or, not)
• $filter with math operators (add, sub, mul, div, mod)
• $filter with string functions (contains, startswith, endswith, tolower, toupper, concat, substring, indexof, length, trim)
• $filter with date/time extraction functions (year, month, day, hour, minute, second, quarter, week, dayofweek)
• $filter with date boundary functions (startOfYear, endOfYear, startOfMonth, endOfMonth, startOfDay, endOfDay, startOfHour, endOfHour)
• $filter with math functions (round, floor, ceiling)
• $filter with collection lambda operators (any, all) — fully implemented with nested support
• $select for field projection
• $orderby for sorting
• $top for limiting results
• $skip for pagination
• $expand for relations (basic)
• $count inline in queries
• $apply with aggregate transformations
• $apply with groupby transformations
• Aggregation functions: count, sum, avg, min, max
• OData v4 response format with @odata.context
• Proper OData headers (OData-Version, Content-Type)
• ETags for efficient caching
• If-None-Match conditional GET (returns 304 Not Modified)

Missing Features (Future Implementation)

The following features are not yet implemented:

- `$search` — Full-text search (parsed but not executed)
- `$filter` with geo functions (`geo.distance`, `geo.intersects`, etc.)
- Advanced `$apply` transformations (`filter`, `compute`, `concat`, `expand`)
- `$compute` — Computed properties
- `$expand` with nested options (e.g., `$expand=category($select=name)`)
- `$levels` — Recursive hierarchies
- `$format` — Explicit format specification (always returns JSON)
- `POST` — Create new entities
- `PATCH` — Update entities
- `PUT` — Replace entities
- `DELETE` — Delete entities
- `$batch` — Batch requests
- Delta tracking — `$deltatoken`
- Function imports and action imports
- `If-Match` — Optimistic concurrency control (ETags for caching only)
- Server-driven paging with `@odata.nextLink`

Best Practices

• Use $select to request only the fields you need
• Implement pagination with $top and $skip for large datasets
• Use $filter to reduce data transfer instead of client-side filtering
• Leverage ETags and If-None-Match for efficient caching
• Use $apply for server-side aggregations instead of aggregating client-side
• Check the $metadata endpoint to discover available entities and their properties