Skip to main content

Creating metrics

Once you've created your semantic models, it's time to start adding metrics. Metrics can be defined in the same YAML files as your semantic models, or split into separate YAML files into any other subdirectories (provided that these subdirectories are also within the same dbt project repo).

This article explains the different supported metric types you can add to your dbt project. The keys for metrics definitions are:

📹 Learn about the dbt Semantic Layer with on-demand video courses!

Explore our dbt Semantic Layer on-demand course to learn how to define and query metrics in your dbt project.

Additionally, dive into mini-courses for querying the dbt Semantic Layer in your favorite tools: Tableau, Excel, Hex, and Mode.

Default granularity for metrics​

It's possible to define a default time granularity for metrics if it's different from the granularity of the default aggregation time dimensions (metric_time). This is useful if your time dimension has a very fine grain, like second or hour, but you typically query metrics rolled up at a coarser grain. The granularity can be set using the time_granularity parameter on the metric, and defaults to day. If day is not available because the dimension is defined at a coarser granularity, it will default to the defined granularity for the dimension.

Example​

You have a semantic model called orders with a time dimension called order_time. You want the orders metric to roll up to monthly by default; however, you want the option to look at these metrics hourly. You can set the time_granularity parameter on the order_time dimension to hour, and then set the time_granularity parameter in the metric to month.

semantic_models:
  ...
  dimensions:
    - name: order_time
      type: time
      type_params:
      time_granularity: hour
  measures:
    - name: orders
      expr: 1
      agg: sum
  metrics:
    - name: orders
      type: simple
      label: Count of Orders
      type_params:
        measure:
          name: orders
      time_granularity: month -- Optional, defaults to day

Conversion metrics​

Conversion metrics help you track when a base event and a subsequent conversion event occur for an entity within a set time period.

models/metrics/file_name.yml
metrics:
  - name: The metric name 
    description: The metric description 
    type: conversion 
    label: YOUR_LABEL 
    type_params: #
      conversion_type_params: 
        entity: ENTITY
        calculation: CALCULATION_TYPE 
        base_measure: 
          name: The name of the measure 
          fill_nulls_with: Set the value in your metric definition instead of null (such as zero) 
          join_to_timespine: true/false
        conversion_measure:
          name: The name of the measure 
          fill_nulls_with: Set the value in your metric definition instead of null (such as zero) 
          join_to_timespine: true/false
        window: TIME_WINDOW
        constant_properties:
          - base_property: DIMENSION or ENTITY 
            conversion_property: DIMENSION or ENTITY

Cumulative metrics​

Cumulative metrics aggregate a measure over a given window. If no window is specified, the window will accumulate the measure over all of the recorded time period. Note that you will need to create the time spine model before you add cumulative metrics.

models/metrics/file_name.yml
# Cumulative metrics aggregate a measure over a given window. The window is considered infinite if no window parameter is passed (accumulate the measure over all of time)
metrics:
  - name: wau_rolling_7
    type: cumulative
    label: Weekly active users
    type_params:
      measure:
        name: active_users
        fill_nulls_with: 0
        join_to_timespine: true
        window: 7 days

Derived metrics​

Derived metrics are defined as an expression of other metrics. Derived metrics allow you to do calculations on top of metrics.

models/metrics/file_name.yml
metrics:
  - name: order_gross_profit
    description: Gross profit from each order.
    type: derived
    label: Order gross profit
    type_params:
      expr: revenue - cost
      metrics:
        - name: order_total
          alias: revenue
        - name: order_cost
          alias: cost

Ratio metrics​

Ratio metrics involve a numerator metric and a denominator metric. A filter string can be applied to both the numerator and denominator or separately to the numerator or denominator.

models/metrics/file_name.yml
metrics:
  - name: cancellation_rate
    type: ratio
    label: Cancellation rate
    type_params:
      numerator: cancellations
      denominator: transaction_amount
    filter: |   
      {{ Dimension('customer__country') }} = 'MX'
  - name: enterprise_cancellation_rate
    type: ratio
    type_params:
      numerator:
        name: cancellations
        filter: {{ Dimension('company__tier') }} = 'enterprise'  
      denominator: transaction_amount
    filter: | 
      {{ Dimension('customer__country') }} = 'MX'

Simple metrics​

Simple metrics point directly to a measure. You may think of it as a function that takes only one measure as the input.

  • name — Use this parameter to define the reference name of the metric. The name must be unique amongst metrics and can include lowercase letters, numbers, and underscores. You can use this name to call the metric from the dbt Semantic Layer API.

Note: If you've already defined the measure using the create_metric: True parameter, you don't need to create simple metrics. However, if you would like to include a constraint on top of the measure, you will need to create a simple type metric.

models/metrics/file_name.yml
metrics:
  - name: cancellations
    description: The number of cancellations
    type: simple
    label: Cancellations
    type_params:
      measure:
        name: cancellations_usd  # Specify the measure you are creating a proxy for.
        fill_nulls_with: 0
    filter: |
      {{ Dimension('order__value')}} > 100 and {{Dimension('user__acquisition')}} is not null
    join_to_timespine: true

Filters​

A filter is configured using Jinja templating. Use the following syntax to reference entities, dimensions, time dimensions, or metrics in filters.

Refer to Metrics as dimensions for details on how to use metrics as dimensions with metric filters:

models/metrics/file_name.yml
filter: | 
  {{ Entity('entity_name') }}

filter: |  
  {{ Dimension('primary_entity__dimension_name') }}

filter: |  
  {{ TimeDimension('time_dimension', 'granularity') }}

filter: |  
 {{ Metric('metric_name', group_by=['entity_name']) }}  # Available in v1.8 or with versionless dbt Cloud.

For example, if you want to filter for the order date dimension grouped by month, use the following syntax:

filter: |  
  {{ TimeDimension('order_date', 'month') }}

Further configuration​

You can set more metadata for your metrics, which can be used by other tools later on. The way this metadata is used will vary based on the specific integration partner

  • Description — Write a detailed description of the metric.
0
Edit this page