User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
LookML Terms and Concepts

The Substitution Operator ($), Scoping and Naming, SQL Dialect, and SQL Block sections have moved to a new Incorporating SQL and Referring to LookML Objects page.

Overview

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

Looks and user-defined dashboards are not part of this diagram, as users create them without using any LookML. However, their queries rely on the underlying LookML elements in the chart above.

LookML Project

A LookML project is a collection of LookML files that describe a set of related models, views, Explores, and (optionally) LookML dashboards.

You will commonly find these structures in a LookML project:

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

In Looker, you can access your 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:

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

Major LookML Structures

As shown in the diagram above, a project contains one or more model files that contain parameters defining a model and its Explores and joins. The project also contains one or more view files, each containing parameters defining that view, its fields (including dimensions and measures) and sets of fields. The project can also contain a project manifest file containing parameters to use view files from other projects or configure localization defaults. This section describes those major structures.

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 Looker, queries are grouped by the model to which they belong. Your users see 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 Looker.

The general form of a model declaration in LookML is shown below. See the Model Parameters documentation page for details.

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

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 an Explore declaration in a model file.

In Looker, view names appear at the front of dimension and measure names in the data table. This makes it clear which view the field belongs to:

A view is stored in a .view.lkml file. The general form of a view declaration is shown below. See the View Parameters documentation page for complete usage details.

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] } }

When you use a measure of type: count in an Explore, the visualization labels the resulting values with the view name rather than the word “Count.” To avoid confusion, we recommend pluralizing your view name, selecting Show Full Field Name under Series in the visualization settings, or using a view_label with a pluralized version of your view name.

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 an 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.

In Looker, your users can see Explores listed in the Explore menu:

By convention, Explores are declared in the model file. The example below demonstrates the declaration for an orders Explore for an e-commerce 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 { join: customers { sql_on: ${orders.customer_id} = ${customers.id} ;; } }

For details on join declarations, see Joins. See the Join Parameters documentation page for complete usage details.

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:

For example, dimensions for a Products view might include product name, product model, product color, product price, product created date, and 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), and average sale price (an average).

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

In Looker, fields are listed on the Explore page when building and running 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.

Here are some example declarations of dimensions and measures:

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 Field Parameters documentation page for complete details on declaring fields and the various settings that can be applied to them.

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.

Here is an example join in an explore declaration:

# ——————————————— # 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.

Project Manifest Files

Your project may contain a project manifest file, which is used for project-level settings, such as listing files from other projects to import into the current project, defining LookML constants, and specifying model localization settings.

Each project can only have one manifest file, and it must be named manifest.lkmland located at the root level of your Git repo. If your project is enabled for folders in the IDE, be sure that the manifest.lkml file is kept at the root level of your project’s directory structure.

You can use a project manifest file either for project import or for localization, but not for both functions.

To import LookML files from a different project, use the project manifest file to specify a name for your current project and the location of any external projects, which could be stored locally or remotely. For example:

# This project project_name: "my_project" # The project to import local_dependency: { project: "my_other_project" } remote_dependency: ga_360_block { url: "git://github.com/llooker/google_ga360.git" ref: "4be130a28f3776c2bf67a9acc637e65c11231bcc" }

After defining the external projects in the project manifest file, you can use the include parameter in your model file to add files from those external project to your current project. For example:

include: "//my_other_project/imported_view.view" include: "//ga_360_block/*.view"

For more information, see the Importing Files from Other Projects documentation page.

To add localization to your model, use the project manifest file to specify default localization settings. For example:

localization_settings: { default_locale: en localization_level: permissive }

Specifying default localization settings is one step in localizing your model. For more information, see the Localizing Your LookML Model documentation page.

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.

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 documentation page for the set parameter for complete usage details for sets.

Drill Down

In Looker, you can drill down on any fields that are set up 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:

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

Derived Tables and Facts Tables

A derived table is a table comprising values from other tables; it 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 dataset 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:

Persistent Derived Tables

There are often cases where computing a derived table takes a significant amount of time. To avoid running an expensive derived-table computation more often than necessary, Looker can cache (or “persist”) the data in a derived table. A persistent derived table (PDT) is simply a derived table that is automatically regenerated. For more information on the ways to persist a derived table, see this page.

Persistent derived tables use a scratch table in the database to save results, which requires additional database configuration depending on the type of database.

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.

Top