Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Looker Connect Training

Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
The Kitchen Table
Technical Tips & Tricks

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

1,744 Topics
406 Replies

When you subscribe we will email you when there is a new topic in this category

1744 Topics

Newest first

Recently active Most replies Most views

samLooker Staff

published in Technical Tips & Tricks

Git Lock ErrorsKnowledge Drop

Last tested: March 2021 If you get an error with a git operation that mentions a lock, there is probably a lock file.These files are usually temporary and will go away if you wait a sec and try the operation again.If you have command line access to the git repository, you can run git gc to figure out where the lock file is - you may see something like:fatal: Unable to create '/mnt/efs/models/<project-name>/.git/refs/heads/master.lock': File exists.If no other git process is currently running, this probably means a git process crashed in this repository earlier. Make sure no other git process is running and remove the file manually to continue. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

How do I visualize a dimension as a measure?Knowledge Drop

Last tested: March 2021 You may be wondering how to plot a dimension on the y-axis of a visualization.Quick SolutionThe quick way to do this is with a table calc involving an existing measure.${dimension} + ( ${measure} * 0)A similar approach can be taken with this table calc for a String dimensionif(is_null(measure),string_dimension,string_dimension)More details on this method in the Help Center article here. Proper SolutionThe right way to do this is with an aggregate in LookML.measure: sum_dimension {type: sumsql: ${dimension} ;;}This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

How do I fix REJECTED_NONFASTFORWARD errors?Knowledge Drop

Last verified: March 2021 This can happen when your branch on the remote is ahead of your local branch and you go to deploy.Solution 1: Shift click the git drop down and hit "pull remote changes".Note that this will overwrite any local changes (anything you've saved without committing) since your last commit.Solution 2: If pulling from remote doesn't work due to a conflict with files, and it's a relatively small amount of files causing a conflict, save them externally, deleted them from the project, and then try pulling remote changes again. Then you can add the files back by dragging and dropping them into the project. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

Will "Clear Cache and Refresh" affect filter suggestions?Knowledge Drop

Last tested: March 2021 No. The suggestions have their own cache that cannot be manually cleared from the explore page. If you need to clear cache for suggestions, here are some other options: If the explore is cached using a datagroup with a sql_trigger, you can manually reset the cache for the entire datagroup in the Admin - Datagroups panel. Note this will bust cache for all queries persisted using that datagroup. You can set suggest_persist_for to "0 seconds" to bust filter suggestion cache for that field. The docs have more info on using suggest_persist_for to set the cache expiration time. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

Can I drill down on a drill down?Knowledge Drop

Last tested: March 2021 Yep! As long as the fields have drill_fields defined, you can continue drilling on the drill modal to infinity and beyond (the drill modal will reload upon each consecutive drill). This content is subject to limited support.

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.

samLooker Staff

published in Technical Tips & Tricks

Can I remove sorts (order by) from the SQL?Knowledge Drop

Last tested: June 2020 This is not an option by default in the UI. There are some workarounds though: Workaround 1 (Downloads): There is a checkbox to remove sorting in the download modal.Workaround 2 (null dimension): This dimension will generate ORDER BY NULL which will help to optimize performance.dimension: no_orderby { type: number sql: null ;; }Workaround 3 (API): Using either the create_query, run_inline_query or create_query_task endpoints, pass in ["__UNSORTED__"] for the `sorts` parameter. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

Does clearing cache clear the drill down cache?Knowledge Drop

Last tested: March 2021 No. When you clear cache, it only clears cache for the current query, not any cached results for the drill down.Workarounds The recommended workaround is to have a Datagroup set up on the explore, so that you're not relying on manual cache clearing. If you need to do this manually, then you must "explore from here" on the drill down and then "clear cache and refresh" on that explore. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

When would my Remote Origin URL start with `ssh://`?Knowledge Drop

Last tested: March 2021 When you're using a non-standard Git port. From git docs:URLs with custom or non-standard git ports can be used with Looker by adding the non-standard port number to the git URL. For example: ssh://git@corporate.git.server.com:22/myorganization/myproject.gitPlease note that you must include the ssh:// for this to work properly. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

SQL Error: "X is not a valid group by expression"Knowledge Drop

Last tested: March 2021 This SQL error means that that database is trying to group on something that it can't. Usually, this means that there are aggregates in a dimension definition.To troubleshoot, first try to isolate the field that throws the error. Then take a look at its LookML definition. One example of aggregation inside dimensions that could cause this is:dimension: bad_dimension {type: numbersql: SUM(${other_dimension}) ;;}The solution is to only do aggregations in measures. This example should instead be a measure:measure: sum_measure {type: sumsql: ${other_dimension} ;;}If you need aggregation to happen in a dimension, so that you can pivot or group by it for example, then see dimensionalizing a measure. This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

Using liquid 'split' to iterate (loop) over a stringKnowledge Drop

Last tested: May 2020 This comes in handy when you has a comma-separated list and you want to format it or apply links to each comma-separated value.For example:List apple, banana orange, apple, banana Since the "list" fields can have either 2 values or 3, we can easily use the split function to format the list with bullet points regardless of how many values there are.dimension: list { type: string sql: ${TABLE}.list ;; html: {% assign words = {{value}} | split: ',' %} <ul> {% for word in words %} <li> {{ word }} </li> {% endfor %} ;; }This generates a bullet list on our original comma-separated list.Link to Shopify Documentation on split This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

Content Validator Quick FactsKnowledge Drop

Last tested: May 2020 The content validator checks to see which of your Looks and dashboard tiles are broken by LookML changes.DOES Replace models, explores, views, fields, fields used in table calculations and custom fields Remove models, explores, views and fields Check lookless tiles (Looker 5.10+) Validates against the mode you're in (dev : active branch or prod) however changes are applied globally Give the ability to delete broken looks/tiles Check personal spaces Check look filters Check dashboard filters (Looker 6.10+) Allow calling through the API (https://docs.looker.com/reference/api-and-integration/api-reference/v3.1/content#validate_content) DOES NOT Remove fields used in table calculations and custom fields Check LookML dashboards (LookML validator does this) Check a subset of content Check Trash (Looker 4.12+) Check content you don't have access to Time out after a certain period of time. (However, restarting the Looker instance does kill the C

samLooker Staff

published in Technical Tips & Tricks

Why is my measure of type:count being made into a COUNT DISTINCT in the SQL?Knowledge Drop

Last tested: May 2020 A count measure usually creates a COUNT(*) in the SQL. However, if joins are involved, then a COUNT(*) would cause a fanout.So to avoid this, Looker generates a COUNT DISTINCT on the view's primary key instead. This should give you the same results as a COUNT(*) on that table with no joins. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How can I make one column have a conditional format style based on another column's value?Knowledge Drop

Last Tested: Jan 21, 2019 This is possible using Liquid in the html: of a given LookML field. The example is not exactly highlighted in this community article, but it can help for a reference point. For example:Articlehtml:{% if value == 'Shipped' or value == 'Complete' %} Using Liquid _valuehtml:{% if value == 'Shipped' or orders.state._value == 'California' %} Where _value is a Liquid variable that lets you refer to other values in the same row. See this page for more details. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I choose the value for comparison in a Single Value Visualization?Knowledge Drop

Last Tested: Jul 3, 2018 Not by default, but we can use the offset function to adjust what value is shown. For example:#comparison table calculation columnoffset(${column_name},-2) You can adjust the "-2" as either a positive or negative value to customize the value. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Are merge results cached?Knowledge Drop

Last Tested: Dec 6, 2018 As of 5.12 there is an option to Clear Cache and Refresh on the merged results page. Before 5.12, you had to click "Explore From Here" for each query and "Clear Cache & Refresh" each explore individually. Re-run the merge results and your data will reflect the refresh. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

If we don't specify a `max_cache_age`on a datagroup, will it default to the 1 hour?Knowledge Drop

Last Tested: Jan 21, 2019 According to the datagroup docs, the cached results will be busted the next time the sql_trigger_value changes. So, no, the cached results will not expire in one hour.The cache age will, however, be affected by explore-level or model-level max_cache_age or persist_for values. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

What's the difference between `add to dashboard` and `save to dashboard` options when creating a tile from a look?Knowledge Drop

Last Tested: Mar 15, 2021 Add to dashboard adds the look to the dashboard. These are therefore linked, and any changes to the look will update this look-based tile in the dashboard, and changes to the tile will update the look too.Save to dashboard adds the looks explore query (i.e. lookless tile) to the dashboard. Changes to the look, or to the tile in the dashboards will not effect each the other in this case.https://docs.looker.com/dashboards/creating-dashboards-beta#adding_query_or_look-linked_tiles_from_a_look This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

My LookML Validator is slow!Knowledge Drop

Last tested: Jun 25, 2020 TL;DR:The bigger the project is, the longer it takes to validate. Quick wins:Be strategic about your "includes" Always including every other view may be easy for development but being strategic by using naming conventions to only include the necessary files will increase performance. Comment or delete unused explores (and joins, fields, etc) Consider splitting your project into multiple projectsCheck out this article for LookML best practices. Long answer:LookML project size (total number of lines) and unnecessary includes both affect the performance of the LookML validator.Validation is a two-step process; 1) the first pass of validation happens by loading every single file in the project, even the ones not referenced anywhere, into memory and does a full translation of the source code one model at a time looking for "compile" errors. 2) the second pass of validation starts at each model and then parses only the files referenced by that model. And then it bu

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How to create a running/rolling anything function (sum, mean...) down a column or across a pivot rowKnowledge Drop

Last tested: Jan 21, 2019 You can do this using a custom table calculation formula.Down a column:offset_list(${view_name.field_name},-1*row()+1,row()) Across a pivot row:pivot_offset_list(${view_name.field_name},-1*pivot_column()+1,pivot_column()) Just wrap your aggregation function around one of these formulas, and you're good to go!Examples:Running standard deviation down a column (yes, you can use this with any aggregation function available for table calculations!)stddev_samp(offset_list(${view_name.field_name},-1*row()+1,row())) Running total across a pivoted row:sum(pivot_offset_list(${order_items.count},-1*pivot_column()+1,pivot_column())) This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why is my percent or average measure showing zero (or rounding to an integer) aka where are my decimals?Knowledge Drop

Last tested: May 1, 2020 Here's a Help Center article to explain this question.ExplanationIf you divide two integers in SQL, you get an integer. Thus 1/2 = 0.WorkaroundThe workaround is to cast one integer to a float. An easy way to do that is to multiply by 1.0.It is very important that the multiplying by 1.0 happens before the division operation.Working examples:1.0 * ${count} / ${sum}${count} * 1.0 / ${sum}${count} / ( ${sum} *1.0)What won't work:${count} / ${sum} *1.01.0 * ($[count}/ ${sum})Note for Druid Database: ${count} * 1.0 / ${sum} will not work. Put 1.0 to the denominator instead ---> ${count} / (${sum} *1.0) Redshift Edge CaseDividing by two SUMs in Redshift seems to default to 4 places of precision, as noted in this Stackoverflow article. The workaround is to cast one of them to a higher numeric datatype. ${sum1}::numeric(19,6)/${sum2}This also happens to measures of type:average on Redshift when the field being averaged is an integer.Presto Edge CaseUsing 1.0 * ${vari

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

"Internal Server Error" when savingKnowledge Drop

Last tested: Jun 23, 2020 Please add possible causes as you come across them in the comments!Check for ad blocker! Try incognito mode or a different browser- this will pinpoint extensions as the culprit. Syntax error in LookML also causes this issue sometimes ( such as json format error/ the issue with dimension etc..) This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

What's the longest allowed filter? Is there a limit on characters or values?Knowledge Drop

Last tested: March 2021 In the Looker product/code, there are no maximums on filter values or length. Theoretically, you can enter as many as you want. However, Looker uses Puma, which limits the url characters to 10240 (source: Puma Code).In the browser, a long URL can break the browser. Stackoverflow cites ~2,000 characters as the lowest limit for a browser (IE/Edge). All other browsers support URLs with > 8000 characters. As long as the URL is hashed (like in an explore), this is not a problem, but dashboard filters are simply added to the URL at the time of this writing. These problems can be solved by putting the values into a yesno dimension and filtering on that. An example:dimension: id_matches_value_list { type: yesno sql: ${id} IN (value1, value2, ... );; }In the database, there is usually a limit on query length, which is relevant since the filters usually go into the WHERE clause. A common limit is 2^16 characters (65336). There's no known workaround for this. This conte

samLooker Staff

published in Technical Tips & Tricks

How can I enter multiple filters into Parameters?Knowledge Drop

Last tested: May 2020 Parameters only accept a single value.In the event that you have a comma separated list like a,b,c in your filter, the parameter syntax {% parameter filter_field %} will bring that in as a single string "a,b,c" .Therefore, if you have multiple values, use templated filters instead.Using a filter field rather than a parameter field, you can enter multiple values and then use templated filters to build the proper SQL. For example, using the LookML:{% condition filter_field %} other_field {% endcondition %}while entering a , b , and c into filter_field in the UI, would generate the SQL:other_field IN (a,b,c) This content is subject to limited support.

samLooker Staff

published in Technical Tips & Tricks

Why can't I access chat?Knowledge Drop

Last tested: April 2021 If you don't see the chat option in the Help Menu:Permissions: Anyone with the develop permission can come on chat. This means that admins have this capability too. Check if the user is missing the develop permission.License type: If you have a partner license type you will not have chat access starting April 1st 2021. As for trials, trials which were initiated after Jan 5th 2021 will not have chat feature toggled on. However, the trials that were initiated before that are being turned off in a phased manner.Closed ports: If your Looker instance is customer-hosted, then your network administrator needs to open several ports on your server to connect to the chat client. If you see the chat option in the Help Menu, but clicking on it doesn't bring up the chat widget:Browser extensions: If you do not see the chat option in the help menu, but clicking it does not bring up the chat widget, check in incognito mode. Often times we've seen extensions such as Ghostery ca

1
...
65
66
67
68
69
70

Page 68 / 70

Badges

sbrylowskihas earned the badge Friendly Face
carriehas earned the badge Helpful
carriehas earned the badge Community Star
carriehas earned the badge Friendly Face
Dawidhas earned the badge Community Star

Show all badges

Powered by inSided

Terms of Service

Sign up

Already have an account? Login

Social Login

or

Username *

E-mail address *

Company

(Private)

Only you and moderators can see this information

*

Password *

I accept the Terms of Service.

If you’re an EEA-based consumer of digital content, services, or goods, and you’ve agreed to our Terms of Service, then EEA consumer laws provide you with a legal guarantee. Under this guarantee, we’re liable for any lack of conformity that you discover for the one-time supply of digital content or services at any time during the “continuous” supply of digital content or services.
Your national laws may provide an even longer guarantee. Your rights under these legal guarantees aren’t limited by any other commercial guarantees that we provide. If you want to make a guarantee claim or if you would like to delete your account data, please contact us lookerconnect-help@google.com.

loginBox.register.email_repeat

Login to the community

No account yet? Create an account

Social Login

or

Username or Email

Password

Remember me

Forgot password?

Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.

Username or e-mail

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

OK

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.

OK

© 2022 Google. All rights reserved.
Privacy | Terms | Community Guidelines