Semantic Layer Overview

The semantic layer is the heart of Drizzle Cube. It transforms your raw database tables into business-friendly concepts that anyone can understand and query safely.

What is a Semantic Layer?

A semantic layer is a business representation of your data that:

• Abstracts complexity - Hide database schema details behind business terms
• Ensures consistency - Single source of truth for metrics and definitions
• Enables self-service - Non-technical users can explore data confidently
• Provides structure - Organize data into logical business entities

Architecture Overview

Applications

• Dashboards
• Reports
• APIs

→

Semantic Layer

• Cubes
• Dimensions
• Measures

→

Database

• Tables
• Views
• Indexes

Core Concepts

The semantic layer is built on three fundamental concepts:

1. Cubes

Cubes are business entities that represent the main subjects you want to analyze - like Sales, Customers, or Products. Each cube defines how to query a specific dataset.

import { defineCube } from 'drizzle-cube/server';
import { eq } from 'drizzle-orm';
import * as schema from './schema';

export const salesCube = defineCube('Sales', {
  title: 'Sales Transactions',
  sql: (ctx) => ({
    from: schema.sales,
    leftJoin: [
      { table: schema.products, on: eq(schema.sales.productId, schema.products.id) },
      { table: schema.customers, on: eq(schema.sales.customerId, schema.customers.id) }
    ]
  }),

  dimensions: {
    // What you can filter and group by
  },

  measures: {
    // What you want to calculate and analyze
  }
});

2. Dimensions

Dimensions are the descriptive attributes of your data - the “what”, “when”, “where”, and “who” that you use to slice and filter your analysis.

dimensions: {
  customerName: {
    name: 'customerName',
    title: 'Customer Name',
    type: 'string',
    sql: schema.customers.name
  },
  productCategory: {
    name: 'productCategory',
    title: 'Product Category',
    type: 'string',
    sql: schema.products.category
  },
  orderDate: {
    name: 'orderDate',
    title: 'Order Date',
    type: 'time',
    sql: schema.sales.orderDate
  }
}

3. Measures

Measures are the numeric calculations you want to perform - counts, sums, averages, and other aggregations that provide business insights.

measures: {
  totalSales: {
    name: 'totalSales',
    title: 'Total Sales',
    type: 'sum',
    sql: schema.sales.amount
  },
  orderCount: {
    name: 'orderCount',
    title: 'Number of Orders',
    type: 'count',
    sql: schema.sales.id
  },
  averageOrderValue: {
    name: 'averageOrderValue',
    title: 'Average Order Value',
    type: 'avg',
    sql: schema.sales.amount
  }
}

Complete Example

Here’s a complete cube definition that brings together all three concepts:

import { defineCube } from 'drizzle-cube/server';
import { eq } from 'drizzle-orm';
import * as schema from './schema';

export const salesCube = defineCube('Sales', {
  title: 'Sales Transactions',
  description: 'All sales data with customer and product information',

  sql: (ctx) => ({
    from: schema.sales,
    leftJoin: [
      { table: schema.products, on: eq(schema.sales.productId, schema.products.id) },
      { table: schema.customers, on: eq(schema.sales.customerId, schema.customers.id) }
    ]
  }),

  dimensions: {
    customerName: {
      name: 'customerName',
      title: 'Customer Name',
      type: 'string',
      sql: schema.customers.name
    },
    productName: {
      name: 'productName',
      title: 'Product Name',
      type: 'string',
      sql: schema.products.name
    },
    productCategory: {
      name: 'productCategory',
      title: 'Product Category',
      type: 'string',
      sql: schema.products.category
    },
    orderDate: {
      name: 'orderDate',
      title: 'Order Date',
      type: 'time',
      sql: schema.sales.orderDate
    }
  },

  measures: {
    totalSales: {
      name: 'totalSales',
      title: 'Total Sales',
      type: 'sum',
      sql: schema.sales.amount
    },
    orderCount: {
      name: 'orderCount',
      title: 'Number of Orders',
      type: 'count',
      sql: schema.sales.id
    },
    averageOrderValue: {
      name: 'averageOrderValue',
      title: 'Average Order Value',
      type: 'avg',
      sql: schema.sales.amount
    },
    totalQuantity: {
      name: 'totalQuantity',
      title: 'Total Quantity Sold',
      type: 'sum',
      sql: schema.sales.quantity
    }
  }
});

How Queries Work

Once you’ve defined your cube, you can query it using a simple JSON structure:

{
  "measures": ["Sales.totalSales", "Sales.orderCount"],
  "dimensions": ["Sales.productCategory", "Sales.customerName"],
  "timeDimensions": [{
    "dimension": "Sales.orderDate",
    "granularity": "month"
  }],
  "filters": [{
    "member": "Sales.productCategory",
    "operator": "equals",
    "values": ["Electronics"]
  }]
}

This query will:

1. Sum up total sales and count orders (measures)
2. Group by product category and customer name (dimensions)
3. Group by month using the order date (time dimension)
4. Filter to only Electronics products (filter)

Common Patterns

Basic Aggregations

measures: {
  // Count records
  recordCount: { type: 'count', sql: schema.table.id },

  // Sum amounts
  totalRevenue: { type: 'sum', sql: schema.table.amount },

  // Calculate averages
  avgOrderValue: { type: 'avg', sql: schema.table.orderValue },

  // Find min/max values
  minPrice: { type: 'min', sql: schema.table.price },
  maxPrice: { type: 'max', sql: schema.table.price }
}

Time Dimensions

dimensions: {
  createdDate: {
    type: 'time',
    sql: schema.table.createdAt
  },
  updatedDate: {
    type: 'time',
    sql: schema.table.updatedAt
  }
}

Categorical Dimensions

dimensions: {
  status: {
    type: 'string',
    sql: schema.table.status
  },
  category: {
    type: 'string',
    sql: schema.table.category
  },
  isActive: {
    type: 'boolean',
    sql: schema.table.isActive
  }
}

Next Steps

• Cubes - Deep dive into cube definitions
• Dimensions - Advanced dimension patterns
• Measures - Custom calculations and aggregations
• Joins - Multi-cube query patterns
• Security - Advanced security patterns