OData

Expose pipeline results to Power BI, Excel, and Grafana via OData v4

Query your pipeline results from Power BI, Excel, or Grafana — no export, no API, no middleware.

OndatraSQL includes a built-in OData v4 server. Add @expose to a model, run ondatrasql serve, and connect your BI tool directly.

Mental Model

Your pipeline writes data. OData lets tools read it.

No ETL into a warehouse. No CSV exports. No custom API.

Why This Exists

Most pipelines end at a table. To get data into a BI tool, you need a separate API layer, a data warehouse with network access, or manual exports.

OndatraSQL serves the data directly. One binary handles both the pipeline and the serving.

Quick Start

1. Mark Models for Serving

Add @expose to any materialized SQL model:

-- models/mart/revenue.sql
-- @kind: table
-- @expose order_id

SELECT
    order_id,
    customer,
    amount,
    order_date
FROM staging.orders

The column after @expose is the OData primary key. Power BI uses it to identify rows during refresh.

2. Run the Pipeline

ondatrasql run

3. Start the Server

ondatrasql serve

OData server starting...
  Endpoint: http://127.0.0.1:8090/odata
  mart_revenue (4 columns)

That’s it. Your data is now queryable via OData v4.

The @expose Directive

@expose marks a model for OData serving. Only materialized SQL models are supported — not views, events, or scripts.

With Key (recommended)

-- @expose order_id

The column becomes the OData <Key> in metadata. Power BI uses it for row identification.

Without Key

-- @expose

All columns become a composite key. Works, but Power BI may warn about it.

Compatibility

Endpoints

Entity names use underscores: mart.revenue becomes mart_revenue in the URL.

Query Parameters

Standard OData v4 query options:

Filter Operators

Examples

# All revenue data
curl "http://localhost:8090/odata/mart_revenue"

# Top 10 by amount
curl "http://localhost:8090/odata/mart_revenue?\$orderby=amount%20desc&\$top=10"

# Filtered with count
curl "http://localhost:8090/odata/mart_revenue?\$filter=amount%20gt%20100&\$count=true"

# Just the row count
curl "http://localhost:8090/odata/mart_revenue/\$count"

# Schema
curl "http://localhost:8090/odata/\$metadata"

Connect Your Tools

OData v4 is an open OASIS standard supported by a wide range of BI tools, analytics platforms, and UI frameworks.

Power BI

1. Open Power BI Desktop
2. Get Data → OData Feed
3. Enter: http://your-server:8090/odata
4. Select the tables you want
5. Load or Transform

Power BI reads $metadata for types, uses $select and $filter for efficient loading, and supports scheduled refresh.

Excel

1. Data tab → Get Data → From Other Sources → From OData Feed
2. Enter: http://your-server:8090/odata
3. Select tables → Load

Excel uses Power Query under the hood — same OData support as Power BI.

Tableau

1. Connect → OData
2. Enter: http://your-server:8090/odata
3. Select tables and build your visualization

Grafana

1. Install the OData datasource plugin
2. Configuration → Data Sources → Add data source → OData
3. URL: http://your-server:8090/odata
4. Select entity set and properties in the query editor

Spotfire

1. Data → Add Data Connection → OData
2. Enter: http://your-server:8090/odata

Salesforce

Salesforce Lightning Connect can read OData feeds as external objects — no data copying required.

1. Setup → External Data Sources → New
2. Type: OData 4.0
3. URL: http://your-server:8090/odata

Any HTTP Client

OData v4 is REST + JSON. Use curl, httpie, Python, or any language:

curl "http://localhost:8090/odata/mart_revenue?\$top=5" | jq .

import pandas as pd, requests
df = pd.json_normalize(requests.get("http://localhost:8090/odata/mart_revenue").json()["value"])

Data Types

OData types are mapped from DuckDB automatically:

NULL values are JSON null. Empty strings are preserved as "".

Configuration

ODATA_PORT=8090 ondatrasql serve

The server binds to 127.0.0.1 by default. To expose externally, put a reverse proxy in front.

Security

• Read-only — only SELECT queries, no writes
• Column validation — queries only access columns that exist in the schema
• No SQL injection — column names validated against information_schema, string values escaped
• Localhost only — binds to 127.0.0.1 by default

Full Example

-- models/staging/orders.sql
-- @kind: merge
-- @unique_key: order_id
-- @incremental: updated_at

SELECT * FROM read_csv('data/orders/*.csv')

-- models/mart/daily_revenue.sql
-- @kind: table
-- @expose order_date

SELECT
    order_date,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM staging.orders
GROUP BY order_date

# Run pipeline
ondatrasql run

# Start OData server
ondatrasql serve

# Query from Power BI, Excel, or Grafana at:
# http://localhost:8090/odata