fanout_on

Usage

view: view_name {
  dimension: field_name {
    fanout_on: repeated_record_name
  }
}

Hierarchy View File view Field Type fanout_on

Possible Field Types Dimension, Dimension Group, Measure Accepts A Google BigQuery Legacy SQL REPEATED subrecord

Definition

The fanout_on parameter specifies the Google BigQuery Legacy SQL REPEATED subrecord on which a dimension or measure should fanout.

Google BigQuery Legacy SQL supports nested records in which a single record can hold multiple subrecords. Expressing these subrecords in Looker causes the result set to "fanout", which means that the parent record gets repeated multiple times. In order for Looker to properly associate the normal data with the repeated data, you can specify the fanout relationship of a subrecord using the fanout_on parameter.

Consider this example data table:

Column Name	Type
name	String, required
age	Integer, required
citiesLived	Record, repeated
citiesLived.place	String, nullable
citiesLived.numberOfYears	Integer, nullable

Data without the citiesLived record may look like this:

name	age
Wilbur Wright	45

However, adding the repeated citiesLived record causes a fanout, where the name and age data is repeated multiple times:

name	age	citiesLived.place	citiesLived.numberOfYears
Wilbur Wright	45	Dayton	40
Wilbur Wright	45	Paris	2
Wilbur Wright	45	Kitty Hawk	1

When modeling this type of data the citiesLived record causes the fanout, so it is used in the fanout_on parameter:

dimension: city_lived {
  sql: ${TABLE}.citiesLived.place ;;
  fanout_on: "citiesLived"
}
dimension: years_lived {
  sql: ${TABLE}.citiesLived.numberOfYears ;;
  fanout_on: "citiesLived"
}
measure: cities_count {
  type: count_distinct
  sql: ${city_lived} ;;
  fanout_on: "citiesLived"
}
measure: city_list {
  type: list
  list_field: city_lived
  fanout_on: "citiesLived"
}

-

Note that both dimensions and measures must use fanout_on if they include a repeated record in their calculation.