LookML Terms and Concepts

LookML
Version

On this Page
Docs Menu

This page defines terms and concepts that appear repeatedly in LookML development. The following diagram shows relationships of which elements are contained within other elements. All terms shown here are defined in the following sections.

Relationships between LookML elements

Definition: LookML Project

A LookML project is a collection of LookML files that describe a set of related models, explores, views, and dashboards.

By convention, LookML code is segregated into three types of files: model files, view files, and dashboard files. In new LookML, these fields have the following extensions: .model.lkml, .view.lkml, and .dashboard.lookml, respectively.

If you are using Git for project version control, one project constitutes a single Git repository.

Relationship between LookML projects, files, database connections, and Git repositories

Projects are managed in Looker under the Develop menu.

Accessing projects under the Develop menu

Where Do LookML Projects and Files Come From?

When you create a new project, Looker’s project generator creates a baseline set of files, which you use as a template for building out the project. Very rarely, if ever, will you write a LookML file from scratch.

When creating a project, you specify a database connection, and Looker’s project generator creates the following:

  • Multiple view files, one file for every table in the database.
  • One model file. The model file declares an explore for every view. Each explore declaration includes join logic to join any view that Looker can determine is related to the explore.

From here, you can refine the project by removing unwanted views and explores, and adding custom dimensions and measures.

Definition: Model

A model is a customized portal into the database, designed to provide intuitive data exploration for specific business users. Multiple models can exist for the same database connection in a single LookML project. Each model can expose different data to different users. For example, sales agents need different data than company executives, and so you would probably develop two models to offer views of the database appropriate for each user.

In the Looker app, queries are grouped by the model they belong to, and business users see models listed under the Explore menu.

Models listed under the Explore menu

A model file specifies the database to connect to and defines a collection of explores for that connection. By convention each file declares exactly one model and, in new LookML, model filenames end in .model.lkml. The name of the model file determines the name that displays in the Looker app.

The general form of a model declaration in LookML is shown below. See the LookML Reference for details.

- connection: connection_name - persist_for: timeframe - case_sensitive: true | false - include: "filename_pattern" # for example: *.view.lookml # More include declarations   - explore: explore_name view: view_name joins: - join: view_name # More join declarations # More explore declarations
connection: connection_name persist_for: timeframe case_sensitive: yes | no include: "filename_pattern" # for example: *.view.lookml # More include declarations   explore: explore_name { view_name: view_name join: view_name { # join parameters } # More join declarations } # More explore declarations

Definition: View

A view declaration defines a list of fields (dimensions or measures) and their linkage to an underlying table or derived table. In LookML a view typically references an underlying database table, but it can also represent a derived table.

A view may join to other views. The relationship between views is typically defined as part of a explore declaration in a model file.

In the Looker app, view names appear at the front of dimension and measure names to qualify what view the field belongs to.

View names listed as part of field names

By convention, in new LookML, a view is stored in a .view.lkml file. The general form of a view declaration is shown below. See the LookML Reference for complete usage details.

- view: view_name fields: - dimension: field_name # dimension_parameters # more dimension declarations - measure: field_name # measure_parameters # more measure declarations sets: first_set: - field_one - field_two
view: view_name { dimension: field_name { # dimension_parameters } # more dimension declarations measure: field_name { # measure_parameters } # more measure declarations set: first_set { fields: [field_one, field_two] } }

Definition: Explore

An explore is a view that users can query. You can think of the explore as a starting point for a query, or in SQL terms, as the FROM in a SQL statement. Not all views are explores, because not all views describe an entity of interest. For example, a “States” view corresponding to a lookup table for state names doesn’t warrant an explore, because business users never need to query it directly. On the other hand, business users probably want a way to query a “Orders” view, and so defining an explore for Orders makes sense.

An explore declaration specifies the join relationships to other views. Continuing with the previous examples, the Orders view might join the States view, identifying the state in which a sale occurred. See Joins for more detail.

Explores listed under the Explore menu

By convention explores are declared in the model file. The example below demonstrates the declaration for an “Orders” explore for an ecommerce database. The views “orders” and “customers” are defined elsewhere, in their respective view files.

Example explore declaration

# ——————————————— # file: ecommercestore.model.lookml # ——————————————— - connection: order_database - include: "filename_pattern" # include all the views - explore: orders joins: - join: customers sql_on: ${orders.customer_id} = ${customers.id}
# ——————————————— # file: ecommercestore.model.lookml # ——————————————— connection: order_database include: "filename_pattern" # include all the views explore: orders { join: customers { sql_on: ${orders.customer_id} = ${customers.id} ;; } }

For details on join declarations, see Joins. See the LookML Reference for complete usage details.

Definition: Dimension and Measure Fields

Views contain fields, mostly dimensions and measures, which are the fundamental building blocks for Looker queries.

In Looker, a dimension is a groupable field and can be used to filter query results. It can be:

  • an attribute, which has a direct association to a column in an underlying table
  • a fact or numerical value
  • a derived value, computed based on the values of other fields in a single row

For example, dimensions for a “Products” view might include: product name, product model, product color, product price, product created date, product end-of-life date.

A measure is a field that uses a SQL aggregate function, such as COUNT, SUM, AVG, MIN or MAX. Any field computed based on the values of other measure values is also a measure. Measures can be used to filter grouped values. For example, measures for a “Sales” view might include: total items sold (a count), total sale price (a sum), average sale price (an average).

The behavior and expected values for a field depend on its declared type, such as string, number, time. For measures, types include aggregate functions, such as sum and percent_of_previous. For details, refer to dimension types and measure types.

In the Looker app, fields are listed on the Explore page when building and running queries.

Dimensions and measures are the building blocks for Looker queries

By convention fields are declared as part of the view they belong to, stored in a view file. The example below shows several dimension and measure declarations. Notice the use of the substitution operator ($) to reference fields without using a fully-scoped SQL column name.

Example declarations of dimensions and measures

- view: orders fields: - dimension: id primary_key: true type: number sql: ${TABLE}.id   - dimension: customer_id sql: ${TABLE}.customer_id   - dimension: amount type: number value_format: '0.00' sql: ${TABLE}.amount   - dimension_group: created type: time timeframes: [date, week] sql: ${TABLE}.created_at   - measure: count type: count # creates sql COUNT(orders.id) sql: ${id}   - measure: total_amount type: sum sql: ${amount} # creates sql SUM(orders.amount)
view: orders { dimension: id { primary_key: yes type: number sql: ${TABLE}.id ;; } dimension: customer_id { sql: ${TABLE}.customer_id ;; } dimension: amount { type: number value_format: "0.00" sql: ${TABLE}.amount ;; } dimension_group: created { type: time timeframes: [date, week] sql: ${TABLE}.created_at ;; } measure: count { type: count # creates sql COUNT(orders.id) sql: ${id} ;; } measure: total_amount { type: sum # creates sql SUM(orders.amount) sql: ${amount} ;; } }

You can also define a dimension_group, which creates multiple time-related dimensions at once, and filter fields, which have a variety of advanced use cases such as templated filters.

See the LookML Reference for complete details on declaring fields and the various settings that can be applied to them.

Definition: Substitution Operator ($)

The substitution operator, $, makes LookML code more reusable and modular, enabling you to reference existing dimensions or measures. This is good for two reasons. First, you might have already worked out a really tricky dimension or measure, and you won’t need to write out all the complexity again. Second, if you change something about a dimension or measure, that change can propogate to everything else that relies on it.

There are several ways that you can use the substitution operator:

  1. ${TABLE}.column_name references a column in the table that is connected to the view you’re working on
  2. ${field_name} references a dimension or measure within the view you’re working on
  3. ${view_name.field_name} references a dimension or measure from another view

Refer to the substitution module on learn.looker.com to view and experiment with simple examples.

Definition: Joins

As part of an explore declaration, each join declaration specifies a view that can be joined into the explore. When a user creates a query that includes fields from multiple views, Looker automatically generates SQL join logic to bring in all fields correctly.

Example join in an explore declaration

# ——————————————— # file: ecommercestore.model.lookml # ——————————————— - connection: order_database - include: "filename_pattern" # include all the views - explore: orders joins: - join: customers sql_on: ${orders.customer_id} = ${customers.id}
# ——————————————— # file: ecommercestore.model.lookml # ——————————————— connection: order_database include: "filename_pattern" # include all the views explore: orders { join: customers { sql_on: ${orders.customer_id} = ${customers.id} ;; } }

For more details check out Working with Joins in LookML.

Definition: Drill Down

In Looker, you can drill down on any fields that are setup that way when writing LookML. Drilling works in both query results tables and dashboards. Drilling starts a new query that is restricted by the value you clicked on.

Drill behavior is different for dimensions and measures:

  • When drilling on a dimension, the new query filters on the drilled value. For example, if you click on a specific date in a query of customer orders by date, the new query will show only orders on the specific date.
  • When drilling on a measure, the new query will show the data set that contributed to the measure. For example, when drilling on a count, the new query will show the rows to calculate that count. When drilling on max, min, and average measures, drilling still shows all the rows that contributed to that measure. This means that drilling on a max measure, for example, shows all the rows that were used to calculate the max value, not just a single row for the max value.

The fields to show for the new drill query are defined by a set.

Definition: Sets

In Looker, a set is a list that defines a group of fields that are used together. Typically sets are used to specify which fields to display after a user drills down into data. Drill sets are specified on a field-by-field basis, so you get complete control over what data is displayed when a user clicks a value in a table or dashboard. Sets can also be used as a security feature to define groups of fields visible to specific users.

The following example shows a set declaration in a view order_items, defining fields that list relevant details about a purchased item. Note that the set references fields from other views by specifying scope.

sets: order_items_stats_set: - id # scope defaults to order_items view - orders.created_date # scope is "orders" view - orders.id - users.name - users.history # show all products this user has purchased - products.item_name - products.brand - products.category - total_sale_price
set: order_items_stats_set { fields: [ id, # scope defaults to order_items view orders.created_date, # scope is "orders" view orders.id, users.name, users.history, # show all products this user has purchased products.item_name, products.brand, products.category, total_sale_price ] }

See the LookML Reference for complete usage details for sets.

Definition: Derived Tables and Facts Tables

A derived table is table comprised of values from other tables, which is accessed as though it were a physical table with its own set of columns. A derived table is exposed as its own view using the derived_table parameter, and defines dimensions and measures in the same manner as conventional views. The view for a derived table can be queried and joined into other views, just like any other view.

Derived tables are created by using the derived_table parameter in a view declaration. For complete details, see Derived Tables Reference.

Using Derived Tables for Facts Tables

In Looker a common use for derived tables is to present a facts table, which computes facts about an entity, based on values derived from other views. For example, a common need is to analyze user traits based on past orders or actions, and then report, sort, and filter those traits like any other facet of a user.

Example: A Derived Table for User Order Facts

Consider an e-commerce data set with a users table containing customer data and an orders table containing details about customer orders. A derived table can be used to create a user-orders facts table, containing user-centric facts such as lifetime total revenue for a user, which doesn’t physically exist in the underlying tables. More example columns are: number of lifetime orders, latest order date, whether the user placed multiple orders, and so forth. See the diagram below.

Because the primary key for the fact table is user_id, the view can be joined one-to-one with the users explore, enabling rich query possibilities. An example is shown below:

Definition: Persistent Derived Tables

There are often cases where the time needed to perform the computation that results in a derived table is significant. Looker can cache (or “persist”) the data in a derived table to avoid running an expensive derived-table computation more often than necessary. Persistent derived tables are simply derived tables that have been given a maximum age before they will regenerate (using the persist for parameter) or a scheduled interval to regenerate (using the sql_trigger_value parameter).

Persistent derived tables use a scratch table in the database to save results, which requires additional database configuration depending on the type of database. See Persistent Derived Tables for more detail.

Definition: Database Connection

Looker issues queries against a database, specified in the LookML model file. A Looker connection specifies a server hosting the database, and parameters defining how Looker should connect to the database. Database setup is typically done once (or infrequently) by the system administrator, and data modelers simply pick from the available connections.

Scoping and Naming

You can name explores, views, fields, and sets. These Looker identifiers are written without quotation marks.

LookML fields and sets have full names and short names. Full names are of the form <view>.<field-name | set-name>. The left side indicates the scope, which is the view that contains the field or set. The right side specifies the particular field or set name.

Short names simply take the form <field-name | set-name>, with no separating period. Looker expands short names into full names by using the scope in which they are used.

Below is an example showing many forms of names and scope. This is an unrealistic group of fields, but is shown to demonstrate a variety of possible scoping expressions.

- view: orders # "orders" becomes the containing scope fields: - measure: count # short name, equivalent to orders.count type: count   - dimension: customer_id # short name, equivalent to orders.customer_id type: number sql: ${TABLE}.customer_id   - dimension: customer_address # short name, equivalent to orders.customer_address sql: ${customer.address} # full name, references a field defined in the "customer" view   sets: drill_fields: # short name, equivalent to orders.drill_fields - count, # short name, equivalent to orders.count - customer.id # full name, references a field defined in the "customer" view
view: orders { # "orders" becomes the containing scope measure: count { # short name, equivalent to orders.count type: count } dimension: customer_id { # short name, equivalent to orders.customer_id type: number sql: ${TABLE}.customer_id ;; } dimension: customer_address { # short name, equivalent to orders.customer_address sql: ${customer.address} ;; # full name, references a field defined in the "customer" view } set: drill_fields { # short name, equivalent to orders.drill_fields fields: [ count, # short name, equivalent to orders.count customer.id # full name, references a field defined in the "customer" view ] } }

In the dimension: customer_address declaration above, note that the underlying view for the SQL block (customer) is different than the enclosing view scope (orders). This can be useful when you need to compare fields between two different views.

When a view (we’ll call it “view A”) refers to a field defined in a different view (we’ll call it “view B”), there are a few things to keep in mind:

  1. The view B file must be included in the same model as view A, using the include parameter.
  2. View B must be joined to view A in one or more explores. See our Working with Joins in LookML page to learn about joins.

Definition: SQL Dialect

Looker supports many database types, such as MySQL, Postgres, Redshift, BigQuery, and so on. Each database supports a slightly different feature set with differing function names, referred to as the SQL dialect.

LookML is designed to work with all SQL dialects, and LookML does not prefer one dialect over the other. However, you will need to include SQL code expressions (known as SQL blocks) in certain LookML parameters. With these parameters, Looker passes the SQL expression directly to your database, so you must use the SQL dialect that matches your database. For example, if you use a SQL function, it must be a function that your database supports.

Definition: SQL Block

Some LookML parameters require you to provide raw SQL expressions, so that Looker can understand how to retrieve data from your database.

LookML parameters starting with sql_ expect a SQL expression of some form. Examples are: sql_always_where, sql_on, and sql_table_name. The most common SQL block parameter is sql for dimension and measure field definitions, which specifies the SQL expression that defines the dimension or measure.

The code you specify in a SQL block can be as simple as a single field name or as complex as a correlated subselect. The content can be quite complex, accommodating almost any need you might have to express custom query logic in raw SQL. Note that the code you use in SQL blocks must match the SQL dialect used by the database.

Example SQL Blocks for Dimensions and Measures

Below are examples of SQL blocks for dimensions and measures. The LookML substitution operator ($) can make these sql declarations appear deceptively unlike SQL. However, after substitution has occurred, the resulting string is pure SQL, which Looker injects into the SELECT clause of the query.

- dimension: id primary_key: true sql: ${TABLE}.id # Specify the primary key, id   - measure: average_cost type: average value_format: '0.00' sql: ${order_items.cost} # Specify the field that you want to average   - dimension: name sql: CONCAT(${first_name}, ' ', ${last_name})   - dimension: days_in_inventory type: int sql: DATEDIFF(${sold_date}, ${created_date})
dimension: id { primary_key: yes sql: ${TABLE}.id ;; # Specify the primary key, id } measure: average_cost { type: average value_format: "0.00" sql: ${order_items.cost} ;; # Specify the field that you want to average } dimension: name { sql: CONCAT(${first_name}, ' ', ${last_name}) ;; } dimension: days_in_inventory { type: int sql: DATEDIFF(${sold_date}, ${created_date}) ;; }

As shown in the last two dimensions above, SQL blocks can use functions supported by the underlying database (such as the MySQL functions CONCAT and DATEDIFF in this example).

Example SQL Block With a Correlated Subselect

In a field’s sql: block you can place any SQL statement, including a correlated subselect. An example is below:

- view: customers fields: - dimension: id primary_key: true sql: ${TABLE}.id   - dimension: first_order_id sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) # correlated subselect to derive the value for "first_order_id"
view: customers { dimension: id { primary_key: yes sql: ${TABLE}.id ;; } dimension: first_order_id { sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;; # correlated subselect to derive the value for "first_order_id" } }

Example SQL Block for Derived Tables

Derived tables use the SQL block to specify the query that derives the table. An example is below:

- view: user_order_facts derived_table: sql: | # Get the number of orders for each user SELECT user_id , COUNT(*) as lifetime_orders FROM orders GROUP BY 1 # later, dimension declarations reference the derived column(s)   fields: - dimension: lifetime_orders type: number
view: user_order_facts { derived_table: { sql: # Get the number of orders for each user SELECT user_id , COUNT(*) as lifetime_orders FROM orders GROUP BY 1 ;; } # later, dimension declarations reference the derived column(s)   dimension: lifetime_orders { type: number } }

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