Customize Looker

This is for:

Developer

Both the SQL behind the analytics model and the Qubit Looker block are designed to be amended, extended, or customized by clients or Qubit partners.

As part of model release cycle, Qubit may roll out new extensions. To prevent any changes from being overwritten, you should ensure that any Looker customizations are made in the v01 files, or in a new file.

The following use cases are listed in order of complexity, and are intended to be followed by clients or partners looking to customize the base content.

Adding a new column to a dashboard

Use case: A new column is needed in a dashboard.

Background:

Step 1

Select edit copy in the upper-right hand corner of the page. All looks can now be edited

Step 2

Identify the look that needs to be edited and select pencil

image43

Step 3

Pull any new measure/dimension into the Look:

image44

Step 4

Select run and then save the Look

Adding a new field to the LookML model

Use cases:

  • You require extra measures or dimensions in a model. For example, measures calculated using specific conditions such as counting visitors who have seen details pages only but exclude listing visitors, etc

  • You find yourself creating the same custom calculation over and over again. It would make more sense to add this as a permanent field

Pre-requisites: Field can be derived from existing measures and/or dimensions

Step 1

Identify the subject area you need to customize and which LookML file it it is linked to. To do this, select intro-look next to the field name in the required subject area:

image45

Step 2

Enable Development Mode by selecting Develop from the menu bar and then select ON to turn the feature. Locate the customizable file. There are 2 LookML files per each subject area:

image46

  1. A base file containing the default Qubit fields, such as q_view. Avoid editing this file. If you do, you’ll have merge conflicts with the next release of the model.

  2. A customizable file that does not have the suffix. This is where your changes should go. Example name: qview_v01_

Tip
Leading-practice

The link you selected might have taken you to either of the above files. If it has taken you to the base file, you’ll need to switch to the customizable file, which is likely displayed directly above the base file in the directory.

Step 3

Add your field to the LookML file. It is best practice to also add a field description and a meaningful label, as well as a comment in LookMl code stating why you added it:

image47

Adding a new field to the LookML model, via SQL

Use case:

  • A new field is required that requires a SQL calculation. For example, adding a window function or creating a new column entirely

The new SQL statement should select * and then additionally the new columns you want to add.

Step 1

Identify the LookML View in which the new field is needed

Step 2

If the sql_table_name parameter is present, make note of the table name, then remove the parameter and replace it with

derived_table: {
  sql:
    SELECT
      *,
      max(property_ts) as latest_data_timestamp
    FROM
      `livetap_view`
  ;;
}

Before edit:

before edit

After edit:

after edit

Adding a new table to the LookML model

Use case:

  • You want to bring in an entirely new table. The new table must have at least one field in common with the view model table so it can be joined

Pre-requisites:

  • Understand the relationship between view and your new data source

  • You must ensure that the primary key is properly defined and enforced. Normally, this means that you need to perform deduplication on the table so that there is at least one column to uniquely identify a row

Note

This means the distinct count of values in the primary key column must be equal to the total number of rows.

Step 1

Perform deduplication on your table as defined in pre-requisites

Step 2

Enable Development Mode by selecting Develop from the menu bar and then select ON to turn the feature. Now create a new LookML view

image50

Step 3

Enter a name for your view

Find your model file with name (usually model_v01), and identify the part that represents an existing explore. Add a join for the new table to the existing explore. You can do that using following template:

  join: new_table {
    view_label: "New table"
    foreign_key: view_v01.view_id
    relationship: one_to_many
  }

Joining a new table with an existing LookML view

Use case:

  • You wish to join in data from BigQuery into your Looker views, such as joining CRM data into the model using an email address or User Id.

Step 1

Perform deduplication on your table as defined in pre-requisites

Step 2

Locate the LookML view you want the new columns to appear in

Step 3

Use the derived table feature to join your table with BI dataset and add new LookML fields

Before edit:

before edit

After edit:

after edit