Measures (or metrics) are aggregations performed inside a SQL group by statement. A simple one is sum(sales), which you could specify in your data model with type: sum and sql: ${TABLE}.sales. Measures can become highly complex, offering flexibility that leverages the full power of your SQL syntax.

Properties of a Measure (or Metric)

• name (Required):

  The name of the measure (or metric). If you reference this measure (or metric) elsewhere in your data model, this is the value you will use to refer to it.

• field_type (Required):

  The field type, which for measures (or metrics) is always measure.

• type (Required):

  The type of aggregation to perform. This can be one of the following:

  • sum
  • average
  • count
  • count_distinct
  • sum_distinct
  • average_distinct
  • median (if supported in your database)
  • max
  • min
  • cumulative
  • number

  Note: sum_distinct and average_distinct require you to pass a value to the sql_distinct_key property. cumulative requires you to pass the measure property.

• description:

  A brief explanation to help end users understand what the measure or metric represents.

• sql (Required):

  The SQL expression that generates the field value. This can be as simple as ${TABLE}.my_field_name, which references a column in the database table, or a more advanced SQL statement that references previously defined fields, like:

  case when ${channel} ilike '%owned' then 1 else 0 end
  

• synonyms:

  A list of alternative terms or phrases that could be used in natural language search. This improves discoverability of the measure when users are asking questions in Fluent. Example:

  synonyms: ['total sales', 'income']
  

• sql_distinct_key:

  This tells Fluent that the measure is calculated on distinct values. For example, if sales are tied to an order_id but are present in multiple rows, you could specify the order_id as the sql_distinct_key and use the type sum_distinct to avoid double counting.

• measure (Required for cumulative metrics):

  For cumulative metrics, this is the measure over which the cumulative calculation will be performed.

• filters:

  A list of filters to apply to the measure.

  These cannot be applied to type: number measures.

  These filters apply to the calculation and are equivalent to SQL WHERE clauses. Each filter has two properties: field and value. Example:

  filters:
    - field: channel
      value: "-Paid"
  

• canon_date:

  The date field to use when trending this measure over time. It defaults to the default_date of the view the measure is in, but you can override it by specifying a different date field here.

• value_meanings:

  For measures with non-obvious values, use this property to explain the meaning of the values. This is particularly helpful for categorical data stored in non-human-readable formats (e.g., integers or codes). Example:

  value_meanings:
    - value: 1
      meaning: "Success"
    - value: 2
      meaning: "Failure"
    - value: 3
      meaning: "Pending"
  

• use_when:

  Provides guidance on when this measure should be used. Example:

  use_when: Use this metric when calculating total revenue across all orders.
  

• dont_confuse_with:

  Helps prevent confusion by specifying fields that are similar but should not be used interchangeably. Example:

  dont_confuse_with: Use 'total_gross_revenue' instead of 'net_revenue' when calculating revenue without applying discounts.
  

Examples

Simple Example: This example defines a few dimensions and measures for the order_lines view, showing how the SQL statements and different properties are applied.

version: 1
type: view
name: order_lines
model_name: demo_model
sql_table_name: prod.order_lines

fields:
- name: order_line_id
  field_type: dimension
  type: string
  sql: ${TABLE}.order_line_id
  primary_key: yes

- name: price
  field_type: dimension
  type: number
  sql: ${TABLE}.item_price

- name: avg_price
  field_type: measure
  type: average
  sql: ${price}

- name: total_price_order_level
  field_type: measure
  type: sum_distinct
  sql_distinct_key: ${order_id}
  sql: ${price}

- name: number_of_orders
  field_type: measure
  type: count_distinct
  sql: ${order_id}

- name: cumulative_orders
  field_type: measure
  type: cumulative
  measure: number_of_orders
  description: "The unique cumulative number of orders"

Summary

In Fluent, measures (or metrics) represent aggregated calculations based on the data in your database. They are defined using properties such as name, field_type, type, and sql. You can also apply filters to measures, set cumulative calculations, and even configure non-additive dimensions for cases where simple aggregation is not applicable (e.g., MRR or inventory). Additional properties such as value_meanings, use_when, and dont_confuse_with help improve the usability of your model, guiding both the LLM and end users in how to best apply these metrics.

Through examples, we see how various types of measures, such as averages, distinct counts, and cumulative measures, can be constructed with appropriate SQL expressions and aggregation types. The rich set of properties ensures that your model remains both flexible and powerful.