Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,744 Topics
  • 406 Replies

1744 Topics

ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

How To Create a Dashboard Filter Through the APIKnowledge Drop

Last tested: Mar 30, 2020 The good news: creating the filter is very easy!The bad news: applying it to tiles is much more involvedtl;drRun create_dashboard_filter endpoint, noting the filter name. Run get_all_dashboardelements to get an array of all tiles on the dashboard. Run get_dashboard_element and copy any existing applied dashboard filters in the "listens" array. Run update_dashboard_element by pasting in the existing filters to the "listens" array, then add your new filter to this array. Repeat steps 3 and 4 for any other elements returned in step 2 that you would like to have the new filterCreating the FilterUse the create_dashboard_filter endpoint: https://docs.looker.com/reference/api-and-integration/api-reference/v3.1/dashboard#create_dashboard_filterFor more complex filter syntax, I'd recommend creating an example filter then using the endpoint to get the filter and see what the API returns. An example of a simple call:Applying the filterThis part is a bit more involved. No

7979
akhilsai
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to extract names from email address in table calcKnowledge Drop

Last tested: May 7, 2020 The ProblemUsers will sometimes want to get a substring of their dimension, for example, extracting first.last from first.last@gmail.comA SolutionWe can pretty easily do this with a table calc. We can either use 2 columns or combine them.Create a "position" column to get the length of the string before the value we want. For emails this will be @.Position: position(${users.email}, "@") Then, we create the substring table calc;Name: substring(${users.email},0,${position}-1) The above 2 steps will extract the names, if you would like to combine those into a single column it would look like:Name: substring(${users.email},0, position(${users.email}, "@")-1) This content is subject to limited support.                   

3379
Rahul12
4 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How do I do a ROW_NUMBER OVER (PARTITION BY ...) with a table calc?Knowledge Drop

Last Tested: Jul 1, 2018 match(${products.brand},${products.brand})if(${partition}=offset(${partition},-1) , 1+row()-${partition} , 1 This content is subject to limited support.                   

71739
Loretta
1 month ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentialsKnowledge Drop

Last tested: Jan 20, 2021If you're seeing this error, check to see if the table that is being queried is a google sheet that's stored in Drive.If that's the case, make sure that the Service Account for BQ that Looker is using has access to the Drive. This content is subject to limited support.                   

188639
n0viandi
7 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Show or hide value labels on a specific seriesKnowledge Drop

Last tested: Mar 31, 2021 There isn’t a native option in the visualization edit panel to toggle value labels on/off for individual series, but please leave feedback for the Looker product team! After authenticating into the feedback portal, you can click here for the open request!Two other options you can use below:Solution 1You can use the "Value Colors" input field to manipulate the coloring of each series by entering color codes or names and comma separating to target series in the order they appear from left to right. If you define the color as "transparent" it will hide the value label for the series! Caveat: "transparent" is only transparent to the underlying graphic, it overlaps with other value labels as "white." If you have overlapping value labels, make sure the series you want with "transparent" value labels is listed last.Solution 2Another workaround we can implement is to use the Excel-style formatting in the value format section. For example, if we write#.00%[>0.05];[&

51028
Avital
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How are files sent to SFTP and S3 buckets named?Knowledge Drop

Last tested: May 31, 2019 All file names look like scheduleName_timestamp_hash, where hash is a random 6-character identifier and the timestamp follows the pattern YYYY-MM-DDTHHMM (e.g. 2019-05-31T0933).There is a feature request ticket to be able to customize the file name of files sent via schedules: Change file name for data sent in schedules This content is subject to limited support.                   

4617
sassanuvat
1 month ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How do I render a local image in Looker?Knowledge Drop

Last tested: March 2021 Looker expects a public URL for your image. So make sure that wherever you store it is publicly accessible. Then, you can call it in LookML like so:view: view_name { dimension: field_name { html: <img src=”http://www.acme.com/product_images/my_image.jpg” /> ;; }}This help center article goes more in depth on the modeling.It's not possible to use a local image on your computer, or on the Looker server. It is also not possible to use non-public URLs, as Looker will have no way to authenticate. This content is subject to limited support.                

14756
sidsenthil
8 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Window Functions in LookerKnowledge Drop

Last tested: April 2021 How do I use window functions in Looker?Window functions are a very powerful tool in SQL because they perform post-aggregation calculations. There are two main ways to implement window functions in Looker:1: Use Table CalculationsCalculating after aggregations is exactly what table calculations excel at. Many common window functions in SQL can be replaced by table calcs like row, offset, or clever combinations thereof. Do keep in mind though, table calculations can only be performed on the data within the Data Tab of the Explore page!2: Use a derived tableIf table calculations don't provide what you need, you can always write a derived table with whatever SQL you want, including window functions.  This content is subject to limited support.                   

93336
nicohein
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

How do I make a Look/Dashboard full screen and remove the Looker banner? (Presentation and Embed mode)Knowledge Drop

Last tested: Feb 24, 2021 You can add /presentation/ or /embed/ in the URL of an explore, Look or dashboard. Both will make the content full screen and remove the top navigation bar. For dashboards next, you can add a parameter, print_mode=true to the URL. (/dashboards-next/1234?print_mode=true)There are subtle differences between the two, but it's most important to note that /embed/ will show exactly the way that embed users will see the content. This content is subject to limited support.                   

12905
S
6 months ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

How do I hide one of my pivot columns?Knowledge Drop

Last tested: Mar 16, 2020 If you find yourself here, +1 this feature request: https://feedback.us.pendo.io/app/#/case/17612We're here, and we want to hide the "other" column:We can accomplish this with pivot_index or pivot_where.You'll need to create a table calculation for each column you want to show, then hide the original measure.pivot_index(${your_measure_here},column_to_show)This ends up looking like this:And in the visualization looks like this: This content is subject to limited support.                  

21655
Raanan.Yossef
1 month ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

How to cache a dashboardKnowledge Drop

Last Tested: Apr 1, 2019 You have two options here.Instant DashboardsThe first option is to use instant dashboards. With instant dashboards, dashboards display the previous run’s data while new queries are run in the background, letting information appear quickly. This is a labs feature. More information here. LookML-level cachingThe second option is to persist the explores that the dashboard's tiles are built on. This can be done in the LookML using datagroups with the 'persist_with' parameter or using the 'persist_for' LookML parameter. 'persist_with/for' can be defined for the entire model, or for individual explores.Note: Instant dashboards need to be turned off for this method to work. Which to choose?The benefit to instant dashboards is that we can see old information while new information loads. Instant dashboards will always hit the database, so you can be sure you're looking at fresh data.The benefit to datagroups is tighter control over your caching given that you know your E

27165
rmehta
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How to create a TopoJSON file for use in a Custom Map LayerKnowledge Drop

Last tested: June 2021This Help Center article explains how to make a topojson file to use in a map layer in Looker.Remember: The property_key field is CASE SENSITIVE! A custom map layer may be set up and referenced correctly, but the map may display blank if property_key is case mismatched.map_layer: my_neighborhood_layer { file: "neighborhoods.topojson" property_key: "neighborhood"} This content is subject to limited support.                

28005
Dawid
1 year ago
davids
Looker Staff
davidsLooker Staff
published in Technical Tips & Tricks

Looker Multistage Development Framework (Dev->QA->Prod)

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.IntroductionThis article provides a comprehensive development framework for managing the promotion of LookML code and content across multiple Looker instances and is intended to aggregate various resources into a cohesive walkthrough, provide a deeper understanding of the advantages and disadvantages of having multiple Looker instances, and present a best practice framework for a development strategy. In This ArticleWhy Have Multiple Looker Instances? Release Management Is a Development Instance Necessary? Benefits and

78914
D
7 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Embedding Looker Content in Salesforce

Looker will not be updating this content, nor guarantees that everything is up-to-date.  In order to embed content in Salesforce, you will need to have Visualforce development permissions and the ability to edit pages and objects in SFDC.This post covers an advanced topic that assumes a familiarity with embedding Looker content. Embedding dashboards and Looks in the applications and platforms where your users spend their time is one of the most powerful ways to leverage Looker. This Help Center article expands upon the concepts addressed in How to Quickly Embed Looker in the Tools and Apps You Use, to provide guidance on embedding content within Salesforce. Adding a New Visualforce Page The examples below use the private embed method, which requires each user to be signed into Looker to be able to view content. Alternatively, you can make content available to all users regardless of Looker access by using the public embed method for Looks.The first step to embedding content in Salesfor

25314
charlottemalt
5 months ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Can you show / hide filters on embedded content?Knowledge Drop

Last tested: Jun 8, 2020 Yes you can! The way to do this is different depending on the type of content.For User defined dashboards, you can use the embed settings option in the cog menu. (click the cog menu on the dashboard > Edit Embed Settings.) For LookML dashboards, you can use the show_filters_bar parameter in the Dashboard LookML file code for your dashboard. For example:embed_style:background_color: "#f6f8fa"show_title: truetitle_color: "#3a4245"show_filters_bar: truetile_text_color: "#3a4245text_tile_text_color: ''show_filters_bar: false For a Look, the filters are hidden by default but can be shown using the Show Filters on Embedded Looks toggle (available as of Looker 7.4). This content is subject to limited support.                  

28454
fabio
Looker Staff
1 month ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

Query task [hash] results not found in cacheKnowledge Drop

Last tested: Aug 19, 2021 This error will happen on some merged result query runs, usually as a result of the cache being busted for one of the source queries.Merged queries essentially work by running all source queries asynchronously, and then once all queries are marked as complete, the results of all source queries are fetched from cache and stitched together as defined in the merge.This problem can arise when, while the underlying queries are running, the cache for one or more of the source queries expires.For example in a simple merged query with two source queries, A and B, if results for query A were to already exist in the cache but B needed to run against the database, if the cache for query A expired between when query B started and when it finished running the results for query A would no longer be found in cache.Alternatively, if query A did not have cache set but finished quickly, and the caching policy for query A was aggressive (e.g. max cache age of 30 seconds), then t

3484
IanT
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Filter on this month but also see previous month's dataKnowledge Drop

Last tested: May 2020 The ProblemYou want to give your users a single date selector, where they can choose a month. Then, when they select a month, they should see data from that month and the previous month.Filtering for this month on a date dimension will remove any data from the past month, unfortunately. How to show both? The SolutionAssume ${date_raw} is the date you want to filter on. (Make sure to use the raw timeframe if it's a dimension group!)First, create a filter field for the user to interact with. Remember that a filter field doesn't immediately update the WHERE clause, but can be targeted with Liquid.filter: date_selector { type: date} Second, create a yesno dimension that captures the value of date_selector using liquid. This will be your "this month" indicator.dimension: is_selected_month { type: yesno sql: {% condition date_selector %} ${date_raw} {% endcondition %} ;;} Third, create a yesno dimension that captures the start and end of date_selector and subtracts a mo

42374
yadderace
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

What is included in "Runtime" in System Activity?Knowledge Drop

Last tested: May 11, 2020 The Question:System Activity users who are trying to assess query performance will often ask things like:What is included in query runtime? How come the query runtime doesn't match the runtime in my database?Answer:Query runtime is the time Looker spends from when the query is asked to when it is returned:Sending the query to the DB Waiting for a result from the DB Receiving the result from the DB and writing the JSON for the visualization to render   This content is subject to limited support.                      

2684
margaret.abercrombie
Looker Staff
10 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Can I download a dashboard as a CSV or PDF via the API?Knowledge Drop

Last tested: Sep 27, 2019 For PDF: Yes. Use create_render_task() to schedule a job to render a dashboard (or other content) as a PDF file.Use render_task(id) to check the status of the render task. When it completes, use render_task_results() to fetch the PDF file.Note that PDF files are binary data, not JSON text. Client apps calling the render_task_results() may need to take special steps to fetch the HTTP response body as binary data, not as json text.For CSV of dashboard data: Yes, with some work. We don't offer an API to run all queries for a dashboard and return all the results. What you can do is fetch the dashboard, iterate through its tiles and find all the queries it refers to, and run those queries via the API, in CSV format. Don't forget to apply filters!If you are familiar with the SDKs, there are some examples there as well:Python: https://github.com/looker-open-source/sdk-codegen/blob/master/examples/python/download_dashboard_pdf.pyRuby: https://github.com/looker-open-so

25074
vishnuprasadb
6 months ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

Faceted Filters in Explores (workaround)Knowledge Drop

Last tested: Nov 26, 2020 Faceted (linked) filters in explores aren't possible in Looker. Be sure to give a +1 to the feature requestSituation:Let's say we two have two filters on an explore page: Country and State. We want the State filter to populate based on the Country filter's selection.Steps:Add a User Attribute for Country. Create a derived table for State by using the following syntax:view: suggestions {derived_table: {sql: select distinct statefrom your_schema_name.your_table_namewherecountry = {{ _user_attributes['country'] | | prepend: "'" | append: "'" }} ;;}dimension: state {type: stringsql: ${TABLE}.state ;;}}On the explore page, apply the Country filter normally, but make sure to apply the State filter based on the dimension from the derived table, not from the original data table. Change User Attribute value to be whatever you select in the Country filter on the explore.This is a more involved workaround and would not scale well if you have many faceted filters on your

3424
evelyn.shen
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

PDT Naming ConventionKnowledge Drop

Last Tested: Jan 31, 2022 PDT name structure:LR$[connection_key][hash]_[view_name]All PDT names begin with LR, short for Looker Read. The connection_key is a two letter string that is unique for the connection. The PDT hash is an encoding slug that follows the connection_key and precedes view_name in the SQL table name. (ex: if the PDT SQL table name is LR$ZLYI79XRAB6OU1633ET9D_CS_HEALTH_CALENDAR_DAILY, the connection_key is ZL and the hash is YI79XRAB6OU1633ET9DThe PDT hash encodes the following information:Connection Scratch Schema Instance Slug Underlying LookML View Name Datagroup name, if used SQL of the sql_trigger/sql_trigger_value, if used Underlying SQL of the PDT (including SQL of dependencies) Increment Key Creation TimestampSince the hash includes the creation timestamp, the PDT name changes every time it is rebuilt, with the exception of incremental PDTs. With incremental PDTs, the PDT name remains the same upon each incremental rebuild, unless there is a change to th

9264
S
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Common SQL error troubleshooting tips in Looker

Overarching themes/questions: Here is a list of common questions our users often need support with, along with troubleshooting steps to try out: 1. Why is my SQL query slow even when there are no queries queued on Looker? Run against database (or SQL Runner): check if that runs fast to see if it is a database problem or looker problem If the query runs slow in both SQL Runner and database, that means that the query itself is slow and that it is not specifically a Looker issue, but instead a database issue or query complexity issue Explain in SQL can help to optimize the query Check join logic, so we can make sure to avoid fanouts Open up the generated SQL in SQL Runner to test your query 2. I have a part of a query, and I see there’s an error from this query. How can I find out which field is causing the error? Start by making the query less complex: Remove all filters and extra fields to isolate the field causing the error  If the query is pivoted, unpivot the field a

28654
markjuan458
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How can I merge user attribute values from multiple groups?Knowledge Drop

Last tested: March 2021 Background: Groups are given user attribute values. For example, group_USA might be given the value for the “country” user attribute as “USA”. With an access filter, users in that group will get WHERE country = “USA” for their queries.The issue: If a user belongs to two groups, like group_USA and group_UK, each with different “country” user attribute values, this user will not inherit both user attribute values. Other types of permissions are additive with groups, but user attributes are not: one will “win out”.To solve the scenario where someone would need access to both countries, you might think you need to create a user attribute for each combination of user attribute values. While that would work, managing all the combinations might get unwieldy. The following pattern is an alternative.1. Create a user attribute for each possible individual value. Ex: a user attribute for every count2. Create a group for each user attribute.3. Assign groups to user attribut

5954
Beto
8 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How do I change the order of my visualization/table?Knowledge Drop

Last tested: Dec 19, 2019 Table NextIn Table Next, you can freely drag columns around in the visualization.Table LegacyIn Table Legacy, the order of the visualization is dependent on the order of the columns in the data table. You can drag the columns to change the order of the series. However, you can't drag between different types of columns.Workaround for Table LegacyIt's true that you can't drag columns between different types of columns. There are 4 main types of columns:Dimensions Table Calculations based on dimensions ("green dimensions") Measures Table Calculations based on measures ("green measures")  You can freely drag columns that are the same type between other columns of the same type.Thus, a workaround for reorganizing any column is to make all desired fields into "green measures"!To turn a measure into a green measure, simply make a new table calculation with the measure definition. ${measure} To turn a dimension or green dimension into a green measure, make a new tabl

41664
BIDevSonu
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Max or Min Date Measure Showing NullsKnowledge Drop

Last Tested: March 21, 2021  Why is this happening?Looker's min and max types only allows for numerical values. Everything else will show a null.How do I fix it?Make a measure of type "date" and apply the "max" aggregate in the SQL. This Help Center article article provides the following example on how we can implement this.## Suppose we have a type: time dimension group, called updated defined as:dimension_group: updated { type: time timeframes: [time, date, week, month, raw] sql: ${TABLE}.updated_at ;;}## We can create a measure to capture the maximum date of this dimension group as follows:measure: last_updated_date { type: date sql: MAX(${updated_raw}) ;; convert_tz: no}This content is subject to limited support.                  

8984
sam
Looker Staff
1 year 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