Understanding Cubes in a Semantic Layer: Beginner Friendly Guide with Food Delivery Example

  

Modern analytics systems are not built directly on raw SQL tables anymore. Instead, teams define a semantic layer that gives business meaning to raw data.

 

In Cube, the core building block of this semantic layer is called a Cube.

 

A cube represents a business entity such as:

 

·      Orders

·      Customers

·      Restaurants

·      Payments

·      Drivers

 

Each cube maps to a table (or SQL query) in your database and defines:

 

·      metrics

·      dimensions

·      joins

·      business rules

·      aggregations

 

Instead of every dashboard or AI application writing SQL manually, they query these reusable business definitions.

 

1. Why Cubes Matter?

Imagine multiple teams writing analytics queries directly on raw tables. One dashboard calculates revenue using "subtotal_amount + tax_amount", another uses "final_amount" and other excludes cancelled orders.

 

Now every report shows different numbers. A semantic layer solves this problem by centralizing business logic in one place.

 

With cubes:

·      revenue is defined once

·      delivery metrics are standardized

·      joins become reusable

·      AI tools can safely generate analytics

·      dashboards become consistent

 

2. Our Example Dataset

For this tutorial, we’ll use the orders table from our food delivery platform schema. The table stores food delivery transactions:

| Column                  | Meaning                     |
| ----------------------- | --------------------------- |
| order_id                | Unique order                |
| customer_id             | Customer who placed order   |
| restaurant_id           | Restaurant fulfilling order |
| order_status            | Delivery state              |
| final_amount            | Total order value           |
| ordered_at              | Order timestamp             |
| actual_delivery_minutes | Delivery duration           |

3. Creating Your First Cube

In Cube, a cube usually maps to a database table using sql_table. Here is a beginner friendly cube definition for the orders table.

 

cubes:
  - name: orders
    sql_table: orders

   

This tells Cube to create a semantic model named orders using the raw table orders.

 

At this point:

·      Cube knows the source table

·      but it still doesn’t know what metrics exist, which columns are dimensions how to aggregate data etc.,

 

That is where dimensions and measures come in.

 

3.1 Adding Dimensions

Dimensions describe the properties of a data record. Think of dimensions as:

 

·      labels

·      categories

·      attributes

·      fields you group or filter by

 

Examples:

·      order status

·      city

·      customer

·      restaurant

·      date

 

cubes:
  - name: orders
  
    sql_table: orders

    dimensions:
      - name: order_id
        sql: order_id
        type: number
        primary_key: true

      - name: order_status
        sql: order_status
        type: string

      - name: ordered_at
        sql: ordered_at
        type: time

a. dimensions

This section contains all dimension definitions for the cube. Think of it as, these are the fields that describe the data. For the orders table, dimensions might include:

 

·      order_id

·      order_status

·      ordered_at

·      customer_id

·      restaurant_id

 

Dimensions are typically:

 

·      identifiers

·      categories

·      timestamps

·      descriptive attributes

 

b. name: order_id

This defines the semantic name of the dimension inside Cube. This is the name users and tools will query. In the example:

·      orders → cube name

·      order_id → dimension name

 

The name does NOT have to match the database column name.

 

For example:

 

- name: id
  sql: order_id

   

This would expose the field as orders.id, even though the database column is order_id.

 

The semantic name allows:

 

·      cleaner APIs

·      business-friendly naming

·      abstraction from physical schemas

·      easier refactoring later

 

c. sql: order_id

This tells Cube to use the order_id column from the database table. This is the actual SQL expression used in generated queries.

 

Cube internally generates SQL like:

 

SELECT order_id FROM orders

   

SQL Property Can Be More Than a Column

 

It can also contain:

 

·      SQL expressions

·      CASE statements

·      calculations

·      concatenations

 

Example:

 

sql: CONCAT('ORD-', order_id)

sql: CASE
       WHEN final_amount > 1000 THEN 'High Value'
       ELSE 'Regular'
     END

   

d. type: number

This tells Cube the data type of the dimension. Cube needs this information to:

 

·      generate correct SQL

·      validate queries

·      enable filtering

·      optimize aggregations

·      power BI integrations

 

Following table summarizes common dimension types.

 

Type	Meaning
string	Text values
number	Numeric values
boolean	True/False
time	Date/Timestamp
geo	Geographic data

 

What Happens if Type is Wrong?

Suppose you define "type: string", instead of number. Following potential problems will occur:

 

·      incorrect filtering behavior

·      inefficient SQL generation

·      broken BI expectations

·      sorting issues

 

Correct typing is important in semantic modeling.

 

e. primary_key: true

This tells Cube that, this dimension uniquely identifies each row in this cube.

 

For the orders table:

 

·      every order has a unique order_id

·      no duplicates exist

·      each row represents exactly one order

 

Why Primary Keys Are Extremely Important?

Primary keys help Cube:

 

·      understand row uniqueness

·      generate correct joins

·      prevent duplicate aggregation

·      optimize queries

·      build pre-aggregations safely

 

In summary, this configuration means create a numeric dimension called order_id using the database column order_id, and treat it as the unique identifier for each order

 

3.2 Adding Measures

Measures define aggregated business metrics.

 

Measures answer questions like:

 

·      How many orders?

·      Total revenue?

·      Average delivery time?

·      Cancellation rate?

 

Example Measure

 

cubes:
  - name: orders

    sql_table: orders

    measures:
      - name: total_orders
        type: count

      - name: total_revenue
        sql: final_amount
        type: sum

      - name: average_delivery_time
        sql: actual_delivery_minutes
        type: avg

   

Understanding Each Measure

a. Count Measure

 

- name: total_orders
  type: count

   

This generates SQL like COUNT(*), Useful for:

 

·      order counts

·      daily activity

·      operational dashboards

 

b. Sum Measure

 

- name: total_revenue
  sql: final_amount
  type: sum

   

This calculates SUM(final_amount), Useful for:

 

·      GMV

·      revenue analytics

·      financial dashboards

 

c. Average Measure

 

- name: average_delivery_time
  sql: actual_delivery_minutes
  type: avg

   

This calculates AVG(actual_delivery_minutes), Useful for:

 

·      SLA tracking

·      logistics analytics

·      operational monitoring

 

Here’s the complete cube:

 

cubes:
  - name: orders

    sql_table: orders

    dimensions:
      - name: order_id
        sql: order_id
        type: number
        primary_key: true

      - name: order_status
        sql: order_status
        type: string

      - name: ordered_at
        sql: ordered_at
        type: time

    measures:
      - name: total_orders
        type: count

      - name: total_revenue
        sql: final_amount
        type: sum

      - name: average_delivery_time
        sql: actual_delivery_minutes
        type: avg

   

3.3 What Happens Behind the Scenes?

When BI tools or AI agents query Cube:

 

·      they do not directly query PostgreSQL

·      they query the semantic layer

 

Cube then:

 

·      understands the business model

·      generates optimized SQL

·      applies business rules

·      handles joins

·      manages caching and pre-aggregations

 

This creates:

 

·      consistency

·      governance

·      reusable analytics

·      AI-friendly data access

 

4. How do Applications actally use Cube?

The next important question is, how do applications actually query this cube? and more importantly "How does Cube generate SQL automatically?""

 

This is where the semantic layer becomes extremely powerful.

 

Applications do NOT send raw SQL to the database. Instead, they send a query specification to Cube.

 

Cube:

·      understands the semantic model

·      validates the query

·      generates optimized SQL

·      executes the SQL

·      returns analytics results

 

This abstraction is the heart of semantic modeling.

 

4.1 What is a Query Specification?

A query specification is a structured request describing:

 

·      which measures to calculate

·      which dimensions to group by

·      filters

·      time ranges

·      sorting

·      limits

 

Instead of writing SQL manually, clients send JSON or YAML like query objects.

 

Example 1: Total revenue grouped by order status

 

Query Specification

 

measures:
  - orders.total_revenue

dimensions:
  - orders.order_status

   

How Cube understand this?

Cube maps orders.total_revenue to

 

sql: final_amount
type: sum

and orders.order_status to

 

sql: order_status

   

Generated SQL looks like below.

 

SELECT
      "orders".order_status "orders__order_status", sum("orders".final_amount) "orders__total_revenue"
    FROM
      orders AS "orders"  GROUP BY 1 ORDER BY 2 DESC

   

Example 2: Time-Series Query Example

Suppose we want Monthly revenue trend

 

measures:
  - orders.total_revenue

timeDimensions:
  - dimension: orders.ordered_at
    granularity: month

   

Cube sees:

·      ordered_at is a time dimension

·      grouping granularity = month

·      measure = revenue sum

 

Generated SQL

SELECT
      date_trunc('month', ("orders".ordered_at::timestamptz AT TIME ZONE 'UTC')) "orders__ordered_at_month", sum("orders".final_amount) "orders__total_revenue"
    FROM
      orders AS "orders"  GROUP BY 1 ORDER BY 1 ASC

   

Cube automatically supports:

·      year

·      quarter

·      month

·      week

·      day

·      hour

·      minute

 

without additional SQL.

 

Example 3: Filtering Example

Suppose we only want delivered orders.

measures:
  - orders.total_revenue

filters:
  - member: orders.order_status
    operator: equals
    values:
      - DELIVERED

   

Generated SQL

 

SELECT
      sum("orders".final_amount) "orders__total_revenue"
    FROM
      orders AS "orders"  WHERE ("orders".order_status = 'DELIVERED')

   

Example 4: Combining Dimensions, Time, and Filters

 

Suppose we want monthly delivered-order revenue by status

measures:
  - orders.total_revenue

dimensions:
  - orders.order_status

timeDimensions:
  - dimension: orders.ordered_at
    granularity: month

filters:
  - member: orders.order_status
    operator: equals
    values:
      - DELIVERED

Generated SQL

 

SELECT
      "orders".order_status "orders__order_status", date_trunc('month', ("orders".ordered_at::timestamptz AT TIME ZONE 'UTC')) "orders__ordered_at_month", sum("orders".final_amount) "orders__total_revenue"
    FROM
      orders AS "orders"  WHERE ("orders".order_status = 'DELIVERED') GROUP BY 1, 2 ORDER BY 2 ASC

   

Example 5: Sorting Example

Suppose we want top statuses by revenue

 

measures:
  - orders.total_revenue

dimensions:
  - orders.order_status

order:
  orders.total_revenue: desc

   

Generate SQL like below.

 

SELECT
      "orders".order_status "orders__order_status", sum("orders".final_amount) "orders__total_revenue"
    FROM
      orders AS "orders"  GROUP BY 1 ORDER BY 2 DESC

   

Example 7: Limiting Results

Suppose we want only the top 5 rows.

 

dimensions:
  - orders.order_id
  - orders.ordered_at

limit: 5

   

Generated SQL

 

SELECT
      "orders".order_id "orders__order_id", "orders".ordered_at "orders__ordered_at"
    FROM
      orders AS "orders"  GROUP BY 1, 2 ORDER BY 1 ASC LIMIT 5

   

Example 8: Here is a more realistic analytics request.

 

measures:
  - orders.total_orders
  - orders.total_revenue
  - orders.average_delivery_time

dimensions:
  - orders.order_status

timeDimensions:
  - dimension: orders.ordered_at
    granularity: month

filters:
  - member: orders.order_status
    operator: notEquals
    values:
      - CANCELLED

order:
  orders.total_revenue: desc

limit: 10

   

Generated SQL Query

 

SELECT
      "orders".order_status "orders__order_status", date_trunc('month', ("orders".ordered_at::timestamptz AT TIME ZONE 'UTC')) "orders__ordered_at_month", count("orders".order_id) "orders__total_orders", sum("orders".final_amount) "orders__total_revenue", avg("orders".actual_delivery_minutes) "orders__average_delivery_time"
    FROM
      orders AS "orders"  WHERE ("orders".order_status <> 'CANCELLED' OR "orders".order_status IS NULL) GROUP BY 1, 2 ORDER BY 2 ASC LIMIT 10

   

5. Why Semantic Query Specs Are Powerful?

Applications no longer need to:

 

·      write SQL

·      understand joins

·      manage aggregations

·      handle time grouping

·      optimize queries

 

The semantic layer handles all of that automatically.

 

Following table summarizes the benefits of Query Specifications.

 

Feature	Benefit
Measures	Standardized business metrics
Dimensions	Reusable grouping attributes
Time Dimensions	Automatic time-series analytics
Filters	Safe query constraints
Ordering	Declarative sorting
Limits	Pagination and optimization

 

Why This is Important for AI Analytics?

AI agents can safely generate query specifications.

 

measures:
  - orders.total_revenue

dimensions:
  - orders.order_status

   

instead of attempting fragile raw SQL generation.

 

This makes:

·      AI analytics safer

·      validation easier

·      governance stronger

·      query generation more reliable

 

In summary, the semantic model defines "what the business data means". The query specification defines "what analytics we want". Cube transforms these semantic queries into optimized SQL automatically.

 

That separation is what makes semantic layers so powerful.

 

 

 

Previous                                                    Next                                                    Home