Skip to content

Latest commit

 

History

History
336 lines (277 loc) · 15.5 KB

analytics.mdx

File metadata and controls

336 lines (277 loc) · 15.5 KB
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 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

{/* 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),
});

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.
  • field is a column reference. A dimension's field names a field of the base object or a relationship.field path; 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 own filter, and a ratio is a derived measure.
  • Metric certification — a certified flag that marks a measure as a human-blessed governance checkpoint — is a design goal of ADR-0021 but is not yet implemented; DatasetMeasureSchema has no certified field 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.

Duration measures

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' } }
  • from and to name two datetime fields of the dataset's own object, by their bare names. A relationship path or an expression is refused when the dataset is parsed. A date, time, number or text field is refused too: os validate reports measure-duration-field-type-refused, and the dataset compile answers 400 DATASET_INVALID.
  • unit is seconds, minutes, hours or days, and the measure answers a number of them (1.5 hours). aggregate is avg, min, max or sum.
  • A record missing either instant is left out, as a null is left out of an avg. A to earlier than its from counts as a negative duration. Over a group with no record carrying both, sum answers 0 and the others answer nothing.
  • A measure filter, and a derived measure naming the duration measure in of, 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_FIELD naming the measure, rather than answered another way: a time dimension bucketed by granularity (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.

Creating one in Studio

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 sum measure 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.

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

{/* 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.

Ordering the result

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.

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

Display values are resolved server-side

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

  • Dimensions — a dimension whose field carries options returns the option label (not the stored value; resolution keys on the options list being there, not on the field's type, so this is not limited to select), a reference dimension (any field whose stored value is another record's id, a user person axis included) 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). A measure that declares polarity (higher, lower or neutral: 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.

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