LOD authoring reference

This page organizes the rules by the questions that come up while writing a model or tracking down an error. If LOD is new to you, read Understand LOD first; it covers the concepts, how to choose among the four uses, and the basic filter distinction. Field and metric names in the examples come from the commerce walkthrough.

Terms used throughout this page:

• Base metric: the existing metric named by an LOD declaration's first argument, such as paid_total in fixed(paid_total, orders.user_id).
• Fixed field: a field declaring fixed; the result can be grouped, filtered and aggregated again.
• LOD metric: a metric declaring include, exclude or fixed; the result is returned directly by the query.
• Context: a query's context_filter and time_range, which restrict the source records used for calculation. Across datasets, only conditions readable through declared relationships or key pairings propagate; see across tables. In contrast, an ordinary filter (where_sql) selects display rows and does not narrow fixed inputs.
• Host: the dataset on which a fixed field is defined. It decides which rows the result has; see across tables.

What can be a base metric?

A base must resolve to one aggregate over one dataset, or a filter derivation of that aggregate. Supported functions are SUM, COUNT, COUNT with DISTINCT, AVG, MIN and MAX. A bare COUNT(*) in a multiple-dataset model needs an explicit home through D-DATASET. Counting a declared non-null identifier also makes ownership explicit.

A LOD metric's base may also be built from several such aggregates: a compose, a ratio or an expression metric. The LOD evaluates each aggregate at its grain and applies the base's own arithmetic, fill included, so exclude(net_revenue) over revenue − refunds is the whole query's revenue minus its refunds, even over two facts; dividing by it gives a share of the total. The authored expression nests the base's whole expression inside the outer aggregate, for example SUM(COALESCE(SUM(orders.amount), 0) - COALESCE(SUM(refunds.amount), 0)). Each aggregate is bucketed on its member's time axis, as in the compose itself, and a fixed level of detail applies the query's time range on those axes; a compose over the level of detail keeps them. An include over aggregates of one dataset on two axes is refused, since it rolls each dataset up once.

An include over a base spanning several datasets computes each dataset's aggregates on its own rows, per display row and declared dimension, then merges them on those groupings before applying the base's arithmetic. A grouping that any of the datasets has is one inner row. An aggregate whose dataset has no rows there is NULL, as a missing member of a cross-fact compose is at the display grain, unless the base's fill replaces it. The outer aggregate skips NULLs. Without a fill, AVG(include(net_revenue, orders.customer_id)) leaves out a customer with orders but no refunds; with fill: 0 that customer counts as revenue − 0. Each declared dimension must be every dataset's own: a field of one of them, paired through D-CONFORM with a field of each other one. Over several datasets, exclude() and fixed() take no grouping fields and compute over the whole query; for a keyed one, put the LOD on each member and compose the results.

A fixed field's base is always a single aggregate. A window result, another LOD metric, or a compose with either as a member cannot be a base. Neither the base nor its grouping fields may depend on a LOD field, even indirectly through an ordinary calculated field. Changing declaration order does not permit nesting. An include base must not share its name with a field on its own dataset.

Fixed fields: choose the grouping and reuse the value

A fixed field uses fixed. Its first argument is the base metric name; subsequent arguments are fixed grouping fields, separated by commas. Those fields belong to the dataset containing the fixed field. Unqualified names resolve there too, but explicit dataset names make ownership clear. Several fields form one combined grouping, not separate calculations per field.

The walkthrough's fixed(paid_total, orders.user_id) totals payments per user. A same-dataset calculation may omit every grouping field to compute one value for its whole calculation population. A cross-dataset calculation may not. A field cannot use include/exclude or wrap its fixed declaration in an aggregate.

Queries can read, group by, filter on or sort the field; ordinary calculated fields can read it too. When an ordinary metric aggregates a fixed field again, each fixed group contributes once within each display group. The ads-channel buyer mean in Understand LOD therefore averages two buyers, not five repeated order-level values. An entity appearing in several display groups contributes once in each one; those groups' entity counts need not be additive.

What the aggregate reads	What contributes once
A stored column or raw-row count	Each source record
A fixed field	Each distinct fixed group within the display group
A calculated field reading only LOD values and constants	The fixed groups of those LOD values
A calculated field also reading a stored column	Each source record
A filter metric whose condition reads a LOD value	What its base aggregates; a filtered row count still counts source records

Using a fixed field only for grouping does not change raw-row metrics' weights. Fields with different fixed groupings may be displayed or filtered together, but a query may reaggregate only values with the same fixed grouping fields. It cannot also request a metric weighted by source records. This holds even when two metrics spell one expression: a filter metric over a row count counts source records, while the same CASE WHEN written directly as a metric reads only LOD values and counts fixed groups.

For a same-dataset calculation, a NULL key forms its own group and matches that group's fixed value. Distinct counting still excludes NULL, so the distinct key count is not the number of all fixed groups including the NULL group. Matching a combined grouping preserves every component.

LOD metrics: return the calculation as a metric

A metric declaration also takes the base metric name first, followed by dimension fields. These fields must belong to the base metric's dataset and must be qualified with their dataset names.

Type	Declaration requirement	Grouping and calculation
include	At least one dimension; exactly one outer aggregate	Add the declared dimensions to the display grouping, compute the base, then apply the outer aggregate
exclude	Dimensions optional; no outer aggregate in the declaration	Remove the declared dimensions and recompute the base; omitting dimensions removes all display grouping
Metric fixed	Grouping fields optional; no outer aggregate in the declaration	Compute at exactly those fields; omitting them produces an overall baseline

The include outer function may be SUM, COUNT, AVG, MIN or MAX; an outer DISTINCT is not accepted. The walkthrough's AVG(include(order_count, orders.user_id)) computes buyer order counts, then averages them. Without group_by, include still computes the declared finer grouping before aggregating to an overall result. A display time grain applies inside that finer calculation. Ordinary filters and context both scope its input.

Exclude also honors ordinary filters and context. It recomputes at the coarser grouping instead of adding displayed values, so distinct counts must be recalculated there too. Every field a keyed exclude names must be in the query's group_by, at any time grain: excluding a field the query does not group by would remove nothing and leave the base, so the query is refused with lod_dimension_not_grouped. A keyless exclude needs no grouping; without group_by, it is the base over selected records.

Metric fixed honors context and ignores ordinary display filters. Every fixed field must appear unchanged in the query's group_by, without a time grain, or the query is refused with lod_dimension_not_grouped. The metric catalog lists the fields a keyed exclude or fixed names as required_dimensions, and the refusal lists the ones missing as missing_dimensions. A keyless overall baseline also works without grouping. To aggregate fixed results further, use a fixed field and an ordinary aggregate; wrapping a fixed metric declaration in another aggregate is unsupported.

Ordinary, exclude and fixed metrics on one fact may share a query, with compose for ratios or other arithmetic. Multiple include metrics may share a query when their base datasets and ordered dimension list agree; their bases and outer functions may differ. Include cannot share that query with ordinary metrics of a dataset its base reads, exclude/fixed metrics or LOD fields actually read by the query.

Where do declarations belong?

A field or metric retains its normal expression.dialects list with an ANSI_SQL expression. Metric SQL references declared semantic fields using dataset-qualified names, not undeclared physical columns.

Put the extension in the field or metric's custom_extensions, with vendor_name: DATUS. data is a JSON-encoded string. Its v member is the extension version string; 1.11 supports this page's rules. Optional requires lists capability keys: lod for fixed fields and derive for derived metrics.

Definition	Location inside the JSON object
Fixed field	lod.expr contains the fixed declaration
LOD metric	derive.type is lod; derive.expr contains include, exclude or fixed
Filtered base metric	derive.type is filter; derive.base names the base and derive.where contains the predicate
Arithmetic over metrics	derive.type is compose; derive.expr uses metric names in an arithmetic expression

This is the walkthrough's buyer lifetime-spending field, placed in the orders dataset's fields list. The full model also defines its paid_total base.

- name: buyer_lifetime_paid
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(orders.paid_amount)
  datatype: Decimal
  custom_extensions:
    - vendor_name: DATUS
      data: '{"v":"1.11","is_dimension":false,"lod":{"expr":"fixed(paid_total, orders.user_id)"}}'

LOD metrics go in the model's metrics list. This is the average number of orders per buyer within each channel, based on order_count (COUNT(DISTINCT orders.order_id)):

- name: mean_orders
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: AVG(COUNT(DISTINCT orders.order_id))
  custom_extensions:
    - vendor_name: DATUS
      data: '{"v":"1.11","derive":{"type":"lod","expr":"AVG(include(order_count, orders.user_id))"}}'

Why are both SQL and an LOD declaration required?

expression describes what is calculated; the DATUS declaration describes its grouping and stages. They must agree. Core SQL must be fully expanded, not a metric name, a constant placeholder or a fixed/include/exclude declaration.

Definition	Required core SQL
Fixed field	The base metric's complete aggregate SQL
Include metric	The declared outer function around the base metric's aggregate SQL
Exclude or fixed metric	SUM around the base metric's aggregate SQL, even when the base uses a different function
Ordinary reaggregation of a fixed field	An ordinary aggregate over the field reference
Compose metric	Each metric name replaced by that metric's complete core SQL

The lifetime-spending field above has core SQL SUM(orders.paid_amount). The ordinary buyer-mean metric uses AVG(orders.buyer_lifetime_paid); the include metric in the previous section is AVG(COUNT(DISTINCT orders.order_id)).

Exclude and fixed metrics are the most confusing case. This is the walkthrough's daily site sales:

- name: site_amount
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(SUM(orders.paid_amount))
  custom_extensions:
    - vendor_name: DATUS
      data: '{"v":"1.11","derive":{"type":"lod","expr":"fixed(paid_total, orders.paid_date)"}}'

The inner SUM(orders.paid_amount) is the SQL of the base metric paid_total. The outer SUM is a fixed wrapper meaning "take the base metric's result once more at another grain". It is SUM even when the base uses COUNT or MAX: a fixed metric based on order_count is written SUM(COUNT(DISTINCT orders.order_id)). The outer SUM exists only so Dosi can check that the SQL agrees with the declaration; it does not add baseline values together. Nested aggregate SQL cannot run directly as ordinary SQL. Dosi uses the declaration to split it into valid stages. A mismatch returns derive_expression_mismatch.

A filtered base's core SQL expands its predicate with parameter defaults; it does not retain parameter markers. A filtered row-count COUNT uses a conditional expression returning 1 for a matching record and NULL otherwise. Do not replace this 1 with an identifier merely because the SQL is equivalent. For a sum or distinct count of values, return the aggregated value on a match and NULL otherwise, then apply the aggregate. A fixed field repeats that filtered aggregate SQL and references the filtered metric by name in lod.expr.

Metric names belong in derive.expr, not in place of core SQL. Keep casts and zero-denominator handling consistent between compose declarations and their fully expanded core expressions.

Different periods: define independent parameterized windows

When a fixed field needs its own period, define a filtered base metric with parameters for that period, then reference it from the field. Put params beside derive in the base metric's DATUS JSON, not on the fixed field.

These are the walkthrough's two definitions for reference-period payments. First the parameterized base metric, in the metrics list:

- name: reference_paid_amount
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(CASE WHEN orders.paid_date_key >= 0 AND orders.paid_date_key < 99991231 THEN orders.paid_amount END)
  custom_extensions:
    - vendor_name: DATUS
      data: '{"v":"1.11",
        "derive":{"type":"filter","base":"paid_total",
          "where":"orders.paid_date_key >= :reference_start AND orders.paid_date_key < :reference_end"},
        "params":[{"name":"reference_start","type":"int","default":0,"min":0,"max":99991231},
                  {"name":"reference_end","type":"int","default":99991231,"min":0,"max":99991231}]}'

Then the fixed field on users that references it, one value per user:

- name: reference_paid
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(CASE WHEN orders.paid_date_key >= 0 AND orders.paid_date_key < 99991231 THEN orders.paid_amount END)
  datatype: Decimal
  custom_extensions:
    - vendor_name: DATUS
      data: '{"v":"1.11","is_dimension":false,"lod":{"expr":"fixed(reference_paid_amount, users.user_id)"}}'

The 0 and 99991231 in the core SQL are the parameter defaults substituted in, as the previous section requires. A query selects April with --param reference_start=20300401 --param reference_end=20300501.

Each parameter declares name, type and default. Types int and float may set min/max; string may set allowed or leave its domain open. Allowed values and numeric bounds are mutually exclusive. With neither, datus_param_unbounded is a compilation warning, not by itself a validate --strict failure. Declare known business domains. Integer bounds do not validate date codes, and D-PARAM has no date type.

A predicate references a parameter with a colon followed by its name. Supply values through query params or CLI --param, not SQL concatenation. Unbound parameters use defaults.

One parameter value goes to every metric in the query that declares a parameter of that name: metrics the query selects directly, and base metrics behind the fixed fields the query reads. Two periods you want to control separately therefore need different names, such as the walkthrough's reference_start for the reference period and observation_start for the observation period; sharing one name forces both periods to be the same. The value must also satisfy the type and domain of every declaration of that name. A fixed field's calculation accepts one value per parameter, never a list.

The value reaches a fixed field's base however the query reads the field: through grouping, filtering, detail projection or a calculated field that depends on it. If no metric in the query declares the name, the query returns unknown_metric_param, usually because of a misspelling. A wrong type, a value outside the domain or a list for a fixed field's parameter returns param_out_of_domain.

When display records and a fixed field observe different periods on the same dataset, put the display date predicate in ordinary where_sql and the field's period in its parameterized base. A query time_range enters the fixed calculation too, intersecting the periods. A field's predicate adds constraints; it does not override source restrictions, time range or context.

For automatic query time-range routing, declare a native time dimension or use D-FORMAT for its encoding. Ordinary integer date-code fields may also be compared directly with integer parameters without being declared as time axes.

Across tables: decide where the result belongs

A fixed field may aggregate records from another dataset. The dataset containing the field is its host. It decides which records can appear in the result; entities present only in the aggregated dataset do not add host rows. Fixed grouping fields still belong to the host, and each needs an explicit correspondence to the other dataset.

Declared correspondence	How the aggregated dataset obtains a grouping field
Direct many-to-one relationship between the two, in either direction	Paired key column
A many-to-one path from the aggregated dataset to the host	Host field through that path
D-CONFORM pairing	Paired field in the other fact

Matching names do not establish correspondence. Missing or conflicting mappings, or a cross-dataset fixed field without grouping keys, are rejected. A regular relationship uses from, to, from_columns and to_columns, defaulting to many-to-one. D-CONFORM uses the same column-pair lists with DATUS cardinality: "many_to_many". It aligns results without joining fact records; see D-CONFORM.

Without matching records, a cross-dataset COUNT is 0 and other aggregates are NULL. A cross-dataset NULL key matches nothing, unlike a same-dataset NULL group. SUM returns NULL for both no rows and all-NULL values. Use an ordinary calculated field with COALESCE when the business meaning specifically requires zero.

The query's time range lands on the aggregated dataset's own time field, whatever the two date columns are called and whether or not they are paired; a dataset with no time field is read whole, with a time_axis_missing warning. A context_filter conjunct applies wherever the aggregated dataset can read it, including many-to-one paths or paired columns. One it cannot read is skipped with a context_skipped warning, and one no part of the query can read is refused (context_unreadable). A condition that reaches the other dataset only through a paired column stored in another time format returns not_implemented; put the period in the time range instead. Ordinary where_sql still does not narrow a fixed input, so a query that needs the field over all of its history puts its own dates there (S-FILTER-2).

Classify two facts independently, then align them

Two facts recording business activity may each define a fixed field and declare those fields as a common D-CONFORM dimension. Each computes its own field and metrics before their grouped results align. Either paired dimension name can be used, including when requesting only the other fact's metric. Both sides' groups are retained; missing COUNT defaults to 0 and SUM stays NULL unless another fill policy is declared. Adding the two counts does not form a distinct union: the same entity may be present on both sides.

This differs from a fixed field that directly aggregates the other fact. Requesting both facts' metrics grouped by that host field returns fan_out_risk. Ordinary relationship join columns cannot read LOD values; D-CONFORM pairing is not that kind of detail join.

Detailed filter rules

A context_filter cannot read a LOD value because it scopes the records used to calculate that value. Conditions on fixed fields apply after their values exist and never feed back into any fixed input. Selected LOD metrics may be used in result filters, after calculation but before sorting and limit; they cannot be grouping dimensions. Filtering an unselected metric returns result_filter_metric_not_selected. One result predicate mixing a metric with an ungrouped field returns result_filter_unprojected.

Each AND conjunct is placed separately. An OR, NOT or comparison mixing raw and fixed fields executes as a whole after the fixed value is available; do not split an OR into separate filters. Relationships needed only for display dimensions or ordinary filters affect displayed records, not fixed inputs, even for INNER relationships. Put a related condition in context when matching records should define the calculation population.

Time ranges are half-open. A fixed time grouping key keeps its original value and storage encoding internally; a display time grain applies when grouping the fixed results again. Include applies that grain inside its finer calculation. A LOD field, or a field depending on one, must not declare dimension.is_time: true. A date datatype is allowed but does not make it an axis for query time ranges.

Query the LOD definitions

Use dosi query to select metrics and grouping fields. The query properties group_by, where_sql, context_filter, time_range, order_by, limit and params correspond to CLI --group-by, --where, --context-filter, --start-time/--end-time, --order, --limit and --param. --metrics supplies metric names. There is no additional query-side LOD language.

Use dosi select for detail rows, with --from for the root dataset and --fields for projected fields. It can read, filter or sort fixed fields on that root, including ones with bases on other datasets. Reading a joined dataset's LOD field or a calculated field depending on it is unsupported; make that dataset the root if its records are the required population.

Detail results preserve root-row multiplicity. --distinct removes duplicate output rows, not records used in LOD calculations. Ordinary fields reachable through many-to-one relationships can be read. Traversing to the many side is refused to avoid multiplying records. Detail field bases use the same --param binding rules.

Limits: which calculations need separate queries?

Restrictions apply to fields and metrics actually read by one query, not to whether their definitions can coexist in a model. Preserve the business population and denominators when splitting queries.

These combinations can share one query:

Combination in one query	Notes
Raw-row metrics with fixed fields only as groups or filters	For example walkthrough question 1, buyers and payments grouped into once-only and repeat buyers
Reaggregation of values with the same fixed grouping fields	Each fixed group contributes once within each display group
Several fixed groupings only displayed or filtered	Only one of them may supply reaggregated values
Ordinary, exclude/fixed and compose metrics on the same fact	For example walkthrough question 4
Several include metrics	Same base datasets, and the same declared dimensions in the same order
LOD metrics with another fact's metrics	Each fact aggregates in its own branch, a LOD over its own fact's rows, filters and dates; the results align on the grouping fields

These combinations need a different formulation or separate queries. When splitting, give every query the same grouping, context and time range, then align results on the grouping fields, so each part keeps the same business population and denominators.

Combination in one query	What to do
Fixed-value reaggregation plus raw-row metrics	Split in two: one query for metrics reaggregating fixed values, one for raw-row metrics
Reaggregation of two different fixed groupings	One query per fixed grouping
Include mixed with ordinary metrics of a fact its base reads, exclude/fixed metrics or LOD fields	Query the include metrics alone and the rest separately, as walkthrough question 3 does
Exclude/fixed metrics mixed with LOD fields read, grouped or filtered	Split into two queries
Keyed exclude fields or metric fixed keys absent from query grouping (lod_dimension_not_grouped)	Add those fields to group_by: an exclude's at any grain, a fixed metric's unchanged with no time grain. To roll a fixed result up to a coarser grouping, use a fixed field and an ordinary aggregate
LOD with window metrics or time_ranges	Run them separately; for a multi-period comparison, one query per period
A fixed field of a dataset the metric only joins, as a group or a condition	Query a metric of that dataset, or declare the fixed field on the metric's own dataset
LOD nested in base or grouping dependencies, or used as a window base	No alternative today; LOD cannot nest
LOD values in ordinary relationship join columns or time axes	No alternative today
Attribution of LOD metrics, conditions or dimensions	Unsupported. Discovery marks LOD metrics non-attributable and may skip LOD dimensions with a warning

Illegal declaration locations, missing bases, unqualified metric dimensions, ambiguous correspondence and nesting are authoring errors. Unsupported query combinations may return not_implemented, fan_out_risk or another specific code; they are not all the same error. A display dimension colliding with a different fixed key's output name returns duplicate_output_name.

Without grouping, empty-input aggregation returns one row: COUNT is 0 and other aggregates are NULL. With grouping, empty input produces no result groups. An outer COUNT counts non-NULL intermediate values, not necessarily source records. LOD does not synthesize calendar rows or missing entities.

Validation, versions and compatibility

Validate the model in datus mode, then compile and execute intended queries. Check unequal record counts per entity, missing matches, filter placement, date boundaries and zero denominators. Compare with independent raw SQL, then vary data and parameters to test the calculation rules.

dosi validate --osi-datus checks structure and compilation. --strict also fails on authoring-lint warnings, including field declarations, but does not treat every compilation warning as an error. dosi info shows the running engine's extension capabilities.

Declare real primary keys, relationship columns and dimension roles. Time routing requires dimension.is_time: true. For a technical field that should not be recommended as an analysis dimension, put is_dimension: false in that field's DATUS JSON; true explicitly recommends it. This does not prohibit an explicitly requested grouping. A core dimension object alone does not clear every role warning: an identifier that is neither a key nor a relationship column may still produce underdeclared_field. Declare its actual role rather than inventing uniqueness. Fixed or LOD-dependent fields cannot be time axes. Generic relationship validation may also report a non-unique target for an intentional fact pairing; do not invent primary keys to silence that warning.

The document shape Dosi accepts

Dosi requires root version: "0.2.0.dev0" and one model per document, in either of two shapes: a semantic_model list with one entry holding the model's name, datasets, relationships and metrics, or those fields directly at the root. This engine rejects version: "0.1.1".

Upstream 0.2.0.dev0 is a mutable development specification: newer snapshots moved the model to the root without changing the version string. Until an upstream release ships that change, prefer the list shape, which the 0.2.0.dev0 schema validates. See One model per document.

Basic mode does not perform the same LOD calculation

Basic mode ignores DATUS extensions with warnings. An aggregate field without active LOD metadata is dropped with aggregate_field_dropped, and references fail. Nested aggregate representations of LOD metrics and their compositions also fail closed. Basic mode does not provide equivalent results.

Shared interfaces are documented in the semantics contract, extension envelope, CLI reference, detail queries and errors.