Define calculations and key performance indicators in your semantic layer
country
, status
).SUM(amount)
defined as a measure named total_amount
, COUNT(user_id)
defined as user_count
). They form the basic building blocks for quantitative analysis.total_revenue / unique_users
, ratio_of_completed_orders
). They encapsulate reusable business definitions.expr
), and a description.
Field | Required | Description |
---|---|---|
name | Yes | Unique name for the metric within the model. |
expr | Yes | The SQL-like expression defining the metric’s calculation. Can reference measures, dimensions, and arguments. |
description | Yes | A human-readable description of the metric. This is mandatory. |
args | No | A list of Argument objects to parameterize the metric’s expression. Defaults to an empty list if omitted. |
expr
expr
field is where you define the metric’s calculation using SQL syntax compatible with your data warehouse.
amount
, user_id
).SUM
, AVG
, COUNT
, COUNT DISTINCT
, MIN
, MAX
, etc.+
, -
, *
, /
), logical (AND
, OR
, NOT
), comparison (=
, >
, <
, etc.) are supported.CASE
, COALESCE
, NULLIF
, date functions, string functions, etc., can be used.expr
, you typically don’t need a table prefix (e.g., SUM(amount)
is usually sufficient if amount
is a measure in the current model). However, using prefixes (e.g., SUM(orders.amount)
) can enhance clarity, especially in complex models. See Best Practices for more on prefixes when using related models.args
field and reference them in the expr
using double curly braces {{parameter_name}}
.
The args
list contains Argument
objects. See the Semantic Model Reference for the full specification of Argument
fields (name
, type
, description
).
The Argument
object specification also includes an optional default
field.
semantic-models.mdx
does not include a default
field for arguments. Parameter values must be provided at query time.related_model_name.field_name
). This requires an relationship
to be defined between the models involved. See the Semantic Model Reference for details on defining relationships. Note that relationships
is the keyword used in the YAML, though it conceptually represents the entity connections between models.
Example: Cross-Model Metric
description
.customer.id
, orders.amount
) in your expr
when referencing fields from related models (via relationships) to prevent ambiguity.()
to ensure correct operator precedence.NULLIF(denominator, 0)
. Example: SUM(profit) / NULLIF(SUM(revenue), 0)
.COALESCE
or other relevant SQL functions to handle them appropriately within calculations, especially when dealing with outer joins.type
for each parameter (string
, number
, date
, boolean
). This enables validation and correct query generation.args
list includes the required name
, type
, and description
fields.default
values for optional parameters.description
for each parameter.expr
using double curly braces: {{parameter_name}}
.expr
references fields from a related model (e.g., customer.id
), the query using that metric must explicitly include the necessary join to that related model via its defined relationship
.expr
. It validates that joins used in a query correspond to defined relationships
.CASE
statements or check for nulls on fields from those models to make the metric resilient to missing data.orders
model context, incorporating various metric types and practices: