Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Escape Liquid CharactersKnowledge Drop

Last tested: Oct 16, 2019 Add back-ticks,Ex:dimension: escape_liquid_1 {type: stringsql: case when 1=1then "\{\{test\}\}"end;;} This content is subject to limited support.                   

1570
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Troubleshooting Timestamp_tzKnowledge Drop

Last tested: Feb 1, 2021 When you autogenerate a new project or Create View from Table from a Snowflake schema, Looker will detect timestamp_tz types in the database and generate cast statements in the generated LookML that correctly handle this datatype. Specifically, in the sql parameter of the dimension groups the LookML generator creates, it will cast the field to timestamp_ntz to account for the actual timezone in the timestamp and not lose any information from the offset timezones.Example:sql: CAST(${TABLE}.created_at AS TIMESTAMP_NTZ) ;; However, there may be some cases where this casting is not autogenerated when the view was created:If you create the view manually instead of autogenerating If you have a timestamp_tz equivalent type field in a SQL dialect where we do not generate this casting (currently we do so for Snowflake) If you are on an older version (before Looker 7.6) where we didn't generate this castingIn these cases, you may encounter strange issues where the values

3150
jamesnestler
Looker Staff
1 year 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.                  

21565
Raanan.Yossef
1 month ago
Jiro
Moderator
JiroModerator
published in Technical Tips & Tricks

"Unlimited Download" not possible in UI for unstreamable resultsKnowledge Drop

Last tested: Dec 9, 2020 To reduce the amount of user initiated instance crashes, the ability for users to download unlimited unstreamable results has been removed from the UI since version 4.14.Things you can do for the UI:Make the query streamable (i.e turn table calculations into measures and remove pivots - check the streaming section on the What are all the Row Limits in Looker? article) Select up to 100,000 rows in the custom download limit Developer can set a custom maximum on the command line with --max-unstreamed-limit startup option. This content is subject to limited support.                   

770
Jiro
Moderator
1 year ago
Jiro
Moderator
JiroModerator
published in Technical Tips & Tricks

How can I delete users in bulk?Knowledge Drop

Last tested: Apr 5, 2019 There is currently no way to delete users in bulk via the UI, and we do recommend that users be disabled rather than deleted.However It is possible to delete a list of users by their ID number using the delete_user API endpoint.It is very important to note that this action cannot be undone and any content or schedules owned by these users will be lost permanently! A better alternative is to update users with the update_user endpoint and disable them by setting is_disabled = true Curl API Example with bash to delete user IDs in a range #!/bin/bash startID= #set user ID to start with endID= #set user ID to finish; it will also be deleted clientID= #your client ID secret= #your client secret url= #your looker API URL e.g. https://mycompany.looker.com:19999 token=`curl -d "client_id=$clientID&client_secret=$secret" https://mycompany.looker.com:19999/login | jq '.access_token' | sed 's/\"//g'` # echo $token for ((i=$startID; i<=$endID; i++)) do # echo $i curl

6950
Jiro
Moderator
1 year ago
Jiro
Moderator
JiroModerator
published in Technical Tips & Tricks

How do I resend a schedule that failed?Knowledge Drop

Last tested: Jun 14, 2019 There is no "resend" option (feature request here) so we recommend using the "Send Test" button in the scheduler modal. If using the "Send Test" method, (if admin) sudo as the user that originally made the scheduled job. This will ensure the recipient list remains the same. This content is subject to limited support.                   

5250
Jiro
Moderator
1 year ago
jon.allen
Looker Staff
jon.allenLooker Staff
published in Technical Tips & Tricks

Why does i__looker (or System Activity) show cache runtimes as being the same as the original query's runtime?Knowledge Drop

Last tested: Mar 5, 2020 The History view's "Runtime in Seconds" will always represent the original query's (the query that was cached) runtime. So this is why you're seeing the cache Result Source queries have the same runtime as the query Result Source queries. The only way to really tell how long it takes for a query to return from cache is through logs.This content is subject to limited support.                 

200
jon.allen
Looker Staff
1 year ago
Jiro
Moderator
JiroModerator
published in Technical Tips & Tricks

Workload Management (WLM) does not work with PDTsKnowledge Drop

Last tested: Mar 12, 2021 WLM is a Redshift tool that assigns different queries to different queues to distribute load.Looker does not support using WLM with PDTs.We can also see this with those using Aptible database tunnels.Why?WLM cancels build statements when they’re moved from one queue to another, which means PDTs essentially get killed and restarted on the DB, but Looker thinks they’re killed so it starts another one. Chaos ensues.Specifically, here is what can happen:Looker starts a PDT. WLM kills PDT. WLM restarts PDT. Looker starts a second PDT that is actually the same. WLM PDT finishes. Looker PDT finishes and errors out because WLM PDT already exists. Looker thinks the PDT build failed and returns you an error in the UI.Why do queries switch queues?Most Redshift clusters have WLM enabled with 1 queue. When there is more than 1 queue, queries that come in to the database are assigned to their appropriate queue. When the query gets assigned to the appropriate queue, it is gi

1630
Jiro
Moderator
1 year ago
J
Looker Staff
Judah_HamaouiLooker Staff
published in Technical Tips & Tricks

Can I use FTP instead of SFTP for scheduled deliveries?Knowledge Drop

Last tested: Apr 23, 2018 No, Looker needs SFTP or FTP with supporting of SFTP on the side of Looker's recipient to deliver looks and dashboards.There is a feature request that you can add to with use cases. This content is subject to limited support.                   

400
J
Looker Staff
1 year ago
jonathon
Looker Staff
jonathonLooker Staff
published in Technical Tips & Tricks

Is there a recommended way to schedule the individual result sets of each Look on a dashboard in one excel file?Knowledge Drop

Last tested: Jul 10, 2017 Currently, this isn't possible from the UI. You can download an archive of multiple CSVs!One advanced workaround would be to use the API. The idea would be that you get the result set of all of the Looks, and then do the formatting of the result sets into a single excel file (most likely with a tabular format). This content is subject to limited support.                   

310
jonathon
Looker Staff
1 year ago
jonathon
Looker Staff
jonathonLooker Staff
published in Technical Tips & Tricks

What does the "Looker is having trouble connecting to your database" error mean?Knowledge Drop

Last tested: Nov 18, 2020 Looker's connection pool timeout is the only known cause of this error.Why did this happen?Basically, the following scenario happened:Looker tried to send a new query to a database. This query could have been triggered by a dashboard, Look, explore, SQL Runner, etc. Looker noticed that it had already sent a number of unreturned queries to the database greater than or equal to the "Max Connections" setting in the Connections page. Looker queued the query to give the database a chance to complete the already running queries. - It's really important that Looker does this. Looker protects our databases and warehouses, so that the they don't get overloaded with requests. This ensures that the specific database's or warehouse's resources are shared amongst all of us, without having the need to micro manage them. "Very cool. Thanks Looker!" After the amount of time specified in the "Connection Pool Timeout" setting in the Connections page, Looker cancelled the queued

7970
jonathon
Looker Staff
1 year ago
jonathon
Looker Staff
jonathonLooker Staff
published in Technical Tips & Tricks

What LookML parameters take liquid output and tags?Knowledge Drop

Last tested: Oct 14, 2020 It can work with various parameters as below:A = Works with the action parameterDV = Works with the default_value (for dashboards) parameterDE = Works with the description parameter at the field level, but will not work with description at the Explore levelF = Works with the filters (for dashboard elements) parameterH = Works with the html parameterLA = Works with the label parameters at the field level, including the label parameter, view_label parameter, group_label parameter, and group_item_label parameter, but will not work with label parameters at the model, Explore, view, or reference line level, or with label as a subparameter of linkLI = Works with the link parameterS = Works with all LookML parameters that begin with sql (e.g. sql, sql_on, and sql_table_name)You can reference this doc to determine the allowed usage: https://docs.looker.com/reference/liquid-variables#liquid_variable_definitions This content is subject to limited support.               

360
jonathon
Looker Staff
1 year 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_

10392
Anna_Mariscotti
9 months ago
kevin.dunn
Looker Staff
kevin.dunnLooker Staff
published in Technical Tips & Tricks

How can I dynamically change my dimension timeframe based on the dates being filtered?Knowledge Drop

Last tested: Sep 25, 2019Sometimes its nice to have your dimension_groups timeframe update automatically based on the date range in the filter. For instance, if we are looking at one year of data, show the data by month, If we are looking at a month of data, show it by week, if we are looking at a week, show it by individual date, etc. We can do this utilizing datediff functions and date_start and date_end liquid parameters that reference our dimension group, and then using a case statement to display different timeframes based on how large the range is. We need a standard dimension group, and a second string dimension that selects different timeframes using a case statement:dimension_group: created {type: timehidden: yestimeframes: [raw,time,date,week,month,quarter,year]sql: ${TABLE}.created_at ;;}dimension: dynamic_date {label: "Dynamic Date"type: stringsql:CASEWHEN datediff({% date_end created_date %}, {% date_start created_date %}) < 30 THEN ${created_date}WHEN datediff({% date_

6300
kevin.dunn
Looker Staff
1 year ago
K
Looker Staff
kai.banksLooker Staff
published in Technical Tips & Tricks

New (Looker 7.0+) PDT Build ProcessKnowledge Drop

Last tested: Dec 17, 2019 IntroAs of Looker 7.0, the PDT build/rebuild process has changed significantly. The intent behind these changes is to harden the build pipeline and prevent some of the harder-to-diagnose PDT build failures.ProcessThe old drop/rename procedure:A build request for a PDT comes in (sql_trigger value changes, a user requests a rebuild derived tables & run, the persist_for expires, etc). The PDT manager builds the new version of the PDT with a temporary name. Once the new PDT has built successfully, the PDT manager drops the old PDT and renames the new PDT to the old PDT's name.The new unique-name procedure:A build request for a PDT comes in The PDT manager builds the new version of the PDT with an incremented table name Once the new PDT has built successfully, the PDT manager points Looker to use this new version of the PDT (via changes to the active_derived_table table in the internal database).Why?PDTs can fail to rebuild for a variety of reasons, most of whi

1210
K
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Where do we save Look and Dashboard inside the server?Knowledge Drop

Last tested: Jun 25, 2020Looker objects (view file, model file, LookML dashboard files, etc) are inside each model’s project in the server. For Looks and User-Defined Dashboard (UDD), Looker saves the metadata in the internal database, and then we display them in the front end. You can use System Activity to query information from the internal database. This content is subject to limited support.                  

580
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Do we store users' embed secret key?Knowledge Drop

Last tested: Oct 21, 2019 Scenario: Users come to chat support to ask if we could give them their embed secret key. Someone in their team reset the embed secret key and now they are unable to see the key due to security reason.Answer: We can not give anyone their embed secret key. Even if it were not against policy, we store the key as a hash in the database, so it's impossible for any human to read and decode the hash and get the key as a clear text. An admin can reset the embed secret key. This content is subject to limited support.                   

140
Lan
Looker Staff
1 year ago
kevin.dunn
Looker Staff
kevin.dunnLooker Staff
published in Technical Tips & Tricks

What is an 'ephemeral' session credential?Knowledge Drop

Last tested: Feb 5, 2021When chromium needs to render content, it is given a one-time 'ephemeral' login, and that is what these session credentials are from. This content is subject to limited support.                  

290
kevin.dunn
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Can I reference a PDT in sql_trigger_value/sql_trigger?Knowledge Drop

Last tested: May 26, 2020 A PDT can be referenced in sql_trigger_value using $view_name.SQL_TABLE_NAME} syntax. This is not supported for sql_trigger . This content is subject to limited support.                  

180
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Pending File Branch pop up - GitKnowledge Drop

Last tested: Jan 14, 2021 That message indicates that you need to save and commit your changes before switching branches if you haven't done yet."Pending Files Branch": {"value": "Please save and commit your changes before creating or switching branches." } This content is subject to limited support.                  

170
Lan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

Cannot use non-literal string filter for parameter fieldKnowledge Drop

Last tested: May 19, 2020 Let's say you have something like this in your LookML:explore: order_items {always_filter: {filters: [parameter_field: "Testing_this"]}} We will get an error in the LookML validator because Looker is interpreting the string in the filters expression as the matches(advanced) filter expression syntax. The underscore _ is saying that we should returning anything that begins with Testing and ends with this. This won't work because the parameter can only have one value.To bypass this problem, we can escape the underscore like “Testing^_this” to get it to read as a literal string instead of filter expression syntax. This content is subject to limited support.                  

6460
mandy.chan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

"no such explore" errorKnowledge Drop

Last tested: Dec 1, 2020 If you're seeing a "no such explore" <explore name> error, this usually occurs because we're referencing an Explore in a file, but haven't included it.For instance, if we're creating a NDT, we'll need to use explore_source to base the NDT off of an Explore, and include the file containing the Explore definition. This content is subject to limited support.                   

720
mandy.chan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

What do the 5 digits mean when you run the sql for once per day at a given hour?Knowledge Drop

Last tested: Mar 19, 2021 In our docs here, https://docs.looker.com/reference/view-params/sql_trigger_value , we give you sql for how you can run a schedule once per day at any given hour.If you run that sql in SQL runner or any database console, you'll see that it returns 5 digits.Those 5 digits are not unix time. Those 5 digits are the number of times that timeframe (X hour) has been passed since the start of unix time. For instance, if you get 18705 returned, it means 18705 days have passed since 1970-01-01 This content is subject to limited support.                  

100
mandy.chan
Looker Staff
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.                      

2634
margaret.abercrombie
Looker Staff
10 months ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

`Create cancelled error` in PDT Log ActionKnowledge Drop

Last tested: Apr 7, 2020 The Create cancelled error in the PDT Log Action field means that the query used to create the derived table was killed by the database for a variety of reasons. Here are some possibilities:The query was canceled by the user There was a time out on the database side There was an error in the SQL while trying to create the PDT This content is subject to limited support.                 

890
mandy.chan
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