The Excel template
The workbook that describes your model: every sheet and column, how upload works.
Download it from Changes & versions: blank, or filled with any version (an export). Sheets:
| Sheet | One row per |
|---|---|
| Instructions | How to fill it in. |
| Model | A model property (name, version, standards, what each sensitivity class means...). |
| Entities | Entity. |
| Fields | Field (attribute or calculated field) of an entity. |
| Values | Allowed value of a field (value, label, note). |
| Filters | Named filter of an entity. |
| Checks | Data-quality check of an entity. |
| Relationships | Relationship (ids can be left empty: they are numbered automatically). |
| Examples | Query example (ids can be left empty). |
Conventions: required columns have a red header; every header has a help comment; closed lists are dropdowns; list cells separate items with | ; lifecycle states are one line per status column; parameters of examples are JSON. Any column the template does not define is a custom property (grey header in exports).
Upload (modeller role): the file (up to 20 MB) is first checked without creating anything. The report gives every problem with sheet, row and column, plus counts of what would be created. Only a file without errors becomes a draft; an existing draft is replaced only when you choose to. Export and re-import lose nothing.
An export by someone without the restricted entitlement leaves out the examples of restricted fields.
A minimal workbook#
You do not need every column to start. This is the smallest model that uploads without a single error: two entities, their fields, one filter and one relationship. It has been checked against the upload report.
Sheets. A workbook must contain all nine sheets, in any order, even the ones you leave empty: Instructions, Model, Entities, Fields, Values, Filters, Checks, Relationships, Examples. Do not rename or delete them. Easiest: download the blank template and fill it in.
Model (row 1 is a header; the model's name is required):
| setting | value |
|---|---|
| name | Shop |
Entities (required columns only, plus a definition):
| key | name | domain | kind | semantic_table | business_key | system_of_record | definition |
|---|---|---|---|---|---|---|---|
| customer | Customer | Sales | master | public.customers | customer_no | CRM | A person or company that buys from us. |
| order | Order | Sales | transaction | public.orders | order_no | Shop | One purchase by a customer. |
Fields (required columns, plus a description and, on the foreign key, the entity it points to):
| entity_key | column | role | business_name | type | sensitivity | description | references |
|---|---|---|---|---|---|---|---|
| customer | id | primary_key | Customer id | integer | internal | ||
| customer | customer_no | attribute | Customer number | text | internal | What people quote when they call us. | |
| customer | attribute | text | personal | ||||
| order | id | primary_key | Order id | integer | internal | ||
| order | order_no | attribute | Order number | text | internal | ||
| order | customer_id | foreign_key | Customer | integer | internal | Customer | |
| order | total | fact_measure | Order total | decimal | confidential | Total in EUR. | |
| order | closed_at | attribute | Closed at | timestamp | internal | Empty while the order is open. |
Filters:
| entity_key | name | filter | synonyms | status |
|---|---|---|---|---|
| order | open order | closed_at IS NULL | not closed \ | in progress |
Relationships (note: from and to use entity names, not keys):
| from | predicate | to | cardinality | join | status |
|---|---|---|---|---|---|
| Order | is placed by | Customer | many-to-one | order.customer_id = customer.id | confirmed |
Values, Checks and Examples can have just their header row (entity_key, column, value, label; entity_key, id, rule; title, sql).
Upload it, read the report, and you have a draft. Then add what makes the model valuable: meanings, allowed values, more filters, calculated fields, checks and worked examples.
Keys or names? entity_key, applies_to and an entity's subtype_of use the entity key (customer). A field's references, and from and to in Relationships, use the entity name (Customer).
Column reference#
Every sheet, every column, taken from the template itself. Required columns have a red header in the workbook; the others are optional. Closed lists are dropdowns.
Entities#
| Column | Needed | Meaning |
|---|---|---|
key | required | Short lower_snake_case id, used everywhere else in this workbook (customer, order_line). |
name | required | Business name shown to people and agents. |
domain | required | Business domain: Sales, Finance, Operations... |
kind | required | What sort of concept it is. One of: master, reference, transaction, fact, relationship, bridge, agreement, supertype, derived, technical. |
definition | optional | One or two sentences: what one row of this entity is. |
semantic_table | required | schema.table in the database that holds it. |
primary_key | optional | Column that identifies a row. |
business_key | required | Column(s) people use to identify it (customer number, order number). |
system_of_record | required | The system that owns the data. |
synonyms | optional | Other words for it, separated by " \ |
not_to_confuse_with | optional | Things it is often mistaken for, with the difference. Separated by " \ |
belongs_to | optional | Parent concept, if any. |
subtype_of | optional | Entity key of the supertype, if any. |
model_status | optional | How settled this entity is in the model. One of: confirmed, new, placeholder. |
standard_resource | optional | Optional. Name of the matching concept in an industry standard you follow. |
standard_reference | optional | Optional. Which standard / document it comes from. |
standard_detail | optional | Optional. Any further reference (API, section). |
lifecycle_states | optional | One line per status column: "status: draft > active > closed". |
example_questions | optional | Typical questions this entity answers. Separated by " \ |
Fields#
| Column | Needed | Meaning |
|---|---|---|
entity_key | required | Entity this field belongs to. |
column | required | Column name in the entity's table; for a calculated field, a short code. |
role | required | primary_key / foreign_key / attribute ... or calculated: a value computed from the data, not a column. One of: attribute, primary_key, foreign_key, alternate_reference, dimension, fact_measure, derived_attribute, lineage, calculated. |
business_name | required | Name people use. |
type | required | Data type. One of: text, integer, decimal, boolean, date, timestamp, text[]. |
description | optional | What the value means (for a calculated field: how it is computed, in words). |
sensitivity | required | internal / confidential / personal / restricted. One of: internal, confidential, personal, restricted. |
agent_access | optional | full, masked (shown as a stable hash to callers without the class) or none. Default: none for restricted, otherwise full. One of: full, masked, none. |
review_status | optional | How settled the definition is. Default needs_review. One of: confirmed_by_team, by_design, high_confidence, needs_review, source_needed. |
references | optional | For foreign keys: the entity NAME it points to. |
format | optional | Pattern, e.g. two letters + 6 digits. |
example | optional | A sample value (never a restricted one). |
business_term | optional | Glossary name of this field. |
business_term_description | optional | Glossary definition. |
standard_name | optional | Optional. The name in the industry standard you follow. |
unit | optional | Unit of the value: count, EUR, hours... |
sql | optional | Calculated fields: optional SQL that computes the value. Context for agents, never run by the app. |
Values#
| Column | Needed | Meaning |
|---|---|---|
entity_key | required | Entity of the field. |
column | required | Field whose values these are. |
value | required | An allowed value (the code stored in the column). |
label | optional | What it reads as to people. |
note | optional | Anything worth knowing about this value. |
Filters#
| Column | Needed | Meaning |
|---|---|---|
entity_key | required | Entity the filter applies to. |
name | required | The words people use: open order, unpaid invoice. |
filter | required | SQL condition on the entity's table, columns unqualified (closed_at IS NULL, amount_due > 0). |
synonyms | optional | Other ways to say it. Separated by " \ |
description | optional | What it means. |
status | optional | governed (agreed) or working (not confirmed yet). Default working. One of: governed, working. |
source | optional | Where the definition comes from. |
Checks#
| Column | Needed | Meaning |
|---|---|---|
entity_key | required | Entity the check is about. |
id | required | Check id, unique per entity (DQ01). |
rule | required | The data quality rule in words. |
sql | optional | Optional SQL returning one column n = number of violations (must be 0); run by the Model check. |
note | optional | Caveats. |
Relationships#
| Column | Needed | Meaning |
|---|---|---|
id | optional | R-001... Leave empty to number automatically. |
from | required | Entity NAME (as in the Entities sheet) where the link starts. |
predicate | required | The verb: places, belongs to, is billed on. |
to | required | Entity NAME where it ends. |
cardinality | required | many-to-one... One of: many-to-one, one-to-many, one-to-one, many-to-many, many-to-zero-or-one (optional), subtype-of. |
join | required | Exact join: a.col = b.col |
status | required | confirmed, optional (may be empty) or unreliable (often empty or wrong). One of: confirmed, optional, unreliable. |
status_detail | optional | Why it has that status. |
sentence | optional | The link as a sentence. Default: built from the names. |
note | optional | Caveats. |
Examples#
| Column | Needed | Meaning |
|---|---|---|
id | optional | EX01, EX02... Leave empty to number automatically. |
title | required | The question the example answers. |
body | optional | A note on the path it takes or what to watch for. |
sql | required | The query, with :parameters. |
params | optional | {"customer_id": "customer"} parameter -> entity key. |
sample_sql | optional | A query returning one row of parameter values, so the example can be tried on real data. |
applies_to | optional | Entity keys this is about. Separated by " \ |
topic | optional | Optional grouping. |
source | optional | Where it comes from. |
Model#
The Model sheet is a list of settings, one per row (name in the first column, value in the second).
| Row | Meaning |
|---|---|
name | Name of the ontology |
id | Short id |
version | Version number |
date | Date |
source | Where it comes from |
status | Review status text |
standards | Standards followed, separated by | |
access.internal | What internal means in your organisation |
access.confidential | What confidential means |
access.personal | What personal means |
access.restricted | What restricted means |
access.row_level | The row-level rule, in words (documentation only: row-level security is not enforced) |
how_to_read | Reading notes, separated by | |