Metrics
Define calculations and key performance indicators in your semantic layer
Introduction to Metrics
Metrics are calculations based on measures or dimensions within your semantic models. They represent key business performance indicators (KPIs) and other frequently used calculations, encapsulating business logic centrally.
Here’s the basic structure for defining a metric:
Metrics vs. Measures
It’s helpful to distinguish Metrics from Measures (and Dimensions):
- Dimensions: Describe attributes used for grouping or filtering (e.g.,
country
,status
). - Measures: Represent direct, aggregatable calculations on a single column, often corresponding to a raw data field (e.g.,
SUM(amount)
defined as a measure namedtotal_amount
,COUNT(user_id)
defined asuser_count
). They form the basic building blocks for quantitative analysis. - Metrics: Define more complex calculations, business logic, or Key Performance Indicators (KPIs) that often combine multiple measures, dimensions, or involve conditional logic (e.g.,
total_revenue / unique_users
,ratio_of_completed_orders
). They encapsulate reusable business definitions.
Guideline:
- If you need a simple, direct aggregation of a column (like sum, count, average), define it as a Measure.
- If you need to define a specific business KPI, a ratio, a calculation involving multiple steps or measures, or a parameterized calculation, define it as a Metric.
Defining Basic Metrics
At their core, metrics combine a name, a calculation expression (expr
), and a description.
Metric Fields
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. |
Writing the expr
The expr
field is where you define the metric’s calculation using SQL syntax compatible with your data warehouse.
- Referencing Fields: You can directly reference dimensions and measures defined within the same model (e.g.,
amount
,user_id
). - Aggregations: Use standard SQL aggregation functions:
SUM
,AVG
,COUNT
,COUNT DISTINCT
,MIN
,MAX
, etc. - Operators: Arithmetic (
+
,-
,*
,/
), logical (AND
,OR
,NOT
), comparison (=
,>
,<
, etc.) are supported. - Functions: Most standard SQL functions like
CASE
,COALESCE
,NULLIF
, date functions, string functions, etc., can be used.
When referencing fields within the same model in the 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.
Example: Basic Metric
Advanced Metric Types
Beyond basic calculations, you can create more dynamic and interconnected metrics.
Parameterized Metrics
Create flexible metrics that accept user inputs at query time. Define parameters using the 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.
The specification in semantic-models.mdx
does not include a default
field for arguments. Parameter values must be provided at query time.
Example: Parameterized Metric
Cross-Model Metrics
Reference fields (dimensions or measures) from related models using dot notation (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
Using cross-model references requires careful consideration of joins when querying. See “Handling Joins and Related Models” below.
Best Practices & Considerations
Follow these guidelines to create robust, understandable, and maintainable metrics.
General Best Practices
Business Focus
Define metrics that represent meaningful business KPIs, translating raw data into actionable insights.
Clear Naming & Descriptions
Use consistent, descriptive names and provide mandatory, clear descriptions explaining the calculation and business purpose.
Leverage Existing Components
Build metrics by referencing existing measures and dimensions within the model rather than duplicating complex logic.
Document Dependencies
If a metric relies on parameters or data from related models (via relationships), clearly state these requirements in its description
.
Expression Best Practices
- Type Consistency: Ensure metric expressions resolve to appropriate data types (usually numeric) suitable for aggregation or further calculations.
- Use Table Prefixes for Related Models: Always use table-qualified names (e.g.,
customer.id
,orders.amount
) in yourexpr
when referencing fields from related models (via relationships) to prevent ambiguity. - SQL Conventions:
- Wrap complex expressions or calculations in parentheses
()
to ensure correct operator precedence. - Handle potential division-by-zero errors using
NULLIF(denominator, 0)
. Example:SUM(profit) / NULLIF(SUM(revenue), 0)
. - Be mindful of nulls. Use
COALESCE
or other relevant SQL functions to handle them appropriately within calculations, especially when dealing with outer joins.
- Wrap complex expressions or calculations in parentheses
- Avoid Circular References: Do not define metrics that refer back to themselves directly or indirectly.
Good Expression Example (Parameterized):
Parameter Best Practices
- Explicit Typing: Always define the
type
for each parameter (string
,number
,date
,boolean
). This enables validation and correct query generation. - Argument Fields: Ensure each argument in the
args
list includes the requiredname
,type
, anddescription
fields. - Defaults: Provide
default
values for optional parameters. - Clear Documentation: Provide a meaningful
description
for each parameter. - Placeholders: Reference parameters in the
expr
using double curly braces:{{parameter_name}}
. - Type Handling:
Handling Joins and Related Models
- Primary Association: Each metric belongs primarily to the model where it’s defined.
- Explicit Joins Required in Query: If a metric’s
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 definedrelationship
. - No Automatic Joins: The semantic layer does not automatically infer or add joins based solely on the metric’s
expr
. It validates that joins used in a query correspond to definedrelationships
. - Robustness with Joins: When dealing with related models (especially via outer joins), use
CASE
statements or check for nulls on fields from those models to make the metric resilient to missing data.
Comprehensive Example
Here’s an example within an orders
model context, incorporating various metric types and practices: