Semantic Model Setup

Building a robust analytics layer starts with a clear, reusable semantic model. This guide shows how to turn raw, replicated tables into business‑ready entities, link them together, and expose trusted metrics.

Key Concepts

Term	Definition
Entity	A logical representation of a table / dataset. Contains dimensions, measures, and relations.
Dimension	A descriptive attribute – date, company name, status, etc.
Measure	A numeric metric, usually aggregated – sales amount, deal count, …
Relation	A logical link between two entities. Always declared in both directions.
Semantic Model	A connected set of entities forming a domain graph.

💡

Access control – Only users with the Admin role can create or edit entities.

Ready‑made HubSpot Semantic Model 🔧

Connect your HubSpot account and RevOS instantly spins up a ready‑made semantic model that mirrors HubSpot’s core CRM objects (companies, contacts, deals, tickets, owners, engagements, and more) — complete with default measures and relations. Use it as‑is or extend it with your own business logic.

👉 Full details: Getting Started with the HubSpot Semantic Model.

Interface Overview

Below is a quick tour of the modeling workspace. You can place screenshots next to each subsection (file names in brackets are suggestions).

Data Model sidebar

Use the Semantic Model icon (two connected circles) to open the modeling area from anywhere in the app.

Overlays tab

Shows every existing entity (overlay).

Click ✏️ Edit to open the JSON editor.

Click 🗑 Delete to remove the entity.

Click + Add Overlay (bottom-left) to create a new one.

Graph tab

Interactive map of all entities and relations. Here you can see all the entites, pre-made also.

Entity Creation Workflow

Know your source table. Semantic Model window does not display a catalog or dropdown. Enter the fully‑qualified table name manually (e.g. my_project.raw_hubspot.deals).

Write the SQL query (sql). Add filters as needed – for example, restrict to the last 3 months.

Verify column names yourself. The editor provides no autocomplete or validation. If you misspell a column, the model will still publish, but downstream queries will be empty or error‑prone.

Mark the primary key. In dimensions, set "primary_key": true on one field.

Define relations in the joins block (see § 5).

Add measures in the measures block. Each measure must have unique name.

Add dimensions in the dimensions block. Each dimension must have unique name.

Save and publish the entity. Teammates can now build reports on top of the model.

Important – Each entity must have one primary key. Without it the model will not build.

Entity Code Template

{
  "sql": "<your SQL – filters optional>",
  "joins": {
    "<target_entity>": {
      "sql": "${CUBE}.field = ${<target_entity>}.field",
      "relationship": "one_to_one"
    }
  },
  "measures": {
    "<measure_name>": {
      "sql": "<expression>",
      "type": "sum" | "count" | "avg" | "min" | "max" | "number"
    }
  },
  "dimensions": {
    "<dimension_name>": {
      "sql": "${CUBE}.<column>",
      "type": "string" | "number" | "time",
      "primary_key": true
    },
   "<another_dimension_name>": {
    "sql": "${CUBE}.<column>",
    "type": "string" | "number" | "time"
	  }
  }
}

Example – minimal `SELECT *` with filtering and a join

Relation Types and When to Use Them

Type	Quick description	Example	How to set
`one_to_one`	Each A has exactly one B	Company ↔️ Company profile	`"relationship": "one_to_one"`
`one_to_many`	One A owns many B	Company → Invoices	`"one_to_many"`
`many_to_one`	Many A refer to one B	Invoice → Company	`"many_to_one"`

360‑degree rule – Declare each relation in both entities. If deals has many_to_one to companies, then companies must have the mirror one_to_many to deals. Otherwise the graph builder cannot find a path.

Modeling many‑to‑many

Direct many_to_many is not supported. Instead create a junction entity – a link table with pairs of keys. Both original objects connect to it with one‑to‑many / many‑to‑one relations, keeping source tables clean and avoiding duplicates.

Example (HubSpot deals ↔️ companies):

Create entity company_deal_link based on the companies table.

In SQL unnest the deal_ids JSON array: UNNEST(deal_ids) AS deal_id.

Relations:

companies ↔️ company_deal_link – one_to_many / many_to_one.

deals ↔️ company_deal_link – one_to_many / many_to_one.

Creating Dimensions

Add an object under dimensions.

Fill sql – usually the column name.

Choose type:

string – text, UUID, email, …

number – numeric IDs or values that shouldn’t be aggregated.

time – dates & timestamps. The system auto‑creates Year → Quarter → Month hierarchy.

Mark the main key with "primary_key": true.

Quick checklist

Does the primary key make each row unique?

Can the value be aggregated? If yes, it’s probably a Measure, not a Dimension.

Creating Measures

Add an object under measures.

Fill in:

sql – an expression or numeric column.

type – sum, count, avg, min, max, or number (custom formula not further aggregated).

Tips

Use SAFE_DIVIDE to protect against division by zero.

Prefix percentages with pct_; suffix totals with _cnt, _sum.