Data models

Describe a table once: the dimensions to group by, the measures to add up, and how it joins to others. Scaffold a model from any table.

View as Markdown Ask ChatGPT Ask Claude

A data model describes one table in business terms, once: what people can group and filter by, what they can add up, and how it connects to other tables. Metrics are built on top of data models. You’ll find them under Semantic layer then Data Models.

Create a data model

The fastest way is to start from a table, and let Orcabase draft the model for you:

Start from a table
In Explore, open the table and click Create data model. Or, on the Data Models page, choose New then New data model from a table.
Review the draft
Orcabase reads the table’s columns and drafts a starting point:
- date and time columns become time dimensions, and the first becomes the default time dimension;
- text, true/false and low-variety columns become dimensions;
- number columns that aren’t IDs become measures that add up (sum);
- a count of rows is always added;
- columns like user_id that match another table’s name are suggested as joins.
Nothing is saved yet. Remove what you don’t need, rename things, and add descriptions.
Create it
Click Create. Orcabase checks every expression against your database as it saves.

For data that isn’t one table, choose New then New data model from SQL and write a SELECT whose result becomes the model, for example sessions built from raw events.

Overview

Field	What it’s for
Name	How metrics and queries refer to the model, like `orders`. Unique in your organization.
Label and description	How it’s shown to people, and what it means.
Source	A table (schema and table name) or SQL.
Primary key	The column or columns that identify a row, like `id`.
Default time dimension	The date that metrics on this model trend over, unless you pick another.

Dimensions

A dimension is something to group or filter by: order date, plan, country. Each has a Type (time, text, number or true/false) and an Expression: the column, or any SQL expression over the table’s columns. Time dimensions list the Time grains they allow: day, week, month, quarter, year. Tick Hidden to keep a dimension out of pickers.

Dimension expressions

country      upper(shipping_country)
is_repeat    order_number > 1
created_at   created_at

Measures

A measure is something to add up or count. Pick an Aggregation (count, count distinct, sum, average, min or max) and an Expression to aggregate. An optional Filter limits which rows count, which is how “paid revenue” differs from “all order value”:

Name	Aggregation	Expression	Filter
`order_count`	count
`paid_amount`	sum	`amount`	`status = 'paid'`
`buyers`	count distinct	`customer_id`

The Used by column shows which metrics rely on each measure. A measure that a metric uses can’t be deleted.

Joins

A join connects this model to another on the same data source: pick the other Data model, the Relationship, and the Join condition, like orders.customer_id = customers.id. Then metrics on orders can be split by the customer’s plan or country.

Relationship	Means	Example
many → one	Many rows here match one row there	Many orders, one customer
one → one	Each row matches at most one row there	A customer and their settings
one → many	One row here matches many rows there	One order, many order items

Get the relationship right

Orcabase uses it to avoid double-counting: it won’t split a measure across a one → many join, because each row would be counted once per match. Declaring a join as many → one when it’s really one → many can produce wrong numbers.

Checks and the Broken badge

Every save checks the model against your database, without reading any rows: each column, function and type in every expression has to resolve. Problems show next to the dimension or measure that caused them. If the table changes later (say a column is renamed), the model shows a Broken badge, with the details one click away, and metric queries on it fail until you fix it. If the database can’t be reached, Orcabase still saves and tells you it couldn’t check.

History and lineage

History keeps every saved version with who changed it and when. Restore this version brings an old one back.
Lineage shows the metrics built on this model.
Renaming a model or a field updates the metrics and saved metric queries that use it, so nothing breaks.
A model that metrics use, or that other models join to, can’t be deleted; the error lists what’s in the way.

Current limits

A model lives on one data source, and joins only connect models on the same data source.
Time grains are counted in UTC: a “day” runs from midnight to midnight UTC.

Next: build metrics on your model.

Something unclear or missing? Troubleshooting covers the common errors, or message us.

Data models

Start from a table

Review the draft

Create it