Cubes Schema

Cubes are the core building blocks of the Olytix Core semantic layer. They define the business logic for measures, dimensions, and relationships that power your analytics queries. This reference documents all fields available when defining cubes.

File Location

Cube definitions are stored in YAML files within the cubes/ directory:

my-project/
└── cubes/
    ├── orders.yml
    ├── customers.yml
    └── products/
        ├── inventory.yml
        └── catalog.yml

Top-Level Structure

cubes:
  - name: cube_name
    # ... cube configuration

Cube Definition

Field	Type	Required	Default	Description
name	string	Yes	-	Unique identifier for the cube
sql	string	Conditional	null	SQL query defining the cube's base data
sql_table	string	Conditional	null	Direct table reference (alternative to sql)
model	string	Conditional	null	Reference to a Olytix Core model (alternative to sql)
extends	string	No	null	Name of another cube to extend
title	string	No	null	Display title for the cube
description	string	No	null	Human-readable description
public	boolean	No	true	Whether the cube is exposed via API
refresh_key	object	No	null	Cache refresh configuration
data_source	string	No	null	Data source override
measures	list	No	[]	List of measure definitions
dimensions	list	No	[]	List of dimension definitions
joins	list	No	[]	List of join definitions
segments	list	No	[]	List of segment definitions
pre_aggregations	list	No	[]	List of pre-aggregation definitions
meta	object	No	{}	Custom metadata key-value pairs

Data Source Options

You must specify one of the following to define the cube's data source:

• sql: A SQL query with optional Jinja templating
• sql_table: Direct reference to a database table
• model: Reference to a Olytix Core model using ref()

# Option 1: SQL query
cubes:
  - name: orders
    sql: "SELECT * FROM {{ ref('fct_orders') }}"

# Option 2: Direct table reference
cubes:
  - name: orders
    sql_table: "analytics.marts.fct_orders"

# Option 3: Model reference
cubes:
  - name: orders
    model: fct_orders

Measure Definition

Measures are quantitative values that can be aggregated.

Field	Type	Required	Default	Description
name	string	Yes	-	Unique identifier within the cube
type	string	No	count	Aggregation type
sql	string	Conditional	null	SQL expression (required for most types)
title	string	No	null	Display title
description	string	No	null	Human-readable description
format	string	No	null	Output format: currency, percent, number
filters	list	No	[]	Filters applied to this measure
rolling_window	object	No	null	Rolling window configuration
drill_members	list	No	[]	Dimensions to show in drill-down
shown	boolean	No	true	Whether to expose via API
meta	object	No	{}	Custom metadata

Measure Types

Type	Description	Requires sql
count	Count of rows	No
count_distinct	Count of unique values	Yes
count_distinct_approx	Approximate count distinct	Yes
sum	Sum of values	Yes
avg	Average of values	Yes
min	Minimum value	Yes
max	Maximum value	Yes
number	Custom SQL expression	Yes
running_total	Cumulative sum	Yes
boolean	Boolean aggregation (ANY/ALL)	Yes

Measure Examples

measures:
  - name: count
    type: count
    description: Total number of orders

  - name: total_revenue
    type: sum
    sql: total_amount
    format: currency
    description: Sum of all order amounts
    drill_members:
      - order_id
      - order_date
      - customer_name

  - name: average_order_value
    type: avg
    sql: total_amount
    format: currency
    description: Average order amount

  - name: unique_customers
    type: count_distinct
    sql: customer_id
    description: Number of unique customers

  - name: completed_orders
    type: count
    filters:
      - sql: "{CUBE}.status = 'completed'"
    description: Orders with completed status

  - name: revenue_margin
    type: number
    sql: "SUM(revenue - cost) / NULLIF(SUM(revenue), 0)"
    format: percent
    description: Revenue margin percentage

Rolling Window Configuration

measures:
  - name: rolling_30d_revenue
    type: sum
    sql: total_amount
    rolling_window:
      trailing: 30
      leading: 0
      offset: start

Dimension Definition

Dimensions are attributes used for grouping and filtering.

Field	Type	Required	Default	Description
name	string	Yes	-	Unique identifier within the cube
sql	string	No	null	SQL expression (defaults to column name)
type	string	No	string	Dimension type
title	string	No	null	Display title
description	string	No	null	Human-readable description
primary_key	boolean	No	false	Whether this is the primary key
shown	boolean	No	true	Whether to expose via API
case	string	No	null	Case transformation: upper, lower
granularities	list	No	[]	Time granularities (for time dimensions)
meta	object	No	{}	Custom metadata

Dimension Types

Type	Description
string	Text values (default)
number	Numeric values
boolean	True/false values
time	Date/time values
geo	Geographic values

Time Dimension Granularities

Granularity	Description
second	Second-level precision
minute	Minute-level precision
hour	Hour-level precision
day	Day-level precision
week	Week-level precision
month	Month-level precision
quarter	Quarter-level precision
year	Year-level precision

Dimension Examples

dimensions:
  - name: order_id
    sql: order_id
    type: number
    primary_key: true
    description: Unique order identifier

  - name: order_date
    sql: order_date
    type: time
    description: Date when the order was placed
    granularities:
      - day
      - week
      - month
      - quarter
      - year

  - name: status
    sql: status
    type: string
    description: Current order status

  - name: customer_name
    sql: customer_name
    type: string
    case: upper
    description: Customer name (uppercase)

  - name: is_premium
    sql: "CASE WHEN total_amount > 1000 THEN true ELSE false END"
    type: boolean
    description: Whether order qualifies as premium

  - name: region
    sql: region_code
    type: geo
    description: Geographic region

Join Definition

Joins define relationships between cubes.

Field	Type	Required	Default	Description
name	string	Yes	-	Name of the target cube
sql	string	Yes	-	SQL ON clause
relationship	string	No	belongs_to	Relationship type
description	string	No	null	Human-readable description

Relationship Types

Type	Description	SQL Equivalent
belongs_to	Many-to-one relationship	LEFT JOIN
has_many	One-to-many relationship	LEFT JOIN
has_one	One-to-one relationship	LEFT JOIN
left	Standard left join	LEFT JOIN
inner	Inner join	INNER JOIN
full	Full outer join	FULL OUTER JOIN
right	Right join	RIGHT JOIN

Join Examples

joins:
  - name: customers
    sql: "{orders}.customer_id = {customers}.customer_id"
    relationship: belongs_to
    description: Customer who placed the order

  - name: order_items
    sql: "{orders}.order_id = {order_items}.order_id"
    relationship: has_many
    description: Line items in the order

Segment Definition

Segments are predefined filters for common use cases.

Field	Type	Required	Default	Description
name	string	Yes	-	Unique identifier
sql	string	Yes	-	SQL WHERE clause condition
title	string	No	null	Display title
description	string	No	null	Human-readable description

Segment Examples

segments:
  - name: completed
    sql: "{CUBE}.status = 'completed'"
    title: Completed Orders
    description: Orders that have been completed

  - name: high_value
    sql: "{CUBE}.total_amount > 1000"
    title: High Value Orders
    description: Orders over $1000

  - name: recent
    sql: "{CUBE}.order_date >= CURRENT_DATE - INTERVAL '30 days'"
    title: Recent Orders
    description: Orders from the last 30 days

Pre-Aggregation Definition

Pre-aggregations are materialized aggregations for performance optimization.

Field	Type	Required	Default	Description
name	string	Yes	-	Unique identifier
measures	list	No	[]	Measures to include
dimensions	list	No	[]	Dimensions to include
time_dimension	string	No	null	Time dimension for partitioning
granularity	string	No	null	Time granularity
partition_granularity	string	No	null	Partition granularity
segments	list	No	[]	Segments to apply
refresh	object	No	{}	Refresh configuration
indexes	list	No	[]	Index definitions
build_range_start	string	No	null	Start of build range
build_range_end	string	No	null	End of build range

Refresh Configuration

Field	Type	Default	Description
every	string	1 hour	Refresh interval
incremental	boolean	false	Incremental refresh
update_window	string	null	Window for updates
timezone	string	UTC	Timezone for scheduling

Pre-Aggregation Examples

pre_aggregations:
  - name: daily_orders
    measures:
      - count
      - total_revenue
    dimensions:
      - status
    time_dimension: order_date
    granularity: day
    refresh:
      every: "1 hour"
      timezone: "America/New_York"

  - name: monthly_by_region
    measures:
      - count
      - total_revenue
      - average_order_value
    dimensions:
      - region
      - status
    time_dimension: order_date
    granularity: month
    partition_granularity: month
    refresh:
      every: "6 hours"
      incremental: true
      update_window: "7 days"
    indexes:
      - columns: [region, status]

Complete Example

cubes:
  - name: orders
    sql: "SELECT * FROM {{ ref('fct_orders') }}"
    title: Orders
    description: E-commerce order transactions
    public: true
    meta:
      owner: analytics-team
      domain: sales

    measures:
      - name: count
        type: count
        description: Total number of orders

      - name: total_revenue
        type: sum
        sql: total_amount
        format: currency
        description: Sum of all order amounts
        drill_members:
          - order_id
          - order_date
          - customer_name

      - name: average_order_value
        type: avg
        sql: total_amount
        format: currency
        description: Average order amount

      - name: unique_customers
        type: count_distinct
        sql: customer_id
        description: Number of unique customers

    dimensions:
      - name: order_id
        sql: order_id
        type: number
        primary_key: true
        description: Unique order identifier

      - name: order_date
        sql: order_date
        type: time
        description: Order creation date
        granularities:
          - day
          - week
          - month
          - quarter
          - year

      - name: status
        sql: status
        type: string
        description: Order status

      - name: region
        sql: region
        type: string
        description: Customer region

    joins:
      - name: customers
        sql: "{orders}.customer_id = {customers}.customer_id"
        relationship: belongs_to
        description: Customer who placed the order

    segments:
      - name: completed
        sql: "{CUBE}.status = 'completed'"
        description: Completed orders only

      - name: high_value
        sql: "{CUBE}.total_amount > 1000"
        description: Orders over $1000

    pre_aggregations:
      - name: daily_stats
        measures:
          - count
          - total_revenue
        dimensions:
          - status
          - region
        time_dimension: order_date
        granularity: day
        refresh:
          every: "1 hour"

Best Practices

1. Define primary keys: Always mark a dimension as primary_key: true for proper join handling.

2. Use descriptive names: Choose clear, business-friendly names for measures and dimensions.

3. Document thoroughly: Provide descriptions for all cube members.

4. Optimize with pre-aggregations: Create pre-aggregations for frequently queried combinations.

5. Use segments for common filters: Define segments for filters that are used repeatedly.

6. Set appropriate granularities: Only include granularities that users actually need.

7. Use drill_members: Define drill-down paths for measures to enable data exploration.