Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2114 Topics

romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

SQL RUNNER & DATABASE (HowTo): Create Schema in SQL RunnerKnowledge Drop

Last tested: Oct 23, 2018 It is possible to create a new schema in the database by sending the command from SQL Runnereg: CREATE SCHEMA schema_name ; (Redshift syntax)⚠️ One thing to know is that the schema may not appear in the system after the creation until a table is added to the schema! ⚠️ User must also have CREATE permission on db http://docs.aws.amazon.com/redshift/latest/dg/r_GRANT.html This content is subject to limited support.                  

290
romain.ducarrouge
Looker Staff
1 year ago
rufus
Looker Staff
rufusLooker Staff
published in Technical Tips & Tricks

How can I reverse a running total so that it goes from bottom up?Knowledge Drop

Last tested: Jan 21, 2019 Running Total Down:sum(offset_list(${measure},1-row(), row()))Running Total Up:sum(offset_list(${measure},0,count(${dimension})-row()+1)) This content is subject to limited support.                 

7841
L
1 year ago
rufus
Looker Staff
rufusLooker Staff
published in Technical Tips & Tricks

How does the SQL Trigger Value Process work for nested PDTsKnowledge Drop

Last tested: Jan 16, 2019 Chat tip: consider using datagroups for dependency PDTs that should always rebuild on the same interval! Looker will take care of the rest.If the PDTs have the same sql_trigger_value, then we do a topological sort to make sure that the upstream PDT is built before the downstream PDT (and this works in general with more complex dependencies). BUT we only look for those dependencies for tables with the same sql_trigger_value! This is also beneficial because we cache the trigger result.If a user tries to take the rebuild order into their own hands by say setting an earlier time for the upstream table, they may actually get the opposite of what they were trying to accomplish.Example of unexpected behavior:upstream trigger time is 12:00am, downstream trigger time 12:05.Upstream checked at 11:59 (not triggered)… some other table checked and triggered takes more than 6 minutes to build…downstream checked and triggered at 12:06 will be built with yesterday’s upstream

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

Why do I get the error `We had issues contacting the CDN. Page speed may be impacted`Knowledge Drop

Last tested: May 1, 2020 ErrorWe had issues contacting the CDN. Page speed may be impactedSummaryThis issue likely originates from the user's computer or the network that the computer is on. However, it's worth checking using the curl command below to ensure that Looker's CDN is working normally for your host.TroubleshootingIf you are on Chrome, try in incognito. If the error goes away: Clear cache Or perform a hard reset first, as this may be less intrusive (Command - Shift - R on Mac, Ctrl - F5 on Windows) Disable adblock Check if we can reach the CDN for your instance by running this in your terminal: curl -I -s -X GET -H "Origin: https://INSTANCE_NAME.looker.com" https://static-b.lookercdn.com/4.12.7/webpack/vendor.webpack-a97be941.js HTTP/1.1 200 OK is the expected response Are all users affected? Bigger issue, possibly cause by firewall, proxy or ad block This problem can also likely be remedied by enabling the browser service-worker cache, even if the customer is unable

4560
romain.ducarrouge
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

Can I have multiple drill sets or drill options?Knowledge Drop

Last tested: Jul 21, 2020 This is a bit of a workaround. Sometimes we will want to have multiple drill choices available, but the "drill_fields" parameter only accepts one set. We can get around this by creating multiple hidden fields with different drill_fields and leveraging the {{ field._link }} parameter. This will have the "drill sets" as different links on the measure, which will pull up the individual drills when selected.Example:measure: count_with_drills {type: countlink: {label: "set 1 link"url: "{{ count_set_1._link }}"}link: {label: "set 2 link"url: "{{ count_set_2._link }}"}}measure: count_set_1 {type: countdrill_fields: [id, ret]hidden: yes}measure: count_set_2 {type: countdrill_fields: [id, order_id]hidden: yes} Note: if the field is long/complicated, you can also create a total dummy field like this:measure: dummy {drill_fields: [id]hidden:yessql: 1=1 ;;}  This content is subject to limited support.                   

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

SFTP results in "Host key mismatch" ErrorKnowledge Drop

Last tested: Aug 18, 2020 This occurs when information about the host being connected to via SFTP doesn't match the saved information about the host on the instance. Via the command line this results in a warning that can be bypassed manually and transfers work fine. Looker does not do this however, and will instead throw a "Host key mismatch" error.To get around this, we need to drop the existing info about the host from the instance so the new info will be populated into the known hosts directory.If you host your own instance, on the instance run cd .ssh and then ssh-keygen -R hostname (where hostname is the name/IP of the SFTP server). From there, simply run another SFTP transfer via Looker, this will add the new info about the host to the known hosts directory and remove the error. If your Looker deployment is hosted by Looker, please open a Looker support request for assistance.This can happen if the SFTP is restarted and the destination IP address has been changed (even though it

5640
ryan.bouquet
Looker Staff
1 year ago
rufus
Looker Staff
rufusLooker Staff
published in Technical Tips & Tricks

What happens if every recipient of a schedule clicks unsubscribe?Knowledge Drop

Last tested: Feb 22, 2021 The last recipient will be prompted with a message indicating that unsubscribing from the schedule will delete it and ask for confirmation.The schedule will then be deleted if you click OK. This content is subject to limited support.                   

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

What should I set the --concurrent-render-jobs startup flag to?Knowledge Drop

Last tested: Jul 1, 2019 The --concurrent-render-jobs startup flag determines how many Chromium processes can be creating PNGs or PDFs at any given time. The default is 2, and each render thread means another Chromium process would be starting up outside of Looker, which can be process intensive.Going over 2 should only be done in unique circumstances, but for really large instances a general rough formula isminimum(cpu core count - 2, (total memory - java heap reservation ) / 2gb )As an example, a m5.4xlarge instance has 16 cores and 64gb of RAM. The default Looker startup script allocates . 60% of the total ram to the Looker process, so the formula would beminimum((16 cores - 2), (64gb - 38.4gb)/2gb) = minimum(14, 12.8) = 12It is always safer to go a little lower. This content is subject to limited support.                   

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

What is SMTP (for Looker)?Knowledge Drop

Last tested: Jul 1, 2019 Simple Mail Transfer Protocol (SMTP) is the universal standard protocol for communication via email. When sending an email from your Looker Google account, you use SMTP. All emails coming from Looker to outside email addresses also use SMTP.In Looker, you can either use custom SMTP settings so that all email messages come from an account you control, or you can use the default SMTP settings to take advantage of Looker’s email service, without the need for additional configuration. So that, all emails will come from noreply@lookermail.com.Custom SMTP is set up in the Admin > SMTP panel. This content is subject to limited support.                  

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

Why Are My Dashboard Filters Becoming "Matches(Advanced)" for Some Users?Knowledge Drop

Last tested: Jun 4, 2020 This has to do with model permissions and filters of type: field. Ensure the users affected have model access to the fields used in the filters.We do this for security reasons. The dropdown menu in a filter can give clues as to what type the field in question is, and we don't want to expose any LookML information for a field that a user does not have permission to access otherwise. This content is subject to limited support.                    

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

Can Looker track how many scheduled emails were opened for a customer? (Read receipts)Knowledge Drop

Last tested: May 14, 2019There isn't any way to see who read emails that have been sent from Looker - feature request for comment is here This content is subject to limited support.                    

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

Startup flags for schedulingKnowledge Drop

Last tested: Jul 1, 2019 Startup Option Description Default Value --scheduler-start-delay=<i> Delay task runner startup (seconds) 60 --scheduler-threads=<i> Number of simultaneous scheduled tasks 10 --alerts-scheduler-threads=<i> Number of simultaneous scheduled alerts 3 --unlimited-scheduler-threads=<i> Number of simultaneous unlimited scheduled tasks 3 --default-mailer-port=<i> Port over which to send mail when using default mailer 587 --max-unstreamed-limit=<i> Elevates the number of rows allowed for download without streaming 100000 --concurrent-render-jobs=<i> Number of simultaneous PhantomJS or Chromium rendering processes 2 --scheduler-query-limit=<i> Limits number of concurrent scheduled queries 10 --scheduler-query-timeout=<i> Length of scheduler timeout to wait for connection 1200 -q, --queue-broker-port=<i> Port for queue broker (used for inter-node communication i

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

The life of a schedule jobKnowledge Drop

Last tested: Jan 24, 2020 When people talk about schedules, they are generally referring to one of two things: schedule plans and schedule jobs.A schedule plan refers to the configuration of a report: it's format, destination, and the conditions -- time interval or datagroup -- that determine when it should be sent. When you are are 'editing' a schedule in the Looker UI, you are editing the schedule plan.A schedule job refers to a single occurrence of a report (specified by the schedule plan) being generated and sent out.A schedule job will go through up to 4 stages in its lifecycle:enqueue_for_execute execute enqueue_for_delivery deliveryenqueue_for_executEThe schedule job is "waiting in line" for an open execution thread.ExecutEExecute is the stage where querying and rendering (if needed) happens. By default, there are 3 schedule execution threads on an instance. The number of scheduler threads can be changed in the startup parameters.enqueue_for_deliveryThe schedule job is "waiting

11702
margaret.abercrombie
Looker Staff
9 months ago
ryan.dunlavy
Looker Staff
ryan.dunlavyLooker Staff
published in Technical Tips & Tricks

Why are the same results sent when "results changed since last run" is selected?Knowledge Drop

Last tested: May 8, 2020 This can happen if the results are the same but the order of the rows is different.The "results changed since last run" option works by creating a hash of the results and checking that against the last run. If the order of the rows is different, even if the data itself is the same, the hash will be different and the schedule will send. The results taken into account include formatting and vis applied, so if hidden field changes, those changes alone will not cause a schedule to send.To avoid this, the data should be sorted in a way that makes the order constant when the data is the same. If all rows are sorted on then the order will never change.Edge CaseIf you're seeing that schedules aren't being sent out as expected, it's possible that when Looker is "checking" it is pulling from cache rather than rerunning the query to then compare states before the send. You can confirm this by looking at the Queries panel under Admin > Queries. Theoretically at whatever

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

Week-related table calculation functionsKnowledge Drop

Last tested: Dec 2, 2019 Currently, there are no week-related table calculation functions available. Below are feature requests and workarounds for various week-related functions.extract_day_of_week / extract_day_of_week_indexA function to extract the day of the week or day of week index from a date field doesn't currently exist - request it here.Workarounds:day_of_week_indexmod(diff_days(date(2008,01,01), ${orders.created_date}) + 1, 7)day_of_weekindex( list("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"), mod(diff_days(date(2008,01,01), ${orders.created_date}) + 1, 7))trunc_weeksA function to get the week from a date field doesn't currently existWorkaround:add_days(-1 * mod(diff_days(date(2008,01,01), ${orders.created_date}) + 1, 7), ${orders.created_date})Weekday / business day count No function currently available natively, request it here.Workaround:diff_days(${start_date}, ${end_date}) + 3 - floor(diff_days(add_days(-1 * (mod(diff_days(date(2008,01,0

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

What is Chromium used for in Looker?Knowledge Drop

Last tested: Jul 1, 2019 Chromium is an open source version of Chrome, and it is what Looker uses to render PNGs/PDFs. Chromium can be run in headless mode, meaning it will start up on the Looker server without having a user interface to interact with and can be controlled remotely. Each time a render job is run, Chromium is started in headless mode and used to try to take a screenshot of the Looker content. This content is subject to limited support.                 

610
ryan.dunlavy
Looker Staff
1 year ago
shivanisankar
shivanisankarNew Member
published in Technical Tips & Tricks

Why are my Quick Start Pivots not Showing Up?Knowledge Drop

Last tested: Feb 9, 2021 Even if you have a value in the pivot subparameter of your query parameter, queries with pivots need to have the pivot defined in both pivots AND in dimensions.Example:query: orders_by_brand_name { dimensions: [products.brand, products.category] measures: [products.count, order_items.order_count] pivots: [products.category] description: "Orders by brand name" limit: 500} You can see that products.category is included in both the dimensions and pivots subparameter. This content is subject to limited support.                  

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

Git error: "deploy aborted, operation incomplete. Verify configuration of the remote repository and retry."Knowledge Drop

Last tested: Apr 21, 2020 If you gets an error like this:Deploy Faileddeploy dev-branch to masterdeploy aborted, operation incomplete. Verify configuration of the remote repository and retry.org.eclipse.jgit.api.errors.TransportException: git@github.com:org/repo.git: expected report for ref refs/heads/master not receivedCheck https://www.githubstatus.com/ - the error probably means github is experiencing issues. You can wait until github is back up and then try to deploy/push again :) This content is subject to limited support.                  

1830
sarah.tayeri
Looker Staff
1 year ago
ryland.wolff
Looker Staff
ryland.wolffLooker Staff
published in Technical Tips & Tricks

What is going on when I see "nesting of 101 is too deep"?Knowledge Drop

Last tested: Jan 14, 2020 When we are creating a custom filter with more than 50 boolean evaluations we will see the error nesting of 101 is too deep. For example, we will generate this error if we have a custom filter like this:${orders.id} = 1 OR${orders.id} = 2 OR${orders.id} = 3 OR…${orders.id} = 50 OR${orders.id} = 51 With more than 50 boolean evaluations, we get too deep. We have two options to resolve this:Solution 1: LookML yesno fieldWe can create a dimension of type: yesno that will run this evaluation that we can then use as a frontend filter. This would look something like this:dimension: one_to_fifty {type: yesnosql: ${id} = 1 OR${id} = 2 OR${id} = 3…${orders.id} = 50 OR${orders.id} = 51;;} Solution 2: Use matches_filter( ) functionIf we want to avoid making any LookML developments or need to be able to modify the filters from the front end we can compress all these individual boolean evaluations into a single evaluation using the matches_filter( ) function. The logic that

8370
ryland.wolff
Looker Staff
1 year ago
S
Looker Staff
swhiteLooker Staff
published in Technical Tips & Tricks

Redshift Metadata tables: stv_locks, stv_inflight, stl_error, pg_locks, pg_classKnowledge Drop

Last tested: Dec 3, 2017 ... are tables that hold data about the database itself; tables, processes etc. DCLers commonly use these concurrently to troubleshoot hanging PDT queries or long running ones, etc. stv_inflight & stv_recents stv_inflight show the queries currently running on the database (owned by the user):SELECT * FROM stv_inflightstv_recents will show the current AND recent queries running on the DBSELECT * FROM stv_recentsand we can join in the stl_errors table to check for clues:SELECT * FROM stv_recents JOIN stl_error on stv_recents.pid = stl_error.pid stv_locks or pg_locksSELECT * from stv_locks(or pg_locks) will return the locks on any tables in Redshift that are currently active. They will disappear as soon as the lock is released.In order to see the table name you'll need to join in stv_table_perm or pg_class:SELECT * FROM stv_locks JOIN stv_tbl_perm ON stv_locks.table_id = stv_tbl_perm.idorSELECT pg_class.relname, pg_locks.* FROM pg_locks LEFT OUTER JOIN pg_clas

3370
S
Looker Staff
1 year ago
sidney.junior
Looker Staff
sidney.juniorLooker Staff
published in Technical Tips & Tricks

Does the embedded domain allowlist admin panel option support wildcards?Knowledge Drop

Last tested: Mar 30, 2021 Short answer: Looker may allow wildcards in the embed domain list, but browsers generally do not allow wildcards in domain names for browser security contexts.Longer answer: The Looker allowlist will allow wildcards, the browser will not. However, in the context of any given page, you just have to ensure that the parent page passes the correct of the two origins that you are using for that specific page load (via the embed_domain parameter), then Looker will use that and it should workFor Example:In the admin UI we would put https://*.domain.comIn the embed URL we would put the actual page we are embedding on embeddedcontent.domain.com This content is subject to limited support.                 

1721
M
11 months ago
srinija.godavarthi
Looker Staff
srinija.godavarthiLooker Staff
published in Technical Tips & Tricks

How to add a new row of data with LookMLKnowledge Drop

Last tested: Mar 17, 2020 For example, starting off with this: And wanting to add a new row of data:Use a LookML case, something like so:when: {label: “show-rss”sql: ${event_type} = ‘show-rss’ ;;}[more when blocks]when: {label: “session”sql: 1=1 ;;}For all of the existing values, you map them back to the same value, then put a fourth when block for session. The idea for the case dimension is just to create a dummy row with no data so that you can use table calcs to put the correct value in each row using functions like offset or index to pick out the specific values This content is subject to limited support.                  

6620
srinija.godavarthi
Looker Staff
1 year ago
S
Looker Staff
swhiteLooker Staff
published in Technical Tips & Tricks

What is connection_reg_r3 ?Knowledge Drop

Last tested: May 14, 2020This is a table that should exist in the scratch schema of a database with PDTs enabled on a connection.The Looker user on the database needs to have ownership of the scratch schema in order to create or access the connection_reg_3 file This content is subject to limited support.                    

2770
S
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.                 

790
S
Looker Staff
1 year ago
S
Looker Staff
swhiteLooker Staff
published in Technical Tips & Tricks

Redshift Schema/Table privileges — ERROR: permission denied for relationKnowledge Drop

Last tested: Oct 24, 2018 A weird quirk that happens specifically with Redshift is that when using a schema that isn't the Public one and tables are being updated with full table drops, every time a table is dropped and re-created it doesn't inherit the access permissions of the old table.This can cause users to get errors when trying to access the relation like this: ```ERROR: permission denied for relation``` If they've GRANTED privileges on the table, they probably only granted them on that version of the table which was dropped at some point for an update.There are (at least) 2 solutions for this problem. The simplest thing to do would be to make the looker DB user an owner of the new schema. That way any tables that are created will be fully accessible by the looker user and therefore Looker.If you are hesitant to give ownership of the schema to the looker user, the other option is to log in as the super user and run this command: ALTER DEFAULT PRIVILEGESFOR USER schema_owning_use

42730
S
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