Semantic Queries

For Data Analysts

Semantic queries allow you to access your data using business concepts rather than raw SQL. This page covers the query syntax, structure, and execution options.

Query Structure

A semantic query consists of:

• Measures - Numeric aggregations (what you want to calculate)
• Dimensions - Categorical attributes (how you want to slice the data)
• Filters - Conditions to limit results
• Time Dimensions - Time-based grouping with granularity
• Segments - Pre-defined filter combinations
• Order - Result sorting
• Limit/Offset - Pagination

Basic Query Syntax

Minimal Query

The simplest query requires at least one measure:

{
  "measures": ["orders.total_revenue"]
}

This returns the total revenue across all orders.

Query with Dimensions

Add dimensions to group your results:

{
  "measures": ["orders.total_revenue", "orders.order_count"],
  "dimensions": ["orders.region", "orders.status"]
}

Response:

{
  "data": [
    {
      "orders.region": "NORTH",
      "orders.status": "completed",
      "orders.total_revenue": 185000.00,
      "orders.order_count": 2340
    },
    {
      "orders.region": "NORTH",
      "orders.status": "pending",
      "orders.total_revenue": 42000.00,
      "orders.order_count": 520
    }
  ],
  "execution_time_ms": 45,
  "cached": false
}

Member References

All measures, dimensions, and filters use dot notation:

{cube_name}.{member_name}

Examples

Reference	Description
orders.total_revenue	Measure from orders cube
orders.region	Dimension from orders cube
customers.segment	Dimension from customers cube
orders.order_date.month	Time dimension with granularity

Cross-Cube Queries

Query multiple cubes when they have defined joins:

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.region", "customers.segment"]
}

Olytix Core automatically applies the join between orders and customers.

Time Dimensions

Time dimensions support granularity for date-based grouping.

Granularity Options

Granularity	Description	Example Output
second	Per second	2024-01-15T14:30:45
minute	Per minute	2024-01-15T14:30:00
hour	Per hour	2024-01-15T14:00:00
day	Per day	2024-01-15
week	Per week	2024-01-15 (Monday)
month	Per month	2024-01-01
quarter	Per quarter	2024-01-01
year	Per year	2024-01-01

Using Time Dimensions

{
  "measures": ["orders.total_revenue"],
  "time_dimensions": [
    {
      "dimension": "orders.order_date",
      "granularity": "month",
      "date_range": ["2024-01-01", "2024-12-31"]
    }
  ]
}

Response:

{
  "data": [
    {
      "orders.order_date.month": "2024-01-01T00:00:00.000Z",
      "orders.total_revenue": 125000.00
    },
    {
      "orders.order_date.month": "2024-02-01T00:00:00.000Z",
      "orders.total_revenue": 142000.00
    }
  ]
}

Date Range Shortcuts

Common date ranges can be specified as strings:

{
  "measures": ["orders.total_revenue"],
  "time_dimensions": [
    {
      "dimension": "orders.order_date",
      "granularity": "day",
      "date_range": "last 7 days"
    }
  ]
}

Supported shortcuts:

• today, yesterday
• this week, last week
• this month, last month
• this quarter, last quarter
• this year, last year
• last N days, last N weeks, last N months

Ordering Results

Sort results by any measure or dimension:

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.customer_id"],
  "order": [
    {
      "member": "orders.total_revenue",
      "direction": "desc"
    }
  ],
  "limit": 10
}

Multiple Sort Columns

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.region", "orders.status"],
  "order": [
    { "member": "orders.region", "direction": "asc" },
    { "member": "orders.total_revenue", "direction": "desc" }
  ]
}

Pagination

Use limit and offset for paginated results:

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.customer_id"],
  "order": [{ "member": "orders.total_revenue", "direction": "desc" }],
  "limit": 50,
  "offset": 100
}

This returns rows 101-150.

Segments

Segments are pre-defined filter combinations defined in your cube:

# In cube definition
segments:
  - name: high_value_orders
    sql: "{CUBE}.total_amount > 1000"

Apply segments in queries:

{
  "measures": ["orders.total_revenue", "orders.order_count"],
  "dimensions": ["orders.region"],
  "segments": ["orders.high_value_orders"]
}

Query Execution Options

Synchronous Query

Standard execution that waits for results:

curl -X POST http://localhost:8000/api/v1/query \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["orders.total_revenue"],
    "dimensions": ["orders.region"]
  }'

Preview SQL

Get the generated SQL without executing:

curl -X POST http://localhost:8000/api/v1/query/sql \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["orders.total_revenue"],
    "dimensions": ["orders.region"]
  }'

Response:

{
  "sql": "SELECT\n  region AS \"orders.region\",\n  SUM(total_amount) AS \"orders.total_revenue\"\nFROM (\n  SELECT * FROM fct_orders\n) AS orders\nGROUP BY region",
  "cubes": ["orders"],
  "uses_pre_aggregation": false
}

Query Explanation

Understand the query execution plan:

curl -X POST http://localhost:8000/api/v1/query/explain \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["orders.total_revenue"],
    "dimensions": ["orders.region", "orders.order_date.month"]
  }'

Response:

{
  "cubes": ["orders"],
  "uses_pre_aggregation": true,
  "pre_aggregation": "orders_by_region_monthly",
  "steps": [
    { "type": "pre_aggregation", "description": "Use pre-aggregation: orders_by_region_monthly" },
    { "type": "base", "description": "Build base query from cube(s)" },
    { "type": "aggregate", "description": "Aggregate measures" }
  ],
  "sql": "SELECT ...",
  "estimated_rows": 48
}

Cost Estimation

Estimate query cost before execution:

curl -X POST http://localhost:8000/api/v1/query/estimate \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["orders.total_revenue"],
    "dimensions": ["orders.customer_id"]
  }'

Response:

{
  "estimated_rows": 50000,
  "estimated_bytes": 5000000,
  "estimated_cost": 50.0,
  "estimated_time_ms": 500,
  "uses_pre_aggregation": false,
  "execution_location": "warehouse"
}

Async Queries

For large datasets, use async queries to avoid timeouts.

Submit Async Query

curl -X POST http://localhost:8000/api/v1/query/async \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["orders.total_revenue"],
    "dimensions": ["orders.customer_id"],
    "limit": 1000000
  }'

Response:

{
  "query_id": "q_abc123xyz",
  "status": "pending",
  "created_at": "2024-01-20T10:30:00Z"
}

Check Query Status

curl http://localhost:8000/api/v1/query/q_abc123xyz

Response (In Progress):

{
  "query_id": "q_abc123xyz",
  "status": "running"
}

Response (Complete):

{
  "query_id": "q_abc123xyz",
  "status": "completed",
  "result": {
    "data": [...],
    "execution_time_ms": 4500
  }
}

Cancel Async Query

curl -X POST http://localhost:8000/api/v1/query/q_abc123xyz/cancel

Streaming Queries

For large result sets, stream results as newline-delimited JSON:

curl -X POST http://localhost:8000/api/v1/query/stream \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["orders.total_revenue"],
    "dimensions": ["orders.customer_id"]
  }'

Response (NDJSON):

{"type":"meta","sql":"SELECT ..."}
{"type":"data","rows":[{"orders.customer_id":"C001","orders.total_revenue":5000},...]}
{"type":"data","rows":[{"orders.customer_id":"C101","orders.total_revenue":3200},...]}
{"type":"end"}

Advanced Options

Ungrouped Results

Return raw rows without aggregation:

{
  "measures": ["orders.total_amount"],
  "dimensions": ["orders.order_id", "orders.customer_id"],
  "ungrouped": true,
  "limit": 100
}

Include Totals

Add a grand total row:

{
  "measures": ["orders.total_revenue"],
  "dimensions": ["orders.region"],
  "total": true
}

Timezone

Specify timezone for time dimension calculations:

{
  "measures": ["orders.total_revenue"],
  "time_dimensions": [
    {
      "dimension": "orders.order_date",
      "granularity": "day"
    }
  ],
  "timezone": "America/New_York"
}

Metadata Endpoints

List Available Measures

curl http://localhost:8000/api/v1/query/meta/metrics

List Available Dimensions

curl http://localhost:8000/api/v1/query/meta/dimensions

List Available Cubes

curl http://localhost:8000/api/v1/query/meta/cubes

Get Cube Details

curl http://localhost:8000/api/v1/query/meta/cubes/orders

Complete Query Example

Here is a comprehensive query using multiple features:

{
  "measures": [
    "orders.total_revenue",
    "orders.order_count",
    "orders.avg_order_value"
  ],
  "dimensions": [
    "orders.region",
    "customers.segment"
  ],
  "time_dimensions": [
    {
      "dimension": "orders.order_date",
      "granularity": "month",
      "date_range": ["2024-01-01", "2024-06-30"]
    }
  ],
  "filters": [
    {
      "member": "orders.status",
      "operator": "equals",
      "values": ["completed"]
    },
    {
      "member": "orders.total_amount",
      "operator": "gte",
      "values": [100]
    }
  ],
  "segments": ["orders.domestic_orders"],
  "order": [
    { "member": "orders.order_date", "direction": "asc" },
    { "member": "orders.total_revenue", "direction": "desc" }
  ],
  "limit": 100,
  "timezone": "UTC"
}

Next Steps

• Learn about filters and operators
• Optimize query performance
• Explore time intelligence