Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications
Explore the product knowledge we've built up together
Looker will not be updating this content, nor guarantees that everything is up-to-date. This article discusses the EXPLAIN command, which is not supported by all database dialects. Be sure to check the Feature Support section in the Looker Database Configuration Instructions specific to your database to confirm whether this function is supported by your database dialect.Very slow SQL queries are sometimes a fact of life for a database. The database could be running as fast as it can while those queries are transforming a lot of data, or the database could be performing certain tasks that are hard for SQL query planners to accomplish. However, sometimes it's possible to optimize these queries by looking at the steps involved in completing the query, and using this information to redesign the query to be faster. In many SQL dialects, this can be achieved by using the EXPLAIN command.This article gives a very brief introduction to and an example of how to interpret the results of the EXPL
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. This article shows a method to generate subtotals with a standard Looker table visualization. In Looker 6.10, the Table-Next visualization was introduced, which can generate subtotals natively. For more information, see this documentation page.Subtotals are handy for a quick summary of grouped items, when you are also viewing from a higher level, such as subtotal of sales by brand, while also viewing the overall inventory sales. This article will show you a breakdown process on how to accomplish this. This is a stopgap solution, so check out this awesome Community post about generating Subtotals with a CROSS JOIN pattern. It provide
Looker will not be updating this content, nor guarantees that everything is up-to-date. Where and When to Use At our Santa Cruz Looker office, we love to track surfing sessions and different attributes about those sessions. Many of our surfers would like to analyze this data in Looker, without going through a complicated ETL process that would require them to understand the inside and outside specification of databases. Instead, we write a script that enables users to pull information from a Google Sheet and push the data to a desired database of our choosing! This makes the data easy to model and Explore via the database connection in Looker. Use Cases Do you have non-technical users who want to upload data, like goals for their current quota or forecasts for upcoming quarters, to your database? How would a non-technical user go about achieving this without involving a database administrator? This article might serve as a potential solution. See It in Action Below is a Google Sheet th
Looker will not be updating this content, nor guarantees that everything is up-to-date. If you'd like to count the days between two dates, you could use the SQL DATEDIFF function, which simply counts the days between two points in time:Redshift:DATEDIFF('day', start, stop) AS daysPostgres:DATEPART('day', start - stop) AS daysMySQL:DATEDIFF(stop, start) AS daysSnowflake:DATEDIFF('day', start, stop) AS daysHowever, the functions above will count all the days between the two specified dates. What if you want to exclude weekend days (Saturday and Sunday)?One way of achieving this is to create a derived table with the necessary logic. But, there is a more straightforward way, using a single short query, which you can define in the sql parameter of a dimension or measure in LookML.Note:You will want to make sure your database will return a day of week index of 0 for Sunday and 6 for Saturday; this is important, or the calculations will be one or two days off. You will also want to make sure
Looker will not be updating this content, nor guarantees that everything is up-to-date. Pivoting results by a dimension in an Explore is a great way to organize results and visualizations in a more readable way. Sometimes, however, pivoted results can display quite a few null values in a column where there are no matching values for a row:So what do we do if we don't want to have to wade through that sea of nulls just to find the first non-null value in a pivot column or row? This article discusses how to find that first non-null value by using table calculations. Getting the First Non-Null Record in a Pivot ColumnThe following table calculation will return the first non-null value of a pivot column as a single constant value:index(${history.query_run_count},min(if(NOT is_null(${history.query_run_count}), row(), null))) Here's how it works: if(NOT is_null(${history.query_run_count}), row(), null) In this statement, the if() function evaluates each row one at a time. If the measure valu
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. 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, like the following table: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, th
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. This article is written with help and examples by Xin Bao, Customer Support Data Analyst.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. SolutionTo begin, we will create an Explore with the fields necessary for performing the
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.14, analysts can add data projections to new or existing Explore queries to help users predict and monitor specific data points with the Forecasting Labs feature. The Problem: I Need to See the Future! I have some data from the past, and I want to use it to predict the future. Trend lines are partially useful, but they don't extend past the current data.This Explore is what I currently have:This Explore is my goal:A Solution: Linear Forecasting with Table CalculationsWe can achieve this kind of forecasting by using table calculations instead of trend lines. At a high level, our two steps are to create future dates and then to calculate the line equation with table calculations. Step 1: Creating Future Dates with Dimension FillWe need our graph to show both existing and future dates along the x-axis. The easiest way to achieve this is to turn on dimension fill and change the fi
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 7.12, you can deploy any Git commit SHA, tag, or branch to production with Advanced Deploy Mode. This helps consolidate repositories in multi-environment developer workflows, where each environment points to a different version of a codebase. It also gives greater control to one or a few developers or administrators over the changes that are deployed to production. MotivationBy setting up a Git workflow across multiple Looker hosts, we can create a dedicated development environment for developers to work in, all while shielding users from being exposed to any experimental code. All development and code review is done in a development or staging environment, and once changes pass your team's quality assurance, they are deployed to staging or production as desired.This article describes two different methods of setting up a workflow among multiple instances, depending on whether you
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 7.12, you can deploy any Git commit SHA, tag, or branch to production with advanced deploy mode. This helps consolidate repositories in multi-environment developer workflows, where each environment points to a different version of a codebase. It also gives one or a few developers or administrators greater control over the changes that are deployed to production. MotivationBy setting up a Git workflow across multiple Looker host instances, you can create a dedicated development environment for developers while preventing end users from accessing 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 how to set up a workflow among multiple instances and multiple Git repositories. If you have multiple
Looker will not be updating this content, nor guarantees that everything is up-to-date. This article is meant to be used in conjunction with the Looker documentation page Setting Up and Testing a Git Connection to configure a LookML project with SSH authentication. This post highlights the differences when setting up version control with a SSH key from Bitbucket Cloud.Bitbucket Cloud is different than Bitbucket Server. Bitbucket Cloud repositories are hosted by Bitbucket, while Bitbucket Server repositories are hosted on a user's own server. The instructions below apply only to Bitbucket Cloud.IMPORTANT: The user who owns the SSH key must be a member of the Bitbucket repository and must have write access to it. To enable write access on the repository, the user must be the owner of the repository, have explicit write access granted to them, or be a member of a group that has write permissions on the repository.As you go through the instructions in the SSH section of the Setting Up and
Looker will not be updating this content, nor guarantees that everything is up-to-date. This Help Center article is a companion guide to the Connecting to Git Using SSH section of the Setting Up and Testing a Git Connection documentation page. This article outlines the steps specific to configuring version control with a Phabricator repository. We recommend that you reference this guide as you move through the steps provided in the documentation. Configuring Git with PhabricatorOpen your project in Looker and then do one of the following: For a new project, click the Configure Git button. For an existing project, click on the Git menu and select Project Settings, and then click the Reset Git Connection button from the Project settings page. Starting in Looker 7.12, the Reset Git Connection button is located on the Configuration tab of the Project Settings page in the new IDE. Navigate to the Project Settings page by clicking the Settings icon from the navigation bar. Get the SSH U
Looker will not be updating this content, nor guarantees that everything is up-to-date. IntroductionThe purpose of this article is to help development teams choose a Git flow strategy that best suits their team structure, delivery cadence, and code review requirements.This article provides three flow examples, each with options for either a single instance or a multi-instance setup. The examples are presented in order of their complexity and rigor. Choose the option that achieves the right balance for your use case.The following three workflow examples are described in this article:GitHub workflow GitLab workflow Gitflow workflowA fourth workflow, for multiple Git repos, is included, but this approach is not recommended. Note: pushing to non-production branchesBy default, Looker will deploy all changes to a repository's default production branch. The Looker documentation on Getting your changes to production describes the options for modifying the default behavior, including the follow
Looker will not be updating this content, nor guarantees that everything is up-to-date. This article is meant to be used in conjunction with the Looker documentation page Setting Up and Testing a Git Connection, which describes how to integrate Looker with Git, including connecting to Git using SSH. This article highlights the steps for setting up version control with an SSH key from Cloud Source Repositories. IMPORTANT: The user who owns the SSH key must be a member of the Cloud Source Repositories repository and must have write access on the repository. To be allowed such access, the user must be the owner of the repository, have explicit write access granted to them, or be a member of a group that has write permissions on the repository.You can follow along with the instructions in the "Connecting to Git Using SSH" section of the Setting Up and Testing a Git Connection documentation page. As you read through and implement the instructions in the SSH section of the documentation page
Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemYou have a field that you want only some of your users to be able to see data in. While data access permissions are usually handled at the model level, you don't want to create a whole new model just to mask this field. You don't mind if other users know that the field exists, but you do want to make sure that the users never see data for that field.The SolutionsThis article discusses two possible ways of approaching this problem:Use the ability to reference user attributes in SQL with Liquid syntax to mask the content of these fields. Use access grants to leverage user attribute values to control which users can view specific content.The following examples use email addresses to illustrate these solutions. User Attributes with Liquid Referencing user attributes in SQL with Liquid syntax lets you mask the contents of these fields, depending on the user who is querying them.Create a new us
Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemWe want to make a weighted average. The SolutionIn this example, we want to create a very basic customer health comparison by taking the average of each customer's order prices and weighting them by how recently each order was placed.First, we give each order a numerical weight, giving a higher weight to more recent orders:dimension: weight { type: number sql: CASE WHEN ${days_since_order} < 30 THEN 3 WHEN ${days_since_order} < 60 THEN 2 ELSE 1 END ;;}From here, we can calculate a weighted price by multiplying the weight by the price, and finally creating the weighted average of this weighted price:dimension: weighted_price { type: number sql: ${sale_price} * ${weight} ;;}measure: weighted_average { type: number sql: sum(${weighted_price})/sum(${weight}) ;; }The result is a weighted average, which focuses on recent orders:
Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemI have pictures that I want to show in my Looker visualization. In a dataset of companies, I could use this to replace a company name with an image in a visualization: The SolutionWe can use the html LookML parameter to have Looker render HTML instead of just showing a text value. In this case, we can use HTML's img tag to have a field just render a picture:dimension: looker_image { type: string sql: ${TABLE}.homepage_url;; html: <img src="https://logo-core.clearbit.com/looker.com" /> ;;}The text for each of the rows has now been replaced by the image defined in the img tag of the HTML. Dynamic ImagesThe next step is to make my images change based on the value of the row. We can input the value of the row into the html parameter using the Liquid variable {{value}}. We can add this to the image URL in a slightly modified version of the dimension above: dimension: brand_images { t
Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemWhen we want to look at our data in terms of 30-day periods, we can use Looker's built in month timeframe for dimension_group fields. However, this can pose a problem, as not all months are the same length. Also, in cases where the current day is in the middle of a month, the data for that "period" is incomplete. The goal in this case is to get a period for every 30 days (i.e., past 0-30 days = 1, past 30-60 days = 2, 60-90 days = 3, etc.), starting from today and moving backwards, by which to group our data. The SolutionIn Looker, we can achieve this pattern by placing a SQL function in the SQL: parameter of a dimension. For example, let's say that we have a date dimension_group called created:dimension_group: created { type: time timeframes: [time, date, month, raw] sql: ${TABLE}.date ;; }Then, all we need to do is create a new dimension of type: number to capture the number of days
Looker will not be updating this content, nor guarantees that everything is up-to-date. By using HTML tags in LookML, it is possible to manipulate certain elements of table visualizations, such as font color or size. Using HTML tags and Liquid variables, you can build a dimension or measure with the style of your choice. Read about the html field parameter and Liquid variables on the html and Liquid variables documentation pages, respectively.For example, this dimension will display names in green:dimension: name { type: string sql: ${TABLE}.name ;; html: <font color="green">{{ value }}</font>;;} This can be helpful for highlighting specific values or conditions with conditional formatting. There is also an option for conditional formatting on the Formatting tab for table visualizations. To apply conditional formatting in your visualization, turn on the Enable Conditional Formatting feature to view the options. You can specify the color code for your visualizat
Looker will not be updating this content, nor guarantees that everything is up-to-date. You can create ad hoc custom groups for dimensions without using logical functions in Looker expressions or developing CASE WHEN logic in sql parameters or type: case fields when the Custom Fields Labs feature is enabled and you have permissions to create custom fields.You can also create ad hoc custom bins for numeric type dimensions without needing to use logical functions in Looker expressions or needing to develop type: tier LookML fields when the Custom Fields Labs feature is enabled and you have permissions to create custom fields.When you are building a chart or table off a high-cardinality dimension, you might want to lump all low-frequency dimensions into an Other bucket to avoid cluttering your output. Referencing the :total of a measure in a table calculation makes other bucketing possible without the need for any LookML coding.Starting off with a count of events on our Looker instance: T
Looker will not be updating this content, nor guarantees that everything is up-to-date. Monthly Recurring Revenue, or MRR, is an important metric for any subscription-based business. It is used to predict future income streams and trajectory, to help measure the health of your business.We can measure simple MRR within Looker, creating a SQL based derived table comprised of window functions.There are different methodologies behind calculating MRR, but in its simplest form MRR consists of:Activation (new customers) Recurring payments (when existing customer payments remain the same month-to-month) Expansion (when existing customer payment is greater than prior month) Contraction (when existing customer payment is lower than prior month) Churn (when payments stop)The following example assumes you have a transaction table that tracks monthly payments by users. The SQL for our derived table would look something like this:SELECT user_id,date_month,prior_amt,current_amt,post_amt,case when pri
Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemA 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 SolutionIn this example we'll use a simple e-commerce model. Here I've pulled my top brands by revenue: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 PivotsTo 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
Looker will not be updating this content, nor guarantees that everything is up-to-date. NOTE: When this entry was originally written, Looker users needed to use the following strategies to properly calculate certain metrics. Symmetric aggregates render much of the information in this article unnecessary.However, this article is still useful for writing SQL in general, and specifically for working with SQL dialects that do not support symmetric aggregates. If you're a SQL user, some of the first SQL concepts you probably learned about were joins and aggregate functions (such as COUNT and SUM). One thing that is not always taught is how these two concepts can interact and sometimes produce incorrect results. In this article we'll discuss what to look out for, the concept of a "fanout," and why it matters to SQL writers and Looker users alike.Starting with a friendly join Let's start off with a simple example, where we'll join together a couple of tables. Our first table will show our cu
Looker will not be updating this content, nor guarantees that everything is up-to-date. When scheduling content in Looker, we can choose which day of month to send the Look: If we choose the 31st of the month, then the schedule simply won't run on months with fewer than 31 days. So how do we select the last day of month? Using a custom filter for LooksFor Looks, we can filter the Look so it only shows any results on the last day of month, and then tell the schedule to only send out on the last day of the month. Here is how:Create a Custom Filter The custom filter we'll apply here is: extract_days(add_days(1,now())) = 1 The above expression returns true if tomorrow is the first of the month, which is another way of saying today is the last day of the month. This custom filter will filter out all results, unless today is the last day of the month. Schedule Only if There are Results Because there will only be results on the last day of the month, we can now schedule this Look to go out
