# 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.

Source: https://tini.so/docs/data-models

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 → Data Models**.

## Create a data model

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

1. **Start from a table**

   In **Explore**, open the table and click **Create data model**. Or, on the Data Models page, choose **New → New data model from a table**.

2. **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.

3. **Create it**

   Click **Create**. Orcabase checks every expression against your database as it saves.

For data that isn’t one table, choose **New → 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.

```text
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 |

> **Important: 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](https://tini.so/docs/metrics) on your model.

---

Previous: [How metrics work](https://tini.so/docs/semantic-layer.md) · Next: [Metrics](https://tini.so/docs/metrics.md)

All docs: https://tini.so/llms.txt