Semantic Model — Measures, Dimensions, Joins

The semantic model — one definition per business numberDatasets, dimensions, measures, calculated members, joins, filters and parameters — defined once, addressed by a name that never changes, and turned into SQL by the engine rather than retyped by every analyst. Every reporting team eventually has three versions of "net revenue": one in the board pack, one in the sales dashboard and one in somebody's personal query, each a slightly different SUM over slightly different rows. The semantic model is where that argument ends. Instead of writing the calculation into each report, it is written once as a named object — a measure called Net revenue, on a dataset called Orders, summing a declared expression — and everything that needs the number refers to the object rather than copying its SQL. The model has seven kinds of object. A DATASET is a logical table: a physical table on a connection, or a SQL view that may itself build on other datasets. A DIMENSION is something to group or slice by — a column or expression, optionally with a hierarchy such as Country → Region → City. A MEASURE is an aggregation: SUM, AVG, MIN, MAX, COUNT, COUNT DISTINCT or a custom aggregate expression, with a display format and a unit. A CALCULATED MEMBER is a formula over measures, such as margin = gross profit ÷ net revenue, and may combine measures from different datasets. A JOIN declares how two datasets relate, with its condition, type and cardinality. A FILTER is a reusable condition such as "completed orders only". A PARAMETER is a typed input — a date, a number, a list of regions — with a default and, optionally, a list of allowed values. Each object has a permanent address of the form urn:analytics:measure:net_revenue, and objects refer to one another with {{…}} tags around those addresses. The friendly name can be changed at any time without breaking anything, because nothing depends on the name. The model is maintained under Settings → Semantic objects, where it is validated, its lineage inspected and the whole model exported or imported as one file. Today the model is consumed by the dashboard engine's semantic widgets — KPI, chart and table widgets that name measures and dimensions instead of columns — through the application's API; the Dashboard ribbon widgets described in chapter 8 still bind straight to a table.

What it does

Seven object types — dataset, dimension, measure, calculated member, join, filter and parameter — each with a name, a Markdown description, tags and a folder for organising a large model
Stable addresses: every object is urn:analytics:<kind>:<id>, with an id of letters, digits and underscores up to 128 characters; the id and kind can never be changed after creation, the display name always can
Datasets over a physical table (connection, schema, table) or over SQL — and a SQL dataset may read other datasets through {{urn:analytics:dataset:…}} tags, so a cleaned view is built once and reused
Declared dataset columns with type, nullability and description, plus a list of hidden columns — PII and internal keys — that report builders should not be offered
Dimensions with a type hint (text, number, date, true/false), an optional level hierarchy, and a high-cardinality flag for attributes such as customer id that should not be dropped onto a chart axis casually
Measures with sum, average, minimum, maximum, count, count distinct or a custom aggregate, a number format such as #,##0.00 or 0.0 %, and a unit such as EUR or ms
Calculated members that combine measures and other calculated members, across datasets, and can sit anywhere a measure can
Joins with an explicit condition, join type (inner, left, right, full) and cardinality (one-to-one, one-to-many, many-to-one, many-to-many)
Reusable filters, scoped to one dataset or general, that may themselves use parameters
Parameters of six types — text, number, true/false, date, list of text, list of numbers — with a default and an optional list of allowed values
Validation of the whole model: references to objects that do not exist, and chains of references that loop back on themselves, are reported with the object and the path
Lineage for every object — what it depends on and what depends on it — and an impact check before deletion that lists every dependent object
Find dead objects: everything that nothing else refers to, as a clean-up list
Export of the whole model as one JSON bundle, and import in merge mode (skip or overwrite existing objects) or replace mode
Two scopes: Shared objects in the installation's catalog, seen by every project (on a server, by every user), and Project objects inside the project folder in SEMANTIC/<kind>/<id>.json, which travel with the folder, its Git history and its cloud copy
An editor with fields for scope, id, name, business definition, synonyms, owner and description above the kind's own definition, and Try a query… to build and run a governed query — measures, dimensions and levels, saved filters, parameters and conditions — with Explain to see the SQL without running it
Certification of each definition by a content steward, with four-eyes review and a Lapsed state on any change, and a History of every version with Restore (chapter 27)
Import of dbt MetricFlow and Apache Ossie (OSI) models as native objects (chapter 27)
Governed metrics as the source of sheet charts, pivots and dashboard widgets, in a cell through =METRIC(), in the Governed metrics pipeline step, and for Ask Greg, the REST API, the MCP server and embedded dashboards (chapter 27)

How it works

Open Settings → Semantic objects. A row of tabs selects the kind — Datasets, Dimensions, Measures, Calculated members, Joins, Filters, Parameters — the list on the left shows that kind's objects with a search box over name, id and description, and the right-hand pane shows the selected object — its scope, name, business definition, synonyms and owner as fields, the kind's own definition as JSON — with its certification, history and lineage.
New fills the editor with a template for the current kind, for example a measure with a placeholder dataset, an expression and agg: "sum". Replace the id and the CHANGE_ME references, then Create. The id becomes part of the permanent address, so choose it the way you would choose a column name: net_revenue rather than measure1.
Saving checks the object's own shape before anything is written: the address must match the kind and id, the name must not be empty, a dataset needs exactly one source — a table or SQL, never both — a measure needs a dataset, an expression and an aggregation, a join needs two different datasets, and no object may refer to itself. A rejected save leaves the stored object untouched and names the problem.
References are written as tags. A calculated member's formula reads {{urn:analytics:measure:gross_profit}} / {{urn:analytics:measure:net_revenue}}; a filter reads order_date >= {{urn:analytics:parameter:period_start}}; a SQL dataset reads SELECT * FROM {{urn:analytics:dataset:orders_raw}} WHERE status <> 'void'. Saving one object never fails because another does not exist yet, so a model can be typed in any order — Validate all then checks the references across the whole model.
Validate all reports two kinds of problem: a dangling reference (an object pointing at an address that does not exist, typically after a delete or a typo) and a cycle (A uses B, B uses C, C uses A), shown as the full path. Find dead lists the objects no other object refers to.
Delete checks first. If other objects depend on the one being deleted, the delete is refused and the dependents are listed; only an explicit Force delete removes it, and Validate all will then show the references left dangling.
When a semantic widget asks for data, the engine turns its bindings into one SELECT. Each measure becomes its aggregate, each calculated member its formula with the measures substituted, each dimension its expression in the SELECT and GROUP BY, each filter a WHERE condition, and a SQL dataset becomes a subquery. A tag that names a missing object, the wrong kind of object for its place, or that loops back on itself stops the query with that message — it is never sent to the database as raw text.
When the measures and dimensions on one widget come from several datasets, the engine starts from the dataset of the first measure and walks the declared joins, in either direction, to reach every other dataset, adding one JOIN per step and reversing LEFT and RIGHT when it walks a join backwards. Column names are prefixed with their dataset, so customer_id on both sides is no longer ambiguous. If no chain of joins connects two datasets, or the datasets sit on different connections, the widget fails with a message saying which join to add.
A join that multiplies rows — walking from the one side to the many side — makes SUM, COUNT and AVG over the one side count some rows more than once. The engine cannot correct that without pre-aggregating, so it says so: the result carries a note naming the join and the measures that may be over-counted. MIN, MAX and COUNT DISTINCT are not affected and are not flagged.
Parameters take their value from the request first, then from the dashboard's own override, then from the parameter's default; with none of the three the query stops and says so. The value is checked against the type and the allowed values and written into the SQL as a literal — quoted and escaped text, a validated number, a date in the dialect's own form (DATE '2026-01-31', or CAST(… AS DATE) on SQL Server), 1/0 where the database has no boolean — so a parameter can never inject SQL. A list renders as ('North', 'South') for use with IN, and an empty list matches nothing.
Objects are stored as one JSON file per object, written atomically with sorted keys so a diff between two versions shows only what changed. Shared objects live in the installation's shared catalog — ANALYTICS_SEMANTIC_DIR, or shared_dir in config.ini (a network folder whose own permissions decide who may change it), or the .semantic folder in the user profile; Project objects live inside the project folder and travel with it. A URN is unique across both scopes, and a project object may build on a shared one but never the other way round.
Export downloads semantic-YYYY-MM-DD.json — every object, without timestamps, in a stable order. Import asks whether existing objects should be overwritten or skipped, then reports how many were written and skipped. Replace mode, available through the API, makes the store match the bundle exactly, deleting objects the bundle does not contain.

Practices that keep it reliable

Model the handful of numbers the business argues about first — revenue, margin, active customers — not every column; a small model that is trusted beats a large one nobody checks
Choose ids as permanent: net_revenue, not nr2 or revenue_new — the id is the address every report will use, and only the name can be changed later
Write the definition into the description: what is included, what is excluded, which currency, who owns it — the description travels with the object and with every export
Build calculated members from measures rather than repeating SQL: margin as {{gross_profit}} / {{net_revenue}} stays right when either measure is corrected
Declare the cardinality of every join honestly and read the fan-out notes — a to-many join with a SUM on the one side is the most common way a dashboard total ends up larger than the ledger
Mark personal and internal columns as hidden on the dataset, and flag customer- or transaction-level dimensions as high cardinality — hidden is a hint to report builders, not access control, so keep PII masking (chapter 11) and database permissions in place
Run Validate all after every batch of edits and Find dead before a release; delete with Force only when the dependents listed are meant to go too
Export the model into the project folder before committing, so the definitions and the reports that use them are reviewed in the same change

In a regulated environment

One definition per number is a control, not a convenience: when the board pack and the operational dashboard read the same measure object, a figure cannot quietly differ between them, and a change to the definition is a single, visible edit.
Addresses are permanent and names are not, so a measure can be renamed for a new audience or translated without breaking a single consumer.
Parameters are typed and allow-listed before they reach SQL, so a value typed into a dashboard control cannot change the shape of the query.
Reading the model needs the Viewer role; creating, changing, deleting and importing objects needs Analyst. Exports and imports of the model are written to the tamper-evident audit trail.
The semantic layer is a licensed component: an installation whose licence excludes it refuses the semantic endpoints rather than half-working. It is included in every plan.
Every create, update and delete is recorded in the synchronisation operation log, and a change from another installation that collides with a local one is kept in the Sync & conflicts inbox, side by side, rather than overwriting it.
The model is included in the administrator's profile backup, alongside connections, mail profiles and the AI configuration, so a rebuilt workstation or server gets its definitions back with everything else.
Stated plainly: each kind's own definition is edited as JSON beneath the form fields, lineage is shown as lists of direct dependencies rather than as a diagram, there is no personal scope, and nothing tests automatically that a measure still runs against its database before it is certified. Charts, pivots, dashboard widgets, METRIC(), pipelines, Ask Greg, the API, the MCP server and embedded dashboards do read the model (chapter 27).

Screens

Settings → Semantic objects — a tab per object type, Validate all, Find dead and JSON export and import. The Revenue measure is open as JSON, with its address (urn:analytics:measure:revenue) and its lineage: one dataset upstream, two calculated members downstream.

Glossary terms on this page

ATX Institute on LinkedIn

This site needs JavaScript for the full experience. Every page above is reachable without it.