Looker FAQs

If you receive the error Java::JavaLang::NullPointerExceptions when scheduling data deliveries to SFTP destinations, this may be due to a bug in JRuby that prevents the use of elliptic-curve (EC) algorithms and/or host keys for SSH connections from Looker to SFTP destinations.

To avoid issues with sending data to SFTP destinations, do not use EC algorithms in the ~/.ssh/config or /etc/ssh_config files or store EC host keys in ~/.ssh on your Looker host.

Looker can authenticate users using one of several authentication server types, such as LDAP, SAML, or Google OAuth. Enabling any of these authentication methods will disable other authentication systems, such as email/password.

Admins can give users an alternate login option that uses their email address if the user or users have either an Admin role or login_special_email permissions.

How do I give an account alternate login credentials?

Implementing the alternate login option configures a user's account user_id to accept the user's email credentials when they log in to the Looker instance. Admins can use either the Looker API to make a POST request or the Looker API SDK in the programming language of the admin's choice. Both options require a few prerequisite steps:

On the authentication page of the Admin panel, enable authentication by choosing the Enabled option.

Under Migration Options, enable the Alternate Login for Specified Users switch.

The user or users must have either an Admin role or login_special_email permissions.

Making a POST Request to the Looker API

Because of its manual nature, this is a better method to use when you have a limited number of users for whom you want to set up the alternate login option.

This example uses a curl command to make a POST request to the create_user_credentials_email API endpoint using a temporary access token:

To generate the temporary token (ACCESS_TOKEN), follow the steps on the API Authentication documentation page in the Authentication without a SDK section.

Using this temporary token in the authorization header, send a POST request to the Looker API using the user's user_id, and include their email in the body of the request.

curl -H "Authorization: token ACCESS_TOKEN" -H 'Content-Type: application/json' -X POST -d '{ "email": "example_name@example_email.com" }' https://<instance_name<.api.looker.com/api/3.1/users/{user_id}/credentials_email

On the Users page of the Admin section, find the user account and click Edit.

Click the Send reset link button. This will send an email to the email address you specified in your POST request.

To use the alternate login method, when the user logs in to Looker, they will need to click the Alternate Login link below the Authenticate button and then enter their name and email address. They can still authenticate using their SAML, LDAP, or OAuth credentials through the Authenticate button.

Using the Looker API SDK

Rather than going through the manual steps of making requests directly to the Looker API, you can instead use a Looker-provided SDK to interact with the API in a programming language of your choice. After you have imported the Looker API SDK and established a client connection, follow these steps:

Use the create_user_credentials_email(user_id, body) function, inserting the user_id and body as specified in the Looker API documentation. You can follow a similar example from this Looker Community topic about automatically provisioning users with the Looker API.

Once the user accounts have been updated using the SDK method, on the Users page of the Admin section, find the user account and click Edit.

Click the Send reset link button. This will send an email to the email address you specified in your POST request.

To use the alternate login method, when the user logs in to Looker, they will need to click the Alternate Login link below the Authenticate button and then enter their name and email address. They can still authenticate using their SAML, LDAP, or OAuth credentials through the Authenticate button.

The Problem

I have made a new model, model_2, in my LookML project, and I would like my Looks to use it. However, I built my Looks based off an Explore in model_1.

How do I take my existing Look and move it to model_2, rather than creating a new Look from scratch?

Solution 1: Content Validator

The Content Validator is a tool that checks Looks and dashboards for errors, and it also has a Find and Replace in All Content feature, which you can use to change the underlying model or Explore for all Looks or dashboard tiles that use model_1.

development mode

Things to note about this method:

Users must have develop permission to use the Content Validator.

The Find and Replace in All Content feature changes all Looks and dashboards on the Looker instance. To apply this change to only a few Looks or dashboard tiles, see the solutions below.

Solution 2: Edit the URL

To change the model for only a few Looks or query-based dashboard tiles ( Look-linked dashboard tiles will automatically be updated with the new model reference), we can take advantage of the fact that Looker keeps all the information about a query in the expanded URL. To use this solution to update the model reference to model_2:

From the Look or dashboard tile, click Explore from Here.

Click the cog in the upper right of the Explore page.

Select Share.

Copy the expanded URL and paste it somewhere else, like in a new browser tab. (Don't visit the URL yet, or Looker will hash it into a shortened URL.)

Change the model name referenced in the URL. The URL takes the following form:

https://instance_name.looker.com/explore/MODEL_NAME/EXPLORE_NAME?fields=...

Visit the newly formatted URL.

Save the Explore as a Look in the same folder (in Looker versions earlier than 6.20, this was called a "Space"), replacing the old Look. To replace a query-based dashboard tile, save the Explore onto a dashboard as a tile and delete the original tile from the dashboard.

This method can be repeated for each Look or query-based dashboard tile that needs to be changed!

Solution 3: Make a "Breaking" Change

What if we don't want to change the model or Explore reference for all content across an instance, but do want to change the reference for more than one Look or dashboard tile at a time? We can leverage the Content Validator to replace the references for a handful of Looks or dashboard tiles all at once:

Purposefully "break" the existing model or Explore reference by renaming that model or Explore in . Using our example in Solution 1 above, we would change the model name from model_1 to, for example, zzz (or another model name you know doesn't exist). This will cause every Look and dashboard that uses model_1 to break. However, they will be broken only in development mode, so no other users will be affected.

Under the Develop menu, open the Content Validator.

In the Content Validator, find the looks or dashboard tiles that you want to change.

Click the inline Replace button to change the model reference to model_2 for each of these looks or dashboard tiles.

Check that the looks or dashboard tiles work as expected now that they point to the new model.

Finally, go back to the model that was "broken" and change its name back to the original. In this case, we would change zzz back to model_1.

Sometimes, you will want to restrict what date dropdown options users can seeeither on a dashboard or in the Explore itself. Thanks to Looker parameters, youll be able to hard code these options, and even give them custom names.

Liquid

How to Do It

To achieve this, you need to do the following:

Step 1: Create a date_time type parameter with all desired allowed_values. The label parameter defines how the value should show up in the UI. The value parameter needs an actual Looker filter expression. This is where you can, for example, change Lookers standard filter expression This Week to show up as Week to date:

parameter: date_filter { type: date_time allowed_value: { label: "Yesterday" value: "Yesterday" } allowed_value: { label: "Week to date" value: "This week" } allowed_value: { label: "Last week" value: "Last Week" } allowed_value: { label: "Month to date" value: "This Month" } allowed_value: { label: "Last month" value: "Last Month" } allowed_value: { label: "Quarter to date" value: "This Quarter" } allowed_value: { label: "Last quarter" value: "Last Quarter" } allowed_value: { label: "Year to date" value: "This Year" } allowed_value: { label: "Full year LY" value: "Last Year" }}

Step 2: Now, you need to add a sql_always_where parameter to the Explore definition. Here, you need to reference the previously created date_filter parameter and underlying date field using :

explore: order_items { sql_always_where: {% condition order_items.date_filter %} order_items.created_at {% endcondition %} ;;}

If the end user does not select any option from the filter dropdown, the sql_always_wherewon'thave any effect on the query, resulting in simple 1=1 condition. If a user selects one of the specified options, Looker will automatically translate the selected filter expression into a working SQL WHERE clause.

To provide more clarity, we have renamed Spaces to folders as of Looker 6.20.

Lookerbot for Slack integrates with Looker so that you can query all your data directly from Slack, allowing everyone in your company to share data easily and answer data-driven questions instantly. Lookerbot can automatically expand Looker URLs. You can also create custom commands to run saved queries.

Lookerbot requires some technical setup before you can work with it in Slack.

Expanding Looker URLs

If Lookerbot is added to a channel and the LOOKER_SLACKBOT_EXPAND_URLS variable is set to true, Lookerbot will automatically expand shared URLs and Look URLs. If the Explore has an associated visualization, Lookerbot will show an image of that visualization. If the Explore is a table, Lookerbot will show the data as text.

For example, these two URLs will expand like so:

https://learnbeta.looker.com/x/3Hq6Q2j # share URLhttps://learnbeta.looker.com/looks/263 # Look URL

configured during setup

The ability to expand Looker URLS is not available in Slack direct messages, because of the distinction made between private/public channels and direct messages in the legacy unfurling API capabilities used in Lookerbot.

Lookerbot will need to be invited to any public or private channels to be able to post in them or send data to them via actions or schedules. If you do not see that Lookerbot is available in a channel, please make sure it has been invited (on your channel settings, select Add people to #channel_name, find Lookerbot the same way you find individual users, and select it). Or, for bots using legacy tokens, make sure that the Looker application has been added to that channel (select Add an App for your channel's settings and find the Looker app).

Slack Commands

Lookerbot also allows you to create custom commands for running saved queries. This allows you to easily run common queries and answer frequent questions!

Slash commands are not required to interact with Lookerbot. To use all of Lookerbot's functions, you can also message the bot directly or mention it in a Slack channel in which Lookerbot is present: @lookerbot help

If you are also using the Slack (Beta) action, you cannot use /looker slash commands. In place of @looker or /looker, use @lookerbot or /lookerbot.

Creating Commands

Go to https://api.slack.com/apps.

Choose Slash Commands and click Create New Command.

Create a command to use for Lookerbot. For example, Looker uses /lookerbot for our internal Slack.

Set the URL to wherever you have your bot server hosted (if you used Heroku to set up the server, this will be the unique app name that you chose). The path to the slash command endpoint is /slack/receive, so if your server is at https://example.com, the URL would be https://example.com/slack/receive.

Under Settings, choose Install App again; then select Reinstall App and authenticate.

Under Basic Information, grab the verification token. You'll use this to set the SLACK_SLASH_COMMAND_TOKEN environment variable.

Creating Custom Commands

To create custom commands, make sure to specify a custom command folder, using the LOOKER_CUSTOM_COMMAND_SPACE_ID variable during setup. Any dashboard saved in this folder will be available to use as a command.

To create a new command:

Create a dashboard with a single element in the Lookerbot command folder. Currently, only dashboards with single elements can be used with Lookerbot.

Give the dashboard a discoverable name. The name of the dashboard will be the name of the command. We recommend using only a few lowercase letters for ease of use.

Add dashboard filters to pass filter values to the command if necessary.

Optionally, add a description to the dashboard. This will appear next to the command when a user types help.

If you would like to split up commands into logical groups, you can create subfolders within the Lookerbot command folder. This will group the commands in the help list.

Using Commands

You use a command by typing the name of the command, and then any filter values you want to add. Filters are optional, and the query will run without that filter if no values are specified. For example, we can run the customer map in command, filtered for New York, like so:

There are three ways to use commands:

If you are also using the Slack (Beta) action, you cannot use /looker slash commands. In place of @looker or /looker, use @lookerbot or /lookerbot.

@mention Lookerbot. This can be done in any channel in which Lookerbot is present.

Use the slash command. This can be done if the slash command has been .

Direct message Lookerbot. If you send a message directly to Lookerbot, you don't need to prepend the command with anything.

Data Formats

Commands will return data in different ways depending on the visualization type of the underlying Look. There are three possible return formats:

Text: If the underlying Look is a table, the data will return as text:

Single Value Text: If the underlying Look is a single value visualization, the data will return as a text value:

Image: If the underlying Look is a chart, the data will return as an image of the visualization:

Hiding Commands and Subfolders

If you have some commands you do not want to appear in the help menu but that you still want to use with Lookerbot, you can hide either the command or the entire subfolder in which the command is saved.

Hide a command or subfolder by starting the name with [Hidden]. For example, this subfolder and this command will not be visible in the help menu:

Lookerbot's help menu lists the available commands as well as a description of the dashboard that gets returned:

In Looker, navigate to the dashboard.

Click Edit, and then click the Settings icon.

In the Description field, enter the description you want to appear in the Lookerbot help menu.

Click Save.

Looker 6.24 introduces a new Slack integration action where you can send or schedule data deliveries to Slack, including direct messages and messages to public or private channels. The existing Slack integration, Slack Attachment (API Token), is still available.

The new Slack integration is in beta and is available to Looker-hosted deployments that satisfy these conditions:

The Looker instance is on 6.24 or later.

The Looker instance is not IP whitelisted.

Enabling the Slack (Beta) Action

Looker admins must go to Admin > Labs and enable the Slack App Labs feature.

After the Labs feature is enabled, Looker admins must go to Admin > Actions to enable the Slack (Beta) action in the Looker Action Hub and connect it to the Slack workspace.

In Looker 6.24, you cannot change your Slack workspace connection, so tread wisely!

From the Actions page, scroll down to the Slack (Beta) action, and click the Enable switch.

On the Slack (Beta) page, click Connect. Looker will request access to the Slack workspace.

Click Allow, and return to the Slack (Beta) page.

Click the Enable switch, and then click Save. Your connection should be indicated by the Test Action field. You can click Test Again to test your Looker connection to your Slack workspace.

Authenticating into Your Slack Workspace

You must authenticate into the same Slack workspace that your Looker admin has connected to Looker.

There are two options for authenticating into your Slack workspace for the first time:

From the Account page

From the Send or Schedule window

Authenticating into Your Slack Workspace from the Account Page

In Looker, click your user profile icon in the upper right of the screen, and select Account.

Under Integrations, click Sign in with Slack. Looker Action Hub

Looker will request access to the Slack workspace. Click Allow.

You're authenticated and can now start to send or schedule dashboards, Looks, and Explores.

Be careful not to revoke your Slack token if you do, any schedules you've created will fail.

Authenticating into Your Slack Workspace from the Send or Schedule Window

See the next section for more information on delivering data to the Slack action.

In Looker, navigate to the content that you'd like to deliver, click the gear menu, and then click Send or Schedule.

In the Send or Schedule window, select the Slack (Beta) action as the delivery destination under Where should this data go?

Next to Slack, click Log in.

Looker will request access to the Slack workspace. Click Allow.

The Send or Schedule window should now show your Slack delivery options.

Delivering Data to Slack (Beta)

Before you can deliver data to the new Slack (Beta) action, Looker admins need to follow the steps above to enable the Slack App Labs feature and enable the Slack (Beta) action in the . You must also authenticate into the same Slack workspace that your admin has connected to Looker.

To deliver a user-defined dashboard, a LookML dashboard, a Look, or an Explore, first navigate to your content and then:

Click the gear menu, and click Send or Schedule, depending on what is available for your content.

In the Send or Schedule window, select the Slack (Beta) action as the delivery destination under Where should this data go? If this is your first time using the Slack (Beta) action, you will need to authenticate into Slack (see the previous section about authenticating into your Slack workspace).

Once you authenticate into Slack, Looker will display delivery options that are specific to Slack (Beta):

In the Share In field, enter the name of the Slack channel where you want to post your data. You can send direct messages or messages to public or private channels. Include the leading # character. For example: #Sales.

In the Comment field, enter any text you want to include with the delivery.

In the Filename field, enter the name you want to give to the attachment file.

If you are scheduling a data delivery, once you have configured all the options, click Save All, and your query or dashboard will be delivered to Slack as scheduled. Or, if you are sending your data, click Send, and your data will be delivered to your Slack channel.

Sharing Links to Looker Content in Slack

You can share links to user-defined dashboards, LookML dashboards, Looks (but not public embedded Looks), or Explores in Slack.

Once the link unfurls in Slack to show the content's title and some of its metadata, other users can click buttons to post it to the channel or add it to their Looker Favorites folder. The content title and metadata will be visible only to those who have access to the underlying content. However, once the content is posted to the channel, anyone in the channel will be able to view the content, even if they do not have access to the underlying content in Looker.

Once you've added content to your Favorites folder, the Looker Slack app will display a message in Slack with a link to access your Favorites folder in Looker.

Using Slash Commands to Retrieve Looker Information in Slack

You can use the following slash commands to retrieve information from Looker and post it to Slack:

/looker and /looker whoami The Looker Slack app greets you by name, tells you the Looker instance to which you're connected, and provides links to your Looker Favorites folder, your personal folder, and Help.

/looker help The Looker Slack app lists supported slash commands.

/looker favorites The Looker Slack app lists the content from your Looker Favorites folder. Click the ellipsis (...) beside each piece of content to access a menu from which you can Post to this Channel or Remove from Favorites.

/looker folder The Looker Slack app lists the content from your Looker personal folder. Click the ellipsis (...) beside each piece of content to access a menu from which you can Post to this Channel or Remove from Favorites (if the content is also in your Favorites folder).

This article can help you troubleshoot the "Non-unique value/primary key (or sql_distinct_key), value overflow or collision when computing sum" error in Looker.

This error can be caused by several issues:

Most Commonly, a non-unique primary key

Incorrect usage of sql_distinct_key

Referencing a field across views when fanouts are involved

Non-Unique Primary Key

The most common cause of this error is that your query involves a non-unique primary key. You specify a primary key by using primary_key: yes on a dimension, and it must be a dimension without any repeated values.

Quick Fix

Once you identify the primary key dimensions in your query, you can test them for uniqueness in Looker's SQL Runner with this query:

SELECT

COUNT(*),

COUNT(DISTINCT your_primary_key)

FROM

your_table_name

If the counts in this query match, the primary key is unique. If the counts do not match, the primary key is not unique and appears in multiple rows. You will need to choose or create a new dimension as your primary key. If no single dimension contains entirely unique values, you may need to create your own primary key dimension.

Incorrect Usage of sql_distinct_key

If any of the measures in your query are of the type sum_distinct, there may be a uniqueness mismatch between the sql_distinct_key and sql parameters of that measure.

Quick Fix

Check out our sum_distinct documentation page for these parameters' requirements.

Referencing Fields Across Views with Fanouts

Your query may use a measure from view A, but the measure references a field from view B. In this situation Looker will use the primary key from view A to calculate that measure. If your query involves a fanout, that may not be the correct primary key to use. (To become familiar with fanouts, check out our related blog entry.)

Quick Fix

To resolve this problem, add the primary key from view B to the problem measure with the sql_distinct_key parameter.

What If None of the Listed Causes Apply, but the Error Still Occurs?

There is a very specific situation in which your primary key can be unique, and the other causes of this problem don't apply to your query, but this error still occurs. First, your query will involve multiple joins of relationship: one_to_many. Second, one of the measures in your query will reference a dimension that combines values from multiple joined views.

To fix this problem, make a note of that measure, then follow these steps:

Find the dimension within the measure that combines values from multiple joined views.

Find the views that are referenced by that dimension.

Concatenate the primary keys from those views using your SQL dialect's concatenation function.

Place that concatenated key into a sql_distinct_key parameter on the problem measure.

Looker has introduced a parameter field, which lets you inject a value directly into the SQL.

There are various materials that show how to use this feature:

The Templated Filters and Parameters documentation page, which discusses creating the field and applying the user input.

The parameter reference page for this field type and its allowed_value subparameter.

The Field Parameters page, which includes the parameter field type and shows which LookML parameters can be subparameters of a parameter field.

The Liquid Variable Reference documentation page, which mentions the {% parameter %} Liquid variable.

The Dimension, Filter and Parameter Types documentation page, which indicates which LookML type parameter values can be used with parameter fields.

In this article, we discuss some great use cases for this feature.

1. Dynamic Dimensions and Measures

Sometimes, we do not want to show all the dimensions and measures in the field picker of an Explore, but still would like to provide the functionality behind each of them.

This can be achieved by creating a dynamic dimension or measure, which lets the user specify the dimension or measure from the filter UI. To do this, you first define a parameter field with a set of allowed_value subparameters:

parameter: date_granularity {

type: string

allowed_value: { value: "Day" }

allowed_value: { value: "Month" }

allowed_value: { value: "Quarter" }

allowed_value: { value: "Year" }

}

The parameter is surfaced as a filter-only field in the UI, and the user can choose one of the allowed values:

dimension fill

You then define a dimension to dynamically choose the column it retrieves, based on the parameter value the user chooses:

Note: Because the resulting dynamic dimension is type: string, cannot be applied to the dimension in an Explore.

dimension: date {

label_from_parameter: date_granularity

sql:

CASE

WHEN {% parameter date_granularity %} = 'Day'

THEN ${created_date}::VARCHAR

WHEN {% parameter date_granularity %} = 'Month'

THEN ${created_month}::VARCHAR

WHEN {% parameter date_granularity %} = 'Quarter'

THEN ${created_quarter}::VARCHAR

WHEN {% parameter date_granularity %} = 'Year'

THEN ${created_year}::VARCHAR

ELSE NULL

END ;;

}

Your SQL dialect may or may not require casting to VARCHAR. Some dialects require that everything in the CASE statement be of the same type.

The same approach can be used for dynamic measures. First, create the parameter with a set of allowed_value parameters:

parameter: metric_selector {

type: string

allowed_value: {

label: "Total Order Profit"

value: "total_order_profit"

}

allowed_value: {

label: "First-Time Shopper Revenue"

value: "total_first_purchase_revenue"

}

allowed_value: {

label: "Returning Shopper Revenue"

value: "total_returning_shopper_revenue"

}

}

This lets the user select the metric:

Then, create a measure that dynamically uses the chosen metric:

measure: metric {

label_from_parameter: metric_selector

type: number

value_format: "$0.0,\"K\""

sql:

CASE

WHEN {% parameter metric_selector %} = 'total_order_profit'

THEN ${total_order_profit}

WHEN {% parameter metric_selector %} = 'total_first_purchase_revenue'

THEN ${total_first_purchase_revenue}

WHEN {% parameter metric_selector %} = 'total_returning_shopper_revenue'

THEN ${total_returning_shopper_revenue}

ELSE NULL

END ;;

}

2. Multiple Schemas With the Same Data Format

Another use case is schemas that are identical in structure. This is often encountered when working with GA Premium data, where one can see tables such as myproject:00001.ga_sessions, myproject:00002.ga_sessions, or myproject:00003.ga_sessions. The 00001, 00002, 00003 prefixes could represent data for different countries, or for Web vs. iOS, etc.

Using a parameter, you can design a dashboard with a filter that lets your users select the country with a user-friendly name!

view: t2 {

sql_table_name: {% parameter tablename %};;

parameter: tablename {

type: unquoted

allowed_value: {

label: UK

value: 00001.ga_sessions

}

allowed_value: {

label: Germany

value: 00002.ga_sessions

}

}

}

If your company has multiple clients, you might store data from each of your clients in its own schema: clienta.users, clientb.users, and so on. Using a similar approach to the country example above, you can let an account manager easily choose which client's data to use.

3. Top N Items vs. the Rest of the Population

People often want to find out the top N performing products and compare those with other products.

Without a parameter, you would have to hard-code certain buckets (for example: top 5, 10, 20, and so forth.) However, with a parameter we can let the end user determine N, and we can label the remainder as Other.

In the context of an e-commerce store, where we want to find out how well the top N products are doing, we would start with a derived table, where we rank each product based on the number of sales.

view: brand_rankings {

derived_table: {

sql:

SELECT brand, count(*) as count, RANK() OVER(ORDER BY COUNT(*) DESC) as rank

FROM order_items LEFT JOIN inventory_items on order_items.inventory_item_id=inventory_items.id

LEFT JOIN products ON inventory_items.product_id = products.id

GROUP BY 1 ;;

}

dimension: brand {

type: string

primary_key: yes

sql: ${TABLE}.brand ;;

}

dimension: rank_raw {

type: number

sql: ${TABLE}.rank ;;

}

}

Using a parameter, we let users specify how many of the top brands they're interested in.

parameter: max_brands {

type: number

}

By referencing the parameter inside the sql parameter of our rank dimension, we can then create the Other bucket on the fly.

dimension: rank {

type: string

sql: CASE WHEN ${rank_raw} <= {% parameter max_brands %} THEN RIGHT('00' + CAST(${rank_raw} AS VARCHAR), 2) ELSE 'Other' END;;

}

dimension: rank_and_brand {

type: string

sql: CASE WHEN ${rank} = 'Other' THEN 'Other' ELSE ${rank} || '-' || ${brand} END;;

}

This results in the user's top N items having their own row and the rest being grouped together as Other:

Additionally, we can create a ranking of countries we have sold to, and set that ranking based on a metric not shown in the visualization, and over a date range different from what is shown in the results.

To do this, create a view like this:

view: top_5_countries {

label: "Countries Ranking"

derived_table: {

sql:

select

country_id as country_code,

{% parameter country_name_criteria %} as country_rank

from

(

select country_code,

rank() over(order by count(*) desc) as visitorCount,

rank() over(order by sum(orders.gross_revenue) desc) as totalGrossRevenue,

rank() over(order by avg(orders.gross_revenue) desc) as averageGrossRevenue

FROM orders

WHERE

{% condition rank_date_range %}created_at {% endcondition %}

group by country_code

) AS country_summary ;;

}

filter: rank_date_range {

type: date

description: "Select a range within which you are ranking the ordering of countries by metric selection. E.g. the rank of countries who had the top 10 highest revenue in May"

}

dimension: country_code {

primary_key: yes

hidden: yes

type: string

sql: ${TABLE}.country_code ;;

}

parameter: country_name_criteria {

label: "Ranking Criteria"

description: "Specify which metric to order the ranking by"

type: unquoted

default_value: "totalGrossRevenue"

allowed_value: {

label: "Total Gross Revenue"

value: "totalGrossRevenue"

}

allowed_value: {

label: "Average Gross Revenue"

value: "averageGrossRevenue"

}

allowed_value: {

label: "Visitor Count"

value: "visitorCount"

}

}

#### This parameter will allow a user to select a Top N ranking limit for bucketing the countries, almost like parameterizing the Row Limit in the UI

parameter: country_rank_limit {

label: "Rank Limit"

description: "Specify the cutoff for overall rank"

type: unquoted

default_value: "5"

allowed_value: {

label: "Top 5"

value: "5"

}

allowed_value: {

label: "Top 10"

value: "10"

}

allowed_value: {

label: "Top 20"

value: "20"

}

allowed_value: {

label: "Top 50"

value: "50"

}

}

dimension: country_rank_top_N {

hidden: yes

description: "Rank within the range selected and list of countries based on metric selected. Useful for sorting visualisation based on ranking."

label_from_parameter: country_name_criteria

label: "Country Code"

type: number

sql: case when ${TABLE}.country_rank<={% parameter country_rank_limit %} then ${TABLE}.country_rank else null end ;;

}

dimension: country_name_top_N {

description: "Name of the Country within the rank selection."

label: "Country Name (Top N)"

type: string

sql: case when ${TABLE}.country_rank<={% parameter country_rank_limit %} then ${TABLE}.country_code else 'other' end ;;

}

}

And then join it to the base Explore:

explore: orders {

join: top_5_countries {

type: inner

sql_on: ${top_5_countries.country_name_top_N} = ${orders.country_code} ;;

relationship: many_to_one

}

}

This allows us to answer such edge-case questions as, "Show me the top 10 countries and their Gross Revenue for the last 7 days, based on how many unique visitors those countries had in the 7 days prior, and display them in order of that visitor count."

4. Conditional Display Value Prompts

Another great use case is defining a threshold (say on sale price) and using that to conditionally format the results. Defining a parameter lets the user set the sensitivity in the UI. Then, by referencing the parameter in the html of the measure, we can conditionally format results.

This is shown below:

parameter: sale_price_sensitivity {

type: number

}

measure: sale_price_sens {

type: number

sql: ${sale_price} ;;

html: {% assign var=_filters['order_items.sale_price_sensitivity'] | plus:0 %}

{% if var < order_items.sale_price._value %}

<div style="color: black; background-color: red; font-size:100%; text-align:center">{{ rendered_value }}</div>

{% else %}

{{rendered_value}}

{% endif %} ;;

}

When a user sets the sale price sensitivity, their query results use conditional color for any sales price at or above that sale price, for example:

The Problem

You may occasionally find that the LookML you see in Looker's production mode doesn't match the LookML on the master branch of your Git repository. This most commonly occurs for one of the following reasons:

Pull requests are enabled on the repository

The LookML has been edited somewhere outside of Looker's development environment, like:

the Git repository itself

another instance of Looker, like a staging instance

The Solution

You can get Looker's production branch in sync with the repository's master branch. Open a new tab and type in a URL following this format:

[instance_url]/webhooks/projects/[project name]/deploy

Replace [instance_url] with your Looker instance URL, which is in a format like instance_name.looker.com. Replace [project name] with the name of the project you want to sync.

After you run this URL, a white screen with a success message will appear:

webhook deploy secret

Now, your production mode in Looker has been updated to reflect your remote master branch. Your personal developer branch and shared branches are unaffected.

Automation

Many Git providers can hit webhooks like this one after every commit, or based on other events you define. This can help automate the process of keeping Looker in sync.

If your Looker project is integrated with pull requests, the Git deploy webhook was most likely set up as part of the process of configuring the project with pull requests.

If not, you can add the Git deploy webhook by doing the following:

From your project, select Project Settings from the Git drop-down menu.

From the Git Integration section, copy the webhook information and paste it to a text file to use later in this procedure.

Next, use your Git provider's interface to create a trigger for the webhook. Here's an example using GitHub:

Navigate to your project's repository settings on your Git provider's website.

In your repository's settings, click on Webhooks.

Click Add webhook. This will open the Add webhooks window.

In the Payload URL text box, paste the webhook information you copied from the Git Integration section in Looker.

Select the option for Just the push event.

Click Add webhook.

See our documentation on Git integration options for further information on setting up the deploy webhook.

You can also require a for pushing changes from your master branch to your production branch.

The Error

A Look or dashboard throws the following error:

Could not find the model or view requested.

The Cause

Each Look or dashboard query is based on a model and an Explore. If the name of the model or Explore is changed in LookML, then the Look or dashboard query will no longer be able to find it.

For example, if the name of an Explore is changed from old-name to new-name, any associated Looks or dashboard queries will still try to use the old-name Explore.

The Solution

1. Use a Label Instead

To just change the name of the Explore in the Explore menu, we can use the label parameter. This avoids the error altogether by preserving the old name in the LookML, while showing users the new name.

explore: old {

label: "new"

...

}

2. Use the Content Validator

The Content Validator has a find and replace functionality. We can use that to replace the Explore name across all Looks. This will change every Look and dashboard query that uses old-name, so take this step with caution.

3. Change the URL

The expanded URL of a query contains the model name, Explore name, and field names. We can take advantage of this functionality to change the Explore name of our Look or dashboard query.

From the Look or dashboard query, select Explore From Here.

Click the gear in the top right next to Run and Edit, then select Share.

Copy the Expanded URL and paste it somewhere else like your browser. (Do not press Enter yet. Otherwise, it will turn into a shortened URL.)

Modify the URL text to change the name of the Explore. The URL takes the form:

https://instance_name.looker.com/explore/MODEL_NAME/EXPLORE_NAME?fields=...

Press Enter or Return to navigate to the URL, then save the Explore as a Look or dashboard query.

Why Do I Need a Data Dictionary in Google Sheets?

While Looker empowers more users to access and explore data, the meanings of defined fields or their sources can be a mystery to many users, leading to confusion and incorrect queries. One way to prevent this confusion is to define a data dictionary using the Looker API to populate field information into an easily accessible Google Sheet. This article describes this process. The solution below is particularly helpful when a description parameter is defined for fields in views.

The Script

This script is defined with a mix of Apps Script and Javascript, using the functions listed in this repository.

Following are some key elements to keep in mind when using the script:

The model_name and explore_name are case-sensitive.

The script populates each view's information into an individual Google Sheet. Each Google Sheet name will match a view name (to populate the entire contents into one sheet, please check out the note below to see the lines enforcing this behavior).

In this script, we use Google Sheet mapping to each view in an Explore. Each Google Sheet is named with the corresponding view name, and only data relevant to that view is displayed in the Google Sheet.

This script also caches the output of the API call for six hours after opening a sheet.

// Replace this with your base domain

var BASE_URL = 'https://instance_name.looker.com:19999/api/3.1';

// Replace this with your API credentials

var CLIENT_ID = 'your_api_ID';

var CLIENT_SECRET = 'your_api_key';

function LOOKER_GET_DATA_DICTIONARY(model_name) {

//checks for previous cached entry

var cache = CacheService.getScriptCache();

var cached = cache.get("api_results");

if (cached != null) {

for (elem in cached) {

results.push(elem)

return results;

}

}

// if nothing in cache, run the call

try {

var options = {

'method': 'get',

'headers': {

'Authorization': 'token ' + login()

}

};

// api call to the /lookml_models/{lookml_model_name} endpoint

var response = UrlFetchApp.fetch(BASE_URL + "/lookml_models/" + model_name, options);

var explores = JSON.parse(response.getContentText()).explores;

var result = [];

// defining the fields to retrieve for the Google Sheets

result.push(["View Name", "Field Type", "Name", "Label", "Type", "Description", "Hidden"]);

// additional details if needed:

//, "SQL", "Source"]);

for (var i = 0; len = explores.length, i < len; i++) {

Logger.log(explores);

var explore = explores[i].name;

var explore_results = UrlFetchApp.fetch(BASE_URL + "/lookml_models/" + model_name + "/explores/" + explore, options);

var connection = JSON.parse(explore_results.getContentText()).connection_name;

var dimensions = JSON.parse(explore_results.getContentText()).fields.dimensions;

var measures = JSON.parse(explore_results.getContentText()).fields.measures;

var current_sheet = SpreadsheetApp.getActiveSheet().getName();

// using this test to retrieve only data relevant to a specific explore

// change explore_name with your explore

if (explore == "explore_name") {

// adding the data for the dimensions

for (var j = 0; j < dimensions.length; j++) {

// checks that only the fields from the underlying Looker view matching the name of the Google sheet are displayed

if (dimensions[j].view.replace("_", " ") == current_sheet.toLowerCase()|| dimensions[j].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase()) {

result.push([dimensions[j].view,

"Dimension",

(dimensions[j].name.substring((dimensions[j].name.indexOf(".")+1), dimensions[j].name.length)).replace(/_/g, " "),

(dimensions[j].label != null ? dimensions[j].label : (dimensions[j].name.substring((dimensions[j].name.indexOf(".")+1), dimensions[j].name.length).replace(/_/g, " "))),

(dimensions[j].type != null ? (dimensions[j].type).replace("_", " ") : "String"),

dimensions[j].description,

dimensions[j].hidden, dimensions[j].view_label

//, (dimensions[j].sql != null ? dimensions[j].sql : ""),

//dimensions[j].source_file

]);

}

}

// adding the data for the measures

for (var k = 0; k < measures.length; k++) {

// checks that only the fields from the view matching the name of the sheet are displayed

if (measures[k].view.replace("_", " ") == current_sheet.toLowerCase() || measures[k].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase()) {

result.push([measures[k].view,

"Measure",

(measures[k].name.substring((measures[k].name.indexOf(".")+1), measures[k].name.length).replace(/_/g, " ")),

(measures[k].label != null ? measures[k].label : (measures[k].name.substring((measures[k].name.indexOf(".")+1), measures[k].name.length)).replace(/_/g, " ")),

(measures[k].type != null ? (measures[k].type).replace("_", " ") : "String"),

measures[k].description,

measures[k].hidden

//, (measures[k].sql != null ? measures[k].sql : ""),

//measures[k].source_file

]);

}

}

}

}

cache.put("api_results", result, 21600); // cache for 6 hours

//the maximum cache time is 6 hours (21600 seconds)

//the default time is 10 minutes (600 seconds).

return result

} catch(err) {

return "Something went wrong. " + err

}

}

function login() {

try {

var post = {

'method': 'post'

};

var response = UrlFetchApp.fetch(BASE_URL + "/login?client_id=" + CLIENT_ID + "&client_secret=" + CLIENT_SECRET, post);

return JSON.parse(response.getContentText()).access_token;

} catch(err) {

Logger.log(err);

return "Could not login to Looker. Check your credentials.";

}

}

With the code above, we can set a cell of our Google Sheet to call the function on a particular model: =LOOKER_GET_DATA_DICTIONARY("model_name")

NOTE: Instead of storing the API credentials in the script directly, you can also use the following code from the repository to require that the credentials be entered in the UI on the Google Sheet:

// set credentials via prompt

function setCred() {

var ui = SpreadsheetApp.getUi();

var base_url_input = ui.prompt("Set your Looker API credentials", "Base URL (e.g. https://instance_name.looker.com:19999/api/3.1):", ui.ButtonSet.OK_CANCEL);

var client_id_input = ui.prompt("Set your Looker API credentials", "Client ID:", ui.ButtonSet.OK_CANCEL);

var client_id_secret = ui.prompt("Set your Looker API credentials", "Client Secret:", ui.ButtonSet.OK_CANCEL);

var scriptProperties = PropertiesService.getScriptProperties();

// assign them to scriptProperties so the user doesn't have to enter them over and over again

scriptProperties.setProperty("BASE_URL", base_url_input.getResponseText());

scriptProperties.setProperty("CLIENT_ID", client_id_input.getResponseText());

scriptProperties.setProperty("CLIENT_SECRET", client_id_secret.getResponseText());

// test the credentials with a /user call

testCred();

}

// testing the existing creds

function testCred() {

var ui = SpreadsheetApp.getUi();

var options = {

"method": "get",

"headers": {

"Authorization": "token " + login()

}

};

try {

var response = UrlFetchApp.fetch(BASE_URL + "/user", options);

var success_header = "Successfully set API credentials!";

var success_content = "Authenticated as " + JSON.parse(response.getContentText()).first_name +

" " + JSON.parse(response.getContentText()).last_name + " (user " + JSON.parse(response.getContentText()).id +").

Keep in mind that API credentials are script/spreadsheet bound. This is needed for the custom formulas to keep on working for other users.

Hit 'Test' to test your credentials or 'Delete' to remove the currently set credentials.";

var result = ui.alert(success_header, success_content, ui.ButtonSet.OK);

} catch (err) {

var result = ui.alert("Invalid credentials / Credentials not set!",

"Doublecheck your base URL and your client ID & secret.", ui.ButtonSet.OK);

}

}

// delete credentials from scriptProperties

function deleteCred() {

var scriptProperties = PropertiesService.getScriptProperties();

scriptProperties.deleteAllProperties();

}

Populating the Entire Output into One Sheet

As noted above, the script populates one Google Sheet per view. If you would like to populate the entire output into one single Sheet, remove the following sections from the above script:

if (dimensions[j].view.replace("_", "") == current_sheet.toLowerCase()|| dimensions[j].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase())

if (measures[k].view.replace("_", "") == current_sheet.toLowerCase() || measures[k].view_label.replace("_", " ").toLowerCase() == current_sheet.toLowerCase())

Additional Resources

This Community post, Writing a Simple Data Dictionary to CSV Using the Looker API and the Python Requests Library, for a detailed workflow to create a CSV data dictionary with Python.

This Help Center article, Creating a Data Dictionary Using Looker's API, also details how to achieve this with Ruby.

Note: As of Looker 6.20, Spaces have been renamed to folders and all space API endpoints are automatically redirected to folder endpoints.

Effectively managing content access in a large Looker deployment is a critical step for protecting your data and your customers' data. Occasionally, it may make the most sense to automate this process with Looker's API. This is especially true for an embedded Looker deployment.

This article will walk through a sequence of API calls that enable you to add or edit content access permissions for both groups and users. We will be leveraging Looker's interactive API docs, which can typically be accessed by the following URL (replacing <instance_name.looker.com> with the URL of your Looker instance): https://<instance_name.looker.com>:19999/api-docs/index.html. If the default URL doesn't respond, check with your Looker administrator for the correct URL for your Looker installation.

You will need an API key and secret to log in and access the Looker API. Please reference the Users documentation for details about generating and using API credentials.

What's Covered in This Article

Checking content access for a given folder

Modifying existing content access permissions for a given folder via the API

Adding content access permissions to a given folder via the API

Checking Content Access for a Given Folder via the API

Folder permissions can be found and updated via the content endpoints. Each space_id has a corresponding content_metadata_id. First, we will need to determine a Space's content_metadata_id, for use in API calls that modify Space permissions.

Example:

We have a folder ID number 115. Using the Get Space endpoint, we can find what that folder's content metadata ID is.

Create Content Metadata Access

From the response, we can see that folder 115's content metadata ID is 24. Now that we have the content metadata ID, we can modify the access to this folder.

Modifying Existing Content Access Permissions for a Given Folder via the API

If you want to modify existing access to this folder, you will want to use the Get All Content Metadata Access endpoint to get existing permissions.

Note: To only get the access for this specific folder, you'll want to define the content access ID in the call, since this endpoint returns all content access by default.

Example:

From the response, we can see that Group ID 32 has View access to this folder (although the response doesn't show this, admins also always have Edit access to each folder).

If we want to modify this so Group ID 32 has edit access to this folder instead, we will use the Update Content Metadata Access endpoint. Grabbing the ID from the previous response, our body for the update endpoint will look something like:

{ "permission_type": "edit"}

And you are done! Group ID 32 now has edit permissions for that folder.

Adding Content Access Permissions to a Given Folder via the API

Now, we want to grant an individual user view access to folder ID 115.

The first thing we will need is that user's ID. Once we have this, we can use the endpoint to create a new content metadata access ID for that folder.

All you need for this endpoint is the content metadata ID for the folder, the user or group ID, and the permission type:

{ "content_metadata_id": "24", "permission_type": "view", "user_id": 323}

And that's it! User ID 323 now has view access to Folder 115.

One Thing to Note

If the folder inherits content access permissions from a parent folder, that folder will return an empty response for the content metadata access endpoint.

We often want to use a saved look as a sort of template and then dynamically override specific filter values, such as when weembed Looker into another application.

This article shows you how to do this.

Overriding Filters

The following example shows a Look where the default value for Orders Created At is saved as on the day 2017-11-01.

We see this URL (replacing <instance_name.looker.com> with the URL to your company's instance): https://<instance_name.looker.com>/looks/:id: In this case, toggle=fil appears in the URL, because we have expanded the filters box to show the value.

Looker Filter Expressions

We can override the filter value by visiting a URL and changing it to this: https://<instance_name.looker.com>/looks/:id?f[orders.created_date]=7+days

Looker automatically rewrites the URL into a uniquequery ID (qid) parameter representing this ad hoc query, just like on an Explore page.

This is the format of the query parameter: f[view.field_name]=filter_expression&f[view.other_field]=filter_expression&etc

Read more about filter expressions on the documentation page.

Other Query Parameters

You can also override other query parameters besides the filter values, such as sort or limit, or even the visualization options. To see the full list of parameters:

Create a query in the Explore page.

Using the gear menu in the top right corner, select Share.

Copy the URL in the Expanded URL field and paste it into a text editor to see the entire URL with all parameters.

Embedding

This technique works with embedding, too:

Add/embed/ in front as usual, and it will work as above.

For SSO embed, make sure you add this to the embed_path component prior to signing the iframe URL.

Goal: Embed Looker iframes into the tools and applications that people are already using in your organization to help bring data into their day-to-day workflows and decision-making processes.

Embedding allows you to bring Looker reports and dashboards into the tools your employees use the most. Private embedding allows you to embed a Look, Explore or dashboard that only Looker users can see.

Here are 5 quick steps for privately embedding a dashboard into applications of webpages within your organization:

Step 1: Identify the dashboard that you want to embed:

https://docs.looker.com/sharing-and-publishing/embedding

Step 2: Update the embed settings to align the look and feel of the dashboard with the application or webpage where it will be embedded:

For the embedded version of the dashboard, you can adjust:

Page and tile background colors

Text and title colors

Visibility of titles and filters

Tile location

The default version will be the same look and feel that you see when you log into Looker to view the dashboard:

If you were embedding a Looker dashboard into an application or webpage with a dark background, you might changes the settings to make the dashboard look more like this:

Once you have made updates, save your settings and preview your embedded version of the dashboard to ensure that you are happy with it:

Step 3: Add /embed/ to the URL.

For example:

https://instance_name.looker.com/dashboards/4

Becomes

https://instance_name.looker.com/embed/dashboards/4

Step 4: Create an iframe with your embedded look or dashboard that can be added into any website or application.

<iframe src="https://instance_name.looker.com/embed/dashboards/4">

Step 5: Insert your iframe code into your application or webpage! You may need help from a system admin of web developer for this step. Looker dashboards (or Looks or Explores) can be embedded into applications like SalesForce, Confluence, Zendesk, and more.

Additional info:

https://llooker.github.io/office-hours-notes/embed.html#exercise

If you're occasionally using Google Sheets to perform on-the-go calculations based on data in Looker, you have probably run into this issue: for the data to be available, you need to save the Look as public in order to bring it into Google Sheets. While this is still a great way to bring data from Looker into Google Sheets (see the Looker-Built Google Sheets Import Script Help Center article), you can also use the Looker API for this. This is the cool part you can do it for any Look you have access to without having to rewrite anything.

The Google Apps script has the built-in URL Fetch Service, which allows us to make HTTP requests straight from our spreadsheet.

In this example, we will define a custom function in Sheets that allows us to bring in the results from any Look.

Looker API documentation You can find the latest version of the script on Github.

The Steps

Open your spreadsheet and navigate to Tools > Script editor. Copy/paste the script below in the editor; you can remove the dummy function myFunction().

At the top, change the base URL to your company domain for Looker, and add in your API credentials (client ID and secret). Note: It's important to set appropriate limits on the API credentials of the user tied to the script, because the same credentials will be accessible to anyone who accesses the sheet.

Click save and give the project a name.

Navigate back to your spreadsheet and start typing LOOKER_RUN_LOOK().

=LOOKER_RUN_LOOK(look_id, format, query_limit)

look_id ( number ) The ID of the Look, for example, 345.

format ( number, optional ) The requested format. The number 1 returns data; the number 2 returns the underlying SQL. The default is 1 (data).

query_limit ( number, optional ) The query limit. Defaults to 5000 if left empty.

You should now have the custom function available in your spreadsheet:

You will see the second parameter taking either 1 or 2 as input. In this function, 1 is used for the results, and 2 is used for the underlying SQL query.

Now, in this spreadsheet, you can query the results of every Look you have access to in Looker!

The Full Script

NOTE: The following script uses Looker API 3.1, which is the default API for Looker 6.12+. For those using Looker API 3.0, please see Migrating to a New API Version for tips on migrating application code to use a new Looker API version.

// Replace this with your base domain e.g. https://instance_name.looker.com:19999/api/3.1var BASE_URL = 'https://instance_name.looker.com:19999/api/3.1';// Replace this with your API credentialsvar CLIENT_ID = 'XXX';var CLIENT_SECRET = 'XXX';/** * Returns the results or the sql of a Look * * @param {number} id The unique ID of the Look * @param {number} opt_format 1 for csv, 2 for raw sql - defaults to csv (optional) * @param {number} opt_limit the query limit - defaults to 5000 if empty (optional) * @return The Look results data * @customfunction */function LOOKER_RUN_LOOK(id, opt_format, opt_limit) { try { var options = { "method": "get", "headers": { "Authorization": "token " + login() } };// set formatting to either csv or the raw sql query since sheets is limited var formatting; // convert param switch (opt_format) { case 1: formatting = "csv"; break; case 2: formatting = "sql"; break; default: formatting = "csv"; }// set a custom limit var limit; if(opt_limit) { limit = opt_limit; // else use the 5k default } else if (opt_limit == -1) { limit = -1; } else { limit = 5000; }// get request for the look var response = UrlFetchApp.fetch(BASE_URL + "/looks/" + id + "/run/" + formatting + "?limit=" + limit, options);// if it's csv, fill it in the cells, if it's the query, use one cell only, if not specified, throw error if (opt_format == 1) { return Utilities.parseCsv(response.getContentText()); } else if (opt_format == 2) { return response.getContentText(); } else { return Utilities.parseCsv(response.getContentText()); } } catch (err) { return "Uh oh! Something went wrong. Check your API credentials and if you're passing the correct parameters and that your Look exists!"; }}function login() { try{ var post = { 'method': 'post' }; var response = UrlFetchApp.fetch(BASE_URL + "/login?client_id=" + CLIENT_ID + "&client_secret=" + CLIENT_SECRET, post); return JSON.parse(response.getContentText()).access_token; } catch(err) { return "Could not login to Looker. " + err }}

Other Potential Uses

Since we are using the Looker API, there are a lot more possible use cases.

This Github repository contains a few more functions that follow the same pattern. You can add custom functions yourself, too; for example, a basic HTTP GET request could be written as follows:

function LOOKER_OTHER_ENDPOINT(input) {var options = { 'method': 'get', 'headers': { 'Authorization': 'token ' + token } };var response = UrlFetchApp.fetch("https://instance_name.looker.com/this/that" + input, options);return Utilities.parseCsv(response.getContentText()) }

Reference Material

Google Apps Script URL Fetch Service documentation

These best practices reflect recommendations shared by a cross-functional team of seasoned Lookers. These insights come from years of experience working with Looker customers, from implementation to long-term success. The practices are written to work for most users and situations, but as always use your best judgment when implementing them.

Database Security

Configure secure access between the Looker application and your database. Looker documentation provides several recommendations, including whitelisting Looker IPs, encrypting SSL transmissions, and connecting to databases via SSH tunnel.

Set up the most locked-down database account permissions for Looker that will still allow the application to perform all needed functions. Check out the Database Configuration Instructions documentation page for database-specific setup requirements.

Product Security

Set up user authentication, either using Looker's native username/password option or, preferably, using a more robust authentication mechanism like 2FA, LDAP, Google OAuth, or SAML. These methods are mutually exclusive and vary in security level. Google Oauth, for example, allows anyone in your email domain to log in to Looker.

Never post API credentials publicly. API credentials should be stored as environmental variables when possible, and should never be shared over email or chat support.

Regularly audit any public access links your users create, and restrict the permission to create them as necessary. The following i__looker Explore lists all public Looks and their creators on your Looker instance. Replace instance_name.looker.com with the URL of your Looker instance:

https://<instance_name.looker.com>/explore/i__looker/look?fields=look.id,look.title,look.public,user.name&f[look.public]=Yes&limit=500&query_timezone=America%2FLos_Angeles&vis=%7B%22type%22%3A%22table%22%7D&filter_config=%7B%22look.public%22%3A%5B%7B%22type%22%3A%22is%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22Yes%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Those with the System Activity Model enabled on their instance (available as a Labs feature as of Looker version 6.2) can track public Looks via the following Explore URL:

https://<instance_name.looker.com>/explore/system__activity/look?fields=look.id,look.title,look.public,user.name&f[look.public]=Yes&limit=500&query_timezone=America%2FLos_Angeles&vis=%7B%22type%22%3A%22table%22%7D&filter_config=%7B%22look.public%22%3A%5B%7B%22type%22%3A%22is%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22Yes%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Set up the most restrictive user permissions and content access that still allow users to carry out their work, paying special attention to who has admin privileges. For a more thorough explanation of these features, please check out our Help Center article Secure your Spaces! A Content Access Walk-through. Note: As of Looker version 6.20, "Spaces" are called folders.

Use the access_filter parameter, in conjunction with user attributes, for applying row-level data security by user or user group.

Leverage access_grants, in conjunction with user attributes, to control access to LookML structures such as Explores, joins, views, and fields by user or group.

Use model sets to apply data set security within the Looker model. Users will be unable to see any content outside their assigned model set.

Avoid assigning users to roles individually whenever possible. Users who are given both individual roles and roles based on group membership inherit all roles from both the individual and the group assignments. This can result in users accumulating permissions beyond what they should have. The best practice is to assign roles based only on group membership whenever possible.

Most users should be Viewers of the Shared folder (except in closed system setups). This ensures that users will be able to see content in the Shared folders and any subfolders to which they have access. Providing users with Manage Access and Edit permissions on the Shared folder means that they will retain those rights within any and all subfolders as well. It can be helpful to make changes lower in the tree first, and THEN revoke access higher up. This is discussed in greater detail below.

Content Security

Implement the appropriate type of Looker system based on the access controls you need to support:

Completely Open: There are no limits to who can see and edit items within the system. The All Users group should be assigned to Manage Access, Edit within the root folder (called Shared). More information about enabling a completely open access system can be found on the Configuring a Completely Open System documentation page.

Open with Content Restrictions: Content needs to be locked down in some way, either so only certain people can edit certain content, or so certain content is entirely invisible to particular people. When possible, plan for a flatter hierarchy, as this is easier to manage than deeply nested folders. For more details on setting up an open system with content restrictions, please check out the Configuring an Open System with Restrictions documentation page.

Closed System: This system completely separates content across groups, preventing users from different groups from even knowing about the others. A closed system is best for multi-tenant Looker installations. Please make sure to read the closed system documentation page before pursuing this option. Once a closed system is enabled, it is difficult to change Folder Management access. This documentation page provides more information about enabling a setup for a Looker instance.

Check out our documentation for more information on Looker Access Control and Permission Management or on Designing and Configuring a System of Access Levels.

You can create multiple schedules for the same piece of content (Looks or dashboards) with different filter values applied to each schedule. Let's say you have a monthly report you need to deliver to external brand managers. If the report is exactly the same, you can schedule a single piece of content to be delivered monthly. But what if you have hundreds of brands for which you need to generate a schedule? Manually creating a schedule for every single brand can be quite a long process.

This example shows how to use the Looker API to retrieve all brands from Looker and programmatically recreate a schedule for each one.

Example

In this example, we will be using Looker API Python SDK client. You'll need to create two files:

A config.yml file

A scheduling script file

The config.yml File

Create a config.yml file in the same directory as your generated Looker SDK to store your Host, Client ID, and Client Secret. This file can live in the main Python script, but for security reasons it's always a good idea to keep these separated. To generate your Looker API3 credentials, go to Admin -> Users and then click Edit by your name. Next to API3 Keys, click Edit Keys, and then click New API3 Key.

The config.ymlfile should look like this:

hosts:

'localhost':

host: 'https://instance_name.looker.com' # This is the URL of your Looker instance

secret: 'your_secret' # This is your user API client ID

token: 'your_token' # This is your user client secret

The Scheduling Script File

Create a scheduling script file named whatever you like (we are using one in this example called brand_schedule.py). We have broken down the script below into multiple sections:

Authenticate into the Looker API:

# Python 3

import looker

import yaml

# open the config file to get API credentials and Looker hostname where open('config.yml') is f:

params = yaml.load(f)

f.close()

base_url = params['hosts']['localhost']['host']

client_id = params['hosts']['localhost']['secret']

client_secret = params['hosts']['localhost']['token']

# instantiate Auth API

unauthenticated_client = looker.ApiClient(base_url)

unauthenticated_authApi = looker.ApiAuthApi(unauthenticated_client)

# authenticate client

token = unauthenticated_authApi.login(client_id=client_id, client_secret=client_secret)

client = looker.ApiClient(base_url, 'Authorization', 'token ' + token.access_token)

Instantiate the Query API client and run_inline_query endpoint to retrieve all brands for which we we want to create a schedule.

In this example, we assume that the only difference between schedules is the brand name and recipient's e-mail address. The frequency, format, and other schedule parameters are exactly the same across all schedules, so we need to query Looker to retrieve all the brands and the e-mail addresses associate with them. We also apply a filter to remove any brands that are not currently active.

# instantiate Query API client

queryApi = looker.QueryApi(client)

# define the body for the run_inline_query looker API

body={

"view": "order_items",

"fields": [

"products.brand",

"products.brand_manager_email"

],

"filters": {

"products.is_active_brand": "Yes"

},

"model": "developer_training"

}

# retrieve all active brands and their brand manager e-mail addresses

allBrands = eval(queryApi.run_inline_query(result_format='json', body=body))

Instantiate schedule API client and create_scheduled_plan for every single brand from the above run_inline_query endpoint. Here, we are creating one schedule plan for every single brand from the previous query:

# instantiate schedule API client

scheduleApi = looker.ScheduledPlanApi(client)

ct = 0

total_ct = len(allBrands)

for b in allBrands:

# define the body for the create_scheduled_plan looker API (iterative for every single brand)

body={

"name": "{} - monthly schedule".format(b['products.brand']),

"user_id": 1,

"run_as_recipient": False,

"enabled": True,

"look_id": 5,

"filters_string": "?order_items.created_date=1%20months%20ago%20for%201%20months&products.brand={}".format(b['products.brand']),

"require_results": False,

"require_no_results": False,

"require_change": False,

"send_all_results": False,

"crontab": "0 6 1 * *",

"timezone": "America/Los_Angeles",

"scheduled_plan_destination": [

{

"format": "inline_table",

"apply_formatting": True,

"apply_vis": True,

"address": "{}".format(b['products.brand_manager_email']),

"type": "email"

}

],

"include_links": True,

}

# create schedule for every single brand

schedules = scheduleApi.create_scheduled_plan(body=body)

# print message for every successfully scheduled brand (optional)

ct += 1

print("Completed schedule creation for {}. {} of {}".format(b['products.brand'], ct, total_ct))

Alternative Scenario

Imagine that the list of active brands changes on a monthly basis. In this case, you could replace the create_scheduled_plan with run_schedule_plan_once endpoint, and run this script every time you would like to generate a one-off schedule for all your brand managers.

Introduction

Admin users have direct access to an instance's usage panel, which connects directly to the underlying application database. Also, users with permission that includes see_system_activity have access to the System Activity Explores and dashboards and the internal i__looker database to view usage, history, and other metadata about a Looker instance.

The History Explore can be accessed at:

https://[instance_name].looker.com/explore/i__looker/history

What is the History Explore?

Every time a query is run in Looker, it is assigned a unique history ID, and that history ID is the base view of the History Explore. That means this Explore is particularly useful for information about users' queries. Because this table can grow rather large, Looker automatically truncates the data in this table every 90 days, which gives you a 90-day look into the query data on the instance.

The History Explore can be used as a more detailed Admin Queries page. It has all of the information exposed there, but goes back further than the latest 50 queries, and has more information about users, roles, groups, and so on.

What the History Explore is Not

The History Explore is not the best place to see ALL users, looks, or dashboards on the instance. The History Explore only tracks query events, so if a user has not done anything in 90 days, or a Look or dashboard has not been queried in 90 days, it will not show up in the History Explore.

For all of your content exploration needs, use the following Explores:

/explore/i__looker/look

/explore/i__looker/dashboard

/explore/i__looker/user

Useful Fields in the History Explore

There are a lot of fields in the History Explore. Here are a few particularly useful/interesting ones:

history.source: The source of the query, such as a Look, dashboard, explore, schedule, API, and so forth.

history.result_source: The source of the results, whether they came back from cache or the database as a query.

history.runtime_in_seconds: The amount of time the query took to run.

query.model: The LookML model associated with a query.

query.explore: The LookML Explore associated with a query.

query.sql_text: The actual SQL that was run for the query.

What Kinds of Questions Can I Ask of the History Explore?

You must change the beginning of the URL to match that of your Looker instance for the links below to work:

What is the average runtime against different models on my instance?

https://[instance_name].looker.com/explore/i__looker/history?fields=query.model,history.average_runtime&sorts=history.average_runtime+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%7D&origin=share-expanded

What is the most popular dashboard by query count?

https://[instance_name].looker.com/explore/i__looker/history?fields=dashboard.title,history.query_run_count&f[dashboard.title]=-NULL&sorts=history.query_run_count+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22dashboard.title%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

Which Explores are most popular with different roles?

https://[instance_name].looker.com/explore/i__looker/history?fields=history.query_run_count,query.view,role.name&pivots=role.name&f[role.name]=-NULL&sorts=history.query_run_count+desc+0&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22role.name%22%3A%5B%7B%22type%22%3A%22%21null%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A1%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded

Has anyone used a field from my_view in the last 90 days?

https://[instance_name].looker.com/explore/i__looker/history?fields=history.created_date,query.link&f[query.formatted_fields]=%25my%5E_view%25&sorts=history.created_date+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22query.formatted_fields%22%3A%5B%7B%22type%22%3A%22contains%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22my_view%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Has anyone queried my_explore in the last 90 days?

https://[instance_name].looker.com/explore/i__looker/history?fields=history.created_date,query.link&f[query.view]=my%5E_explore&sorts=history.created_date+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22query.view%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22my_explore%22%7D%2C%7B%7D%5D%2C%22id%22%3A5%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded

Looker supports exporting a Look directly to Google Sheets. In this article, we present some extra Google Sheets functions that make the process smoother.

Basic Setup

You can find the basic setup for exporting a Look to Google Sheets on the Public Sharing, Importing, and Embedding documentation page. The process is:

Click the gear menu on the Look page, and select Edit Settings.

Turn on Public Access.

Save the change.

Copy the URL under Google Spreadsheet.

Paste the URL into the cell of the Google spreadsheet where you would like the upper-left cell of your data to appear, then hit Enter.

Custom Import Function

The URL above includes the Google Sheets' built-in ImportXML function. After receiving some reports of unreliability and long loading times, we developed a custom lookerFetchData function, which addresses some of these problems.

function lookerFetchData(url) {

url = url.replace(/(\/\w+\.)txt|html|csv|gsxml(\?.*)?/, "$1csv$2");

var csvString = UrlFetchApp.fetch(url).getContentText();

var dataIn = Utilities.parseCsv(csvString);

var dataOut = dataIn.map(function(row) {

return row.map(function(val) {

if (val == '') return '';

var dateMatch = /(\d{4})\-(\d{2})-*(\d{0,})/.exec(val);

while (dateMatch != null) {

// first index is full match

return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3] || 1);

};

if (val.match(/[-a-zA-Z]/)) {

return String(val)

};

val = val.replace(/[^\d.]/g, '');

if (val.match(/[0-9.]+/))

return Number(val);

return Number(parseInt(val));

});

});

return dataOut;

}

If you have a field with a more granular timestamp format, such as yyyy-MM-dd HH:mm:ss, use the script below to export the full timestamp:

function lookerFetchData(url) {

url = url.replace(/(\/\w+\.)txt|html|csv|gsxml(\?.*)?/, "$1csv$2");

var csvString = UrlFetchApp.fetch(url).getContentText();

var dataIn = Utilities.parseCsv(csvString);

var dataOut = dataIn.map(function(row) {

return row.map(function(val) {

if (val == '') return '';

var dateMatch = /(\d{4})\-(\d{2})-*(\d{2})_(\d{2})\:(\d{2})\:(\d{2})/.exec(val);

while (dateMatch != null) {

// first index is full match

//return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3] || 1);

return Utilities.formatDate(new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3], dateMatch[4], dateMatch[5], dateMatch[6]|| 1), "GMT", "MM-dd-yyyy HH:mm:ss");

};

if (val.match(/[-a-zA-Z]/)) {

return String(val)

};

val = val.replace(/[^\d.]/g, '');

if (val.match(/[0-9.]+/))

return Number(val);

return Number(parseInt(val));

});

});

return dataOut;

}

To use this script inside Google Sheets:

Navigate to Tools > Script Editor from the top section of the sheet.

Create a new blank project.

Paste the code above into the script editor and save you may give it any name. Close the script editor and refresh the Google Sheet.

Replace ImportXML from the vanilla setup with lookerFetchData.

Auto Refresh Script

Finally, if you want your Google Sheet to automatically refresh and pull data from Looker, you can use the following script:

function onOpen(){

/// get current spreadsheet url and sheet

var e=SpreadsheetApp.getActiveSpreadsheet();

/// create menu -- refresh and refresh all under "Looker"

var t=[{name:"Refresh This Sheet",functionName:"Refresh"},{name:"Refresh All Sheets",functionName:"RefreshAll"}];

e.addMenu("Looker",t)

Refresh(e)

}

function onInstall(e){

onOpen(e)

}

function Refresh(e){

/// current sheet if no active sheet passed

var t=SpreadsheetApp.getActiveSheet();

if(typeof e!=="undefined"){

t=e

}

/// find which cell has our fetch function

var n=t.getDataRange();

var r=find("ImportXML",n);

var y=find("lookerFetchData", n);

for(var i=0;i<r.length;i++){

/// add refresh time

r[i].setValue(r[i].getFormula().replace(/(.gsxml[^"]*)"/,".gsxml?apply_formatting=true&apply_vis=true&refresh="+(new Date).getTime()+'"'))

}

for(var i=0;i<y.length;i++){

y[i].setValue(y[i].getFormula().replace(/(.html[^"]*|.txt[^"]*)"/,".txt?apply_formatting=true&apply_vis=true&refresh="+(new Date).getTime()+'"'))

}

}

function RefreshAll(){

var e=SpreadsheetApp.getActiveSpreadsheet().getSheets();

for(var t=0;t<e.length;t++){

Refresh(e[t])

}

}

/// find all formulas that(used in refresh)

function find(e,t){

var n=t.getFormulas();

/// n is now a list of coordinates with formulas (eg A1)

var r=[];

for(var i=0;i<n.length;i++){

for(var s=0;s<n[i].length;s++){

var x = n[i][s].toUpperCase();

if(x.indexOf(e)>-1){

r.push(t.getCell(i+1,s+1))

}

}

}

return r

}

The steps to enable this are the same as with the Import function:

Navigate to Tools > Script Editor from the top section of the sheet.

Create a new blank project.

Paste the code above into the script editor and save you may give it any name. Close the script editor and refresh the Google Sheet.

One of the most useful features of Looker Table Calculations is the ability to reference values in other rows or columns of a query, without having to write crazy window functions or derived tables in SQL.

Here are some examples:

Using the offset() function

Let's say I have order profit by week:

I want to know how each week compares to the previous week, so I add a table calculation with the formula:

This gives me a third column (green, so I know it's not part of the SQL result set):

You can also do calculations like moving averages:

The table calculation editor is there to help you with autocomplete and function definitions:

Using the pivot_offset() function

Now, you want to look at the data by user state, so you add Users State and pivot by Orders Created Week:

With data like this, we can still get the week-over-week change by using the pivot_offset() function:

Voil:

When you have pivots, table calculations can be useful in other ways as well. For example, you can create columns that reference the pivot value by creating a table calculation that references the pivoting field's name:

which yields:

Motivation

By setting up a Git workflow across multiple Looker host instances, you can create a dedicated development environment for developers whilst preventing other users from coming into contact with experimental code. All development and code review is done in a development or staging instance. Once changes pass your team's quality assurance review, they are deployed to a staging or production instance as desired.

This article describes the method for setting up a workflow among multiple instances and multiple Git repositories. If you have multiple instances and one Git repository, see the Git Workflow Using One Repository Across Multiple Instances Development, Staging, and Production Help Center article.

Looker recommends using a single repository whenever possible.

Advanced Git Setup for Multiple Repositories

This setup requires multiple Git repositories and some knowledge of Git commands. If you have questions about how to set up this workflow, please contact Looker support.

You may want to set up multiple Looker hosts to achieve a development workflow that looks something like:

[1] Development > Production

[2] Development > Staging > Production

Each of these stages resides on a dedicated Looker host instance. This configuration uses a manual Git process to move code between two separate GitHub repos that can be automated or kicked off by a button.

Although this method shows a development workflow configuration [1] based on an existing single Looker project, looker_project, it could easily be adapted for [2] or any number of intermediate hosts between Development and Production as needed.

Overview

Setting Up Version Control

The general process is as follows:

Create two GitHub repos, dev and prod.

Associate each repo with their respective Looker instances, Development and Production, according to the Setting Up and Testing a Git Connection documentation.

Create a local Git repo that is a clone of dev, perhaps on a server owned by the approver. Add the prod GitHub repo as a second remote:

git remote add production <<ssh_url>>

Create a webhook trigger in the production GitHub repo to notify prod Looker to sync with GitHub as new code is pushed. In essence, set up a webhook on a push event to notify the Production Looker instance.

Implement some kind of quality control around the second item in the migration process shown above, based on what works best for your team.

Step-by-Step Guide

To designate the existing devrepo and set up a prod repo on a second host, follow these steps:

In the Github UI, go to the existing project, select Settings, and ensure that master is set as the default branch. Update if needed.

Create a local folder and clone the looker_project:

mkdir ~/Desktop/looker_project

cd ~/Desktop/looker_project

git clone [email protected]:MyOrgNameInGithub/looker_project.git

Switch to the cloned project and make sure you're on the master branch:

cd looker_project

git checkout master

If we view the Git remotes for the cloned repo, we should see the origin as the original repo location:

git remote -v

Create a new empty repo via the GitHub UI. For our example, let's name the repo looker_project_prod to house our prod project:

Add our new empty repo as a new remote for the cloned repo, as below:

git remote add prod [email protected]:MyOrgName/looker_project_prod.git

In this case, prod is simply a name we ascribe to the remote; it can be whatever we want. We can check that it was added successfully per the above.

Perform an initial push to our new remote of the master branch:

git checkout master

This will ensure we're on the correct branch:

git push prod master

This will push the master branch of our cloned repo to our new remote, prod.

In the GitHub UI, we should see that the cloned LookML has been pushed to our new project on the master branch.

Now, create a new empty project on the Production Looker instance:

Enter Development Mode, select the Develop menu, then Manage LookML Projects:

Create a New LookML project:

Choose a name (like looker_project_prod in our example) and select Blank Project.

Select Create Project.

Follow Looker Git setup instruction prompts to connect to our new repo. For a complete tutorial, see our Setting Up and Testing a Git Connection documentation.

Add the Looker SSH key to the GitHub project using the UI. Ensure that you provide read/write access initially.

Choose Sync Development Mode when prompted. Looker should pull in the code from the new prod repo to the local Looker file system.

Next, configure the new repo via the Github UI to hit Looker's deploy webhook on the production host.

In the GitHub UI, select Settings, then Webhooks, then Add webhook.

Enter the URL in the following format: [instance_name.looker.com]/webhooks/projects/[project name]/deploy

For example: https://instance_name.looker.com/webhooks/projects/looker_project_prod/deploy

This will cause the Looker host to pull the master branch from the Git repo to its local file system, making the changes visible.

Starting in Looker 5.20, you can add a secret for your deploy webhook.

Once configured per the above, we can reset the Git connection using the Looker UI on the prod host:

In the Github UI, we can make the SSH key read-only for the prod repo by not selecting Allow write access, so that no development can happen on the Production instance.

This means that any user entering Development Mode (including during setup) will see the error below while syncing their Development Mode. This, in effect, is what we want.

Proposed Development Workflow

LookML development takes place on the dev Looker instance, and all code is committed and deployed via the Looker UI. This pushes the LookML to the master branch of the development repo.

We can enable pull requests to allow for code review before commits are merged into master per the instructions on the documentation page.

To move LookML from the dev repo to the prod repo, we just need to run the following in the cloned repo (while on the master branch):

git pull

git push prod master

When the prod repo detects the push, GitHub will hit the deploy webhook on the Looker prod instance to pull in these changes.

This article is written with help and examples by Xin Bao.

A common question asked of analysts is how to find the top performers in a cohort over a specific amount of time. For example, "What are the 10 states with the highest total sales each day in the past month?"

You can leverage table calculations in Looker to effectively answer this type of question and clearly visualize it for end users. This article takes you through the steps using an e-commerce dataset containing user state data, sales data, and order data.

Solution

To begin, we will create an Explore with the fields necessary for performing the analysis. In this case, we will select the orders.created_date and users.state dimensions, along with the order_items.total_sales measure.We will also filter the results for the desired time frame (in this example, the month of January 2019). Finally, we will pivot on the users.state dimension:

row and column limit

To find the top 10 states for every day in the past month, we will use two functions pivot_row() and large(). The large() function will give the nth biggest value of a list, and the pivot_row() function will generate the list of those values in each row.

When we have the tenth largest value of the list, we then compare the measure with the tenth largest value of the row. Next, we will create a logical if() statement to display only the values that are greater than or equal to the tenth largest value of the row and list. If a value does not meet this criteria, it will display a null.

The final table calculation will look like the following, with the U.S. Dollars

(2) format specified:

if(${order_items.total_sales}>=large(pivot_row(${order_items.total_sales}), 10), ${order_items.total_sales}, null)

This table calculation can be adjusted as needed to fit other use cases. Replace the number 10 with the number of members of the highest-selling cohort you would like to see.

With the addition of the table calculation, the Explore will look like this:

Visualizing the Results

Now that we have the table calculation, we are ready to visualize the results.

First, make sure to hide the original measure (total_sales) from the visualization to display only the results from the table calculation.

Next, select the most appropriate visualization type. For this use case, we are using scatterplot.

Switch off the Plot Null Values option in the visualization settings.

Voila, now you can show the result!

Be aware of the column limit in Explores. Although Looker can display a maximum of 200 columns, you will begin seeing warnings when your table has more than 50 columns. For the best performance and to enhance the readability of the visualization, we always recommend fewer columns. For more details, check out Looker's documentation page.

Table calculations, as discussed in Using Table Calculations, make it easy to create on-the-fly metrics and enable the creation of calculations that operate on the table data returned by a SQL query. This is great for calculating metrics like percent of previous and percent change.

This article shows you how to calculate these metrics, down columns and across rows.

Calculating Down Columns Using offset()

The offset() table calculation function lets you reference other rows in the same column you are operating in. This is perfect for calculating percent of previous and percent change down columns.

For an example, see the table below:

pivot_offset()

Percent of Previous

To calculate the percent of previous of orders.count, we can make a table calculation like this:

${orders.count} / offset(${orders.count}, -1)

This divides each value in the table by the value in the row above it.

Percent Change

We calculate percent change similarly to percent of previous, except we want to subtract the entire calculation by 1:

${orders.count} / offset(${orders.count}, -1) -1

These two metrics yield the following results:

Customize the format of table calculation values by selecting a specified format from the default formatting drop-down menu in the table calculation modal. Alternatively, use custom formatting to get the formatting you want, as explained in Adding Custom Formatting to Numeric Fields (if you want something other than the available default formats).

Calculating Across Rows Using pivot_offset()

With pivoted results, the table calculation function lets us reference values that are on the same row in different columns. In the following example using pivot_offset(), -1 references the previous column (rather than the previous row, as in the example above).

For an example, see the table below:

Percent of Previous

To calculate percent of previous across rows, we use pivot_offset() in the same way we used offset() above:

${orders.count} / pivot_offset(${orders.count}, -1)

This divides each value in the table by the value on the same row, located one column to the left. We get the following table:

Percent Change

Again, we can use pivot_offset() the same way we use offset() when calculating down columns:

(${orders.count} / pivot_offset(${orders.count}, -1)) - 1

This is the same as percent of previous, just subtracted by 1. This gives us these results:

It is usually best to rebuild persistent derived tables (PDTs) on a consistent schedule, using either a datagroup or a sql_trigger_value. However, to ensure that the table data is fresh, you might sometimes want to manually rebuild a derived table when you want to query it.

The Solution

You can rebuild a PDT from a Look or an Explore with fields from a PDT view selected by clicking the Rebuild Derived Tables & Run option from the gear icon in an Explore or a Look:

production mode

The Rebuild Derived Tables & Run option will appear when the following criteria are met:

The user has the develop permission.

The query uses a PDT that has already been built and the query has finished running.

This approach will rebuild a derived table directly referenced in the query, as well as queue any children of the persistent derived table for the regenerator to rebuild.

Note: For Looker-hosted instances, manually rebuilding derived tables will only work if the table takes less than one hour to rebuild (if it takes longer to rebuild the table, the browser session will time out). See the Query Timeouts and Queueing Help Center article for more information about timeouts that may affect Looker processes.

Manually Rebuilding Tables the Results

The user who clicks Rebuild Derived Tables & Run will need to wait for the PDT rebuild to finish before seeing the query results. Queries run by other users during this time will use the existing table. However, all user queries will use the new version of the table once it has completed rebuilding. Other users may also be impacted by the database strain that the rebuild may cause.

If a user clicks Rebuild Derived Tables & Run while in development mode, the PDT will be affected as noted above if the SQL is identical. If the PDT in the user's development mode is different, then the production version of the PDT will not be affected.

If you receive the error Certificates do not conform to algorithm constraints when trying to save changes to a LookML project, this can be due to a bug in OpenJDK 8 that prevents the use of the RSASSA-PSS signature algorithm on SSL certificates. If you receive this error, try installing a different SSH certificate that does not use the RSASSA-PSS signature algorithm.

Looker uses the Chromium renderer to run certain Looker features. If your instance is hosted by Looker, this software is already installed. However, if you host your own Looker instance, you will need to install the renderer.

For Looker 5.22 through 6.2, you will need to install the renderer before enabling the Enhanced Rendering Labs feature, which, in conjunction with the renderer, allows you to enjoy certain Looker features.

Instructions for installing Chromium on Ubuntu are in the Chromium section of our Customer-Hosted Installation documentation. Instructions for installing Chromium on Debian are in the Help Center article of that name.

Chromium is more difficult to install on some Linux distributions, such as Amazon Linux. Customers with Looker running on those distributions can install Chrome instead.

Version and Location

Make sure you install Chrome into an executable path on the machine that hosts the Looker instance.

If you are installing Chrome on a clustered Looker installation, the versions on all nodes must be fully identical. Even minor differences, such as different build numbers, can prevent enabling the Enhanced Rendering Labs feature (if you are using Looker 5.22 through 6.2).

Installation Steps

These instructions can be used to install Chrome on Amazon Linux:

Use the sudo command to perform these steps as the Looker user. If you install Chromium while logged in as the root user, Looker might not be able to execute PDF or PNG rendering.

Get the latest version of Chrome by running:

wget https://dl.google.com/linux/direct/google-chrome-stable_current_x86_64.rpm

Install Chrome by running:

sudo yum install ./google-chrome-stable_current_x86_64.rpm

Looker will look for the Chromium executable in/usr/bin/chromium, so you will need to create a symbolic link from there to Chrome. This can be done by running:

sudo ln -s /usr/bin/google-chrome-stable /usr/bin/chromium

You can use instructions like those from the intoli blog to resolve any dependency issues.

This article recommends using their script by running:

curl https://intoli.com/install-google-chrome.sh | bash

You should always inspect any bash script and understand what it is doing before running it on your Looker instance.

The Chromium Project is an open source project started by Google to share the non-proprietary parts of Google Chrome. If your instance is hosted by Looker, this software is already installed.

The Customer-Hosted Installation of Rendering Software documentation page shows how to install Chromium on Ubuntu. This article provides instructions for installing Chromium if you are using a Debian system.

Version and Location

Make sure you install Chromium 65 or later into an executable path on the machine that hosts the Looker instance. The Looker application needs to access Chromium using the chromium command or via a custom path set with the CHROMIUM_PATH environment variable.

If you are using a Debian 8 (jessie) Linux system, then you cannot use this feature because Debian 8 only supports up to Chromium version 57. However, Debian 9 (stretch) includes the security updates and latest features, including support for the latest Chromium version.

If you are installing Chromium on a clustered Looker installation, the Chromium version on each node must be fully identical. Even minor differences, such as different build numbers, can prevent the Enhanced Rendering Labs feature from enabling properly.

Installation Steps

Use the sudo command to perform these steps as the Looker user. If you install Chromium while logged in as the root user, Looker might not be able to execute PDF or PNG rendering.

1. On the command line, enter the following command:

apt-get update && apt-get install -y chromium

2. If you are using Docker with a bare-bones Debian install, you will need to install procps and ensure you are not running as root.

3. After you set any environment variables, you will need to restart the Looker application for them to take effect. You may also need to restart any open terminal sessions, or run source ~/.bash_profile, to get the terminal to read the new configuration.

4. After installing Chromium, you should get a valid response from either chromium

--version or $CHROMIUM_PATH --version depending on whether you set the environment variable.

5. Enable the Enhanced Rendering Labs feature.

Optional Environment Variables

See the Looker Documentation page for some optional environment variables, such as CHROMIUM_PATH, CHROMIUM_PID_DIR, CHROMIUM_PORT_RANGE_START, and CHROMIUM_PORT_RANGE_END. Additional Contributor: Veronica Phillips

In addition to delivering content to Looker's built-in destinations, you can deliver content to third-party services integrated with Looker via an action hub server. Looker hosts and provides a stateless server called the Looker Action Hub, which implements Looker's Action API and exposes popular actions for all Looker users.

Looker is already integrated with several services (or actions), including Slack, but admins need to connect Slack to the Looker Action Hub and enable Slack in the Looker Actions panel before Looker can send the results of a query or a dashboard directly to Slack as an attached file.

For more information about enabling the Slack integration in Looker, see this README file.

Delivering Data to Slack

First, Looker admins must enable Slack in the Looker Actions panel.

Create your query or dashboard, and then schedule a data delivery or use the Send option for a one-time delivery.

When configuring the schedule or sending your data, choose the Slack Attachment option in the Destination field. Looker will display delivery options that are specific to Slack: other advanced scheduling options

In the Share In field, enter the name of the Slack channel where you want to post your data. Include the leading # character. For example: #Sales.

In the Comment field, enter any text you want to include with the attachment.

In the Filename field, enter a file name you want to give to the attachment file.

For Slack attachments, Looker also includes formatting and .

If you are scheduling a data delivery, once you have configured all the options, click Save All, and your query or dashboard will be delivered to Slack as scheduled. Or, if you are sending your data, click Send and your data will be delivered to your Slack channel.

Note: Make sure to invite the Looker user bot to any non-private channels or groups to which you might want to send data (on your channel settings, select Add people to #channel_name, find the Looker user bot the same way you find individual users, and select the bot). If you set up the Slack Attachment action using a legacy token, you will need to invite the Looker application to your channel (select Add an App for your channel's settings and find the Looker app). A channel will not appear as an option in the Share In delivery option field until you invite the user or add the app to the channel.

This article lists the different row limits that can be found across Looker. The rule of thumb is:

If results are being displayed in the browser, the row limit is 5,000.

If results are being exported from Looker, users can choose to export All Results as long as the results stream.

We'll go into more detail about streaming at the end of the article. Table calculations are the most common reason that a result set doesn't stream, so removing table calculations usually allows for an unlimited download. The All Results option can also be unavailable when any of the conditions described in the documentation are met.

Row Limits

Explores, Looks, Dashboards, SQL Runner

Queries created via the Explore page will have a maximum limit of 5,000 rows. This limit also applies to Looks and dashboards, and to queries created in SQL Runner.

Downloading

In most cases, it is possible to download the unlimited result set from an Explore, Look, or dashboard. It is only possible to download All Results if the result set streams, and if the user downloading has the download_without_limit permission.

If these conditions are not met, then a custom limit can be set in the download modal instead. This custom limit has a default maximum of 100,000 rows.

Note: Downloading specifically for dashboards is discussed in greater detail in the documentation for Viewing Dashboards in Looker.

Scheduling and Sending

Email schedules can send up to 15 MB of data as long as the results stream. All emails with the All Results option applied are subject to the 15 MB size limit.

Schedules sent via webhook, S3, or SFTP can send an unlimited result set as long as the result set streams.

File Format Matters

When downloading, sending and scheduling content, PDF and PNG file formats will maintain the browser row limit of 5,000. The All Results option is typically only available for Markdown, Text, CSV, JSON - Simple, XLSX, or HTML formats for scheduling, sending and downloading. The data formats available for sending and scheduling data depend on the destination. The documentation on Scheduling Data Deliveries lists each scheduling destination option in greater detail, and the options for sending are available in the documentation about Sending Data Now. The Downloading Data documentation provides more information about file format options for downloading.

API

API calls are subject to the same row limits of the context they are called in. API calls based on an Explore, Look, or dashboard are subject to the 5,000 row limit, while certain download and schedule file formats can be unlimited.

To download unlimited results via API, use the appropriate Run call (such as Run Inline Query or Run Look ) with the limit parameter set to -1 (negative one). -1 is the number that Looker uses to represent unlimited results.

Check the limits referenced in the sections above as appropriate to the type of API call you're using.

Streaming

Streaming refers to Looker's ability to process data in chunks, rather than all at once. If Looker can stream a result set, then unlimited downloads are possible. There are only two cases where streaming is not possible:

Table Calculations

Table Calculations cannot be streamed. Therefore, to download unlimited results for a query, table calculations must be removed from the query.

Database Limitations

Some databases cannot stream if pivots are included in the query. For these databases, pivots must be removed in order to download unlimited results.

Some databases can't stream any results, which means that unlimited downloads are not possible. Below is a list of the databases that support streaming, as well as databases that support streaming with pivots applied:

You can use parameters to allow users to change timeframes on the fly, like a filter-style toggle where users can choose between rolling up their data at the date, week, or month level.

First, assume we have a dimension group with these desired timeframes:

dimension_group: created {

type: time

timeframes: [date, week, month, raw]

sql: ${TABLE}.created ;;

}

Next, set up a parameter that suggests the timeframes we want. Keep in mind that defining a parameter by itself doesn't do anything; it will just provide a text box in the UI.

parameter: timeframe_picker {

label: "Date Granularity"

type: string

allowed_value: { value: "Date" }

allowed_value: { value: "Week" }

allowed_value: { value: "Month" }

default_value: "Date"

}

Finally, create a dimension that chooses a timeframe from the dimension group, based on input from the filter. It's OK for this dimension to be of type: string, as long as the timeframes you choose all return strings. If they return different types, you can cast them to strings, as shown in the note below.

Note: Because the resulting dynamic dimension is type: string, dimension fill cannot be applied to the dynamic_timeframe dimension in an Explore.

dimension: dynamic_timeframe {

type: string

sql:

CASE

WHEN {% parameter timeframe_picker %} = 'Date' THEN ${orders.created_date}

WHEN {% parameter timeframe_picker %} = 'Week' THEN ${orders.created_week}

WHEN{% parameter timeframe_picker %} = 'Month' THEN ${orders.created_month}

END ;;

}

Now you can change up the parameter to select your timeframe!

Note: Looker converts certain timeframes to string values, while others remain of type: date or type: integer. Make sure that all outcomes of your case statement have the same datatype. For example, ${view.created_date} would result in the datatype date.

Depending on your SQL dialect, you can use CAST(${orders.created_date} AS string/char) (MySQL, BigQuery) or TO_CHAR(${orders.created_date}, 'YYYY-MM-DD') (Redshift, Postgres).

Use Cases

You may want the fields displayed in a dashboard or Look to be dynamic, based on a user's selection of the metrics or data granularity they wish to see. There is a LookML parameter called parameter that takes filter values input by the user and inserts them into your LookML, dynamically creating the set of fields returned in the UI.

Dynamic Dimensions

Let's say you have multiple dimensions or dimension group timeframes that represent different date granularities (like day, month, quarter, year), and you would like users to be able to select by which timeframe the data is grouped on a dashboard. We can define the following parameter, which will be displayed as a filter on your dashboard:

parameter: date_granularity { type: string allowed_value: { value: "Day" } allowed_value: { value: "Month" } allowed_value: { value: "Quarter" } allowed_value: { value: "Year" } }

We will then apply this parameter to a dimension that ties the parameter's filter values to our different timeframe fields.

dimension: date {

label_from_parameter: date_granularity

sql:

CASE

WHEN {% parameter date_granularity %} = 'Day' THEN ${created_date}

WHEN {% parameter date_granularity %} = 'Month' THEN ${created_month}

WHEN {% parameter date_granularity %} = 'Quarter' THEN ${created_quarter}

WHEN {% parameter date_granularity %} = 'Year' THEN ${created_year}

ELSE NULL

END ;;

}

The specified timeframe field will now be returned based on whichever parameter value the user selects on the dashboard. Note that we can use label_from_parameter to pass the selected value label to our tile.

Liquid Variable Reference

Note: Because the resulting dynamic dimension is type: string, dimension fill cannot be applied to the dimension in an Explore.

Dynamic Measures

A similar approach can be applied to measures. This is especially useful when you have a group of related metrics that you may not want to display individually in the field picker, or that you wish to display dynamically on a Look or dashboard, based on user selection.

The following pattern is used to map parameter values to the different metrics that will be available on our dashboard.

parameter: metric_selector {

type: string

allowed_value: {

label: "Total Revenue"

value: "total revenue"

}

allowed_value: {

label: "First-Time Shopper Revenue"

value: "total_first_purchase_revenue"

}

allowed_value: {

label: "Returning Shopper Revenue"

value: "total_returning_shopper_revenue"

}

}

measure: metric {

label_from_parameter: metric_selector

type: number

value_format: "$0.0,\"K\""

sql:

CASE

WHEN {% parameter metric_selector %} = 'total_revenue' THEN ${total_revenue}

WHEN {% parameter metric_selector %} = 'total_first_purchase_revenue' THEN ${total_first_purchase_revenue}

WHEN {% parameter metric_selector %} = 'total_returning_shopper_revenue' THEN ${total_returning_shopper_revenue}

ELSE NULL

END ;;

}

In the UI, our dashboard will look like this:

Note: As an alternative to a CASE statement, you can write the condition with a Liquid {% if %} logical comparison statement. This solution can be used to simplify the SQL query output to improve performance. A CASE statement is evaluated for each row in a table, whereas a Liquid {% if %} statement inserts the correct value directly into the query. Using the date granularity example from above, here is what this alternate solution would look like:

dimension: date {

label_from_parameter: date_granularity

sql:

{% if date_granularity._parameter_value == 'day' %}

${created_date}

{% elsif date_granularity._parameter_value == 'month' %}

${created_month}

{% else %}

${created_date}

{% endif %};;

}

Here the Liquid variable parameter_name._parameter_value is used to insert the value of the parameter specified with parameter_name.

For more examples of creating logical statements with Liquid, see the documentation page for .

The Problem

I have a LookML project connected to a Git repository. I would now like to move that LookML to a new repository.

The Solutions

There are two solutions, one simple and one advanced. The simple solution preserves the history of the master branch, while the advanced solution preserves the history of all branches. Both solutions are described in the following sections.

Simple Solution: Preserve History for the Master Branch

If we reset the Git connection and enter a new Git URL, then the LookML will be moved over to that repository. Follow these steps:

Go to the Project Settings page for that project: read more about this here

On the Project Settings page, click the Reset Git Connection button:

On the Configure Git page, enter the new Git URL (the Git URL for the repository to which you want to migrate), then click the Continue button.

If you are using SSH to connect, be sure to click the Reset Key button. Otherwise, the same SSH key will be used, which can cause a conflict if both repositories are hosted by the same service (in this case, Github).

For SSH connections, add the new deploy key to your Git repo, and be sure to grant Write access in the Git repo deploy key settings. If you are using HTTPS, enter the login credentials for your Git repo (see the Setting Up and Testing a Git Connection documentation page for full instructions on setting up Git).

That is all! Now your project is connected to the new repository.

Note: Your project LookML will not appear in the master branch of the new repository until you deploy your project to production. Unless you deploy to production, your LookML code will appear only on your development branch in the new repository after you commit the code, or push it to remote (a git command available in the shift-click git menu ).

Advanced Solution: Preserve All Branches

The above steps preserve the history of the master branch and the branches of any developers who choose to log in and sync their Development Mode. To preserve all branches and their history, a more involved process is necessary.

First, you need to own the original repository. You can find the URL at the bottom of the Project Settings page. If it begins with llooker/, then it is Looker-hosted. We can grant access to a Looker-hosted repository for a limited time while you complete the migration; to get the process started, please contact us and include the repo URL and your Github user name.

For this example, in a situation where [email protected]:llooker/PROJECT_NAME.git is the original repo and [email protected]:your_organization/PROJECT_NAME.git is the new repo:

Clone the original repository onto your computer, and pull down the branches you want to preserve.

git clone [email protected]:llooker/PROJECT_NAME.git

cd PROJECT_NAME

git checkout master

git pull

Reset the repo's remote URL ().

git remote -v

This shows you the remote(s) to which your repo is currently pointing. The results will look something like:

origin [email protected]:llooker/PROJECT_NAME.git (fetch)

origin [email protected]:llooker/PROJECT_NAME.git (push)

Now set the origin remote to the new repo:

git remote set-url origin [email protected]:your_organization/PROJECT_NAME.git

git push origin master

Now, to bring in history and files for dev branches, you'll need to do git checkout dev_branch_name and git push origin for each dev branch. This can be done manually, or in a loop in a script such as:

#!/bin/bash

for branch in $(git branch --all | grep '^\s*remotes' | egrep --invert-match '(:?HEAD|master)$'); do

git branch --track "${branch##*/}" "$branch"

done

Then push the branches with git push --all.

Reset the git connection in Looker to this new URL and set up a deploy key on this new repo (see the steps in the simple solution section above).

You can create running totals very easily with a table calculation that will sum the numbers going down the columns. If you switch the sorting, you will still get running totals going downward.

What if you want to sort your data newest to oldest and have the running total calculated bottom to top? You can also do this with table calculations.

Suppose I had a measure called "Flights Count" and wanted to sort by years in descending order.

The reverse running total is created with two table calculations:

The first is just a normal running total:

The second gets the sum of "Flights Count" for the first row, then each subsequent row subtracts the running total from the sum:

To provide more clarity, we have renamed Spaces to folders as of Looker 6.20.

In Looker 6.16 and higher, there are two complementary ways to organize content within Looker: folders and boards. To use boards in Looker 6.16 and 6.18, your admin must enable the content curation beta (boards) Labs feature.

Folders, which store dashboards, Looks, and other folders, are the primary way that people organize content in Looker. Folders can also be used to control content access. By managing access to a folder, you can also manage access to the content stored in that folder. Learn more about folders on the Finding Content in Looker documentation page.

To curate existing dashboards and Looks, we released the first phase of Looker's content curation solution, boards, in July. It is out of beta and available to everyone in the September 2019 release (6.20).

Like search, Popular, and Favorites, boards provide another way to surface and navigate to existing dashboards and Looks stored in folders.

Key benefits:

Easily curate dashboards and Looks for a team or initiative without moving the underlying content, which is stored in folders.

Pin dashboards created in LookML (LookML dashboards) to a board. (Note:In the 6.20 release, you must do this from the LookML dashboard itself.)

Pin the same Looks, user-defined dashboards, and LookML dashboards to multiple boards.

Support new users by making it easier for them to find relevant content in Looker.

Populate an information section; add context with Markdown links and descriptions to help guide colleagues.

Several specific features:

Anyone can curate content for a team or initiative and include information and links.

When someone adds a board to their list, it appears in the scrollable left navigation pane.

The left sidebar is now simplified. Folders are still accessible in theAll folderssection.

By default, all boards can be viewed by anyone at the organization, and they can also be made private.

Frequently asked questions

Q: What is the difference between folders and boards?

A: Folders represent the underlying file system where your dashboards and Looks are stored. Folders also control access to content. All content access is managed at the folder level. Like search, as well asPopular andFavorites, boards represent another discovery solution for existing content. They are, in essence, lists of existing dashboards and Looks stored elsewhere in Looker's underlying file system. For this reason, dashboards and Looks can appear on multiple boards.

Q: How will boards work with folders?

A: They will work in tandem with folders. Dashboards and Looks are technically stored in folders, but can appear on multiple boards.

Q: How will access & permissions work for boards?

A: Anyone can view a board by default, but creators can choose to make the board private. To choose the access level for a board, select theshareicon in the upper right of the board.

Q: What if someone doesn't have access to content? Will they see that content on the board?

A: Unlike with folders, access to a board does not control access to the underlying content. Boards honor any existing access levels; people will not see content that they do not have access to see.

Q: How can I reduce clutter people will see in search results? As an admin, can I limit who can create a board?

A:Today, people often create documents outside Looker with lists of relevant dashboards and Looks within Looker. We believe anyone should be able to do this within Looker. Still, contributing to cluttered experience in places like search results counteracts our goal of making it easier for people to find relevant content. Given this, we will enable Looker admins to control whether or not boards appear in discovery channels like search by default (coming soon).

Q: How can I encourage my colleagues to add a board to their navigation/sidebar in Looker?

A: When you click the share icon on a board, you have the option to copy and send someone the URL. Additionally, you can choose to send an email notification to newly added users or groups. The email notification will encourage recipients to add the board to their lists.

Coming soon

Admin controls over defaults: choosing whether or not, by default, boards are visible in search and other discovery channels

The ability to move content across sections

Additional areas for descriptions

Improved search

In Looker 6.24, we've introduced dashboards (beta), which have a fresh, modern look and feel; customizable, user-friendly filters; a new dashboard creation experience and editing flow; and much-improved performance.

able to edit a dashboard

Dashboards (beta) can be implemented by enabling the New Dashboard Experience Labs feature, which allows you to switch back and forth between dashboards (beta) and original dashboards.

Some features and functionality are not yet included, but are being considered for dashboards (beta), such as:

In the main dashboard menu:

Download a dashboard as a PDF

Download a dashboard as a CSV zip file

Get a dashboard's LookML

Send a dashboard

Schedule a dashboard

Editing a dashboard:

Add a Look

Edit dashboard settings

Tiles on a dashboard:

Download data from a tile

Schedule alerts

Support for merged results tiles

Support for static map visualizations

Support for grid layout

Enable data actions

Edit tiles:

View, add, or edit a note

View original Look

Hide tile title

Other:

View navigation breadcrumbs

While not currently present in dashboards (beta), these features are still available when you switch to original dashboards.

Switching between Dashboards (Beta) and Original Dashboards

In the short-term, you can use the original version of your dashboard when you need features not currently present in dashboards (beta) and switch back to dashboards (beta) when you're ready to take advantage of its benefits.

Using the Dashboard Menu to Switch

If you are and the New Dashboard Experience Labs feature is enabled, you can switch between original and dashboards (beta) from the menu in the top right corner of the dashboard.

To Switch from a Dashboard (Beta) to an Original Dashboard:

To Switch from an Original Dashboard to a Dashboard (Beta):

Changing the URL to Switch

If you don't have editing capabilities on a dashboard but the New Dashboard Experience Labs feature is enabled, you can switch between the original version and the dashboard (beta) version of the dashboard by adding or removing -next after dashboards in the dashboard URL. A URL with -next after dashboard shows the dashboard (beta) version:

If you remove the -next, you see the original dashboard version:

You can return to the dashboards (beta) version of the dashboard by adding -next back into the URL.

Looker support can be reached by using our in-app chat application.Simply click the Chat option from the Help drop down in the upper right corner and type your question. Alternatively, requests can be submitted using this support request form.

Details on our SLAs and overall support program can be found in our Looker Support Terms.

Hours of Coverage

English Language Looker Support is available 24 hours a day, 5 days a week. Support hours are:

Sunday, 5:00pm PST - Friday, 6:00pm PST

Sunday, 8:00pm EST - Friday, 9:00pm EST

Monday, 12:00am GMT - Friday, 2:00am GMT

Monday, 9:00am JST - Saturday, 9:00am JST

Japanese Language Looker Support is available from:

9:00am JST to 5:00pm JST, Monday-Friday

Coverage is subject to major holidays observed in the US, UK, and Japan.

Who can access support?

Looker Support is available to administrators and developers on your Looker instance.This allows us to focus on your most involved users, who may need in-depth help with the Looker product. You should develop your own internal process for helping your end users, especially because youll know the ins and outs of your data better than anyone.

Ticketing System

There is no ticketing system that you need to login to, to reach support. Although Looker maintains a ticketing system internally, we think it should be transparent to you. We can provide a ticket number if you like, but it is not part of our standard process, and you should be able to exchange chats or emails with Looker like you would a colleague.

Escalation

Looker does not have an escalation path, because every Support Analyst is trained to the same, high level. We do have internal processes to make sure lengthy issues maintain focus, and to pull in engineering resources if necessary, but you do not need to follow a special process or achieve a certain level of severity to reach these resources.

The Problem

A common issue users run into while modeling is sorting by pivots. For example, I might want to pivot my top 10 brands by total sales.

The most elegant solution I've found happens to work well for several other use cases. In this article I'll show how we can satisfy each in turn, by progressively adding to an ephemeral derived table.

The Solution

In this example we'll use a simple e-commerce model. Here I've pulled my top brands by revenue:

using charts

Now, if I'd like to see how these top 10 perform over time, a simple pivot won't help. It will sort alphanumerically by default:

I would have to filter my brand by each of the brand names that were in the top 10. This can work for a one-off analysis, but won't scale effectively for my team.

Sort by Pivots

To accomplish this, we can have Looker rank our brands for us, using an ephemeral derived table.

The idea is to dynamically calculate statistics at a brand level and then join them back into our model.

The first step is to create the query we'll use for the ranking. I'll often write these by hand, but a quick shortcut is to assemble the query in an Explore, then punch it out to SQL Runner, make any needed modifications, and then bring it into my project as a derived table:

SELECT

products.brand AS "brand",

COALESCE(SUM(order_items.sale_price ), 0) AS "total_revenue", # Included for clarity

RANK() OVER (ORDER BY COALESCE(SUM(order_items.sale_price ), 0) DESC) AS "RNK"

FROM

public.order_items AS order_items

LEFT JOIN public.orders AS orders ON ( orders.id = order_items.order_id)

LEFT JOIN public.inventory_items AS inventory_items ON ( order_items.inventory_item_id = inventory_items.id )

LEFT JOIN public.products AS products ON ( inventory_items.product_id = products.id )

WHERE

1=1

GROUP BY 1

ORDER BY 2 DESC

LIMIT 20

The query above groups by the dimension by which we wanted to pivot/sort, and establishes a rank function on total revenue. Now, we can bring it into our model as a derived table. You can run this in SQL Runner and then have Looker automatically generate the LookML.

Looker prompted me for a view name and I called our new view brand_rank_by_sales:

view: brand_rank_by_sales {

derived_table: {

sql:

SELECT

products.brand AS "brand",

COALESCE(SUM(order_items.sale_price ), 0) AS "total_revenue",

RANK() OVER (ORDER BY COALESCE(SUM(order_items.sale_price ), 0) DESC) AS "RNK"

FROM

public.order_items AS order_items

LEFT JOIN public.orders AS orders ON orders.id = order_items.order_id

LEFT JOIN public.inventory_items AS inventory_items ON order_items.inventory_item_id = inventory_items.id

LEFT JOIN public.products AS products ON inventory_items.product_id = products.id

WHERE

1=1

# This is a "templated filter" - any filter I place on my orders created date will be mirrored here

AND {% condition orders.created_date %} orders.created_at {% endcondition %}

GROUP BY 1

ORDER BY 2 DESC

;;

}

measure: count {

type: count

drill_fields: [detail*]

}

dimension: brand {

type: string

sql: ${TABLE}.brand ;;

}

dimension: total_revenue {

type: number

sql: ${TABLE}.total_revenue ;;

}

dimension: rnk {

ype: number

sql: ${TABLE}.rnk ;;

}

set: detail {

fields: [brand, total_revenue, rnk]

}

}

Now, we can join it into the rest of our e-commerce Explore, as a many-to-one relationship joined on brand:

explore: order_items {

join: orders {

relationship: many_to_one

sql_on: ${orders.id} = ${order_items.order_id} ;;

}

join: users {

relationship: many_to_one

sql_on: ${users.id} = ${orders.user_id} ;;

}

join: inventory_items {

type: left_outer

sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;

relationship: one_to_one

}

join: products {

type: left_outer

sql_on: ${inventory_items.product_id} = ${products.id} ;;

relationship: many_to_one

}

join: brand_rank_by_sales {

type: left_outer

sql_on: ${products.brand} = ${brand_rank_by_sales.brand} ;;

relationship: many_to_one

}

}

Now, we have the tools we need to look at the performance of our top 10 brands' revenue over time.

I can now pivot by my brand and simply filter the brand_rank_by_sales.rnk field, on the condition of less than or equal to 10.

To allow clear sorting by our pivot field, we can combine the rank and the brand name by adding a new dimension that will support being sorted alphanumerically. The CASE WHEN statement will simply prepend a 0 to ranks less than 10, for proper sorting.

dimension: ranked_brand {

type: string

sql:

CASE

WHEN

${rnk} < 10 THEN '0'|| ${rnk} || ') ' || ${brand}

ELSE ${rnk} || ') ' || ${brand}

END ;;

}

Now, we can pivot by our brand_rank_by_sales.ranked_brand field and filter for the top 10 rank:

Group Our Long Tail into a Single "Other" Bucket

Adding on to the pattern above, I may want to take everything below a certain rank and place it into a long tail or "other" bucket. This can be used for time-series analysis, but isespeciallyhelpful in pie charts. You can find more information on creating "other" buckets using table calculations or.

I'll simply add the following two fields:

filter: other_bucket_threshold {

type: number

}

dimension: ranked_brand_with_tail {

type: string

sql:

CASE

WHEN {% condition other_bucket_threshold %} ${rnk} {% endcondition %}

THEN ${ranked_brand}

ELSE 'x) Other'

END ;;

}

The benefit of this approach is that the end-user can dynamically adjust the tail threshold, which may vary or need to be tuned for the subset under consideration.

Drill into Your Outliers Using Percentile Rank

Add the following field to your derived table:

,PERCENT_RANK () OVER (ORDER BY COALESCE(SUM(order_items.sale_price ), 0)) AS "percent_rank"

And the following dimensions to the view:

dimension: percent_rank {

type: number

value_format_name: percent_2

sql: ${TABLE}.percent_rank ;;

}

dimension: percentile_tiers {

type: tier

tiers: [0.25,0.5,0.75,0.9,0.95]

sql: ${percent_rank} ;;

}

Now, we can drill into our outliers:

What's the Difference?

Look-linked tiles are saved queries that are generated from an Explore. Looks have to be saved to a folder (called "Spaces" before Looker 6.20) in order to be added as a tile to a dashboard, which must also be in the folder.

Tiles are visualizations added from an Explore or a Look to a dashboard. Tiles can be Look-linked or query-based. Query tiles differ from Look-linked tiles because they are only stored on dashboards.

The Benefits of Each

A benefit of using Look-linked tiles is that they are saved in more than one location. This is handy if the tile based on that Look is deleted from the dashboard. Another reason to use Look-linked tiles is to have a single Look appear on multiple dashboards or appear on a dashboard multiple times. Since all of the tiles reference the same Look, any update to the Look will be reflected on all tiles that reference it.

Query tiles are beneficial because they don't have to be stored in two places. It's easier to save the query visualization result from an Explore directly to a dashboard. This prevents folders from being overrun with tons of Looks that are only used on dashboards.

How Do I Tell Which One I Have?

A Look-linked tile is identifiable by clicking the three-dot menu and selecting the option View Original Look. A query tile is identifiable because it lacks the View Original Look option and shows only the Explore From Here option.

Creating User-Defined Dashboards: Adding Query Tiles to a Dashboard

How Do I Convert One to Another?

To convert a Look-based tile to a query tile, choose Edit on the dashboard and click on the three-dot menu, then select Convert Look to Tile.

To convert a query tile to a Look-linked tile:

Choose the Explore From Here option on the menu of the query tile.

Go to the settings of the Explore and save the Explore as a Look.

Add the Look to your dashboard.

Delete the old, query-based tile.

Related Links:

When a query times out or gets cancelled, it can be tricky to pin down the cause. A key piece of info: Looker does not timeout queries once they have been sent to the database. That means that all the timeouts we discuss here will be either before Looker sends the query to the database, or occurring somewhere outside of Looker.

Timeouts in Looker

Looker does not timeout queries once they have been sent to the database. We do, however, timeout queries that have been waiting in the Looker queue.

Per-Connection Query Limit and Timeout

You can set a concurrent query limit and timeout in the connection panel.

here

Max Connections means "max concurrent queries". In the example above, Looker will only allow 75 queries to be registered as "waiting on database". These will appear as "running" in the Admin Settings - Queries page. Any queries beyond this will be queued in Looker, and will appear as "Queued" in the Queries page. Looker will timeout queries after 120 seconds of being in this queue.

Note: This is different from your database queue! Though Looker may have 75 queries that all appear as "running" in the Admin > Queries page, that does not mean that all of them are running on your database. Your database may have its own queueing rules - see "Database Timeout" below.

Per-User Query Limit and Timeout

Each Looker user is allowed to run 15 queries simultaneously by default. Any extra queries are queued in Looker until one of the 15 running queries finishes. If they stay in queue for over 600 seconds, then Looker drops them from the queue.

Scheduler Query Limit and Timeout

Scheduled queries are run by the scheduler as a given user. Thus, they are subject to the connection rules above, and the per-user rules. In addition, there is a scheduler concurrent query limit of 10 and a timeout of 1200 seconds.

Renderer Query Limit and Timeout

The PDF dashboard renderer process has a job limit of two. If a job times out, you'll see a RendererTimeout error message in the Admin Schedule History page. If you receive this error, please notify Looker Support and provide as much detail as possible.

Changing the Defaults

If you are self-hosted, you can change some of these defaults in the Looker Startup Options.

The relevant options are:

--per-user-query-limit=<i> Limits number of concurrent queries per user

(default: 15)

--per-user-query-timeout=<i> Length of per-user timeout to wait for connection

(default: 600)

--scheduler-query-limit=<i> Limits number of concurrent scheduled queries

(default: 10)

--scheduler-query-timeout=<i> Length of scheduler timeout to wait for

connection (default: 1200)

--concurrent-render-jobs=<i> Number of simultaneous phantomjs processes

(default: 2)

If you are hosted by Looker, you can request these defaults to be changed by contacting Looker Support. We'll need authorization from a technical contact on your instance to make the changes.

Webhook Timeout

Looker will attempt data delivery to a webhook for a maximum of 30 minutes. To avoid this timeout, make sure that the specified destination can respond within 30 minutes to the webhook delivery attempt.

Timeouts Outside of Looker

Outside of the Looker product, there are many places where queries can be queued and killed.

Proxy Timeout

Anything that comes between your Looker instance and browser has a potential to timeout running queries that are run in the browser. Looker-hosted instances use a proxy that times out queries after 60 minutes. Self-hosted Looker instances often have a proxy that times out connections in much less time, such as 60 seconds. You can read more on Looker proxy timeouts .

Database Timeout

The database can have its own rules for queueing and timeouts that are completely independent of Looker. For instance, Redshift has a default concurrent query limit of five. So let's say the "Max Connections" is set to 75, and a user runs 100 queries in Looker. 25 of those queries will be queued in Looker, and 75 sent to Redshift. Of those 75, only five will be run by Redshift and the remaining 70 will go in the Redshift queue.

Check the documentation for your database to see what kind of options you have when it comes to query timeouts and queueing.

We release a new version every month at Looker, chock-full of new features. Most of our customers are able to take advantage of these features as soon as they are available, because we manage and update their Looker instances for them. However, if you self-host your Looker instance or have a hold on updating because of feature deprecations, it is easy to fall behind and miss out on the latest and greatest Looker has to offer.

Why Should You Update?

In addition to the fact that we are constantly releasing new functionality to make your experience better, there are a couple of important reasons to stay current:

Looker provides product support coverage for Looker instances on the latest stable version and the two previous versions, as well as the Extended Support Release. Because we release a new version every month, three months without updating puts you outside the stable window. The latest version provides all new features and the latest bug fixes.

At Looker, we take security very seriously and update our product to address any new web vulnerabilities. We try our hardest to keep supported releases as safe as possible, but we ask you to do your part by staying current with Looker to ensure that your Looker instance has access to the latest security patches.

Steps and Considerations When Updating

1. Address the Original Need for Holding on Update

Sometimes you may ask to prevent updates for a specific functional reason. This could be a significant change in functionality for which you need more time to train your users, or it could be a feature or tool that was deprecated in a new release that is operationally critical for your business (like an API endpoint used by a script or separate application).

2. Review Release Notes

Looker Release Notes discuss what is in each release, so you can stay abreast of new features your users should take advantage of and anything that may affect rollout of a new version. See the Release Highlights section for the most exciting new features, and make sure to review the Preparing for Release section to identify things to watch out for.

3. Update Any LookML Parameters That Have Been Completely Removed

Enhancements to LookML include deprecations of parameters. Update your LookML to keep everything working in the new version. Specifically, check the Legacy Feature End-of-Life Schedule. Be sure to follow the Transition Guidelines to check for deprecated LookML parameters that have been completely removed, in or prior to the version to which you are updating.

NOTE: It's best practice to update your LookML to remove ALL deprecated LookML, as those parameters will be removed in a future release.

4. Take a Backup of Your Instance (and Restore to a Staging Environment)

Creating regular backups is strongly recommended and should be done before each update. Additionally, while this is not required, if you are updating several versions it's a good idea to set up a staging server where you can test the updated version before rolling it out to your entire company.

Customer-Hosted Looker

If you host your own Looker instance, you can follow this guide for creating backups and use this guide for restoring backups to a new staging environment.

Looker-Hosted Looker

If your instance is hosted by Looker, reach out to your Looker contact, or Looker Support, to have our team take a backup and set up a staging server for you.

5. Update the Staging Instance and Smoke Test

Customer-Hosted Looker

If you host your own Looker instance, download the latest release JAR and follow the Update Instructions documentation page to update your staging instance to the latest version. Be sure to check the installation requirements for the latest server requirements particularly the Java version.

Looker-Hosted Looker

If your instance is hosted by Looker, reach out to your Looker contact, or Looker Support, to have our team update your staging instance for you.

Once your staging instance is up to date, log in and test things out! Make sure your important dashboards and Looks run without error and that there are no (new) LookML errors when you validate your model.

6. Update the Production Environment

Assuming all looks good on your staging instance, you can go ahead and update your production instance to the latest version, following the same process you used for staging. Be sure you have taken a recent backup in case you need to restore for any reason.

Tags and Attributes

Objects often have lists of attributes associated with them. Stack Overflow posts are tagged with the subjects they are about. Github issues are labeled with product areas they affect. Users may be tagged with different attributes over time to know the campaigns they were contacted by. Even words in a headline can be used as tags for an article.

The common data pattern is that an object may have zero or more tags associated with it. The tags can be used to find or group objects, or to find objects where tags may appear together. In objects with a given tag, how often do other tags appear? This block describes how to build tags for objects in LookML and build some interesting queries.

Two implementations

Many SQL databases have an "Array" type that can be used to store an array of strings in a particular data record. Postgres, Presto, Snowflake, Bigquery and Athena all support arrays. They will use the first example as a pattern.

Redshift and MySQL don't support arrays and will use the technique in the second implementation.

Implementation#1:Stack Overflow Data

In the SQL dialects that support arrays, functions can return arrays of strings. The most function is probably SPLIT(), which takes a string and breaks it into an array of strings.

We will start with a very simple model of Stack Overflow data. Stack Overflow is a place where people can ask question and get answers. Posts are generally questions, and the questions are tagged with subjects so experts who watch a particular subject can find the questions and answer them.

STRING_TO_ARRAY

The Model

The model below implements a view for the posts, but it also joins in tag twice, so it can be used to answer co-occurrence questions.

explore: stackoverflow_posts { join: tag { sql: LEFT JOIN UNNEST(SPLIT(${stackoverflow_posts.tags},'|')) as tag ;; relationship: one_to_many } join: tag2 { from: tag sql: LEFT JOIN UNNEST(SPLIT(${stackoverflow_posts.tags},'|')) as tag2 ;; relationship: one_to_many sql_where: ${tag.name} <> ${tag2.name} ;; }}view: stackoverflow_posts { sql_table_name: `bigquery-public-data.stackoverflow.stackoverflow_posts` ;;dimension: id {primary_key: yes} dimension: title {} dimension: tags {} dimension: owner_display_name {} dimension: score {type: number} dimension_group: creation { type: time sql: ${TABLE}.creation_date ;; } measure: count { type: count drill_fields: [id, creation_time, title, tags, score] } measure: average_score { type: average sql: ${score} ;; }}view: tag { dimension: name { sql: ${TABLE} ;; }}

Looking at these records, we can see that the tags are contained in a single string. For example, the tags for a question that reads "How to build EclipseLink?"are:

java | maven | jpa | eclipselink

Questions like What are the most common tags in articles that are also tagged with `java`? and How many articles are there that are tagged `java` and `maven`? are generally hard to answer in SQL, but answering these questions becomes easy using the model above.

Getting Fancy: Comparing SQL Dialects

Suppose we want to find patterns in Stack Overflow, with questions that reference multiple SQL dialects and determine which dialects appear together. By filtering multiple tags on a set of SQL dialects, we can build a matrix (that drills!) all by just using the model above.

How it Works

The SQL for the first data table is pretty simple:

SELECT stackoverflow_posts.id AS stackoverflow_posts_id, stackoverflow_posts.tags AS stackoverflow_posts_tags, stackoverflow_posts.title AS stackoverflow_posts_titleFROM `bigquery-public-data.stackoverflow.stackoverflow_posts` AS stackoverflow_postsWHERE ((stackoverflow_posts.tags IS NOT NULL AND LENGTH(stackoverflow_posts.tags) <> 0 ))ORDER BY 1 DESCLIMIT 500

Arrays, SPLIT and UNNEST

An array is a variable that contains a list of values.

Different dialects have different names for functions that convert string into an array of values. Snowflake and BigQuery have SPLIT, and Postgres has . All of these functions will parse a string and return an array. For example, in BigQuery and Snowflake:

SELECT SPLIT('java|maven|jpa|eclipselink','|')

returns

['java','maven','jpa','eclipselink']

UNNEST

Once the data is in an array, we can turn it into a table function and join it back in. This fans out the table and joins it back in.

First the SQL:

SELECT stackoverflow_posts.id AS stackoverflow_posts_id, tag AS tag_name, stackoverflow_posts.tags AS stackoverflow_posts_tags, stackoverflow_posts.title AS stackoverflow_posts_titleFROM `bigquery-public-data.stackoverflow.stackoverflow_posts` AS stackoverflow_postsLEFT JOIN UNNEST(SPLIT(stackoverflow_posts.tags,'|')) as tag WHERE ((stackoverflow_posts.tags IS NOT NULL AND LENGTH(stackoverflow_posts.tags) <> 0 ))GROUP BY 1,2,3,4ORDER BY 1 DESCLIMIT 500

Notice this is identical except for two lines:

tag AS tag_name

and

LEFT JOIN UNNEST(SPLIT(stackoverflow_posts.tags,'|')) as tag

The first change is simply a dimension reference to tag. The interesting stuff happens in the second line. The SPLIT function turns the string field tags into an array of tag names. UNNEST joins the array as if it were a table. The effect is to repeat each row for each element of the array. If we run this query, we see the same data as before, except each article is repeated for each tag.

Implementation#2:Redshift, MySQL and Other SQL Dialects That Don't Support Arrays

This mechanism can be simulated using a numbers table with a SPLIT_PART() function. SPLIT_PART functions do something similar to the SPLIT functions in SQL dialects that support arrays, except that they only return a single string value, generally the "Nth" one. In order to fan out the results like we did above, we will need a numbers table to cross join.

An Example: Words in Product Names

We are going to use our e-commerce data and break out each individual word in the name of items. We will then be able to look at occurrence and pricing by words, search for words that appear together, and examine rates of co-occurence.

The Data Model

connection: "demonew_events_ecommerce"explore: products { join: word { type: cross relationship: one_to_many sql_where: ${word.name} <> '' ;; } join: word2 { from: word type: cross relationship: one_to_many sql_where: ${word2.name} <> '' AND ${word.name} <> ${word2.name} ;; }}view: products { sql_table_name: products ;;dimension: id {primary_key: yes} dimension: brand {} dimension: category {} dimension: department {} dimension: name {} dimension: retail_price {type: number} dimension: sku {} measure: count { type: count drill_fields: [id, name, brand, category, retail_price] } measure: average_price { type: average sql: ${retail_price} ;; }}view: word { extends: [numbers16] dimension: name { sql: SPLIT_PART(${products.name},' ', ${num}::integer) ;; }}explore: numbers16 {}view: numbers16 { derived_table: { sql: SELECT row_number() OVER () num FROM (SELECT 1 n UNION SELECT 2 n ) t1, (SELECT 1 n UNION SELECT 2 n ) t2, (SELECT 1 n UNION SELECT 2 n ) t3, (SELECT 1 n UNION SELECT 2 n ) t4 ;; } dimension: num {hidden: yes}}

The Raw Data

The Most Common Words in Product Titles

From the data, it looks like women's products, on average, cost more than men's.

Most Common Co-occurrence of Words

Comparing Hanes and Calvin Klein

Filtering on brand and then dimensionalizing by word lets us compare brands:

Overview

The html and link parameters can be used to create custom drill paths in dimensions or measures by creating links to related Explores, Looks, or dashboards.

Custom Drill Using HTML

Following the example below, we can click on a value in the name dimension in Dashboard A, and drill to a related Dashboard B that is filtered on the value we clicked on. To do this, we can insert the URL of our target dashboard into an <a href="...."> </a> tag. After applying our HTML parameter, our name dimension will look like this:

dimension: name {

sql: ${name}

html:

<a href="/dashboards/dashboardnumber?NameFilter={{ value }}&Filter2=filter_value">{{ value }}</a> ;;

}

This approach assumes that we have a global filter, called name_filter and based on the name dimension, on the dashboard to which we are drilling. This name_filter filter will accept the value from Dashboard A that we click on to drill to Dashboard B. By using the Liquid variable {{ value }}, we can insert the name value we select on Dashboard A into the filter of the dashboard URL, which will dynamically filter our target dashboard by the selected value.

User-Defined vs LookML Dashboards

Note that while the URL filter syntax for User-Defined Dashboards (UDDs) is case sensitive and does not incorporate spaces, LookML dashboard URLs are referenced using their affiliated model, name, and special characters:

dimension: name {

sql: ${name}

html:

<a href="/dashboards/model::dashboard_name?name_filter={{ value }}&filter_2=filter_value">{{ value }}</a> ;;

}

Drill to an Explore

The URL for an Explore drill will be slightly different. To drill to an Explore using declared fields, we can obtain the expanded Explore URL and apply the {{ value }} variable into the filter we would like to receive the value we click on (here, we apply the variable to our view_name.filter_1 filter):

dimension: name {

sql: ${name};;

html: <a href="/explore/model/explore_name?fields=view_name.field_name1,view_name.field_name2,view_name.field_name3&f[view_name.filter_1]={{ value }}&pivots=view.field_2">{{ value }}</a> ;;

}

To drill to an Explore using a set rather than declared fields, we can use the syntax fields=view_name.set_name, replacing the name of the view and the name of the set with your view name and set name:

dimension: name {

sql: ${name};;

html: <a href="/explore/model/explore_name?fields=view_name.set_name*&f[view_name.filter_1]={{ value }}&pivots=view_name.field_2">{{ value }}</a> ;;

}

Custom Drill Using Link

The link parameter functions in a similar way to the html parameter when it comes to custom drilling, but includes the label and icon_url subparameters so you can further customize how the drill link will appear to users.

Drill to a Dashboard

The following code will drill to a dashboard with state and age filters:

dimension: state {

sql: ${TABLE}.state ;;

link: {

label: "Drill Dashboard"

url: "/dashboards/1114?State={{ value }}&Age={{ _filters['users.age'] | url_encode }}"

}

}

We are again using the Liquid variable {{ value }} to dynamically filter the target dashboard's state filter by our selected value. In this example, we are also using the Liquid variable {{ _filters['view_name.field_name'] }} to pass the original query's filter values into the drill link. In this case, our original query is being filtered by users.age and this filter value is being passed into our drill dashboard's age filter.

Note that url_encode can be applied to Liquid variables like {{ value }} and {{ _filters }}, to ensure that all values passed into the URL (i.e., special characters) are converted into URL-safe characters.

Note: If we use the {{ _filters['view_name.field_name'] }} variable to pass a filter value from a dashboard, the variable must refer to a field type filter.

Drill to a Look

Our link syntax will be slightly different when drilling to a Look. Replicating our example from above for a Look, we use:

dimension: name {

link: {

label: "Drill Look"

url:"/looks/looknumber?&f[users.state]={{ value }}&f[users.age]={{ _filters['users.age'] | url_encode }}"

}

}

Notice the beginning ampersand and bracketed filter values.

Drill to an Explore

This example will drill to an Explore called ecommerce, and pass the original query's state filter into the Explore query's state filter:

dimension: name {

link: {

label: "Drill Explore"

url:"/explore/model_name/ecommerce?fields=users.id,users.name&f[users.state]={{ _filters['users.state'] | url_encode }}"

}

}

Again, the Explore URL can be found by selecting Share from the Explore's gear menu, and copying the expanded URL. We can then replace the filter values specified in the expanded URL with the _filters variable.

URL Encoding Other Comparison Operators

If you would like to set your drill-down filter value to a comparison operator other than equal to (=), you can do so by URL encoding the operator.

For example, if we wanted an order_id filter on a drill-down Look to query values that are less than (<) the order_id field associated with the value we are drilling into, we would partially encode the operator character (here, %3E) and add it to the URL:

f[orders.order_id]=%3E{{ other_orders.order_id._value }}

Other special characters can be encoded with URL encoder tools, such as the URL Decoder/Encoder by Meyerweb, or the HTML URL Encoding Reference by w3schools.

Including Multiple Filters

More than one filter can be applied to drill-down Looks, dashboards, and Explores by using an ampersand (&) to separate each filter:

dimension: name {

link: {

label: "Drill Look"

url:"/looks/looknumber?&f[users.state]={{ value }}&f[users.region]={{ users.region._value }}&f[users.age]={{ _filters['users.age'] | url_encode }}"

}

}

Escaping Commas

When creating custom drills with html and link parameters, you can escape commas in drill values with the filterable_value variable where you might normally use the value variable.

The following link drills to an Explore that will filter the results by the users.city value that is selected:

dimension: city {

type: string

sql: ${TABLE}.city ;;

link: {

label: "Drill by City"

url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]={{ value }}"

}

}

If the end user clicks Santa Cruz, CA to drill to the results filtered by this city, the resulting drill-down will return all results that contain either Santa Cruz or CA.

If filterable_value is used instead of value, the comma will be escaped:

dimension: city {

type: string

sql: ${TABLE}.city ;;

link: {

label: "Drill by City"

url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]={{ filterable_value }}"

}

}

The resulting drill-down will return all results that contain the entire string value Santa Cruz, CA.

If we want to hard-code a filter value that contains a comma in a drill-down URL, we can escape the comma by wrapping the value in double quotes and then escaping them with a forward slash (/):

dimension: city {

type: string

sql: ${TABLE}.city;;

link: {

label: "Drill by City"

url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]=\"Santa Cruz, CA\"&sorts=users.email"

}

}

The Problem

We have a string in our database that contains a comma. For our example, we will be using the string Santa Cruz, CA.

We want to filter on all records that contain this string. However, Looker treats commas as a way to separate values. This means that writing Santa Cruz, CA in an Explore or a Look filter will return all results that contain either Santa Cruz or CA.

The Solution

The solution is to "escape" the comma so that it is treated as part of the string. There are different ways to do this, depending on the context.

Regular UI Filters

When typing the string into the filter, you can include a backslash (\) character to escape the comma. This tells Looker to treat it as an actual comma. To escape the comma with the backslash, we would write Santa Cruz\, CA.

Matches Advanced Filters

The above trick works for regular string filters, but the rules for the Matches (Advanced) option are a little different. Here, the escape character is a carat (^). So, in this example, we would write Santa Cruz^, CA:

Custom Drilling using HTML and Link

LookML Filters

The carat (^) escape character also works in LookML filters. In a filtered measure, we would use the same syntax:

measure: filtered_count { type: count filters: { field: city value: "Santa Cruz^, CA" }}

Custom Drilling and Links

When creating custom drills with html and link parameters, you can escape commas in drill values with the filterable_value variable where you might normally use the value variable.

The following link drills to an Explore that will filter the results by the users.city value that is selected:

dimension: city {

type: string

sql: ${TABLE}.city ;;

link: {

label: "Drill by City"

url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]={{ value }}"

}

}

If the end user clicks Santa Cruz, CA to drill to the results filtered by this city, the resulting drill-down will return all results that contain either Santa Cruz or CA.

If filterable_value is used instead of value, the comma will be escaped:

dimension: city {

type: string

sql: ${TABLE}.city ;;

link: {

label: "Drill by City"

url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]={{ filterable_value }}"

}

}

The resulting drill-down will return all results that contain the entire string value Santa Cruz, CA

If we want to hard code a filter value that contains a comma in a drill-down URL, we can escape the comma by wrapping the value in double-quotes and then escaping them with a forward slash (/):

dimension: city {

type: string

sql: ${TABLE}.city;;

link: {

label: "Drill by City"

url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]=\"Santa Cruz, CA\"&sorts=users.email"

}

}

Check out the Help Center article for more ways to create custom drills.

Setting an alert based on time series data differs from setting an alert based on other data types. For time series data, the alert condition is based on comparing specific rows in the series rather than on the complete result set. Working this way with time series data allows us to perform additional operations that compare data from two rows in the series using additional alert condition options not available to other data types, such as changes by, increases by, and decreases by. When using these comparison conditions with time series data, the alert query will compare the latest row of data with its previous row.To keep track of where we are in the time series, so that we base our alert condition only on data that wasn't there the previous time the alert-check query was run, Looker must persist the Time Series ID of the latest time series data every time it runs the alert-check query.

There are two important cases to consider when choosing alert conditions that use time series data. Both cases depend on the relationship between the shortest interval between the time series rows (time series interval) and how often the alert query is run (alert check interval). The time series interval is based on the time dimension in the series that has the smallest granularity. So, if the data is aggregated by the hour, your time series interval is hourly. The alert check interval is the amount of time between scheduled alert-check queries, and is set by the alert creator.

Ideally, the time series interval and alert check interval are the same; however, that is not always the case. If an ETL job is configured to load hourly data every night, or a query fails for some reason, it's important to understand how alert queries work when these intervals are out of sync.

Alert-Checking Approach

Alert queries will check the latest row of time series data for whether either of the following is true:

Its Time Series ID is more recent than the most recent Time Series ID observed during the previous alert check.

It is the latest Time Series ID in the time series, even if it has the same Time Series ID as from the previous check.

When alerts were introduced in Looker 6.16, Looker alert queries only observe value changes for the latest rows in the time series.

Case: The Time Series Interval Is Shorter than the Alert Check Interval

Example: Check daily whether the hourly sales are greater than the goal.

Time Series Interval = hourly Alert Check Interval = daily

This approach involves checking hourly data with an alert check interval that is greater than an hour.

Run 5/25/19 9:00 AM

Time Series ID

Measure

5/25/19 8:00 AM

200

< alert check

5/25/19 9:00 AM

250

< alert check

Run 5/25/19 11:00 AM

Time Series ID

Measure

5/25/19 8:00 AM

200

5/25/19 9:00 AM

250

< previous alert

5/25/19 10:00 AM

300

< alert check

5/25/19 11:00 AM

300

< alert check

Run 5/25/19 12:00 PM (no new data)

Time Series ID

Measure

5/25/19 8:00 AM

200

5/25/19 9:00 AM

250

5/25/19 10:00 AM

300

< previous alert

5/25/19 11:00 AM

300

< alert check

Case: The Time Series Interval Is Longer than the Alert Check Interval

Example: Check hourly whether today's cumulative sales totals are greater than the goal.

Time Series Interval = daily Alert Check Interval = hourly

This approach involves checking data aggregated by date many times throughout the day. Let's say that we've set up an alert to notify us if the daily sales total equals or exceeds 200. The sales total is increasing during each alert check as it accumulates throughout the day, so Looker continually rechecks the latest time series value against the value that triggered the previous alert.

Run 5/25/19 9:00 AM

Time Series ID

Measure

5/24/19

200

< previous alert

5/25/19

50

< alert check (no notification)

Run 5/25/19 10:00 AM

Time Series ID

Measure

5/24/19

200

< previous alert

5/25/19

100

< alert check (no notification)

Run 5/25/19 11:00 AM

Time Series ID

Measure

5/24/19

200

< previous alert

5/25/19

150

< alert check (no notification)

This article can help you troubleshoot the "column must appear in the GROUP BY clause or be used in an aggregate function" error in Looker.

This error commonly occurs when a dimension is used in a measure that does not have an aggregate type. Our measure type documentation contains a list of measure types and whether each is an aggregate type.

For example, a type: number measure is not an aggregate measure, so this LookML would produce an error:

measure: bad_measure {

type: number

sql: ${measure} + ${dimension} ;;

}

Typically you fix this problem by first turning the dimension into a measure, and then using that new measure in your calculations. For example:

measure: dimension_total {

type: sum

sql: ${dimension} ;;

}

measure: good_measure {

type: number

sql: ${measure} + ${dimension_total} ;;

}

The Problem

Looker expects that each view defines exactly one dimension with the primary_key parameter, and that there are no duplicate values of any primary keys within a table.

If this constraint is not respected, aggregates across joins can get counted twice or more, leading to unusually large numbers. Looker usually detects these and will show this the error described in this Help Center article.

Pinpointing the Problem

To fix this we need to make sure each table has its own unique primary key. To test a given field for uniqueness, we can run the following simple query in SQL Runner that tells us how many rows are in the database table, per the value of that field:

SELECT field, count( * ) FROM table group by 1 order by 2 DESC

If there are any counts above 1 in the results, then that field is not unique and should not be the primary key.

Solutions

Ideally, there is a field in the database that is unique at the row level, or the database can be updated so that there is one (by adding a primary key column to a table, for example). The primary_key parameter can be applied to that unique field. Here are two options for creating primary keys.

Using Compound Primary Keys

Sometimes, no single field in a table is unique, but a combination of fields is unique. In this case, we can make a compound primary key by concatenating two or more fields in the SQL parameter:

dimension: compound_primary_key {

primary_key: yes

hidden: yes

type: string

sql: CONCAT(${TABLE}.primary_key_1, ' ', ${TABLE}.primary_key_2) ;;

}

Things to keep in mind about this approach:

This example will create a compound primary key of type: string. If the underlying fields are integers, they may need to be cast to strings first.

Note: CONCAT works in some databases, like MySQL, but other databases might have a different equivalent. Redshift, for example, uses || as its concatenation operator.

Using row_number to Generate a Primary Key for a Derived Table

If you receive this error with a derived table, you can use the row_number() window function in Postgres and Redshift databases to make a unique field. This field can then be used as a primary key:

view: derived_table_name {

derived_table {

sql:

SELECT

row_number() OVER(ORDER BY created_at) AS prim_key,

*

FROM orders ;;

}

dimension: prim_key {

type: number

primary_key: yes

sql: ${TABLE}.prim_key ;;

}

}

In MySQL, you can use a variable that iterates every row, to achieve the same effect:

view: derived_table_name {

derived_table {

sql:

SELECT

CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key, t.*

FROM orders t,

(SELECT @rownum := 0) r ;;

}

dimension: prim_key {

type: number

primary_key: yes

sql: ${TABLE}.prim_key ;;

}

}

Basic Measure Drilling

One of the unique things about Looker is that it connects directly to your database. That means you can always access the freshest data, and you can always drill down to the most granular level available. So, while of course you can see yearly or monthly summaries, Looker also gives you the option of drilling in to the day, hour, or even second instantly.

We can see this below, where we start with an all-time category overview, then drill to a monthly sales chart of a single category (Jeans).

custom visualization

Drilling Can be More Powerful in Several Ways

Looker's web-native, modern architecture means it is possible to do far more than just drill from one level to the next-most granular. We can build out any custom drill path your heart desires, no matter how sophisticated, with just a few simple parameters.

To demonstrate this, we show sample code below for some of the most common drill patterns, including:

Sorting and limiting (for example, show me the top 20)

Pivoting data

Drilling to advanced visualizations with trendlines

Drilling to any custom visualization and building it using D3 and Javascript

Drilling to a table calculation with conditional formatting

Adding Custom Limits (up to 5000)

To show only the first 20 results, you can use the link parameter and set the url subparameter to "{{ link }}&limit=20", as in the following LookML code:

measure: returned_count {

type: count_distinct

sql: ${id} ;;

filters: {

field: is_returned

value: "yes"

}

drill_fields: [detail*]

link: {

label: "Explore Top 20 Results"

url: "{{ link }}&limit=20"

}

}

set: detail {

fields: [id, order_id, status, created_date, sale_price, products.brand, products.item_name, users.email]

}

This allows users to drill into the top 20 results by selecting Explore Top 20 Results on the drill menu:

Adding Sorts

You can also display the 20 results by Sale Price, as in the following example:

measure: returned_count {

type: count_distinct

sql: ${id} ;;

filters: {

field: is_returned

value: "yes"

}

drill_fields: [detail*]

link: {

label: "Explore Top 20 Results by Sale Price"

url: "{{ link }}&sorts=order_items.sale_price+desc&limit=20"

}

}

set: detail {

fields: [id, order_id, status, created_date, sale_price, products.brand, products.item_name, users.email]

}

This allows users to drill into the top 20 results by sale price by selecting Explore Top 20 Results by Sale Price on the drill menu:

Adding Pivots

You can add a pivot on the Age Tier field to show the year and gross margin percentage tier for each age group when a user selects Total Sale Price by Month for Each Age Tier on the drill menu:

measure: order_count {

type: count_distinct

drill_fields: [created_year, item_gross_margin_percentage_tier, users.age_tier, total_sale_price]

link: {

label: "Total Sale Price by Month for Each Age Tier"

url: "{{link}}&pivots=users.age_tier"

}

sql: ${order_id} ;;

}

UsingVisual Drilling

This has all been good for looking at data, but what if we want to visualize it? Looker has a Labs feature called Visual Drilling, which allows users to drill into a chart. With zero customization and a limited drill set, we can get lots of different visualizations.

As an example, to show how many items have been sold by day, you can create a measure that drills into the Created Date and Total Sale Price fields:

measure: count {

type: count_distinct

sql: ${id} ;;

drill_fields: [created_date, total_sale_price]

}

Visual Drilling was based on a simple approach; the next question you may be asking is, "How can I control what visualization Looker shows?"

Make a Scatter Plot with Limit and Moving Average

Suppose you want to show how many items have been sold by day, with a 30-day moving average:

To do this, you can define the following LookML:

measure: count {

type: count_distinct

sql: ${id} ;;

drill_fields: [created_date, total_sale_price]

link: {

label: "Show as scatter plot"

url: "

{% assign vis_config = '{

\"stacking\" : \"\",

\"show_value_labels\" : false,

\"label_density\" : 25,

\"legend_position\" : \"center\",

\"x_axis_gridlines\" : true,

\"y_axis_gridlines\" : true,

\"show_view_names\" : false,

\"limit_displayed_rows\" : false,

\"y_axis_combined\" : true,

\"show_y_axis_labels\" : true,

\"show_y_axis_ticks\" : true,

\"y_axis_tick_density\" : \"default\",

\"y_axis_tick_density_custom\": 5,

\"show_x_axis_label\" : false,

\"show_x_axis_ticks\" : true,

\"x_axis_scale\" : \"auto\",

\"y_axis_scale_mode\" : \"linear\",

\"show_null_points\" : true,

\"point_style\" : \"circle\",

\"ordering\" : \"none\",

\"show_null_labels\" : false,

\"show_totals_labels\" : false,

\"show_silhouette\" : false,

\"totals_color\" : \"#808080\",

\"type\" : \"looker_scatter\",

\"interpolation\" : \"linear\",

\"series_types\" : {},

\"colors\": [

\"palette: Santa Cruz\"

],

\"series_colors\" : {},

\"x_axis_datetime_tick_count\": null,

\"trend_lines\": [

{

\"color\" : \"#000000\",

\"label_position\" : \"left\",

\"period\" : 30,

\"regression_type\" : \"average\",

\"series_index\" : 1,

\"show_label\" : true,

\"label_type\" : \"string\",

\"label\" : \"30 day moving average\"

}

]

}' %}

{{ link }}&vis_config={{ vis_config | encode_uri }}&toggle=dat,pik,vis&limit=5000"

}

}

What's Happening Here?

We are passing Looker's visualization settings into a URL using Liquid variables. These settings will control the visual drilling window. Below are some more examples.

Pivots and Stacked Line Chart

You can create a stacked line chart showing the Total Sale Price for each Month Number, pivoting on Created Year:

This isthe LookML for the above example:

measure: total_sale_price {

type: sum

value_format_name: usd

sql: ${sale_price} ;;

drill_fields: [total_sale_price, created_month_name, created_year]

link: {

label: "Show as stacked line"

url: "

{% assign vis_config = '{

\"stacking\" : \"normal\",

\"legend_position\" : \"right\",

\"x_axis_gridlines\" : false,

\"y_axis_gridlines\" : true,

\"show_view_names\" : false,

\"y_axis_combined\" : true,

\"show_y_axis_labels\" : true,

\"show_y_axis_ticks\" : true,

\"y_axis_tick_density\" : \"default\",

\"show_x_axis_label\" : true,

\"show_x_axis_ticks\" : true,

\"show_null_points\" : false,

\"interpolation\" : \"monotone\",

\"type\" : \"looker_line\",

\"colors\": [

\"#5245ed\",

\"#ff8f95\",

\"#1ea8df\",

\"#353b49\",

\"#49cec1\",

\"#b3a0dd\"

],

\"x_axis_label\" : \"Month Number\"

}' %}

{{ link }}&vis_config={{ vis_config | encode_uri }}&sorts=order_items.created_year+asc,order_items.created_month_name+asc&pivots=order_items.created_year&toggle=dat,pik,vis&limit=500&column_limit=15"

} # NOTE the &pivots=

}

Drilling to a Custom Visualization

You can allow users to drill to a :

To do this, include the custom visualization in the visualization settings you specify for the link parameter:

measure: average_shipping_time {

type: average

value_format_name: decimal_2

sql: ${shipping_time} ;;

drill_fields: [products.category, users.age_tier, average_shipping_time]

link: {

label: "See as custom viz (heatmap)"

url: "

{% assign vis_config = '{

\"minColor\" : \"#d6d6d6\",

\"maxColor\" : \"#9a33e3\",

\"dataLabels\" : false,

\"custom_color_enabled\" : false,

\"custom_color\" : \"forestgreen\",

\"show_single_value_title\": true,

\"show_comparison\" : false,

\"comparison_type\" : \"value\",

\"comparison_reverse_colors\": false,

\"show_comparison_label\" : true,

\"show_view_names\" : true,

\"show_row_numbers\" : true,

\"truncate_column_names\" : false,

\"hide_totals\" : false,

\"hide_row_totals\" : false,

\"table_theme\" : \"editable\",

\"limit_displayed_rows\" : false,

\"enable_conditional_formatting\": false,

\"conditional_formatting_include_totals\": false,

\"conditional_formatting_include_nulls\": false,

\"type\" : \"highcharts_heatmap\",

\"stacking\" : \"\",

\"show_value_labels\" : false,

\"label_density\" : 25,

\"legend_position\" : \"center\",

\"x_axis_gridlines\" : false,

\"y_axis_gridlines\" : true,

\"y_axis_combined\" : true,

\"show_y_axis_labels\" : true,

\"show_y_axis_ticks\" : true,

\"y_axis_tick_density\" : \"default\",

\"y_axis_tick_density_custom\": 5,

\"show_x_axis_label\" : true,

\"show_x_axis_ticks\" : true,

\"x_axis_scale\" : \"auto\",

\"y_axis_scale_mode\" : \"linear\",

\"ordering\" : \"none\",

\"show_null_labels\" : false,

\"show_totals_labels\" : false,

\"show_silhouette\" : false,

\"totals_color\" : \"#808080\",

\"series_types\" : {},

\"hidden_fields\" : [

\"order_items.count\",

\"order_items.total_sale_price\"

]

}' %}

{{ link }}&vis_config={{ vis_config | encode_uri }}&sorts=products.category+asc,users.age_tier+asc&toggle=dat,pik,vis&limit=5000"

}

}

Drilling to a Table Calculation with Conditional Formatting

You can also allow users to drill to a table calculation with conditional formatting:

This can be defined in LookML as in the following example:

measure: total_sale_price {

type: sum

value_format_name: usd

sql: ${sale_price} ;;

drill_fields: [created_month, users.gender, total_sale_price]

link: {

label: "Table Calc & Total"

url: "

{% assign table_calc = '[

{ \"table_calculation\": \"percent_of_total\",

\"label\": \"Percent of Total\",

\"expression\": \"${order_items.total_sale_price:row_total} / sum(${order_items.total_sale_price:row_total})\",

\"value_format\": null,

\"value_format_name\": \"percent_2\",

\"_kind_hint\": \"supermeasure\",

\"_type_hint\": \"number\"

},

{ \"table_calculation\": \"growth_rate\",

\"label\": \"Growth Rate\",

\"expression\": \"${order_items.total_sale_price} / offset(${order_items.total_sale_price},1) - 1\",

\"value_format\": null,

\"value_format_name\": \"percent_2\",

\"_kind_hint\": \"measure\",

\"_type_hint\": \"number\"

}]' %}

{% assign vis_config = '{

\"type\": \"table\",

\"show_view_names\": false,

\"show_row_numbers\": false,

\"truncate_column_names\": false,

\"table_theme\": \"gray\",

\"enable_conditional_formatting\": true,

\"conditional_formatting\": [

{

\"type\": \"low to high\",

\"value\": null,

\"background_color\": null,

\"font_color\": null,

\"palette\": {

\"name\": \"Custom\",

\"colors\": [

\"#FFFFFF\",

\"#6e00ff\"

]},

\"bold\": false,

\"italic\": false,

\"strikethrough\": false,

\"fields\": [

\"growth_rate\"

]},{

\"type\": \"low to high\",

\"value\": null,

\"background_color\": null,

\"font_color\": null,

\"palette\": {

\"name\": \"Custom\",

\"colors\": [

\"#FFFFFF\",

\"#88ff78\"

]},

\"bold\": false,

\"italic\": false,

\"strikethrough\": false,

\"fields\": [

\"percent_of_total\"

]}]}' %}

{{link}}&total=on&row_total=right&dynamic_fields={{ table_calc | replace: ' ', '' | encode_uri }}&pivots=users.gender&vis_config={{ vis_config | replace: ' ', '' | encode_uri }}"

}

}

When you define a link or html parameter in a measure that references a value from another field using the {{ view.field._value }} or {{ field._value }} syntax, Looker pulls the field into the SQL query in order to grab the field value. Because of this, Liquid can affect how SQL queries are generated and how many columns the GROUP BY clause uses, which can cause unexpected behavior when working with aggregate measures, like count.

For example, if you have the two following measures:

measure: count_without_liquid {

type: count

}

measure: count_with_liquid {

type: count

link: {

label: "Status Count"

url: "https://www.google.com/search?q={{ status._value }}"

}

}

When you generate a query using the count_without_liquid measure, you get the following results:

The SQL generated for the above results is below:

But, when you generate a query using the count_with_liquid measure, you get the following results:

As you can see, instead of a count for each month in the query, you receive a count for each month AND each status. That is because in the generated SQL, the status field was added to the query so that its value could be retrieved. And because it was added to the query, it was also added to the GROUP BY clause:

You can stop this from happening using the row[] function with the Liquid variable, which does not add the referenced field into the query:

link: {

label: "{% if row['view_name.field_name'] %} some_label {% endif %}"

url: "https://www.google.com/search?q={{ row['view_name.field_name'] }}"

}

Note that when using this syntax, the link works only if the field is selected or included in the query by some other means.

To sum up, the use of the row[] syntax will not cause the field to be added to the query like {{ field_name._value }} does. The dynamic label will cause the link to have no label if the field is not available, which causes the link to disappear from the link menu.

Creating a Moving Average with Table Calcs

Sometimes we want to display overall trends in our data more clearly, using moving (or rolling) averages. We can create flexible moving averages with table calculations in Looker, using the function offset_list(). This will return a list of values in a specified window, which we can then average.

To create a 7-day moving average, we will use the following calculation:

mean(offset_list(${field_being_averaged},0,7))

offset_list takes three arguments: the column from which you want to grab the offset values, how far from the current row you want to start the offset, and how long you want the offset list to be. You can change the number of rows (the last argument of the function, or 7 in this case) to increase your window, and you can change where the window will start (the second to last argument of the function, or 0). Positive numbers will move down, and negative numbers will move up. So to get the average of the ten values above the current row, you can do:

mean(offset_list(${value},-10,10))

Visualized, our moving average will look like this:

trend line feature

Creating a Moving Average with Trend Lines

We can also create a moving average using the available in the visualization edit panel, in the y-axis tab. Each point on the moving average trend line is the average value of the previous data points. The window for the number of previous data points to include can be specified with the Period setting.

Difference Between Trend Line and Table Calc

When deciding between using a trend line or a table calculation, it is important to note that the trend line moving average includes nulls by treating them as 0, whereas the table calculation moving average will ignore nulls (unless adjusted to do differently).

For example, let's say you have results for null, 3, 3. In the table calc mean(offset_list(${field_being_averaged},0,3)), the denominator would NOT include nulls, and the average would be 6/2 = 3.

We can adjust our table calc to match the behavior of the trend line, using something like mean(offset_list(coalesce(${field_being_averaged}, 0),0,3)) to replace nulls with 0s. The denominator would therefore not include nulls and the average would be 6/3 = 2.

Both methods are correct ways to calculate moving averages. To decide which one to use, you will need to determine whether nulls represent 0's in your data or whether they should be ignored. If you want them to be treated as 0s, then you can use the trend line moving average, or adjust your table calculation to replace nulls as 0s.

The Problem

I have a field that I only want some of my users to be able to see data in. While data access permissions are usually handled at the model level, I would rather not create a whole new model just to mask this field. I don't mind if other users know that the field exists, but I want to make sure they never see data for that field.

The Solution

In this case, we can use the ability to reference user attributes in SQL with Liquid syntax. This way, we can mask the contents of these fields, depending on the user who is querying them. Let's use the example of a Social Security number:

User Attributes with Liquid

First, we create a new user attribute called can_see_ssn. We set User Access to View, because we don't want users to have control over the value of this attribute, but we don't mind if they see it. We will set the default value to No, so we have to explicitly change the value to Yes for users and groups that should see this attribute. When done, the settings will look something like this: Access Grant documentation

Now, we can create a new dimension that references this user attribute and only returns values when it's set to Yes.

dimension: ssn {

type: number

sql:

CASE

WHEN '{{ _user_attributes['can_see_ssn'] }}' = 'yes'

THEN ${TABLE}.ssn

ELSE 0

END ;;

}

Bonus: We can take advantage of the fact that Liquid works in the label parameter, to let users know the field is redacted for them.

dimension: ssn {

type: number

label: "{% if _user_attributes['can_see_ssn'] == 'yes' %} SSN

{% else %} SSN (Redacted due to insufficient permissions)

{% endif %}"

sql:

CASE

WHEN '{{ _user_attributes['can_see_ssn'] }}' = 'yes'

THEN ${TABLE}.ssn

ELSE 0

END ;;

}

Access Grants

Access grants are another way to leverage user attribute values to control which end users are able to view specific Explores, joins, views or fields. Access grants are defined at the model level with the access_grant parameter. As part of the definition, you associate the access grant with a user attribute. You also specify which user attribute values provide access to the access grant.

Once defined, access grants can be applied to Explores, joins, views or fields with the required_access_grants parameter to restrict access to those structures.

For example, we can create an access grant to restrict user access to the ssn field:

access_grant: can_see_ssn {

user_attribute: can_see_ssn

allowed_values: ["yes"]

}

and apply it to the ssn dimension like so:

dimension: ssn {

type: number

sql: ${TABLE}.ssn;

required_access_grants: [can_see_ssn]

}

This will only allow users with the can_see_ssn user attribute value "yes" to view the ssn field.

Note: It is important to keep in mind that access grants are additive when nested at multiple LookML structure levels (Explore, join, view, field). Please review the for additional details.