Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Error: Every derived table must have its own aliasKnowledge Drop

Last tested: Aug 17, 2020 This is a MySQL error that gets triggered if two PDTs are named the same.If you've checked no PDTs share a name, check the dt sql for missing parentheses/brackets - not closing the parentheses can also throw this error. This content is subject to limited support.                  

450
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Min/max date using table calcsKnowledge Drop

Last tested: Mar 29, 2021 How can I get min/max date of a date range using table calcs? If you include the date field in the results, and sort desc:Use this to get the max date: index(${date_field},1)Use this to get the min date: index(${date_field}, max(row())) This content is subject to limited support.                  

1960
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Dashboard limits results on tile when row limit isn't setKnowledge Drop

Last Tested: Nov 12, 2019 If you leave the row limit blank in an explore/look, and add the query/look to a dashboard, the dashboards adds a row limit of 1000 rows. So if you have a table calc that depends on going through more than 1000 rows, you need to add in a row limit rather than leave it blank.The workaround is to use row limit 5000 instead of a blank row limit. This content is subject to limited support.                   

4510
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

System Activity Cache fieldsKnowledge Drop

Last tested: Feb 6, 2020 The System Activity history history table fields about cache require a bit of background to be understood and used effectively. The following will help clear up what the fields mean.Background:In general, when a query is run, Looker checks render cache first, then results cache (that's because render cache is quicker to check, because we don't need to generate the sql to create the render key - to create the results cache key, the sql needs to be generated). More details about render cache vs results cache here.About the Cache (Yes/No) field from the System Activity history explore: If the result is "Yes", that means that Looker *tried* to check cache - so even if we didn't pull the results from cache, the field can still be "Yes". If the result is "No", that means the query was requested to skip checking cache - for example, the result would be "No" if the query came from clear cache and refresh, an instant dashboard reload (after a stale result was shown), or

2230
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Value format for billionsKnowledge Drop

Last tested: Mar 30, 2021 To get a value as a number in billions with 3 decimals (1.234 B):value_format: "0.000,,,\"B\"" This content is subject to limited support.                  

4110
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Why do we truncate internal db tables?Knowledge Drop

Last tested: Jan 22, 2021 Since new rows are constantly being added to tables in the internal db, it’s no surprise that the tables can grow to be unmanageably large quite quickly. Since the the internal db tables are also constantly being queried, it benefits us to keep the tables at a manageable size in order to maintain optimal performance. That’s why we prune some tables accessible via system activity. Certain tables are pruned based on the number of rows in the table, while other tables are pruned based on the number of historical days that the data represents. This content is subject to limited support.                  

170
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Startup failed. Unable to load java dependencies.Knowledge Drop

Last tested: Sep 21, 2020 A self-hosted user may run into this error upon restarting/updating your instance.If the error is prepended by WARN: there must be at least 750MB of free space in the temp directory, then you probably don't have enough free space. Go and run df -h as the Looker user to see where you may need to free up space.If the error doesn't have that associated free space warning, here are some things to have checked:Do you have the appropriate java version (java -version) Do you have downloaded both jar files (core and dependencies) following the exact instructions (public-facing in docs) Both jars are in the same directory (they should be) The shell script is being run from that same directory (it should be) Check for any typos This content is subject to limited support.                  

800
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Failed to send mail: end of file reached for office365Knowledge Drop

Last tested: Jan 31, 2020 If you're following these steps, and running into a "Failed to send mail: end of file reached" error, you are probably using smtp.office365.com as your Mail Server rather than your specific MX server.To find the MX server for your domain: type nslookup, then set type=mx, then enter your domain name. For example, if your domain name is lookertest.com:$ nslookup> set type=mx> lookertest.comServer: 8.8.4.4Address: 8.8.4.4#53Non-authoritative answer:lookertest.com mail exchanger = 0 lookertest-com.mail.protection.outlook.com. Authoritative answers can be found from:> exit  From this example the MX server is "lookertest-com.mail.protection.outlook.com".You can then enter this as the Mail Server, and the error should resolve. This content is subject to limited support.                  

1590
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Conditional formatting for line chart?Knowledge Drop

Last tested: Jul 22, 2020 There is no way to do this natively, but we can use table calculations as a workaround!Steps:Use if() in a table calc to create the "break" in the line by casting values after a certain point as null. Then create second table calc to cast values before that point as null Hide the original measure from the visualization Toggle off "plot null values" in the visualization settings Edit the series colors + types however you want :)Some examples: This content is subject to limited support.                  

2860
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Error: The Server encountered an error processing result dataKnowledge Drop

Last tested: Aug 17, 2020 If you run into this error when using sql runner, check to see whether one of the columns you are querying is of type: binary. Sql runner can't query binary data, so to get the columns through we can cast as varchar or string.You can vote on this feature request: Use SQL Runner with Binary Type Data This content is subject to limited support.                  

940
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

I host my own instance - can I update to a specific patch (not just the latest)?Knowledge Drop

Last tested: Apr 2, 2020No, you can only update to the latest patch in a given minor release. This content is subject to limited support.                  

120
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Why can't I set a column limit when pivoting?Knowledge Drop

Last tested: Aug 11, 2020 Some dialects pivot with front-end work - Looker can only apply column limits when the pivoting is accomplished in the SQL query.For example, Denodo pivots in the front end, so we can't apply a column limit to explores that are built off a Denodo connection. This content is subject to limited support.                  

390
sarah.tayeri
Looker Staff
1 year ago
sarah.tayeri
Looker Staff
sarah.tayeriLooker Staff
published in Technical Tips & Tricks

Is the "join" for merged results case sensitive?Knowledge Drop

Last tested: Mar 19, 2020 Yep! The capitalization will need to match in order for the queries to merge This content is subject to limited support.                  

320
sarah.tayeri
Looker Staff
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How do I reference a filter value in a Liquid if statement?Knowledge Drop

Last tested: Nov 1, 2017 You'll want to use the _filters['field_name'] syntax. Note this does not work in sqlExample:filter: number_filter {type: number} dimension: company_number {html: {% if _filters['number_filter'] == "2" %}{{value}}{% else %}Not 2{% endif %};;sql: ${company} ;;}   This content is subject to limited support.                  

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

Can Looker connect to a database through a jumpbox?Knowledge Drop

Last tested: Mar 30, 2018 Yes! This is just our regular SSH tunnel setup. A jumpbox is another name for the tunnel server in this diagram from docs. This content is subject to limited support.                  

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

Error: Cannot use "_filters" in SQLKnowledge Drop

Last tested: Jul 13, 2018 _filters is not sanitized for SQL. So it can expose customers to SQL injection. Thus it is not supported in any parameter that uses SQL.Try using a parameter or templated filter instead.Or if appropriate, use html: to display it as per this ​post This content is subject to limited support.                  

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

Is there a way to extend the expires_at for the authorization token to be beyond 1 hour?Knowledge Drop

Last tested: Jun 30, 2017 No, there is not a way to extend this beyond 1 hour.Instead, you should code your app to check for expiration of the access_token before each API call. This content is subject to limited support.                  

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

Why would i__looker/System Activity report a null user?Knowledge Drop

Last tested: May 11, 2020 This means that a user isn't tied to the record or process. One example of this is the PDT regenerator process.If you're using the History explore, check the history message, which might tell you more about this "user". This content is subject to limited support.                  

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

How do I know if a user has used Two Factor?Knowledge Drop

Last tested: Sep 12, 2019 Every user is subject to Two Factor. So if 2FA is enabled, then every user will see a 2FA screen when logging in to Looker with an email/password combination.You can see whether or not a user has used 2FA from their User page, which you can get to in Admin > Users.If they don't have a "Two-factor Secret" section with a Reset button next to it, they have never logged in via 2FA. (you can do a find on the user page for `Secret`) If they do have a "Two Factor Secret" section with a Reset button next to it, they have logged in via 2FA at least once. If the Reset button has a green success message next to it, that means the user's 2FA has been reset. They will see a barcode the next time they log in to Looker. This green message persists until they scan that barcode.  This content is subject to limited support.                  

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

Why aren't I seeing the "Configure Git" button on a new project?Knowledge Drop

Last tested: Sep 14, 2020 You need the deploy permission in order to Configure Git for a new project. This content is subject to limited support.                  

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

When would I use the | symbol after the sql parameter in old LookML?Knowledge Drop

Last tested: Feb 20, 2018 The pipe | symbol is only used in old LookML. It tells the validator that multiple lines of code are coming. This has been replaced in New LookML with the ability to use double semicolons to end code blocks like html or SQL. # Example of | used correctly. Without the |, this would error.sql: | CASE WHEN 1=1 THEN 'grand'ELSE 'not as grand'END More examples: https://oldlookmldocs.looker.com/reference/view-params/sql-for-derived_table This content is subject to limited support.                  

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

Why would you see "This project has undeployed changes, but you do not have permission to deploy"?Knowledge Drop

Last tested: Sep 14, 2020 This message usually means that the Looker user lacks the deploy permission on this model.If it's an Admin user with all permissions, this indicates a permission problem on the repo side. Run the git connection test to validate if this is the case. If so, try resetting the Git connection and making sure to give the deploy key write access, or if using HTTPS, make sure the credentials used have access. This content is subject to limited support.                  

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

What column compression encodings are used for PDTs in Redshift?Knowledge Drop

Last tested: Sep 12, 2019 Since we use "CREATE TABLE AS" for our PDTs, these Redshift rules apply.TLDR:Redshift automatically analyzes the tables and determines what it thinks is the best compression per column. We build PDT using CTAS (create table as select), and Redshift does not support specifying compression as part of the CTAS statement This content is subject to limited support.                  

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

Why are Look/Dashboard IDs null in System Activity History explore?Knowledge Drop

Last tested: Apr 17, 2018 Probably because the query isn't tied to a Look or a dashboard, for example if the query was run from an explore page.There are some cases where look/dashboard ids won't show up even if the query is from a look or dashboard. API queries from looks and dashboards will show up with Source = API 3.0 and will not have a look or dashboard id. Scheduled job queries will show up with two entries in the history table, one with Source = Scheduled Task and one with Source = Saved Look, but neither one will show the look id.​ This content is subject to limited support.                  

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

Is there a performance difference between hiding dimensions and deleting them entirely?Knowledge Drop

Last tested: May 11, 2020 If you don't want to use a given dimension, you have three options:Apply hidden: yes (or exclude the field from a given explore with fields) Comment out the fields in LookML using # Delete the fields from the LookMLAny of these options will help performance in the explore, since they will remove them from the field picker.Hidden fields still have to be validated, so if you're worried about validation, then choose option 2 or 3. Large comment blocks can have impact on validation times, so option 2 is the most performant. This content is subject to limited support.                  

260
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