Understand LOD — calculate at one grain, display at another

LOD handles calculations that first group records one way, then use those results in a query grouped another way. It lets you define an intermediate calculation separately from the final presentation.

This page explains the concepts and choices, summarizes the key limits, and runs a first query with a complete model before explaining filter scope. When you write a model, look up individual rules in the LOD authoring reference. A complete runnable case is the commerce walkthrough.

What is LOD?

LOD stands for Level of Detail. For a metric calculation, it describes what you group by and how detailed that grouping is. This is the calculation's grain.

Consider a small question: how much has each paying user acquired through ads spent on average? In the walkthrough's data through May 31, 2030, that channel has two buyers:

Buyer	Orders	Lifetime spending
U01	4	CNY 500
U04	1	CNY 300

The answer is (500 + 300) / 2 = 400. First total spending per buyer, then average those two totals. U01 must not contribute more times to the average simply because they placed more orders.

There are two groupings here: calculate per buyer, then display per acquisition channel. The first is the calculation grain; the second is the display grain. Grouping fields are also called dimensions. The query's group_by controls the display; an LOD declaration controls the intermediate calculation that needs its own grouping.

In Dosi, you declare LOD in the semantic model. Queries still select metrics and grouping fields; Dosi generates SQL for the calculation stages. LOD is a DATUS extension to Apache Ossie (formerly OSI). The database does not need a function named LOD.

When do you need LOD?

Ordinary aggregation usually suffices when you only need a sum, count or average at the query's grouping. Consider LOD for these requirements:

• Classify or filter by an aggregate. Count purchases per buyer before classifying buyers as once-only or repeat buyers.
• Aggregate intermediate results. Total spending per buyer before averaging buyers within each acquisition channel.
• Compare results at different groupings or scopes. Display daily app sales alongside daily site sales or the app's monthly total.

Before choosing an expression, state which records enter the calculation and how many times each entity contributes. Returning a number is not sufficient to establish that it answers the business question.

Choose among four uses

First decide whether you need a reusable field or a metric returned by the query. A field can be classified, filtered, projected into detail rows or aggregated again. A metric is a statistical result selected by a query.

What you need	Use	Declaration in the model (commerce walkthrough)
An aggregate per entity that you can use as a field	fixed field	fixed(paid_total, orders.user_id): each buyer's lifetime payments
A finer calculation within each display group, followed by an average, maximum or other aggregate	include metric	AVG(include(order_count, orders.user_id)): count orders per buyer, then average
A coarser subtotal within the selected records	exclude metric	exclude(paid_total, orders.paid_date): remove date to get the channel's period total
A baseline grouped by specified fields, unaffected by ordinary display filters	fixed metric	fixed(paid_total, orders.paid_date): daily whole-site sales

The first argument of a declaration is an existing metric, called the base metric: here paid_total is the payment total and order_count is the order count. The remaining arguments are grouping fields.

fixed appears twice because the result has different uses. Buyer lifetime spending needs to be a field that another metric averages. Daily site sales can be a metric displayed next to daily app sales. Decide how you will use the result before choosing a fixed field or a fixed metric.

include means calculate at a finer grouping first: to show average purchase frequency by channel, count orders per buyer within each channel, then average. exclude means remove some grouping: when displaying channel and date, remove date to obtain the channel total for the selected period.

These definitions can share one model. They cannot necessarily share one query; see the combination limits.

Check the limits before designing a query

LOD definitions can coexist in one model even when a query cannot combine them. Keep these limits in mind before choosing report columns:

• No nested LOD. A base metric and its grouping keys cannot depend on another LOD value, even indirectly.
• Include metrics form a separate query from their own fact's other metrics. They cannot share a query with that fact's ordinary metrics, exclude/fixed metrics or fields that read LOD; another fact's metrics are fine. Include metrics queried together need the same base dataset and ordered dimension list.
• Re-aggregating fixed fields changes the counting unit. Query these metrics separately from raw-row metrics; only one fixed-key family can supply re-aggregated values in a query. Using a fixed field only for grouping or filtering still permits ordinary metrics.
• A keyed fixed metric requires its exact keys in group_by. A time grain such as :day is not the same key. To aggregate fixed results to a coarser grouping, use a fixed field and an ordinary aggregate.
• Window metrics and time_ranges cannot be combined with LOD. Execute separate queries. Exclude/fixed metrics also cannot share a query with LOD fields. Metrics of another fact table can share a query with any LOD metric: each fact aggregates on its own and the results align on the grouping fields.
• Use datus mode. Basic mode does not execute an equivalent LOD calculation. LOD also does not automatically fill missing dates or entities.

The complete limits and alternatives distinguish unsupported combinations from supported ones.

Run a minimal model

Start with two buyers and three orders. Save this SQL as lod-minimal-seed.sql:

CREATE TABLE orders (
  order_id VARCHAR PRIMARY KEY,
  user_id VARCHAR,
  paid_amount DECIMAL(12,2)
);
INSERT INTO orders VALUES
  ('O01', 'U01', 100),
  ('O02', 'U01', 300),
  ('O03', 'U02', 200);

Save the following complete model as lod-minimal.yaml. It needs no other model files. paid_total sums payments; buyer_lifetime_paid fixes that sum by buyer; mean_lifetime_paid averages the resulting buyer values.

version: 0.2.0.dev0
semantic_model:
- name: lod_minimal
  datasets:
  - name: orders
    source: orders
    primary_key:
    - order_id
    fields:
    - name: order_id
      expression:
        dialects:
        - dialect: ANSI_SQL
          expression: order_id
      datatype: String
      custom_extensions:
      - vendor_name: DATUS
        data: '{"v":"1.11","is_dimension":false}'
    - name: user_id
      expression:
        dialects:
        - dialect: ANSI_SQL
          expression: user_id
      datatype: String
      custom_extensions:
      - vendor_name: DATUS
        data: '{"v":"1.11","is_dimension":false}'
    - name: paid_amount
      expression:
        dialects:
        - dialect: ANSI_SQL
          expression: paid_amount
      datatype: Decimal
      custom_extensions:
      - vendor_name: DATUS
        data: '{"v":"1.11","is_dimension":false}'
    - 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)"}}'
  metrics:
  - name: paid_total
    expression:
      dialects:
      - dialect: ANSI_SQL
        expression: SUM(orders.paid_amount)
  - name: mean_lifetime_paid
    expression:
      dialects:
      - dialect: ANSI_SQL
        expression: AVG(orders.buyer_lifetime_paid)

With Dosi and the DuckDB CLI installed, run these commands in the directory containing both files. Use a new database file:

duckdb lod-minimal.duckdb < lod-minimal-seed.sql
dosi validate --model lod-minimal.yaml --strict
dosi query --model lod-minimal.yaml --metrics mean_lifetime_paid \
  --execute --db lod-minimal.duckdb --format json

The returned mean_lifetime_paid is 300: U01 contributes 400 once and U02 contributes 200 once, so (400 + 200) / 2 = 300. Averaging the three raw order amounts would return 200 and answer a different question. Even though the fixed value is available on each order, its re-aggregation counts each buyer once.

The fixed declaration belongs to the field's custom_extensions; its first argument names the existing base metric. The field's ordinary expression repeats that base metric's complete SQL. data is a JSON-encoded string.

For include, exclude and fixed metrics, put the declaration under derive: {type: "lod", expr: ...} in the metric's DATUS JSON. Include's core SQL wraps the base aggregate in the declared outer aggregate; exclude/fixed metrics use a SUM wrapper, even if their base is COUNT or MAX. For example, fixed(paid_total, orders.paid_date) uses SUM(SUM(orders.paid_amount)). Over a compose, ratio or expression base the wrapper holds the base's whole expression, as in SUM(COALESCE(SUM(orders.amount), 0) - COALESCE(SUM(refunds.amount), 0)) for exclude(net_revenue). This nested SQL represents calculation stages for Dosi to validate and lower; it is not SQL to execute directly against the database. See the expression rules and the commerce model for complete examples of these forms.

Filters decide which records enter the calculation

Grouping determines how records are aggregated; filters determine which records participate. Fixed means a fixed grouping, not unconditional access to all history.

A query has two kinds of conditions:

• Context (context_filter, CLI --context-filter) and the time range (time_range) restrict the calculation input, including fixed calculations. Each calculation reads them on the dataset it aggregates: the time range lands on that dataset's own time field, and a dataset without one is read whole, with a warning. A context_filter condition applies where that dataset can read it through a declared relationship or key pairing; one it cannot read is skipped and reported in the result's warnings. Define a period that differs from the query's in its base metric (cross-table rules).
• Ordinary filters (where_sql, CLI --where) select which rows are displayed. They narrow ordinary metrics and include/exclude inputs, but not the input of fixed.

In the app sales report, April 2, 2030 has CNY 160 of app sales and CNY 500 of site sales. App sales for all of April total CNY 570. One report row needs these three scopes:

Result	Records it needs	Use
Daily app sales: 160	App orders on April 2	Ordinary payment metric displayed by date and channel
Daily site sales: 500	All channels' orders on April 2	Metric fixed by date, preserving the whole-site scope
App monthly total: 570	App orders throughout April	Exclude date while retaining channel

The query supplies April as its time range and app as an ordinary display filter. The time range scopes all three calculations. The app filter scopes the daily numerator and channel subtotal, but not the fixed daily site baseline. App's share of that day's site sales is therefore 160 / 500 = 0.32. The walkthrough's question 4 runs this query and shows its results.

Choose a filter location by its purpose:

Purpose	Location	Effect on a fixed calculation
Restrict the records or time used for calculation	context_filter, time_range	Restricts the input; the time range lands on each dataset's own time field, a condition only where that dataset can read it
Select ordinary field values to display	where_sql	Does not narrow the fixed input; does narrow include/exclude inputs
Select by a computed fixed value or selected metric	where_sql	Runs after the corresponding value has been computed

The last two rows both use where_sql and need no extra marking. Dosi decides when a condition runs from the fields it references: a condition on ordinary fields selects display records before calculation; a condition on a fixed field or a selected metric runs after those values exist. For example, orders.channel = 'app' is the first kind and orders.buyer_order_count >= 2 is the second.

Restrictions in a dataset's source always apply. Define independent periods through parameters. See detailed filter rules for mixed AND/OR predicates, related dimensions and time encodings.

Turn a business question into a model

Write down these five decisions in ordinary language before choosing syntax:

1. What does one source row represent? Declare actual primary keys. A repeating identifier must not be declared unique merely to pass validation.
2. Which entities belong in the result? Distinguish no activity from being outside the population. Decide whether missing records mean zero or NULL.
3. What do you calculate first, and what do you display? Prefer a fixed field when the intermediate value will be classified, filtered or aggregated.
4. How often should each entity contribute? Per-person, per-record and per-day averages have different denominators. Define each side of a ratio.
5. Which conditions affect calculation, and which select results? Define separate observation windows and explicit key mappings for other datasets.

Choose the simplest expression that preserves these decisions. A total divided by an entity count equals an average over entities only when populations and weights agree. Check the model against independent raw-data calculations, then change the data and query parameters and check again.

Next steps

• Run the commerce walkthrough: one model, five questions, with expected results to compare against.
• While writing a model, use the LOD authoring reference: base-metric requirements, declaration format, parameterized windows, cross-dataset calculations, which query combinations must be split, and validation.