Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Knowledge base
Community ResourcesUsing the Community3 Account Info & Registration4 Community Feedback1
Community
WelcomeGetting started145 News & announcements125 Who's hiring494 Release notes (archive)63 Lesson Comments15 Challenge10
Looker developmentLookML1833 SQL323
Exploring & curating dataExplores561 Dashboards & looks1566
Application developmentEmbedding Looker / Powered by Looker155 Open source projects54 Looker API378
AdministrationUser access & management70 General Looker administration453
Data science & analyticsGot a question?83 Share your best practices16
The Kitchen TableTechnical Tips & Tricks1739 Common Account Questions14 Blog Archives347 DCL Mic Drops5 Video Tutorials 🎥4
コミュニティフォーラム (Japanese)ニュースと告知50 ヘルプとサポート14 コラム69

Ask question / Create topic
Login

Community
The Kitchen Table

The Kitchen Table

Explore the product knowledge we've built up together

2109 Topics

Newest first

Recently active Most replies Most views

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

If you have a templated filter in a derived table and you reference that table in another derived table, can you pass the filter value through?Knowledge Drop

Last tested: Jan 10, 2019 No, you would have to create the templated filter in your new derived table. The DT doesn’t “store” the templated filter, it’s just part of the sql. This content is subject to limited support.

270

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

Can a sql trigger value return more than 1 row, and 1 column?Knowledge Drop

Last tested: Jan 16, 2019 According to docs, the sql_trigger_value will only consider the first row and column in the SQL you write. It is recommended to have your trigger value return only 1 value, to prevent confusion and protect non-streaming SQL dialects from loading large result sets into memory.If you really want more than 1 column considered by your sql trigger value, you can concatenate the columns, which will cause it to trigger if there is a change in any of these columns. This content is subject to limited support.

170

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

PDT rebuild error: `SQL Error in CREATE TABLE as SELECT: This connection has been closed`Knowledge Drop

Last tested: Nov 5, 2019 This is likely a timeout error.If the PDT is persisted with the persist_for parameter then this PDT can only be built by querying it via an explore/look/dashboard and the build is subject to Looker's 60 minute proxy timeout.If you have determined that this PDT is timing out due to Looker's proxy timeout, consider persisting using a sql_trigger_value instead. PDTs persisted with a sql_trigger_value are generally built by the regenerator and are not affected by Looker's 60 minute proxy timeout. This content is subject to limited support.

580

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

"Variable not found" when dynamically querying derived tables using parametersKnowledge Drop

Last tested: Nov 26, 2020 Are you using the method outlined in Dynamically Query Tables Using Parameters with derived tables and hitting a pesky "Variable not found" error message in the LookML Validator?While derived tables in the format ${derived_table_or_view_name.SQL_TABLE_NAME} don't need to be joined (see If you reference a derived table in the SQL of another derived table, using ${viewname.SQL_TABLE_NAME}, do you have to join those views at the explore level?), they need to be joined if we're using a parameter. This is because the views must be joined in order to "listen" to the parameter.Example:view: selected_table {sql_table_name:{% if timeframe_selection._parameter_value == 'weekly' %}${weekly_view.SQL_TABLE_NAME}{% elsif timeframe_selection._parameter_value == 'daily' %}${daily_view.SQL_TABLE_NAME}{ % else % }${full_view.SQL_TABLE_NAME}{ % endif % } ;;parameter: timeframe_selection {type: unquotedallowed_value: {label: "Weekly"value: "weekly"}allowed_value: {label: "Daily"v

390

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

If a user within a group email address unsubscribes from a schedule, will the affect other recipients in this email group?Knowledge Drop

Last tested: Dec 7, 2020 Yes, this will unsubscribe all recipients in the group email from the schedule. This is mentioned in Docs, and there is a Community post on handling/monitoring unsubscriptions. This content is subject to limited support.

690

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

Does Looker support UNION in NDTs?Knowledge Drop

Last tested: Dec 27, 2018Looker does not currently support UNIONs in NDTs. This content is subject to limited support.

190

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

How are scheduled tasks distributed on a cluster?Knowledge Drop

Last tested: Jan 5, 2018 Schedule execution is distributed equally across the cluster. Each node will run the scheduler thread amount (default 10 per node and 2 render tasks per node).For example, if you have a 3 node cluster with default settings, you can have 30 concurrent schedules (assuming none were rendered tasks) or 6 concurrent schedules (if they are rendered tasks). You can change default scheduler thread and render job limit with startup flags on Looker. This content is subject to limited support.

200

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

Can I write PDTs to a separate database using the PDT override feature in the connection settings?Knowledge Drop

Last tested: Jan 17, 2019 TLDR: Wherever the PDTs are located, they must remain accessible and readable by the default (non-PDT) connection. SO a “separate” db likely won't work as the PDTs would source from and be written to the separate db, but be inaccessible to the non-override connection.Writing PDTs to a different database using the PDT override feature is only supported for systems that support “databases” as the superset namespace of schemas. For example, Snowflake has a concept called “warehouse”--multiple warehouses can be configured to access the same underlying database storage but have different CPU/memory resources, so in this sense, specifying a different data "warehouse" may work.For most other databases, although you'll find that it is possible to use the PDT Overrides column to configure PDT processes to *write* to a separate database, if the PDTs are not visible to your default (non-override) connection, you won’t be able to query them. This will result in read error

2710

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

What's the deal with templated filters and PDTs?Knowledge Drop

Last tested: Sep 6, 2019 In general, using templated filters in PDTs (or any liquid, like parameters, really) is NOT a good idea. The dynamic filters will cause the table to rebuild every time the filter changes, and it causes a huge amount of strain on your database. You should stick with a plain derived table, since the table must build from scratch anyway every time the filter value is changed.There is a feature request for allowing liquid in PDTs - please add your use case! This content is subject to limited support.

320

1 year ago

romain.ducarrougeLooker Staff

published in Technical Tips & Tricks

BIGQUERY (HowTo): PDT Trigger Value for Business Hours (Specific Hours) Rebuild onlyKnowledge Drop

Last tested: Sep 10, 2019 What to use for a sql_trigger_value to cause an hourly rebuild but only during the day (7am to 7 pm in database time) for BigQuery. SELECT IF(EXTRACT(HOUR FROM CURRENT_DATETIME()) BETWEEN 7 AND 19, EXTRACT(HOUR FROM CURRENT_DATETIME()), 0) This content is subject to limited support.

1 year ago

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.

1931

1 year ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

BIGQUERY (HowTo): Can I parameterize a BigQuery connection with user attributes?Knowledge Drop

Last tested: Jan 21, 2019 We do not support parameterized connections with UA's in BQ, due to the necessity of a service account private key (which is a JSON file--so cannot be set to a UA). There is a feature request for this to be supported in BQ.There is a workaround using model sets, however. In this set up, we'll create multiple connections (1 per BQ project) and within out LookML project, 1 model per connection. We will then define our explore in a view file, or a .explore.lkml file (as these do not require a defined connection).Model file 1:include: "bq_base_explores.view.lkml"connection: "bq-project-1" Model file 2:include: "bq_base_explores.view.lkml"connection: "bq-project-2" View file/explore file (bq_base_explores.view.lkml) :explore: abcexplore: xyz We will then define 2 model sets, one with permissions to model 1 and one for model 2, and assign these to users who would fall into our different UA groups. Our explores will be available to both sets of users, but will be que

1280

1 year ago

romain.ducarrougeLooker Staff

published in Technical Tips & Tricks

GIT - Find all commits for a userKnowledge Drop

Last tested: Sep 6, 2019 There are a few ways to get at this:Click on "History" in the UI under the Git ButtonORVisit the remote repo and take advantage of its capabilitiesOROn the command line, in the folder where git is set up, use:git log --author=username@email_domain.com orgit log --author="Jon" This will output:commit f38dbb030dda2ce2ee64e51270ef682ef51345e8Author: Jon Doe <jon@email.com> Date: Mon Dec 18 12:53:03 2017 +0000 updating the users view file This content is subject to limited support.

2560

1 year ago

romain.ducarrougeLooker Staff

published in Technical Tips & Tricks

Lookerbot error "Could not send to Webhook"Knowledge Drop

Last tested: Jan 22, 2019 There are a few reasons for this:If the format of the scheduled content is set to HTML, that format is not supported by Slack2017-07-26 05:02:18.648 +0000 [ERROR|007e6|] :: Looker::Model::OutgoingWebhook::OutgoingWebhookError : Could not send webhook to "https://my_app.com/slack/post/channel/channelname". <!DOCTYPE html> 2. The URL may be wrong. Make sure it includes all parts, especially the channel name. According to lookerbot docs, it must take the form:https://APP_DOMAIN/slack/post/channel/channelname/ This content is subject to limited support.

2210

1 year ago

romain.ducarrougeLooker Staff

published in Technical Tips & Tricks

Define a view to create a separate PDT that gets generated each day with a title like “pdt_20190219”Knowledge Drop

Last tested: Feb 19, 2019 Turns out we can use liquid in the create_process part of the derived table for this!We use this in the first sql_step to define the name of the PDT to generate.view: yesterday_data {derived_table: {explore_source: order_items {column: pk2_created_date { field: order_items.created_date}column: pk2_brand { field: products.brand }column: total_orders {field: order_items.count }filters: {field: order_items.created_datevalue: "1 days ago for 1 day"}}}} explore: table_date {}view: table_date {derived_table: {datagroup_trigger: incremental_pdt_default_datagroupcreate_process: {sql_step: CREATE TABLE db_scratch.table_{{'now' | date: "%s" | minus : 86400 | date: "%Y%m%d" }} AS ${yesterday_data.SQL_TABLE_NAME} ;;sql_step: CREATE TABLE ${SQL_TABLE_NAME} AS SELECT 1 as one ;; This content is subject to limited support.

400

1 year ago

romain.ducarrougeLooker Staff

published in Technical Tips & Tricks

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.

5581

7 months ago

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

630

1 year ago

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

3030

1 year ago

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.

4520

1 year ago

Page 63 / 85

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

Sign up

Already have an account? Login

Social Login

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

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.

This file cannot be downloaded

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

The Kitchen Table

If you have a templated filter in a derived table and you reference that table in another derived table, can you pass the filter value through?Knowledge Drop

Can a sql trigger value return more than 1 row, and 1 column?Knowledge Drop

PDT rebuild error: `SQL Error in CREATE TABLE as SELECT: This connection has been closed`Knowledge Drop

"Variable not found" when dynamically querying derived tables using parametersKnowledge Drop

If a user within a group email address unsubscribes from a schedule, will the affect other recipients in this email group?Knowledge Drop

Does Looker support UNION in NDTs?Knowledge Drop

How are scheduled tasks distributed on a cluster?Knowledge Drop

Can I write PDTs to a separate database using the PDT override feature in the connection settings?Knowledge Drop

What's the deal with templated filters and PDTs?Knowledge Drop

BIGQUERY (HowTo): PDT Trigger Value for Business Hours (Specific Hours) Rebuild onlyKnowledge Drop

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

BIGQUERY (HowTo): Can I parameterize a BigQuery connection with user attributes?Knowledge Drop

GIT - Find all commits for a userKnowledge Drop

Lookerbot error "Could not send to Webhook"Knowledge Drop

Define a view to create a separate PDT that gets generated each day with a title like “pdt_20190219”Knowledge Drop

What is the minimum interval (maximum frequency) to rebuild PDTs?Knowledge Drop

How do I round to the nearest 10 in table calc?Knowledge Drop

Max Custom Row Limit for Download from the UIKnowledge Drop

GIT - Find differences between 2 branchesKnowledge Drop

Download Looker Jar ErrorsKnowledge Drop

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

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

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

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

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

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded