Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Moving Averages in Table Calculations

Last tested: Sep 2020 You can make a moving average of a value in Looker with this simple table calc using offset_list:mean(offset_list(${value},0,7))For every row, this will give you the average of the 7 rows below. You can change the number of rows (it's the last parameter) to increase your window. You can also change the 0 to wherever you'd like the window to start. Positive numbers will move down, and negative numbers move up. So to get average of the ten values above you, you can do:mean(offset_list(${value},-10,10))This Help Center Article explains in more detailMoving average with dynamic number of rowsWe can get another level of flexibility by passing a parameter value into a table calc using a dimension:parameter: period { type: unquoted suggestions: ["1", "2", "3", "4", "5", "6", "7", "10"] } dimension: parameter_taker { sql: {% parameter period %} ;; type: string }And adapting our rolling average calculation from this:mean(offset_list(${inventory_items.coun

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

How to preserve whitespace in string fieldsKnowledge Drop

Last tested: Sep 3, 2020 The ProblemBy default, Looker does not preserve whitespace in string fields. If you have long strings that are broken up by line breaks or other whitespace, these can become difficult to read when stripped of their whitespace.A SolutionThe html field parameter can be used in combination with the rendered_value Liquid variable to specify that whitespace should be preserved.The following LookML code can be added to the field definition:html: <p><span style="white-space:pre-wrap;">{{ rendered_value }}</span></p>;; {{ rendered_value }} returns the value of the field with Looker’s default formatting. The HTML <p> tag defines a paragraph, and the <span> tag is an inline container used to style text. The white-space CSS property specifies how whitespace should be handled for an element, and the pre-wrap value for this property specifies that whitespace should be preserved by the browser, and that text should wrap when necessary and

2710
Department of Customer Love
Looker Staff
1 year ago
iqbal.sandhu
Looker Staff
iqbal.sandhuLooker Staff
published in Technical Tips & Tricks

Slack Action (the Slack Attachment API Token) won't show ChannelsKnowledge Drop

Last tested: Nov 6, 2019 Are you trying to send data from Looker to Slack? Can you see user names, but no Slack channels in the drop down menu? You've come to the right community article!Troubleshooting:Step 1: Make sure you're talking about Action HubCheck this Sending Data from Looker with the Slack Attachment (API Token) Action link to verify you're on the same pageStep 2: Make sure you set up the action properlyConfirm you've followed all the steps here, specifically adding the permissions for channels:read, users:read, and files:write:userStep 3: Make sure you added the action to the Slack channelHave you added the app to the channel yet? You should see an add app to channel button in the settings cog dropdown in SlackStep 4: If all else fails, re-add the bot to the workplace​ This content is subject to limited support.                   

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

How to scale labeling for quantitative data on the X-axis of a visualizationKnowledge Drop

Last tested: Jan 2, 2020 The ProblemSomeone sends you the following screenshot from a dashboard tile or a visualization from an explore, and wants to know why not all months are being represented on the X-axis.​​A SolutionBy default, the displayed points on both axes of the chart scale with the size of the window. The following screenshot is the same visualization as above, automatically scaled to display in a smaller window.​​How would one change this to force all data points on the X-axis to be represented properly?In the visualization in the explore (i.e. if you are troubleshooting a dashboard tile, navigate to the underlying visualization), click the gear icon at the top right hand corner of the pane. Scroll over to 'X', and change the 'Scale Type' to 'Ordinal'. Note that this can be accomplished through LookML.​​A CaveatNote that even when the scale type is set to ordinal, there will be times when not every data point will be shown on the X-axis due to space limitations:​​ This co

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

Advanced LookML - Liquid Use CasesKnowledge Drop

Last tested: Aug 28, 2019 Liquid is an open-source, Ruby-based template language created by Shopify It can be used in conjunction with LookML to build a more flexible, dynamic code.Liquid code is denoted by braces { } and falls into 3 different categories:TAG: {% %}used to create logicOBJECT:{{ }}, {{ value }}output pieces of dataFILTERS:modify the output of numbers, strings, objects, and variables,denoted by a pipe character |{{ value | capitalize }} = VALUEWe can pass values through multiple filters at the same time to compute complex sums.{{ product.sale_price | minus: product.price | times: 100.0 | divided_by: product.sale_price }}Check out the following external links, there is some really good content here to get deeper into all the things Liquid can do. And handy to have for reference.Cheat SheetLiquid WikiConditional LogicWhere can I use Liquid in Looker? There are several places in LookML where Liquid can be used:The html parameterThe HTML parameter enables you to specify the

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

What happens when you push a PDT to production?Knowledge Drop

  Last tested: Sep 2019 1. If there is a previous version of this PDT in production, the existing production table will be dropped by the reaper2. If there is a version of that PDT in dev mode, the dev mode PDT will become the production PDT.3. If there is not a dev mode version of that PDT, a new PDT will be generated by whichever happens first:a) a user queries the PDT via an explore/look/dashboardb) the PDT is triggered the next time the regenerator checks trigger valuesNote: if the PDT takes more than an hour to build, explore queries that use this PDT will time out while waiting for this PDT to build. The PDT build will NOT timeout as long as there are no settings on the database that would cause the query to timeout.Chat tip: If you generate the table for a new PDT in dev mode by querying it, then push to production after it is built, users can immediately query that table without a build timeout. This content is subject to limited support.                

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

SMTP Error: "Failed to send mail: hostname [hostname] does not match the server certificate."Knowledge Drop

  Last tested: April 2021 Check to see if TSL/SSL is enabled.That error message means that the host you're connecting to, like foo.bar.com, has an SSL cert which is for a different host, like bob.bar.com. So in this case the mail server could have the wrong cert on it (not usually the case) or Looker had the wrong name.One scenario is if you're using a mail server running on the same host as Looker. You could configure Looker to talk to localhost which is fine except that the SSL cert would be for something like mail.foo.com.Another possibility is if the mail server is using a default, self-signed certificate with some bogus hostname. In that case if you want to use SSL you'd need to go get a real cert.  This content is subject to limited support.                

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

Can the PDT scratch schema exist in a different db host than the original tables used to create it?Knowledge Drop

Last tested: April 2021 No. Even PDT Override doesn't allow for this.The challenge here is that Looker would have to issue a multi part query: first to the database with the PDTs, and then to the database with the actual tables. Writing a coherent query with joins and such that involves two different databases is outside the scope of our product at this time. The closest we can get is to "join" two different queries on the frontend using Merged Results.  This content is subject to limited support.                

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

Do the LookML Validator or Content Validator time out?Knowledge Drop

Last tested: May 2021 No, they do not. They will go on forever. TroubleshootingThe only known way to kill the LookML Validator or Content Validator process is to restart the instance. How long does the content validator take?This can vary wildly depending on the number of Looks and dashboards on the instance. On one instance with 50,000 Looks and 10,000 dashboards, it took about an hour for content validation to complete. Takeaway: If the Content Validator seems slow, it is recommended to wait it out rather than initiate it again. Starting the Content Validator again while it’s already running (eg by opening a new tab) will cause 2 simultaneous processes to run, and will not speed up the 1st process. This content is subject to limited support.                

1310
sam
Looker Staff
1 year ago
molly.lippsett
Looker Staff
molly.lippsettLooker Staff
published in Technical Tips & Tricks

refs/heads/branch REJECTED_OTHER_REASON: pre-receive hook declinedKnowledge Drop

Last tested: Apr 2, 2020 Some users enforce strict policies on their commits from their remote git provider. These sometimes come in the form of pre-commit or pre-receive hooks. These are essentially scripts that git executes before or after an event, like a commit, or a push, that check things such as the length of the commit, the domain the commit is coming from, specified naming conventions needed, and so on. What happens?if a commit or push made through Looker doesn't pass the hook's rules, you'll see an error like this: What does this mean?This commit or deploy is essentially stuck in limbo - Looker thought it was ok, but the remote repository disagreed and sent it back. If we can figure out what is specified in the hook's rules, we might just be able to make a small change, revalidate LookML and make new commit with a message that fits the bill. But if a specific domain is required to deploy code to production changes are required on the side of the remote Git provider - the 'loo

13671
kevin.dunn
Looker Staff
1 year ago
rachel.johnson
Looker Staff
rachel.johnsonLooker Staff
published in Technical Tips & Tricks

MYSQL—HowTo: Why does MySQL need database permissions for derived tables?Knowledge Drop

Last tested: Mar 9, 2021 At the time the code for the MySQL dialect was written, it did not support WITH, so we had to write derived tables a bit differently. Instead of WITH we use CREATE temp TABLE, which requires database permissions. (This is still different than a PDT since we are not writing the table to the schema, just creating a temp table)This error can occur when trying to use derived tables without setting up the database permissions:temp derived_table sql_availability_audit creation failed: SQL Error in CREATE TABLE as SELECT: java.sql.SQLSyntaxErrorException: Access denied for user 'looker'@'%' to database 'looker_tmp' Later versions of MySQL now support CTEs (WITH), but as of Mar. 2021 Looker still uses temporary tables for ephemeral derived tables. This content is subject to limited support.                   

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

How do I update LookML via the API?Knowledge Drop

Last tested: June 2021 This is currently not possible. The lookml-model endpoint does have "explores" in the body, but it's read-only.As a workaround, we recommend using the API of your Git provider. For example, these are Github's API Docs. This content is subject to limited support.                

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

Why are my totals not showing up?Knowledge Drop

Last tested: June 2020 Totals only apply to measures. They won't appear on dimensions, or on table calcs prior to Looker 6.2 (See this Community post: Why don't my table calculations have totals). This is because to run a total, we remove the dimensions and re-run the query, so that any measures are "totaled". If the totals are not showing up for measures either, it may be because a measure filter is filtering out the totals results. The HAVING clause that a measure filter generates for the main query is also generated for the totals query, so the totals row won't show up if the results don't meet the HAVING condition.   This content is subject to limited support.                

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

How can an admin see what permissions an embed user has?Knowledge Drops

Last tested: Dec 2020Click on the user in Admin > Users. Under “Roles” for that user, click the embedded role. You’ll see a popup with permission sets and model sets.  Another quick way to check this is via the browser’s Network console. For example: Open Network console. (For Chrome, this can be found under the three dots menu, then “More Tools > Developer Tools.) Run the page with the Embedded iframe. Filter Network responses for embed and click on that line. You're looking for the encoded URL like %2embed%2F.... Click on Headers and scroll to the Query String Parameters section. Click view parsed (It may already be defaulted). You'll then see a section like: permissions:["see_user_dashboards","see_lookml_dashboards","save_content","access_data","see_looks","schedule_external_look_emails","embed_browse_spaces","schedule_look_emails","explore","see_sql","download_without_limit"]  This content is subject to limited support.                

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

How to check if a Looker instance is upKnowledge Drop

Last tested: June 2021 If you host your own Looker instance, you may want a way to monitor instance uptime and downtime. One easy way to do this is with the `alive` endpoint. Replacing `customer.looker.com` with your Looker URL, the following command will return a 200 response if the Looker instance is alive: curl -i -k https://customer.looker.com/alive  Alternatively, you can check directly in the browser by visiting “customer.looker.com/availability”. This content is subject to limited support.                

3950
sam
Looker Staff
1 year ago
Michael
MichaelMember
published in DCL Mic Drops

New DCL Mic Drop: Flavia OliveiraMic Drop

DCL Mic Drop #5 The Department of Customer Love (DCL) is Looker’s all-knowing support team. Yes, they're actually called that. They’re kind of the brains behind this whole operation! You may have talked to them via support chat or on a ticket, or maybe even in the Looker Community. Besides being true Looker savants and genuinely interesting characters, DCLers deeply understand our users’ experiences and challenges because they’re in the code with our users every day, helping work through it.  We’re super excited that now each month, we’ll publish a new Mic Drop to give you a glimpse behind the curtain to meet one of these fabled DCLers. We will learn a little more about the individual behind the chat magic, as well as leverage their expertise as they select 3-5 Knowledge Drops that you should definitely check out! This month, we are lucky enough to have chatted with @flaviagdo!   Michael: Hey Flavia, thanks for being open to doing this month’s DCL Mic Drop! Before anything else happens

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

Why can't I query my stable view name?Knowledge Drop

Last tested: Mar 23, 2020 You will not be able to query the stable view name until the PDT rebuilds - meaning either you1) change the SQL and push that to production (which causes a rebuild) or2) manually rebuild the PDT or3) the PDT is organically rebuilt by the regenerator or4) the PDT is organically rebuilt when a query comes in and its persist_for is expiredThe stable view will only be published after its underlying PDT is naturally rebuilt by Looker.If you create a PDT for the first time with publish_as_db_view:yes then the first time it’s built it’ll have the flag. The confusing case happens when you’ve already got some PDT that you want to add publish_as_db_view to; when you add it and push to production you might expect to immediately be able to query that stable view, but you can’t - you need the PDT to build/rebuild before it picks up the flag This content is subject to limited support.                   

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

Can I use the mobile app while allowlisting IPs in Looker?Knowledge Drop

Last tested: May 7, 2021 Not natively. If you allowlist specific IPs in Looker, then the IPs of mobile phones won't be able to reach Looker. Mobile IPs are temporary (change every few days) so allowlisting the phone IPs isn't a sustainable workaround.Workaround: Use a VPN on their phone to connect to a static IP that can be allowlisted.Feature Request to comment on: https://portal.feedback.us.pendo.io/app/#/case/60895 This content is subject to limited support.                   

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

How do self-hosted users find looker logs?Knowledge Drop

Last tested: Jul 11, 2017 Step 1: Where to find logsThe standard subdirectories under the Looker user’s home directory (usually /home/looker) are described belowThe logs are inside the log directory. Step 2: What day to getThe current day's log is called looker.log and is currently being written to. The other logs have dates in their filenames.IMPORTANT: If you are downloading your today's log, make a copy of today's log. if you tamper with looker.log in any way then the instance will stop writing log files. This content is subject to limited support.                 

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

tmp directory getting too largeKnowledge Drop

Last tested: May 10, 2019 If your instance is hosted by Looker, the .tmp directory is cleared on startup.If you host your own instance, and this directory gets too large, you can safely delete certain files in .tmp when the instance is shutdown.Admins may want to add something like this to their startup scripts:rm -rf ~/looker/.tmp/jruby* ~/looker/.tmp/looker-tmp* This will clear the .tmp folder of unneeded files on startup. This content is subject to limited support.                 

210
S
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

Can I remove the from cache/ last run at time from the top of a dashboard?Knowledge Drop

Last tested: Jun 25, 2019 Nope. You can vote for this feature request here: Hide staleness time in Dashboards/ last run time/ from cache message This content is subject to limited support.                    

90
A
Looker Staff
1 year ago
Dean_Hicks
Looker Staff
Dean_HicksLooker Staff
published in Technical Tips & Tricks

Upload to SFTP host [host] failed due to error: NoMethodError. message: undefined method `write' for nil:NilClassKnowledge Drop

Last tested: Oct 18, 2019 The Problem:Error: Upload to SFTP host [host] failed due to error: NoMethodError. message: undefined method `write' for nil:NilClassTroubleshooting steps:This error can happen when some part of the connection and transferring of data to the SFTP server failed for schedules that send with "All Results" selected. In order to get a better error message, try sending again with "Results in Table" selected.Some good things to check in general for SFTP issues:- Trailing slash on the path - the path should always end in a trailing / and should point to a specific directory on the SFTP server.- Have our IP Addresses been whitelisted on the server?- Did the schedules work previously and broke? Or have they not been able to successfully set up?Also check for a / in the schedule name. This content is subject to limited support.                   

4690
Dean_Hicks
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

extension: required and validationKnowledge Drop

Last tested: Apr 30, 2019 The extension: required parameter flags an Explore as requiring extension, which means that the Explore cannot be used on its own. The contents and settings of the Explore will only be used when the Explore is extended using the extends parameter in another Explore.What this means in terms of validation: If there are any validation errors in the base explore then these errors will not surface until the explore is extended and the extended explore inherits the errors. Essentially, there are no errors in the base explore until that explore is extended and we don't resolve the error in the extended explore. This content is subject to limited support.                   

160
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

error "orphaned job finalized by Scheduler::Custodian"Knowledge Drop

Last tested: Mar 25, 2019 The custodian was removed in Looker v6.6, so only older versions of Looker will show this message.The custodian ran during the Looker start up process and cleaned up any schedules that haven't completed and have no chance of completing. An example of this is a schedule that is in the "execute" state but there is no query running on the database for this schedule.So that error message indicated that the schedule was cleaned up by the custodian because there was no change of completion. This content is subject to limited support.                   

530
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

Why don't I see the gear menu next to the Run button or the three dot menu at the top right corner of the tiles in an embed dashboard?Knowledge Drop

Last tested: Apr 22, 2019When the embed user has no permissions for anything that would appear in that menu, the menu is left out. This content is subject to limited support.                   

470
A
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