fanout_on

LookML
Version

On this Page
Docs Menu

Go to Field Parameter List

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.

Still have questions?
Go to Discourse - or - Email Support
Top