Analytics Datasets

Related ADR: ADR-0021 — Analytics: one semantic dataset layer

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.

Why a semantic layer

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:

1. No joins — "revenue by account region" needs order ⋈ account. An inline single-object query can't reach it.
2. Metric drift — "revenue" defined three times in three grammars diverges across a report, a dashboard tile, and a list chart. A governance red line.
3. 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.

Authoring a dataset

// 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', certified: true },
    { 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%' },
  ],
});

Register it in your stack alongside objects / dashboards:

export default defineStack({
  // ...
  datasets: Object.values(datasets),
});

Key rules

• No raw SQL, no hand-authored joins. The author declares which relationships to include; the compiler derives the join from the object graph.
• certified: true marks a human-blessed metric — the review checkpoint. Reviewing AI output collapses to "did it use certified measures correctly."
• 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.

Binding a dashboard widget

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.

Binding a report

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 ({date-macro} placeholders are resolved by the renderer before querying).

Cross-object joins

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

How it runs

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 same governed path backs the Studio dataset preview, dashboard widgets, and dataset-bound reports — so the numbers match everywhere.

Display values are resolved server-side

The query result is presentation-ready — authors do not format dimension or measure values by hand:

• Dimensions — a select dimension returns its option label (not the stored value), a lookup / master_detail dimension returns the related record's display name (not the FK id), and a date dimension with a dateGranularity returns 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 label and format on the result fields, so a KPI or chart legend reads "Total Spent / $616,000" rather than "spent_sum / 616000". The renderer applies the format at display time (it can't be baked into the numeric row value charts plot).

Migrating from inline queries

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:

1. Dual-form (additive). A report/widget kept its legacy inline query AND gained a dataset binding. A read-only reconciliation harness asserted both forms returned identical numbers (the financial-correctness gate).
2. Single-form (terminal). Once every surface reconciled, the inline query fields (objectName/columns/groupings on reports, valueField/aggregate on widgets, xAxisField/yAxisFields/aggregation on 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; reach for a named dataset when a metric is shared or must be certified, and an inline anonymous dataset for a one-off single-object KPI.