Breaking a Wide Table into Multiple Views for End Users

(Anika Kuesters Smith) #1

See also: Labeling Views to Make Your Models More Understandable for information about view_label:.

Sometimes all (or most of) the data you want to analyze is embedded in a single wide table with many many columns. In these cases, it’s common for attributes of multiple entities to be mixed into a single table, and for tens or hundreds of fields to exist. If you create one view for the entire table, it may confuse users, because users generally expect a single view to correspond to a single entity. Naming also becomes an issue, because field names have to indicate what entity they relate to.

LookML allows you to scope fields from one table into different views, and assign every field a meaningful identifier. In the Looker user interface, this allows users to access fields for specific entities separately, which can improve their ability to construct meaningful queries.

Below is a simplified example. You can see the dimension and measure names are scoped with a period. This causes the field to be treated as if it was part of the specified view, instead the view containing the declaration.

- view: this_view_is_never_used_directly
  sql_table_name: auction_data_table

  - dimension:       # 'buyers' scope is different than the enclosing view
    sql: ${TABLE}.auctionbuyer   # Note that dimension name is different (and simpler)
                                 # than the table's field name
  - measure: buyers.count        # Appears in the Looker UI as 'BUYERS Count'
    type: count_distinct
    sql: ${}

  - dimension:      # Appears in the Looker UI as 'SELLERS Name'
    sql: ${TABLE}.auctionseller

  - measure: sellers.count
    type: count_distinct
    sql: ${}

  - dimension: products.description
    sql: ${TABLE}.itemdescription

  - dimension: products.quality
    sql: ${TABLE}.itemcondition

This pattern is uncommon, but useful in this particular case for simplifying wide tables. This pattern is not recommended for other uses, because it confuses users if fields that don’t exist within a physical table are given a name associated with that table. It can also make troubleshooting, referencing, and joining more difficult.