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
  • 402 Replies

1744 Topics

ryland.wolff
Looker Staff
ryland.wolffLooker Staff
published in Technical Tips & Tricks

Why isn't my measure plotting with a built-in map layer?Knowledge Drop

Last tested: Mar 10, 2020 Does your map look like this:It looks like we have a map_layer properly defined. And we can even see our values populated in the legend of the visualization, but nothing populating for the states. The LookML looks legit like this:dimension: state {type: stringsql: ${TABLE}.state;;map_layer_name: us_states} If we see this behavior, we should double check for whitespace in the underlying field. We can do this in the Explore by adding an `ends with` filter like the following:Since we still have results, we know we have trailing whitespace in the State field which is preventing us from mapping the State values to the map_layer property_key.The solution is to make a change in the underlying database or wrap the field in a TRIM() function like so:dimension: state {type: stringsql:TRIM(${TABLE}.state);;map_layer_name: us_states} Then we should see these values plot as expectedAnother case is for when we are filtering on the information that does match with Full count

790
ryland.wolff
Looker Staff
1 year ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

"Uh oh. Something went wrong while saving the scheduled delivery" error when saving a schedule, 422 in the JS consoleKnowledge Drop

Last tested: Nov 23, 2020 This often comes up on dashboards-next. This error will come up when some part of the validation of the schedule failed. This could be a schedule that was configured to do something the user didn't have permission to do, or send in a format not allowed.If you see this error open up the dev tools network tab, click on the erroring request with the 422 response, and click on the "Preview" tab. This will hopefully have an error message for what went wrong, which might lead you to a solution. This content is subject to limited support.                  

1060
ryan.dunlavy
Looker Staff
1 year ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

How can I download metadata about a visualizations configuration?Knowledge Drop

Last tested: Oct 24, 2018 You can Command+Shift+click on the gear menu for the visualization to download a .json file with the visualization settings metadata for a particular visualization. This content is subject to limited support.                  

280
ryan.dunlavy
Looker Staff
1 year ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

What is the custodian? (7.2+)Knowledge Drop

Last tested: Feb 4, 2020 The custodian in 7.2+ is meant to automatically find schedules that are stuck in a state that they cannot recover from and mark them as complete with an error message. It will not affect any schedules that are actually executing.The custodian runs as a background process every 5 minutes on all nodes and does two things:First, it checks all schedules that are marked as currently executing or delivering in the backend database along with the threads they're marked as running on and checks those threads to make sure those threads current state indicates it's working on that job. Second, it will check for the most recent jobs that started executing over an hour ago, and look for any jobs still in the queue waiting to execute that should have started before that one. If there are ones that are stuck in the queue, it will finalize those with an error message. This content is subject to limited support.                  

20
ryan.dunlavy
Looker Staff
1 year ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

Rendering error: session.command.page.cap…gQuality, viewPort, true) must not be nullKnowledge Drop

Last tested: Mar 18, 2020 This error will happen if Looker loses communication with Chromium while it's rendering a PNG. The most common cause of this is Chromium not having enough resources to run, and can be especially bad in containerized deployments of Looker that don't have enough memory or shared memory -- specifically not enough allocated to /dev/shm.The error message will look slightly different if a PDF is being rendered: session.command.page.cap…gQuality, viewPort, true) must not be null This content is subject to limited support.                  

70
ryan.dunlavy
Looker Staff
1 year ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

Render error: Page not loaded within 300000 msKnowledge Drop

Last tested: Apr 10, 2020 This error generally means that Looker tried to have the Chromium process load a page in order to capture its contents in a PDF or PNG, but the page did not fire the DOMContentLoaded event within 5 minutes. This most commonly happens when loading the actual content (dashboard, look, etc)Other than unexpected issues, the main culprit of this in the past has been certain outbound requests being black holed, so they hang indefinitely while the page is trying to load until this timeout is hit. Looker will use localhost for requests*, but it’ll also make requests to some external servers (e.g. ping.looker.com, google-analytics.com, etc).If you are an on premise user, check if you are allowing all outbound traffic. If not, you can load the dashboard, look or explore you're trying to render in the UI and look at the network tab in the Chrome dev tools. You should try to curl every URL that isn't your host URL (so in the example below, everything that doesn't start wi

770
ryan.dunlavy
Looker Staff
1 year ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

What causes an "undefined method `getURLs' for #" error when starting LookerKnowledge Drop

Last tested:  This is caused by trying to run Looker version 7.14 or earlier with a newer Java version that isn't supported yet. Make sure the jar is Looker version 7.16+ you're using Java 9+, or downgrade Java to JDK 8https://docs.looker.com/setup-and-management/on-prem-install/installation?version=7.16Example full errorNoMethodError: undefined method `getURLs' for #<Java::JdkInternalLoader::ClassLoaders::AppClassLoader:0x58cda03f>Did you mean? getClass  This content is subject to limited support.                  

560
ryan.dunlavy
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

How do I set the visualization type of a query in the API?Knowledge Drop

Last tested: Jun 20, 2019 When using the API for endpoints such as "run_inline_query" and "create_query" we can set the visualization type of the returned visualization using the "vis_config" parameter.The documentation is very vague for this field:"Visualization configuration properties. These properties are typically opaque and differ based on the type of visualization used. There is no specified set of allowed keys. The values can be any type supported by JSON. A "type" key with a string value is often present, and is used by Looker to determine which visualization to present. Visualizations ignore unknown vis_config properties."However, when using "column" or "bar" for the type value, the result is always a table. What is going on??This is due to the last line in the description. Values "column" and "bar" are invalid for the type field so those values are ignored the vis defaults to a table type. Looker-defined visualizations begin with "looker_", so for example "looker_bar" or "lo

2330
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

Can I only publish one database view even if the view is in multiple models?Knowledge Drop

Last tested: Jun 22, 2020 Yep, using refinements!Instead of applying "publish_as_db_view: yes" in the view itself, instead we will use refinements on the one model we want to build it for. There we refine it like so: view: +pdt_name {derived table: {publish_as_deb_view: yes}} That way, the publish parameter is only applied inside the one model instead of for each model the table is included in This content is subject to limited support.                  

180
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

Nested Pivots and Table CalculationsKnowledge Drop

Last tested: Apr 27, 2020 If you have nested pivots, this can make pivot_where/pivot_index very complex to use. You would think you would need to nest the calls to pivot_where/pivot_index within each other. But no, that would make too much sense.Instead, we need to use AND logic. So for example, look at this explore:This gets the nested pivot column where the status is complete for the 4th quarter:pivot_where(${orders.status} = "complete" AND to_string(${orders.created_quarter}) = "2019-10", ${orders.count}) This content is subject to limited support.                  

3320
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

Why do the logs stop after connecting to the internal db during startup? (Aurora internal db)Knowledge Drop

Last tested: Aug 26, 2020 If you see logs like this that just stop immediately after when starting up:2020-08-26 15:15:44.974 +0000 [INFO|007e6|main] :: Starting Looker in clustered environment: Version 7.10.31-fb654d2020-08-26 15:16:07.300 +0000 [INFO|007e6|db:*] :: Initializing database adapter for mysql2020-08-26 15:16:07.308 +0000 [INFO|007e6|db.connection] :: Extended looker connection with validation timeout of 3600 seconds2020-08-26 15:16:07.312 +0000 [INFO|007e6|db.connection] :: Created pool for name: looker max connections: 72 pools for this connection: 1This means the customer is using Aurora for their internal database. Upgrading from version 2.06 to 2.07 causes this issue to occur.Aurora is NOT supported as an internal database, so the solution is to switch to a standard MySQL internal db. Docs that call out what is supported here: https://docs.looker.com/setup-and-management/tutorials/clustering#mysql_application_databaseAs of August 31, 2020, Amazon updated all Aurora in

90
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

Cascading PDTs w/Different Datagroups on Each Dependency (last pdt wait for others to build first)Knowledge Drop

Last tested: Oct 30, 2020 This is pretty jank. Optimally, you should instead set the child PDTs to use the same caching policy, that way Looker handles cascading pdts internally for you, like in this article: https://help.looker.com/hc/en-us/articles/360023904433-Using-datagroups-with-Cascading-PDTsHowever, this won't work if you only want the final PDT to build after all dependencies have updated, and those tables all build based on different triggers. In that case, keep reading:HOW IT WORKSWe are going to manually create a table in their scratch schema to track the updates of each table. Create a table with a date field and X booleans, one for each dependency PDT (in this example code, X = 3). Upon creation, also add in a row with the current time and false for all the other values.From there, change all the PDTS to use create_process. The first sql_step should create the table as normal, then the second one we are going to need to update the update_table like so:insert into update_t

2480
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

How do I set a filter default to filter for all results?Knowledge Drop

Last tested: Oct 28, 2019 In the default value option in the dashboard filter modal, select "is equal to" and then leave the next box blank. This will save to the dashboard as "is any value". This content is subject to limited support.                  

3200
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

What Does "Net::SFTP::StatusException (4, "failure")" Error Mean?Knowledge Drop

Last tested: Apr 20, 2020 This is a generic error thrown by an SFTP server, it is very similar to the "RenderNoRender" error in that it's a catch-all rather than a specific error.There are a few different things that can throw this, this article goes over some common causes to check: https://winscp.net/eng/docs/sftp_codes#code_4Special considerations for Files.com SFTP ServersFor whatever reason, this is files.com's "access denied" and "path does not exist" error as well. So if they are using files.com, first check the path is correct and that the user in question has write permission to the destination folder. Note that with files.com, the path is relative to the default directory set for the user, so be sure to check that as well. This content is subject to limited support.                  

11900
ryan.bouquet
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

User doesn't have bigquery.jobs.get permissionKnowledge Drop

Last tested: Jan 7, 2020 The ProblemUser doesn't have bigquery.jobs.get permission for writing to the bigquery scratch schema.You can check if it is permission based by having them add the Admin role in big query. If this fixes their issue then try the following solution:A SolutionIn the doc for Google BigQuery it give instructions for adding for the BQ user the roles:BigQuery > BigQuery Job User BigQuery > BigQuery Data EditorIt mentions that custom roles aren't supported but if you are getting the error above try have them create a custom role to add to this user with permission bigquery.jobs.getAnother issue it could be if they have access to some PDT's but not others is that non-admin BQ users can only run jobs.get on jobs that they created themselves This content is subject to limited support.                  

2790
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

How to list all the views used in an explore in system activity?Knowledge Drop

Last tested: Jun 9, 2020 Use this link changing the domain name to your instance one.There are a few caveats though, if you have fields: [] then it won't list this view since there are no fields to list. [DOMAIN_NAME]/explore/system__activity/field_usage?fields=view_name_list,field_usage.explore&pivots=field_usage.explore&f[view_name]=-EMPTY&sorts=field_usage.explore,calculation_2+desc+0&limit=500&query_timezone=Europe%2FDublin&vis=%7B%7D&filter_config=%7B%22view_name%22%3A%5B%7B%22type%22%3A%22%21empty%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%7B%22dimension%22%3A%22view_name%22%2C%22label%22%3A%22View+name%22%2C%22expression%22%3A%22+substring%28%24%7Bfield_usage.field%7D%2C+1%2Cposition%28%24%7Bfield_usage.field%7D%2C%5C%22.%5C%22%29-1%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22dimension%22%2C%22_type_hint%22%3A%22str

2730
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

Does resetting my git connection cause PDTs to rebuild?Knowledge Drop

Last tested: Sep 20, 2019 No, this doesn't cause PDTs to rebuild. :thumbsup: This content is subject to limited support.                  

40
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

SSO Embed locale/ localisationKnowledge Drop

Last tested: Dec 10, 2020 You can set an embed users locale by adding it to the user_atttributes parameter of the url. Example;user_attributes { "locale" : "fr_FR" }If you are not seeing these being applied for an embed user;Make sure that you are using the correct and full local name from this doc https://docs.looker.com/admin-options/tutorials/localizing-looker . For example if you use "de" instead of "de_DE" for german this wont translate yourcontent. You can check what locale has been set for an embed user also by sudoing as them, and clicking on the account page from the top right of the instance.​​Just CTRL+F for "locale" and you will see the current setting for that user. This might be coming from the instances locale in the Admin> Localisation page, or from the SSO embed link itself.​ This content is subject to limited support.                  

1510
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

Pound Name Value Format £Knowledge Drop

Last tested: Aug 20, 2019 This format will apply a pound format to values without decimals where name_value_format is an option: [$£-en-GB]#,##0 Result:This following format will give you the decimals up to the number of zeros you add after the decimal, this example will give up to two decimal places: [$£-en-GB]#,##0.00 Result:If you want to show in millions with and "M" suffixed at the end you will need to add two commas after the 0 values you have used. Every comma will bring the decimal place up 3 points so we need two for a million, but you could use one for thousands:[$£-en-GB]#,##0.00,,"M" Result:I used excel to find this value for £. If you create a tile with a number in it> click format cells> choose the format you want from the options they have > instead of clicking okay click custom on the left and then copy the format that shows there. This content is subject to limited support.                  

7130
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

Cannot use native derived table "NDT_name" with "bind_all_filters" outside of its source explore "source_explore_name"Knowledge Drop

Last tested: Sep 22, 2020 The ProblemI have an NDT using bind_all_filters but get error:Cannot use native derived table "NDT_name" with "bind_all_filters" outside of its source explore "source_explore_name" This might be that:You have joined/explored view "NDT_name" without joining the view "source_explore_name" in too. An NDT with bind_all_filters: yes must be joined into the same Explore that is specified in the NDT’s explore_source parameter. If you want to use the NDT in a different Explore, use the bind_filters parameter instead https://docs.looker.com/reference/view-params/explore_source#bind_all_filters_example You are extending the explore with the NDT in it which we currently can't do: This content is subject to limited support.                  

2720
ruth.mcelroy
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Get the second max value with table calcKnowledge Drop

Last tested: Jun 1, 2018 We can use the approach below to exclude the max value using and if() then applying a max on the remaining valuesmax(if(${orders.count}!=max(${orders.count}), ${orders.count}, null)) (and so on for 3rd max...) For pivoted results:max(pivot_row(if(pivot_index(${orders.count},pivot_column())!=max(pivot_row(${orders.count})), ${orders.count}, null))) This content is subject to limited support.                  

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

Quick Start card truncation rulesKnowledge Drop

Last tested: Nov 3, 2020 In Looker v7.18 Explores have added the option to display Quick Start queries, which allow the user to click on these to load pre-defined queries on the explore to get started in there analysis faster.As these Quick Cards can populate the Explore space, they are displayed in card format. These cards contain various items, title and description for example.The rules for truncating the content in these Quick Start cards is as follows:Tool tip only displays if the description needs to be truncated Tile height is determined by the tallest tile (all other tiles inherit this height)Extra rules: All tiles have a maximum and minimum pixel height. Minimum is 115px tall. Maximum is 135px Tall. All tiles have a width of 160px.If you have a title with 1 line, the description can be max 4 lines If you have a title with 2 lines, the description can be a max of 3 lines If you have a title with 3 lines, the description can be max 2 lines If you have a title with 4 lines, the d

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

HTML adding up and down arrow to value to cells on table vizKnowledge Drop

Last tested: Jul 30, 2018 dimension: random_value {label: "random value with html pointers"sql: rand() ;;html:{% if value > 0.5 %}{% assign indicator = "green,▲" | split: ',' %}{% elsif value < 0.5 %}{% assign indicator = "red,▼" | split: ',' %}{% else %}{% assign indicator = "black,▬"] | split: ',' %}{% endif %}<font color="{{indicator[0]}}">{% if value == 99999.12345 %} &infin{% else %}{{rendered_value}}{% endif %} {{indicator[1]}}</font> ;;} This content is subject to limited support.                  

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

How do I dynamically get the max index for a pivot?Knowledge Drop

Last tested: Sep 6, 2018 pivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))  This content is subject to limited support.                   

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

How do I show integer % value on pie chartKnowledge Drop

Last tested: Apr 11, 2019 The percentage gets calculated automatically based on the values in the Data section. Instead, if you change the label type to "Value", then the labels will be exactly what is shown in the data table. Steps:Hide the measure from the vis make a table calc that calculates the percentage${measure}/${measure:total} apply the Percent(0) formatting (or desired formatting) in the chart option change the 'Label Type' to 'Value' This content is subject to limited support.                  

6020
romain.ducarrouge
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