Model your data

Every query in a data app and every answer from an agent goes through the semantic layer: data models that map your tables, and datasets that join them and define metrics. Model first, then build on top. A number defined once here means the same thing on every page that uses it.

This page walks through modeling a small sales dataset from three tables: orders, order_items and products.

1. Start from the questionsCopy Anchor Link

Write down the questions the app must answer, such as "revenue by month" or "average order value by order status". Each quantity becomes a metric (revenue, average order value), and each "by …" becomes a dimension (month, status).

Model only what those questions need, and add more when the next app needs it.

2. Map each table to a data modelCopy Anchor Link

Create one model per table or view, in models/. Keep it close to the schema: a dimension for each column you want to query, with the column's type.

Model orders {
  type: 'table'
  label: 'Orders'
  data_source_name: 'warehouse'
  table_name: '"public"."orders"'

  dimension id {
    label: 'Id'
    type: 'number'
    definition: @sql {{ #SOURCE.id }};;
  }
  dimension status {
    label: 'Status'
    type: 'text'
    definition: @sql {{ #SOURCE.status }};;
  }
  dimension created_at {
    label: 'Created At'
    type: 'datetime'
    definition: @sql {{ #SOURCE.created_at }};;
  }
}

order_items and products follow the same pattern, with order_id, product_id and quantity on one and id, name and price on the other.

• data_source_name is a connection from .anfra/data_sources.yml.
• Every dimension needs a label. Labels and descriptions are what agents and readers see, so write them for people.
• Include the key columns used for joins, even ones no one will group by.

3. Join models in a datasetCopy Anchor Link

Create a dataset in datasets/ that lists the models and the relationships between them. Apps query a dataset by name, so make one for each business area, such as sales or marketing, not one per app.

Dataset sales {
  label: 'Sales'
  data_source_name: 'warehouse'
  models: [orders, order_items, products]
  relationships: [
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true)
  ]
}

In relationship(a > b, true), a is the many side and b the one side: many order items belong to one order. true marks the relationship as active.

Get the direction right. anfra uses it to pick the join and to compute each metric at the right level, so a count of orders is not multiplied by the number of items in each order.

4. Define metrics onceCopy Anchor Link

Add metrics to the dataset. A metric can use fields from any model in the dataset, and it can build on other metrics.

Dataset sales {
  // models and relationships as above

  metric revenue {
    label: 'Revenue'
    type: 'number'
    description: 'Sum of quantity × list price across all order items.'
    format: '[$$]#,###0.00'
    definition: @aql sum(order_items.quantity * products.price);;
  }
  metric order_count {
    label: 'Orders'
    type: 'number'
    definition: @aql count(orders.id);;
  }
  metric average_order_value {
    label: 'Average Order Value'
    type: 'number'
    format: '[$$]#,###0.00'
    definition: @aql revenue / order_count;;
  }
}

• Build ratios from other metrics, as average_order_value does. Never compute them in app JavaScript, where every app would need its own copy.
• Write the description for each metric. It is how a reader, or an agent, knows what "revenue" includes.
• Use a measure on a data model only when the aggregation needs nothing but that model's own fields. See Metrics vs. measures.

5. Check the dimensions you group byCopy Anchor Link

The dimensions on your models are already available through the dataset, so orders.status and orders.created_at can be used straight away. A date or datetime dimension supports every time grain, so a query asks for orders.created_at | month() without a separate "month" field.

Add a dimension only when a question needs a value that isn't a column yet, such as a customer segment derived from several columns. See Dimensions.

6. Validate and testCopy Anchor Link

Check the model before building an app on it:

# The AML compiles and type-checks
anfra validate

# The datasets, fields and metrics you expect are there
anfra show

# Read the SQL anfra generates
anfra query -d sales -g -a 'explore {
  dimensions { status: orders.status }
  measures { revenue: revenue, orders: order_count }
}'

# Run it
anfra query -d sales -a 'explore {
  dimensions { month: orders.created_at | month() }
  measures { revenue: revenue, orders: order_count, aov: average_order_value }
}'

Read the generated SQL for each new metric: check the joins, and check that each aggregate runs on the table it should. Then compare one number with a figure you already trust, such as last month's revenue in your finance report.

Keep the model and the apps in syncCopy Anchor Link

When an app needs a number that doesn't exist yet, add it to the dataset and use it from the app. Don't compute it in the page. While anfra serve runs, apps reload with the new datasets as soon as an .aml file changes.

Models and datasets are plain files in Git, so review a metric change like any other code change. See Version control with Git.

Next stepsCopy Anchor Link