13. Modeling patterns

A data model in OGML is a graph: collections (raw data, imports, settings), views that read them and each other, and the queries of reports reading the views.

collection data_ventes ─┐
collection data_prix ───┼─► view sales.rows ─► view sales.margins (materialized) ─► reports
collection settings ────┘

13.1 Layers

Keep each view small and named after what it holds; stack them with on view.

13.2 Clean the raw data once

define view production.rows | exec
  | on data_production
  | where day? != null                                    -- empty spreadsheet rows have no day
  | derive date = date_format(day, "%F"), week = date_format(day, "%Y-W%V"), weight = to_float(weight)
  | select date, week, site, product, units, weight
| end
| end

• Guard every field that some rows may lack, with ?.
• Convert once (to_float, to_string, date_format), so the layers above never repeat it.
• select the columns the model needs: later views and caches stay small.

13.3 Reference data and classification

-- Exact match, one row expected
on view sales.rows as s
| lookup prices as p on p.article == s.article into p
| derive cost = if(len(p) == 1, get(at(p, 0), "cost", null), null)
| drop p

-- Longest prefix wins (chart of accounts)
on ledger as a
| lookup rules as r | where starts_with(a.account, r.prefix) | derive n = len(prefix) | sort n desc | first | into rule | end
| derive category = get(coalesce(at(rule, 0), {}), "category", "unclassified")

-- A small fixed table, in the query
| lookup values [{code: "A", label: "Retail"}, {code: "B", label: "Wholesale"}] as f on code == family into f

13.4 Stacking sources

-- Actual and budget side by side, aligned on the same fields
on view ledger.actual | select month, account, amount, scenario
| union | on view ledger.budget | select month, account, amount, scenario | end

-- Several sites, one model
on data_site_a | derive site = "A" | union | on data_site_b | derive site = "B" | end

13.5 Time series

-- Complete the months, then compare with last year
on view sales.monthly
| fill month from $from to $to step "month" by product with amount = 0
| window | partition by product | order by month | lag amount 12 as last_year | cumsum amount as ytd | end
| derive growth = div(amount - coalesce(last_year, 0), last_year, null)

13.6 Settings read by a view

define view ledger.balances | exec
  | let $s = (on ledger_settings | limit 1)
  | let $opening ?= get(coalesce(at($s, 0), {}), "opening", 1)
  | on ledger | where fiscal_month(date, $opening) <= $month
| end
| end

A let query reads the Place’s own settings at each read; ?= lets a caller override them.

13.7 Report views with parameters

define view sales.report | exec
  | let $gran ?= "month"
  | on view sales.by_month                          -- materialized, no parameter
  | where month >= $from and month <= $to
  | derive key = if($gran == "year", substr(month, 0, 4), month)
  | group | by key, region | sum total | end
| end
| end

Constant conditions fold away once parameters are bound (5.5), so a switch like $gran costs nothing.

13.8 Checklist

• Every field read by a filter exists on every row, or is read with ?.
• Costly shapes read by several queries are materialized; parameters live in the thin views above them.
• Joins expected to match once are checked (len(p) == 1) and fall back explicitly.
• Dates are one shape per field (ISO text of one length, or epoch milliseconds).
• Views are named by layer and subject (sales.rows, sales.by_week).
• explain on view … shows filters reaching the source.