Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2113 Topics

molly.lippsett
Looker Staff
molly.lippsettLooker Staff
published in Technical Tips & Tricks

Setting up Phabricator and Looker for Version Control

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

1020
molly.lippsett
Looker Staff
3 months ago
davids
Looker Staff
davidsLooker Staff
published in Technical Tips & Tricks

Git Deployment Strategies with Looker

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

7030
davids
Looker Staff
3 months ago
kenneth.vinson
Looker Staff
kenneth.vinsonLooker Staff
published in Technical Tips & Tricks

Using Cloud Source Repositories for Version Control in Looker

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

1880
kenneth.vinson
Looker Staff
3 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Masking Sensitive Fields for Certain Users

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

6940
sam
Looker Staff
3 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How to Make a Weighted Average

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: 

5330
sam
Looker Staff
3 months ago
molly.lippsett
Looker Staff
molly.lippsettLooker Staff
published in Technical Tips & Tricks

How to Change Colors and Fonts Using HTML

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

19340
molly.lippsett
Looker Staff
3 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Calculating Simple Monthly Recurring Revenue (MRR)

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

2760
Department of Customer Love
Moderator
3 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Sort by Pivots, Rank with Other Bucket, and Percentile Tail Analysis

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

5710
Department of Customer Love
Moderator
3 months ago
Brett_Sauve
Looker Staff
Brett_SauveLooker Staff
published in Technical Tips & Tricks

The problem of SQL fanouts

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

10980
Brett_Sauve
Looker Staff
3 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Scheduling on the Last Day of Month

 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

1770
sam
Looker Staff
3 months ago
Zam
ZamMember
published in Technical Tips & Tricks

Exporting Data to Google Sheets

Looker will not be updating this content, nor guarantees that everything is up-to-date. You can export data directly to Google Sheets using the Google Sheets action from the Looker Action Hub rather than implementing the method described in this article. See the Looker Actions — Google Sheets Help Center article for more information.Looker supports exporting a public Look to Google Sheets. In this post, we present some functions that make the process smoother. Basic setup Looker admins must enable the Public URLs setting.To obtain the function that enables you to import data from a Look into a Google Sheet:Navigate to the Look that you want to export to a Google Sheet. Click the gear menu and select Edit Settings. Copy the function under Google Spreadsheet. This function includes the ImportXML function that is built into Google Sheets. Paste the function into the upper-left cell of the Google spreadsheet where you would like your data to appear, and then press Enter.Refreshing your dat

21530
Zam
3 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How to Bulk Convert Looks to Lookless Dashboard Tiles (Using the Looker API)

Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemI have a dashboard with Look-based tiles, or a tile that is linked to a Look, as explained in the Creating User-Defined Dashboards documentation. I would like to convert all the tiles on the dashboard to Lookless tiles in order to remove clutter from my folder (called "Spaces" before Looker 6.20), since Look-linked tiles require that the associated Looks exist in the same folder as the dashboard.Each dashboard tile has an option to Convert Look to Tile, as detailed in the Editing User-Defined Dashboards documentation. This is a great workflow for converting tiles on dashboards with few tiles, or in cases where we may not want to convert all tiles on a dashboard, as it involves editing each tile individually. However, what if we want to convert all tiles on a dashboard in bulk?The SolutionUsing the Looker API 4.0, it is possible to do this with a script in the programming language of your

850
Department of Customer Love
Moderator
3 months ago
kenneth.vinson
Looker Staff
kenneth.vinsonLooker Staff
published in Technical Tips & Tricks

Recommendations and Pitfalls for Looker in Kubernetes

Looker will not be updating this content, nor guarantees that everything is up-to-date. OverviewDeploying the Looker application usually involves database servers, load balancers, and multiple servers running the Looker application itself, as well as other ancillary software. Looker is packaged as a monolithic application distributed in a JAR file. Each node in a Looker cluster is a running instance of Looker on either a Linux server or a container.Required componentsThe following components are required for a clustered Looker deployment:GKE Google Kubernetes Engine, managed Kubernetes service (it is possible, but not recommended, to run your own clusters) MySQL Relational database system used by Looker as its "internal" database Shared file system Shared POSIX-compliant multi access block device that all Looker cluster nodes store model data on Containerized version of the Looker application JAR files Containers that contain the Looker application and other required components and

3740
kenneth.vinson
Looker Staff
3 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

What HTML tags are allowed? (HTML Sanitization)Knowledge Drop

Last tested: March 2021 This doc lists all of the allowed HTML tags that can be used in html blocks in Looker. This content is subject to limited support.                

2830
slot deposit dana
3 months ago
kevin.dunn
Looker Staff
kevin.dunnLooker Staff
published in Technical Tips & Tricks

How to use liquid date formatting with a dynamic timeframe fieldKnowledge Drop

Last tested: May 14, 2020 The ProblemYou've seen that we can dynamically select timeframes using a parameter and dimension group and you've seen that we can do custom date formatting on individual fields using liquid, but you want to do custom formatting on the dynamic fieldA SolutionPut all that date formatting into a nice conditional statement in liquid!parameter: date_granularity {type: stringallowed_value: { value: "Day" }allowed_value: { value: "Week"}allowed_value: { value: "Month" }allowed_value: { value: "Quarter" }allowed_value: { value: "Year" }}dimension: dynamic_created_date {label_from_parameter: date_granularitysql:CASEWHEN {% parameter date_granularity %} = 'Day' THEN cast(${created_date} as VARCHAR)WHEN {% parameter date_granularity %} = 'Week' THEN ${created_week}WHEN {% parameter date_granularity %} = 'Month' THEN ${created_month}WHEN {% parameter date_granularity %} = 'Quarter' THEN ${created_quarter}WHEN {% parameter date_granularity %} = 'Year' THEN cast(${created_

7492
Anna_Mariscotti
3 months ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Can I filter data using table calculations?Knowledge Drop

Last Tested: Feb 4, 2019 Indirectly yes, example in Looker Docs here.The trick is to use "hide no's from visualization". This requires you to have your "filter condition" as a boolean table calc.Note that this isn't actually filtering in the SQL clause, and that this will only work on result sets with 5000 rows or less. There is a feature request for allowing a result set filter based on table calcs. This content is subject to limited support.                  

53141
robertc
3 months ago
V
Looker Staff
vishal.dharmadhikariLooker Staff
published in Technical Tips & Tricks

Why can't I see my explore in the Explore drop-down menu?Knowledge Drop

Last tested: Aug 31, 2020 First confirm that the Explore has been saved and then refresh the page to see if the explore is populating in the Explore dropdown.If the explore still isn't showing in the Explore menu, check if:The extension: required parameter is defined in the Explore. An Explore with extension: required is not visible to users on its own; it is intended only to act as a starting point to be extended by other Explores (documentation). The required_access_grants parameter is used on the Explore. If this parameter is used and a user doesn't have the necessary corresponding user attribute values, they won't be able to see the Explore in the dropdown menu (documentation). If the Explore is extending an Explore, check whether the base/extended Explore has hidden: yes defined.There is also a known issue in which if there is more than one model with the same name, the explore menu will sometimes show the explores from the unconfigured model instead of the explores from the confi

13943
B
3 months ago
tig
Looker Staff
tigLooker Staff
published in Technical Tips & Tricks

Advanced Templated Filters

Looker will not be updating this content, nor guarantees that everything is up-to-date. This page assumes you understand the Templated Filters and Parameters page in Looker Docs. Be sure to check out the parameter discussion on that page, because using a parameter field is a better approach for many use cases.The advanced templated filter applications discussed below are useful for a few use cases, such as making a derived table obey top-level filters for performance or aggregation reasons, creating dynamic filtered dimensions and measures, and creating dynamic WHERE clauses in queries. Referencing a Dimension Instead of a Filter in Templated Filter TagsIn this example, we have a filter field to accept user input:filter: my_filter_name { ... }This filter dynamically modifies the derived table's SQL, like so:{% condition my_filter_name %} ...Alternatively, a dimension filter can also be referenced in a templated filter tag. For example, you can define a dimension:dimension: my_dimensio

12221
Pinkone
3 months ago
molly.lippsett
Looker Staff
molly.lippsettLooker Staff
published in Technical Tips & Tricks

Excel: We found a problem with some contentKnowledge Drop

Last tested: Apr 2, 2020 We have seen an issue when downloading certain formats of data in Excel. The issue crops up in an error message like this when trying to open the downloaded spreadsheet:If you say Yes, it will tell you "Excel was able to open the file by repairing or removing the unreadable content"You can use binary testing (aka delete each column one by one) to narrow down the particular column and row that holds the data causing the problem - in a lot of cases these are columns that hold phone numbers, which Excel doesn't seem to like.From here, you can use SQL or REGEXP_REPLACE on the underlying data field to get it into a format Excel will accept, to fix the error. This content is subject to limited support.                  

2171
von_court
3 months ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Download Looker Jar ErrorsKnowledge Drop

Last tested: Mar 19, 2020 Invalid RequestThis is a catch-all error message for a number of different issues on the download page. Here are some of the main reasons this error can come up:You are using the wrong license key You are using the license key for a Looker-hosted instance (only self-hosted licenses can download jar files) You are using the wrong version name when selecting Specific Version (a valid example is looker-7.4-latest.jar) You are trying to download a jar for an unsupported release (more info on supported releases here)You can find more information on the looker jar file download page here.ERROR 400: Bad RequestOne reason for this issue is the formatting for the wget command when using the API method for downloading the jar files. Following the example from the doc: wget -O- --header=Content-Type:application/json </code>--post-data='{"lic": "YOURLOOKERLICENSE", "email": "contact@email.com", "latest":"specific", </code>"specific":"looker-5.24-latest.jar"}'

891
mconigliaro-cirrusmd
3 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How do Filter Suggestions Work?Knowledge Drop

Last tested: Aug 21, 2020 Filter suggestions are a way to save time when typing into filters and ensure you're choosing an option that exists in the data. When you click into a filter box, a list of suggestions will appear below the field. Where does this list of suggestions come from?Looker will run a query against the database to get all possible options for that field. It will look something like this:SELECT DISTINCT <field_name>FROM <table>WHERE (<field_name> LIKE '%' OR <field_name> LIKE '% %')GROUP BY 1ORDER BY 1LIMIT 1000 If the user types some characters into the filter box, Looker will substitute the appropriate conditions into the WHERE clause to filter the results. Looker will then show the first 100 of those results. Can I change what suggestions are populated?Yes! Check out this docs article. Are suggestions cached?Yes, they are! The cache length for filter suggestions is determined by the suggest_persist_for LookML parameter, which has a default va

10181
R
3 months ago
Lan
Community Admin
LanCommunity Admin
published in Video Tutorials 🎥

About Video Tutorials 🎥

Hi Good Lookers 😊 This subcategory contains video tutorials on some of the most popular use cases or errors. We hope you would find these tutorials useful!  Note: We will not be updating video tutorials, nor do we guarantee that everything is up-to-date. Please do not contact Looker Support for questions on these videos. If you’re looking for officially supported resources, you can always visit our Help Center and review our Docs. 🎥🎥🎥🎥🎥🎥🎥Video Tutorial: Debug "Unknown or inaccessible views or fields in an explore" errorVideo Tutorial: Install a custom visualization to a Looker instanceVideo Tutorial: Automate Looker tasks with Cloud Functions and Google Sheets

2690
Lan
Community Admin
3 months ago
Lan
Community Admin
LanCommunity Admin
published in Video Tutorials 🎥

Video Tutorial: Debug "Unknown or inaccessible views or fields in an explore" errorTutorial

 This tutorial walks through 5 common reasons why the error "Unknown or inaccessible views or fields in an explore" may occur and how to fix these issues.   

1530
Lan
Community Admin
3 months ago
Lan
Community Admin
LanCommunity Admin
published in Video Tutorials 🎥

Video Tutorial: Install a custom visualization to a Looker instanceTutorial

This tutorial walks through steps to deploy a custom visualization to a LookML project using the `visualization` parameter. Customers with self-hosted instances that do not open access to Looker's Marketplace can also use the steps to install visualization from the Marketplace.   

770
Lan
Community Admin
3 months ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

Can I use existing tables or PDTs with the aggregate awareness feature?Knowledge Drop

Last tested: Jun 3, 2020 No, the Aggregate Awareness feature new to 7.8 must be used with Looker-materialized tables defined in LookML.Below is the reasoning as to why aggregate awareness currently must use Looker-materialized aggregate tables:A key aspect of AA is that it depends on a Looker-defined query instead of pure SQL to define the aggregate tables. This is really important because it gives us a ton of information that would otherwise have to be explicitly modeled, for example but not limited to:which columns from the agg table map to which fields in the explore what explore the agg table is applicable to what explore-level filters need to be considered (like access_filters) how long the table is considered good for before it becomes out of dateAlso, by owning the PDTs we can take some liberties with the table creation that yield better features or user experience, for example:decomposing averages into one column for the numerator and one for the denominator (and in 7.10, decom

1011
S
3 months ago

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges
Terms of Service