Understanding and Creating LookML Models

LookML, Looker's proprietary data modeling language, provides a highly adaptable and human-readable way to describe and manipulate data sets. A LookML model serves as a blueprint of your data, defining how Looker interacts with your database, and shapes the data for exploration and visualization. This article gives an in-depth explanation of LookML models, their structure, and how to create them.

Understanding LookML Models

In Looker, a LookML model is composed of a series of project files that describe the relationships, calculations, and data transformations for your SQL tables. A model consists of two primary elements:

1. Explores: An Explore serves as a starting point for queries in Looker. It's based on one or more views and can contain joins, filters, and other SQL-like operations.

2. Views: A View in Looker corresponds to a SQL table or a derived table. It defines the fields (columns) that can be selected or filtered in a query.

Creating a LookML Model

In your Looker project, a LookML model is created as a .model.lkml file. The model file defines one or more explores which are then available for use in the Explore UI. Below are the steps to create a LookML model:

1. Navigate to your project's folder in the Looker IDE.
2. Click "+ Add" and then choose "Create Model".
3. Enter a name for the model. This name will also be the name of the .model.lkml file.
4. In the new .model.lkml file, define an explore. To do this, type explore: , followed by the name of the explore, which is usually the same as the primary view you will base the explore on.

Example of a simple LookML model:

# ecommerce.model.lkml
model: ecommerce {
  explore: orders {
    label: "Online Orders"
    description: "Exploration of all online orders"
  }
}

The ecommerce model above defines a single orders explore. Note that the explore does not yet specify any joins, fields, or other elements. We'll define these in a separate .view.lkml file.

Creating a LookML View

A LookML view is created as a .view.lkml file. This file defines a view based on a table in your database and describes all the fields that can be selected or used in filters, calculations, and more.

1. Navigate to your project's folder in the Looker IDE.
2. Click "+ Add" and then choose "Create View".
3. Enter a name for the view. This name will also be the name of the .view.lkml file.
4. In the new .view.lkml file, define the SQL table and the fields.

Example of a simple LookML view:

# orders.view.lkml
view: orders {
  sql_table_name: ecommerce.orders ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: customer_id {
    type: number
    sql: ${TABLE}.customer_id ;;
  }

  dimension: order_date {
    type: date
    sql: ${TABLE}.order_date ;;
  }

  measure: total_amount {
    type: sum
    sql: ${TABLE}.amount ;;
  }
}

The orders view above is based on the orders table in the ecommerce database. It defines four fields: id, customer_id, order_date, and total_amount.

Connecting Views to Explores

Now that we have a model with an explore and a view, we need to connect them. This is done in the .model.lkml file:

# ecommerce.model.lkml
model: ecommerce {
  explore: orders {
    view_name: orders
    label: "Online Orders"
    description: "Exploration of all online orders"
  }
}

By adding the view_name: orders line, we have connected the orders view to the orders explore. Now, the id, customer_id, order_date, and total_amount fields defined in the orders view will be available for querying in the orders explore.

Summary

LookML models provide a schema-like blueprint for your data in Looker. By understanding and creating models and views, you can define how users interact with your data in the Looker Explore UI. This article gives a primer on LookML models and views, but LookML offers many more features to handle complex relationships and data transformations. Stay tuned for further in-depth articles about developing in Looker.