Semantic Layer Measure Definition Examples

This list showcases different Semantic Layer definitions and examples.

# Example YAML Definitions

# Dbt Semantic Layer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


semantic_models:
  - name: the_name_of_the_semantic_model ## Required
    description: same as always ## Optional
    model: ref('some_model') ## Required
    defaults: ## Required
      agg_time_dimension: dimension_name ## Required if the model contains measures
    entities: ## Required
      - see more information in entities
    measures: ## Optional
      - see more information in the measures section
    dimensions: ## Required
      - see more information in the dimensions section
    primary_entity: >-
      if the semantic model has no primary entity, then this property is required. #Optional if a primary entity exists, otherwise Required

Find all the properties and it’s use in Semantic models | dbt Developer Hub.

# SLayer

SLayer, a news OSS semantic layer.

# Models

Defines models this way:

1
2
3
4
5
6
7
8
9


name: orders
sql_table: public.orders
data_source: my_postgres
columns:
  - {name: id, type: number, primary_key: true}
  - {name: status, type: string}
  - {name: revenue, sql: amount, type: number}
measures:
  - {name: aov, formula: "sum(revenue) / count(*)"}

# Aggregations:

1
2
3
4
5


aggregations:
  - name: weighted_avg
    formula: "sum({value} * {weight}) / sum({weight})"
  - name: trimmed_mean
    formula: "avg(CASE WHEN {value} BETWEEN {low} AND {high} THEN {value} END)"

And support multi-stage metrics including Granularity:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


  {
    "name": "monthly_store_revenue",
    "source_model": "orders",
    "measures": [{"formula": "sum(order_total)", "name": "rev"}],
    "dimensions": ["stores.name"],
    "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}]
  },
  {
    "source_model": "monthly_store_revenue",
    "measures": [{"formula": "avg(rev)"}],
    "dimensions": ["stores.name"]
  }
]

Examples from Models, built-in-aggregations, Multi-stage queries, made easy.

# Queries

Something other Semantic Layer do usually not provide, queries on runtime, specifies what data to retrieve from a model above.

Here a Dimension:

1
2
3
4
5


"dimensions": [
  "region",
  {"expression": "lower(city)", "name": "city_lc"},
  {"expression": "CASE WHEN amount > 2000 THEN 'big' ELSE 'small' END", "name": "size"}
]

Or grouping by an expression over an aggregate:

1
2
3
4
5
6
7
8


{
  "source_model": "orders",
  "dimensions": [
    "region",
    {"expression": "CASE WHEN sum(amount, partition_by=city) > 5000 THEN 1 ELSE 0 END", "name": "band"}
  ],
  "measures": [{"formula": "sum(amount)", "name": "band_total"}]
}

dbt Semantic Layer, we can see tabs, use MetricFlow to define a semantic model that uses order_total as a metric and a sample schema to create consistent and accurate results which eliminating confusion, code duplication, and streamlining your workflow - example from dbt Developer Hub:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41


semantic_models:
  - name: orders    # The name of the semantic model
    description: |
      A model containing order data. The grain of the table is the order id.
    model: ref('orders') #The name of the dbt model and schema
    defaults:
      agg_time_dimension: metric_time
    entities: # Entities, which usually correspond to keys in the table. 
      - name: order_id
        type: primary
      - name: customer
        type: foreign
        expr: customer_id
    measures:   # Measures, which are the aggregations on the columns in the table.
      - name: order_total
        agg: sum
    dimensions: # Dimensions are either categorical or time. They add additional context to metrics and the typical querying pattern is Metric by Dimension.
      - name: metric_time
        expr: cast(ordered_at as date)
        type: time
        type_params:
          time_granularity: day
  - name: customers    # The name of the second semantic model
    description: >
      Customer dimension table. The grain of the table is one row per
        customer.
    model: ref('customers') #The name of the dbt model and schema
    defaults:
      agg_time_dimension: first_ordered_at
    entities: # Entities, which  usually correspond to keys in the table.
      - name: customer 
        type: primary
        expr: customer_id
    dimensions: # Dimensions are either categorical or time. They add additional context to metrics and the typical querying pattern is Metric by Dimension.
      - name: is_new_customer
        type: categorical
        expr: case when first_ordered_at is not null then true else false end
      - name: first_ordered_at
        type: time
        type_params:
          time_granularity: day

where as the SQL looks something like this

1
2
3
4
5
6
7
8


select
    date_trunc('day',orders.ordered_at) as day, 
    case when customers.first_ordered_at is not null then true else false end as is_new_customer,
    sum(orders.order_total) as order_total
from orders
left join customers
on orders.customer_id = customers.customer_id
group by 1, 2

# Open Standard

There’s now the Open Semantic Interchange (OSI) initiative, which resulted in Apache Ossie (renamed), which tried to interchange all semantic modeling with an **open-source, vendor-agnostic specification **.

Origin: Semantic Layer