| title | Analytics datasets — modelling for reports |
|---|---|
| navTitle | Analytics Datasets |
| description | The dataset semantic layer (ADR-0021) — define a metric once, bind reports and dashboards to it by name. |
Related ADR: ADR-0021 — Analytics: one semantic
datasetlayer
A dataset is a named, reusable analytical definition — a base object, the
relationships to include, and the declared dimensions (groupable axes) and
measures (aggregatable values). Reports and dashboards bind to a dataset by
reference and select dimensions/measures by name — they never re-declare
object / field / aggregate inline.
This is the industry-convergent shape (Looker LookML, Power BI dataset+model, dbt metrics, Salesforce CRM-Analytics): a governed semantic layer below; thin presentations above.
Without one, the same metric is re-defined inline in every surface — a report's
columns, a dashboard widget's valueField + aggregate, a list-view chart.
That produces three defects fatal for an enterprise core system:
- No joins — "revenue by account region" needs
order ⋈ account. An inline single-object query can't reach it. - Metric drift — "revenue" defined three times in three grammars diverges across a report, a dashboard tile, and a list chart. A governance red line.
- No source of truth — no drill-through, no certification, no reuse.
A dataset fixes all three: revenue is defined once, joins are derived from
the object graph, and every surface references the same definition.
{/* os:check */}
// src/datasets/sales.dataset.ts
import { defineDataset } from '@objectstack/spec/ui';
export const SalesDataset = defineDataset({
name: 'sales',
label: 'Sales',
object: 'opportunity',
// Relationships to include BY NAME (lookup / master_detail field names).
// Joins are COMPILED from these — you never write an ON clause.
include: ['account'],
// Definition-level scope (the dataset's intrinsic filter).
filter: { is_deleted: { $ne: true } },
// Groupable axes — a base field, or a `relationship.field` path.
dimensions: [
{ name: 'stage', field: 'stage', type: 'string' },
{ name: 'region', field: 'account.region', type: 'string' },
{ name: 'close_date', field: 'close_date', type: 'date', dateGranularity: 'month' },
],
// Aggregatable values — defined ONCE here; referenced everywhere by name.
measures: [
{ name: 'opp_count', aggregate: 'count' },
{ name: 'revenue', aggregate: 'sum', field: 'amount', format: '$0,0' },
{ name: 'won_amount', aggregate: 'sum', field: 'amount', filter: { stage: 'closed_won' } },
// Derived measure — references OTHER measures by name only (no raw fields/SQL).
{ name: 'win_rate', derived: { op: 'ratio', of: ['won_amount', 'revenue'] }, format: '0.0%' },
// Duration measure — the time between two datetime fields of each record.
{ name: 'avg_days_to_close', aggregate: 'avg', duration: { from: 'created_at', to: 'closed_at', unit: 'days' } },
],
});Register it in your stack alongside objects / dashboards:
export default defineStack({
// ...
datasets: Object.values(datasets),
});- No raw SQL, no hand-authored joins. The author declares which relationships to include; the compiler derives the join from the object graph.
fieldis a column reference. A dimension'sfieldnames a field of the base object or arelationship.fieldpath; a measure's may also be'*'(or be omitted) for a count. A SQL expression there is refused at parse: a conditional count or sum is a measure with its ownfilter, and a ratio is aderivedmeasure.- Metric certification — a
certifiedflag that marks a measure as a human-blessed governance checkpoint — is a design goal of ADR-0021 but is not yet implemented;DatasetMeasureSchemahas nocertifiedfield today. - Derived measures are first-class but closed: they reference other
measures by name only (
ratio/sum/difference/product). - RLS / tenant scoping is enforced by the runtime, per joined object — never declared in the dataset. There is one place to reason about access.
A measure can aggregate the time between two datetime fields of the same
record (time to resolve, time to first response, cycle time) with duration
in place of field:
{ name: 'avg_hours_to_resolve', aggregate: 'avg', duration: { from: 'created_at', to: 'resolved_at', unit: 'hours' } }fromandtoname twodatetimefields of the dataset's own object, by their bare names. A relationship path or an expression is refused when the dataset is parsed. Adate,time, number or text field is refused too:os validatereportsmeasure-duration-field-type-refused, and the dataset compile answers400 DATASET_INVALID.unitisseconds,minutes,hoursordays, and the measure answers a number of them (1.5hours).aggregateisavg,min,maxorsum.- A record missing either instant is left out, as a null is left out of an
avg. Atoearlier than itsfromcounts as a negative duration. Over a group with no record carrying both,sumanswers0and the others answer nothing. - A measure
filter, and aderivedmeasure naming the duration measure inof, work as they do for any measure. - It runs as one SQL statement on SQLite, PostgreSQL and MySQL, computed in
double precision on each. A query the service cannot compile to one such
statement is refused with
400 INVALID_FIELDnaming the measure, rather than answered another way: a time dimension bucketed bygranularity(a monthly trend), a field-to-field comparison in a filter, an object served through engine middleware or an external datasource, or a datasource that is not SQL. - Comparing each record's duration against a value held on another object (an SLA target per ticket type, say) is not part of it.
A package author does not have to leave the Studio to get a dataset over the package's own objects. The New report dialog's dataset picker and the dashboard widget inspector's both carry a New dataset button. Its dialog asks for:
- a name (the identifier is derived from it and stays editable);
- the object the dataset reads, preselected when the package has exactly one;
- optionally the fields to group by (select, radio, boolean, lookup and
master-detail fields, one dimension each) and the number fields to
total (one
summeasure each).
It saves an ordinary dataset draft in the package, published with the rest of
the package, and the picker selects it. Every dataset written this way counts
its records (a count measure), so a report or a chart can bind it at once.
Nothing is derived without the author: there are no implicit datasets. Joins,
filters, date dimensions and derived measures are edited afterwards on the
dataset's page in Studio's All metadata area.
While no dataset reads one of the package's objects, the Studio pickers say so beside the button, and they list the package's own datasets first.
A widget selects dimensions/measures by name. Its presentation-scope filter
flows into the query as the runtime filter:
{
id: 'revenue_by_stage',
type: 'bar',
title: 'Pipeline by Stage',
dataset: 'sales',
dimensions: ['stage'], // X / group / split
values: ['revenue'], // Y — the measure name, not amount+sum
filter: { stage: { $nin: ['closed_lost'] } }, // presentation scope (runtimeFilter)
}A metric (KPI) widget omits dimensions and shows the single measure value.
{/* os:check */}
export const SalesByStageReport = {
name: 'sales_by_stage',
label: 'Sales by Stage',
dataset: 'sales',
rows: ['stage'], // dimension names down
values: ['revenue'], // measure names
runtimeFilter: { close_date: { $gte: '{current_quarter_start}' } },
};rows are the pivot's down-axis dimensions; values are measure names. A matrix
report adds across-axis dimensions; runtimeFilter is the render-time scope.
A selected date dimension is chronological by default — ascending, on the down axis and the across axis alike — so a month-bucketed matrix reads left-to-right in time without declaring anything. Every other dimension keeps the order the grouping produced until you ask for one.
To ask, declare order: a list of sort keys, most significant first. Each key
names a dimension the report groups by (rows / columns) or a measure it
displays (values); anything else is an authoring error rather than an ordering
that silently does nothing.
{/* os:check */}
export const HoursByOwnerReport = {
name: 'hours_by_owner',
label: 'Hours by Owner',
type: 'matrix',
dataset: 'tasks',
rows: ['owner'], // down axis
columns: ['closed_month'], // across axis — a month-bucketed date dimension
values: ['est_hours'],
order: [
{ by: 'closed_month', direction: 'desc' }, // newest month first (overrides the default)
{ by: 'est_hours', direction: 'desc' }, // then the biggest owner first
],
};Ordering is applied server-side over the whole grid — after measure-scoped filters merge and derived measures evaluate — so a derived measure is a valid sort key even though no single SQL statement computes it. Null / empty buckets sort last in both directions.
A joined report orders per block: put order on each blocks[] entry, not on
the container.
Placeholders inside a filter / runtimeFilter — {date-macro}s and the
session tokens {current_user_id} / {current_org_id} — are resolved on both
sides: by the renderer before it queries, and by the analytics dataset executor
server-side. The server pass is what makes a widget work at all, since the
native-SQL strategy compiles raw SQL and binds comparands directly without ever
passing through a renderer. An unrecognised placeholder is a build error and a
runtime error — never a literal string that quietly matches nothing.
Because the dataset's include compiles to the analytics runtime's join path,
any report or widget can be multi-object safely — the headline enterprise
capability the inline single-object query could never reach:
{ dataset: 'sales', dimensions: ['region'], values: ['revenue'] }
// → revenue by account.region, joined + RLS-enforced per object.Joins are derived from the dataset's include declarations: list the relationship
paths to join (include: ['account', 'account.owner']), and dimensions/measures then
reference fields along them by dotted path (account.owner.region). Only declared
paths are joinable. Paths may chain multiple to-one hops (ADR-0071 — declaring
account.owner implicitly includes account), are capped at 3 join hops (4 objects),
and are to-one only; to-many traversal is rejected (aggregate the many-side as its
own measure instead).
A dataset compiles to the Cube analytics runtime (IAnalyticsService). The
REST surface is:
POST {basePath}/analytics/dataset/query
{ datasetName: 'sales',
selection: { dimensions: ['stage'], measures: ['revenue'], runtimeFilter: {...} } }
The body takes selection, previewDrafts, and exactly one of dataset (an
inline definition) or datasetName (a saved one). Every selection member, the
filter included, goes inside selection: any other top-level key is refused
with 400 VALIDATION_FAILED naming it (a runtimeFilter beside selection
answers "did you mean selection.runtimeFilter?"), and so is a body naming
both dataset and datasetName.
The same governed path backs the Studio dataset preview, dashboard widgets, and dataset-bound reports — so the numbers match everywhere.
The query result is presentation-ready — authors do not format dimension or measure values by hand:
- Dimensions — a dimension whose field carries
optionsreturns the option label (not the stored value; resolution keys on theoptionslist being there, not on the field's type, so this is not limited toselect), a reference dimension (any field whose stored value is another record's id, auserperson axis included) returns the related record's display name (not the FK id), and adatedimension with adateGranularityreturns a human bucket label (month→2026-04,quarter→2026-Q2,year→2026). Unresolved values pass through unchanged, never blank. - Measures — each measure column carries its
labelandformaton the resultfields, so a KPI or chart legend reads "Total Spent / $616,000" rather than "spent_sum / 616000". The renderer applies theformatat display time (it can't be baked into the numeric row value charts plot). A measure that declarespolarity(higher,lowerorneutral: which direction of change is good news) carries it on its column too, so a period-over-period delta on a lower-is-better KPI draws a rise as bad news; undeclared keeps a rise green and a fall red.
ADR-0021's terminal state is one author-facing shape, and the cutover is now
complete: report, dashboard widgets, and list-view charts are all
dataset-bound today (ReportSchema, DashboardWidgetSchema,
ListChartConfigSchema in packages/spec/src/ui). The migration ran in two
steps so it could be verified safely:
- Dual-form (additive). A report/widget kept its legacy inline query AND
gained a
datasetbinding. A read-only reconciliation harness asserted both forms returned identical numbers (the financial-correctness gate). - Single-form (terminal). Once every surface reconciled, the inline query
fields (
objectName/columns/groupingson reports,valueField/aggregateon widgets,xAxisField/yAxisFields/aggregationon list charts) were removed and each schema collapsed to the single dataset shape. The presentation schemas themselves were kept — only their inline query fields went away.
Author new analytics directly in dataset form: define the metric once as a
named dataset registered in your stack, then bind reports, dashboard widgets,
and list-view charts to it by name. Presentations reference a dataset by its
name string — the shipped presentation schemas carry no inline/anonymous
dataset shape, so even a one-off single-object KPI is a small named dataset.
A list-view chart's binding must resolve. A dataset no package declares, or a
dimensions or values entry the dataset does not declare, is refused at
publish, for a view and for an object's own listViews alike, and fails
os validate (chart-dataset-unknown, chart-dimension-unknown,
chart-measure-unknown). A values entry names a dataset measure, never
the base field it aggregates.
A list-view chart draws the rows of its dataset's base object, never the
list's own records: the list's filter does not reach the dataset query. So a
list view's chart must bind a dataset whose object is the object the list
lists. A dataset over another object is refused at publish and fails
os validate (list-view-chart-dataset-foreign-object), even when its
include reaches the list's object through a relationship, because include
adds columns to group by, not rows. Put a chart of another object's data on
that object's list view or on a dashboard.