home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
LookML Refinements

This is an advanced topic that assumes a solid knowledge of LookML.

Overview

With LookML refinements, you can adapt an existing view or Explore without editing the LookML file that contains it. This is ideal for:

For example, if you have the following view file in your project:

view: flights { sql_table_name: flightstats.accidents ;;   dimension: id { label: "id" primary_key: yes type: number sql: ${TABLE}.id ;; } }

You can refine the flights view as shown below: Use the view parameter with the same view name, but add a plus sign (+) in front of the name to indicate that it’s a refinement of an existing view.

This refinement adds an air_carrier dimension to the existing flights view:

view: +flights { dimension: air_carrier { type: string sql: ${TABLE}.air_carrier ;; } }

This refinement can go in any LookML file in the project, such as a model file, view file, or in its own dedicated LookML file. See the Using Refinements in Your LookML Project section for how it works.

The refinement combined with the original LookML has the end result as if this were the original LookML for the view:

view: flights { sql_table_name: flightstats.accidents ;;   dimension: id { label: "id" primary_key: yes type: number sql: ${TABLE}.id ;; }   dimension: air_carrier { type: string sql: ${TABLE}.air_carrier ;; } }

In the Looker UI, users will see the Air Carrier dimension, just as if you had added the dimension to the original view file itself.

See the example below for more detailed implementation information.

Refinements Compared to Extends

Looker also supports extending LookML objects. Extending is useful when you want to create a new copy of an existing view or Explore so that you can add new objects to it. For example, you can create a base view that defines all of your fields, and then create multiple new views that extend the base view. These new views can then be modified to hide certain fields in the base view, or to change definitions or labels for the fields from the base view.

Refinements are useful when you want to modify an existing view or Explore with some tweaks or adjustments to certain objects, but you don’t want to create copies of the view or Explore. Refinements are ideal for situations where you cannot or do not want to modify the base view or Explore, and for situations where creating a new view or Explore would require extensive changes to other LookML references. See the Example section below for an example of this use case.

For most use cases, refinements are a simpler and cleaner alternative to extends.

Advanced LookML developers may want to use the extends parameter inside a LookML refinement. See the Refinements Can Contain Extends section below for more information.

Refinements Override Parameters

It is important to note that a refinement will override the original settings of an object. In the example below, the original view has a hidden dimension (hidden: yes):

view: faa_flights { dimension: carrier { hidden: yes } }

And in another file we’ve added a refinement to that dimension with hidden: no:

include: "/views/faa_flights.view.lkml" view: +faa_flights { dimension: carrier { hidden: no } }

The last refinement takes precedent, so hidden: no is applied and the dimension will be displayed in the final view.

The exception to this is the extends parameter. When used in a refinement, the extends parameter does not override the extends parameter in the original object or in previous refinements. Instead, the value in the extends parameter of a refinement is appended to the end of the list of items extended in the original object or in previous refinements. See the Refinement extends Are Additive section below for details and an example.

Refinements Are Applied in Order

An object can be refined multiple times and in multiple places, which enables Looker developers to use refinements in a lot of creative ways. But this also means that developers have to be very mindful of the order in which refinements are applied:

You can use the final: yes flag to verify that your refinements are being applied in the order you expect. See the Using final: yes to Prevent Further Refinements section for details.

For example, in the view file below we have two refinements of the faa_flights view. The first refinement hides a dimension (hidden: yes), and the second refinement displays the dimension (hidden: no). When there are conflicts like this, the refinement furthest down in the file takes precedent:

include: "//e_faa_original/views/faa_flights.view.lkml" view: +faa_flights { dimension: carrier { hidden: yes } }   view: +faa_flights { dimension: carrier { hidden: no } }

The logic is similar for including multiple files in a project: Refinements in the last file listed in the includes will take precedent. For example, if we have a model file that includes these files:

include: "/refinements/distance_analysis.lkml" include: "/refinements/finishing_touches.lkml"

Any refinements in the distance_analysis.lkml will be applied first, and then any refinements in the finishing_touches.lkml file will be applied. If there are any conflicts, the refinements in the last file, finishing_touches.lkml, will take precedent.

Because refinements leverage the order of includes, you should not use wildcards in your includes if you want to use refinements. As a general practice, it’s best to avoid using wildcards with views anyway, especially if your project has a large number of view files or if your project uses persistent derived tables (PDTs). But with refinements in particular, using wildcards in your includes is not recommended.

Using final: yes to Prevent Further Refinements

As described above, the same object can be refined multiple times in multiple places, and the last refinement will override all previous refinements.

If you have a refinement that you want to be considered the final refinement for the view or Explore, you can add the final: yes flag to the refinement. The Looker IDE will return a LookML error if there are existing refinements that would be applied after this final refinement, or if a developer tries to add a new refinement that would be applied after this final refinement. For example, the second refinement in this view file would create a LookML error because the previous refinement has the final: yes flag:

include: "//e_faa_original/views/faa_flights.view.lkml" view: +faa_flights { final: yes dimension: carrier { hidden: yes } }   view: +faa_flights { dimension: carrier { hidden: no } }

Adding the final: yes flag to a refinement is a good way to verify that your refinements are being applied in the order you intend.

Refinements Can Contain Extends

Advanced LookML developers may want to use an extends parameter inside a LookML refinement, which adds the extended object to the object being refined.

To summarize the behavior of extends and refinements:

As an example of the standard usage of refinements, here is an Explore called orders and the +orders Explore that refines it:

explore: orders { view_name: orders # other Explore parameters } explore: +orders { label: "Orders Information" # other Explore parameters to build on the original Explore }

On top of this, we can add a refinement that includes an extends. Building on the example, here is the same orders Explore. But in addition, there’s a base Explore called users_base, and now the +orders refinement has an extends parameter that brings in the users_base:

explore: users_base { view_name: users extension: required # other Explore parameters } explore: orders { view_name: orders # other Explore parameters } explore: +orders { label: "Orders Information" extends: [users_base] # other Explore parameters to build on the original Explore }

What’s special here is that the +orders refinement has an extends within it. The result is that +orders view will now extend the users_base Explore.

How Looker Implements extends Inside Refinements

Extending an object inside of a refinement is an advanced LookML concept. Before using extends in a refinement, you should have a deep understanding of the following:

Lastly, you should understand how Looker combines these principles to implement extends used in refinements. Here is the order that Looker implements, with each step overriding the previous in the case of conflicts:

  1. Values from the extends specified in the object
  2. Values from the extends specified in refinements of the object
  3. Values from the object
  4. Values from the refinements of the object

To illustrate, we’ll use the following example to follow the value of the label parameter through each step of the implementation:

explore: orders_base { label: "Orders Base" view_name: orders extension: required } explore: users_base { label: "Users Base" view_name: users extension: required } explore: orders { label: "Orders" extends: [orders_base] } explore: +orders { label: "Orders Refined" extends: [users_base] }

Here is how Looker implements the value of label for the orders Explore in this example:

  1. Values from the extends specified in the object. Since the orders Explore has an extends parameter, Looker starts with the LookML elements from the object being extended, which in this case is the orders_base Explore. At this point, the label value is “Orders Base”.
  2. Values from the extends specified in refinements of the object. Since orders has a refinement, and the refinement has an extends parameter, Looker then applies LookML elements from the refinement’s extension, which in this case is the users_base Explore. At this point, the label value is “Users Base”.
  3. Values from the object. Now that all extensions have been addressed, Looker applies elements from the extending object, which in this case is the orders Explore. If there are any conflicts, the extending object wins. So now, the label value is “Orders”.
  4. Values from the refinements of the object. Finally, Looker applies elements from any refinements of the orders Explore. If there are any conflicts, the refinement object wins. So now, the label value is “Orders Refined”.

Refinement extends Are Additive

As described in the Refinements Override Parameters section above, refinements generally override the original settings of an object. This is not exactly the case for the extends parameter. When used in a refinement, the value in the extends parameter is appended to the list of items extended in the original object or in previous refinements, if any. Then, if there are any conflicts, priority is given to the last item in the chain of extends.

For example, here is a base Explore called orders_base and an orders Explore that extends the base. In addition, there’s a users_base Explore and the +orders refinement that extends users_base:

explore: orders_base { view_name: orders extension: required # other Explore parameters } explore: users_base { view_name: users extension: required # other Explore parameters } explore: orders { extends: [orders_base] # other Explore parameters to build on the base Explore } explore: +orders { extends: [users_base] # other Explore parameters to build on the base Explores }

The orders Explore extends the orders_base, then the +orders refinements adds the users_base to the extends list. The result is that +orders Explore will now extend both orders_base and users_base, as if this were the original LookML for the Explore:

explore: orders { extends: [orders_base, users_base] }

Then, if there are any conflicts, priority is given to the last item in the chain of extends. In this example, the elements in users_base would override any conflicting elements in orders_base.

The concept of extending more than one object at a time is discussed on the Reusing Code with Extends documentation page.

One last thing to note: in this example, the order of the explore parameters doesn’t matter. But in cases with multiple refinements of the same object, the order of the refinements does matter. As described in the Refinements Are Applied in Order section above, the last refinement in a file overrides previous refinements.

Using Refinements in Your LookML Project

Here are the broad steps for refining views and Explores in your project:

  1. Identify the view or Explore you want to refine.
  2. Decide where you want to house your refinements. You can add refinements in any existing LookML file, or you can create separate LookML files for your refinements. (See the procedure for Creating a Data Test File for an example of creating generic LookML files.)
  3. Use the include parameter to incorporate your refinements into your model:
    • In the file where you write your refinements, you must include the files of the LookML that you’re refining. The Looker IDE will give you warnings if you try to refine an object that is not included.
    • In your model file, include the files where your refinements are defined. You can combine files and use includes in very creative ways; see the Using Refinements to Add Layers to Your Model section below for details.

Example

Refining LookML objects is an easy way to adapt views and Explores without having to edit the original LookML. This is especially handy when your views and Explores are read-only in your project, such as with files imported from other projects. Let’s walk through an example of refining an Explore.

Here is the LookML for the aircraft Explore:

explore: aircraft { join: aircraft_types { type: left_outer sql_on: ${aircraft.id} = ${aircraft_types.id} ;; relationship: many_to_one }   join: aircraft_engine_types { type: left_outer sql_on: ${aircraft.id} = ${aircraft_engine_types.id} ;; relationship: many_to_one } }

This Explore contains several views, each of which has many dimensions.

Now let’s say that we have another LookML project called e_faa_refined, and this project is importing the aircraft Explore file. In the e_faa_refined project we want to dramatically simplify the aircraft Explore.

Because the aircraft Explore is an imported file, we can’t edit it directly. So let’s add a refinement to it instead. In this case, we’ve created a separate file called refinements.lkml that contains this LookML:

include: "//e_faa_original/Explores/aircraft.explore.lkml" explore: +aircraft { label: "Aircraft Simplified" fields: [aircraft.aircraft_serial, aircraft.name, aircraft.count] }

The refinements.lkml file contains the following:

The end result is as if this were our original aircraft Explore and aircraft view:

explore: aircraft { label: "Aircraft Simplified" } view: aircraft { sql_table_name: flightstats.aircraft ;;   dimension: aircraft_serial { type: string sql: ${TABLE}.aircraft_serial ;; }   dimension: name { type: string sql: ${TABLE}.name ;; }   measure: count { type: count } }

Other Use Cases for Refinements

As mentioned above, refinements are ideal for adapting LookML objects that are read-only, such as Looker Blocks or imported files.

But once you get a feel for adding refinements and including them in your models, you can do very cool things with your projects, as described in the following examples.

Using Refinements to Add Analysis

You can use refinements to add analysis to your model without touching the original LookML files. For example, say we have a project where our views and Explores are generated from tables in your database and stored in a LookML file called faa_basic.lkml. We can create an faa_analysis.lkml file where we use refinements to add analysis. In this example, we’ve created a new derived table with distance analysis and then joined the derived table into an existing flights Explore. We’ve also refined the existing flights view to add new fields from the analysis:

include: "faa_basic.lkml" explore: +flights { join: distance_stats { relationship: one_to_one type: cross } } view: distance_stats { derived_table: { explore_source: flights { bind_all_filters: yes column: distance_avg {field:flights.distance_avg} column: distance_stddev {field:flights.distance_stddev} } } dimension: avg { type:number sql: CAST(${TABLE}.distance_avg as INT64) ;; } dimension: stddev { type:number sql: CAST(${TABLE}.distance_stddev as INT64) ;; } dimension: distance_auto_tier { sql: CASE WHEN ${flights.distance} < ${avg} + ${stddev} * -2 THEN CONCAT('T00 (-inf,', CAST(${avg} + ${stddev} * -2 AS STRING),')') WHEN ${flights.distance} < ${avg} + ${stddev} * -1 THEN CONCAT('T01 [', CAST(${avg} + ${stddev} * -2 AS STRING),',', CAST(${avg} + ${stddev} * -1 AS STRING),')') WHEN ${flights.distance} < ${avg} + ${stddev} * 0 THEN CONCAT('T02 [', CAST(${avg} + ${stddev} * -1 AS STRING),',', CAST(${avg} + ${stddev} * 0 AS STRING),')') WHEN ${flights.distance} < ${avg} + ${stddev} * 1 THEN CONCAT('T03 [', CAST(${avg} + ${stddev} * 0 AS STRING),',', CAST(${avg} + ${stddev} * 1 AS STRING),')') WHEN ${flights.distance} < ${avg} + ${stddev} * 2 THEN CONCAT('T04 [', CAST(${avg} + ${stddev} * 1 AS STRING),',', CAST(${avg} + ${stddev} * 2 AS STRING),')') WHEN ${flights.distance} >= ${avg} + ${stddev} * 2 THEN CONCAT('T05 [', CAST(${avg} + ${stddev} * 2 AS STRING),',', 'inf',')') ELSE 'yea' END ;; } } view: +flights { measure: distance_avg { type: average sql: ${distance} ;; } measure: distance_stddev { type: number sql: STDDEV(${distance}) ;; } dimension: distance_tiered2 { type: tier sql: ${distance} ;; tiers: [500,1300] } }

Using Refinements to Add Layers to Your Model

Another interesting use case for refinements is to add layers to your project. You can create multiple refinement files and then include them strategically to add layers.

For example, in our FAA project we can create an faa_raw.lkml file that contains all the views and Explores generated from tables in your database. This file has a view for every table in the database, each with a dimensions for each database column.

In addition to the raw file, you can create an faa_basic.lkml file to add a new layer with basic refinements, such as adding joins to your Explores, or adding measures to your views, like this:

include: "faa_raw.lkml" explore: +flights { join: carriers { sql_on: ${flights.carrier} = ${carriers.name} ;; } } view: +flights { measure: total_seats { type: sum sql: ${aircraft_models.seats} ;; } }

You can then add an faa_analysis.layer.lkml file to add a new layer with analysis (see the previous section for an example of an analysis file).

From there, we just need to include all of our refinement files into our model file. We can also use our model file to add refinements to point our views at the database tables we want to reference:

connection: "publicdata_standard_sql" include: "faa_raw.lkml" include: "faa_basic.lkml" include: "faa_analysis.lkml" view: +flights { sql_table_name: lookerdata.faa.flights;; } view: +airports { sql_table_name: lookerdata.faa.airports;; } view: +aircraft { sql_table_name: lookerdata.faa.aircraft;; } view: +aircraft_models{ sql_table_name: lookerdata.faa.aircraft_models;; } view: +carriers { sql_table_name: lookerdata.faa.carriers;; }

You can duplicate this model file and point to different database tables, or you can include different refinement files that you’ve created to define other layers you want in your model.

Using Refinements for PDTs

As described in the Refinements Compared to Extends section above, an extension creates a new copy of the object being extended. In the case of persistent derived tables (PDTs), you shouldn’t use extensions, since each extension of a PDT will create a new copy of the table in your database.

However, you can add refinements to the PDT’s view, since refinements don’t create a new copy of the object being refined.

Things to Consider

When refining an object, be aware that localization rules apply to your refinements as well. If you are refining an object and then defining new labels or descriptions, you should provide localization definitions in your project’s locale strings files. See the Localizing Your LookML Model documentation page for more information.

Top