Exploring Views, Explores, and Models

One of the pillars of Looker's powerful capabilities is LookML, its proprietary data modeling language. This article will take a deep dive into LookML, focusing on the three key concepts: Views, Explores, and Models.

LookML Views

LookML views are the building blocks of LookML models. They define the SQL transformations that Looker will apply to your raw data. Here's an example of a LookML view:

view: customers {
  sql_table_name: ecommerce.customers ;;
  
  dimension: customer_id {
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  
  dimension: first_name {
    type: string
    sql: ${TABLE}.first_name ;;
  }
  
  dimension: last_name {
    type: string
    sql: ${TABLE}.last_name ;;
  }
  
  dimension: created_at {
    type: time
    sql: ${TABLE}.created_at ;;
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
  }
}

In this example, the customers view maps to the customers table in the ecommerce schema of your database. The view defines four dimensions, which are fields that you can use in your queries.

LookML Explores

LookML Explores provide a starting point for data exploration. They define what happens when you click the "Explore" button in Looker. An Explore includes one or more views and specifies how they join together.

Here's an example of a LookML Explore:

explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
    relationship: many_to_one
  }
}

In this example, the orders Explore joins the orders and customers views based on the customer_id field. The relationship parameter indicates that each order can have one customer, but each customer can have many orders (hence, a many-to-one relationship).

LookML Models

Finally, LookML Models are the highest level of abstraction in LookML. A Model includes one or more Explores, which in turn include one or more views.

Here's an example of a LookML Model:

model: ecommerce {
  explore: orders {
    label: "Order Analysis"
    join: customers {
      sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
      relationship: many_to_one
    }
  }
}

In this example, the ecommerce model includes the orders Explore, which joins the orders and customers views.

Bringing It All Together

Understanding how views, Explores, and models fit together is crucial to leveraging Looker effectively. A LookML model is a collection of Explores, each of which represents a particular path of data exploration. Each Explore is composed of one or more views, which specify the underlying SQL for transforming the raw data.

For example, in the ecommerce model, a user might start an Explore from the orders Explore. They could then bring in data from the customers view using the join defined in the Explore. The dimensions and measures defined in the orders and customers views determine what fields the user can include in their query.

Extending LookML Models

You can extend the functionality of LookML models by using advanced features like derived tables, filters, and measures. Derived tables allow you to precompute complex calculations and make them available as a table in your database. Filters allow you to restrict the data that's available in an Explore, and measures provide predefined calculations that you can use in your queries.

For instance, you might add a lifetime_value measure to the customers view to calculate the total revenue from each customer:

view: customers {
  # ...

  measure: lifetime_value {
    type: sum
    sql: ${orders.amount} ;;
    drill_fields: [detail*]
  }
}

With this measure, users can now easily include the lifetime value of customers in their queries without having to write any SQL.

In conclusion, LookML is a powerful data modeling language that enables you to create flexible and reusable data models in Looker. By understanding the key concepts of views, Explores, and models, you can leverage Looker's full potential to deliver actionable insights from your data.