Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2114 Topics

Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Is data downloaded from a dashboard in the same timezone as the dashboard setting?Knowledge Drop

Last tested: Aug 16, 2019 If you download from the dashboard menu, the data will be in the same timezone as the dashboard setting.If you download from an individual tile of the dashboard, the data will be in the timezone of the saved query tile. This content is subject to limited support.                  

550
Peggy_Beard
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

What permission is required to create a project?Knowledge Drop

Last tested: Apr 12, 2018The manage_models permission is required to create a project. This permission is not in the Developer permission set for the Developer role. This content is subject to limited support.                   

370
Peggy_Beard
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

What format do state names need to be in for the map_layer_name: us_states to workKnowledge Drop

Last tested: Mar 7, 2019 According to our documentation, "us_states - Accepts full state names and two-letter state abbreviations."In testing, the state name must be in Capitalized format like "Florida". All caps like "FLORIDA" or all lower case like "florida" will not be mapped.A user who had the names in all caps came up with thissql: concat(upper(substring(${TABLE}.state,0,1)),lower(substring(${TABLE}.state,1)));;State values with whitespace surrounding also won't be mapped - you can use a TRIM() function around the state column to ensure this is not an issue. This content is subject to limited support.                  

1080
Peggy_Beard
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Do conditional format fields need to be numeric?Knowledge Drop

Last tested: Apr 5, 2019 Yes! For table visualization conditional formatting, the fields must be numeric.If you have strings or another data type, consider using HTML in your LookML as in this Community article, or go +1 this Feature Request. This content is subject to limited support.                  

10760
Peggy_Beard
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Why are there duplicate groups with a 'shadowing saml config group xx' extension after the group name?Knowledge Drop

Last tested: Feb 1, 2019The SAML group is appended with the 'shadowing saml config group xx' , which will show up in the Group name in the Looker Admin Groups. When a SAML config is updated, the groups are re-mapped. Each update will increment the XX number. As long as there are members in the prior group (because those users have not logged in again), the group will continue to exist. This content is subject to limited support.                  

240
Peggy_Beard
1 year ago
quinn.morrison
Looker Staff
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.                   

300
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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.                 

200
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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.                   

860
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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

460
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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.                   

880
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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.                   

740
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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.                 

230
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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

3150
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
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.                   

390
quinn.morrison
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
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.                   

230
romain.ducarrouge
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.                   

2931
sunnygudVZ
1 year ago
quinn.morrison
Looker Staff
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

2060
quinn.morrison
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
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.                  

8780
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
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.                 

3550
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
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.                   

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

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

Last tested: Sep 6, 2019 5 min is the minimum interval or the maximum frequency, as per our docs, and is also the default frequencyThis interval is set on the database connection panel in the text box labelled PDT and Datagroup Maintenance Schedule as a cron expression  This content is subject to limited support.                  

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

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

Last tested: Jan 21, 2019 if(mod(${count}, 10) >= 5, ceiling(${count}/10)*10, floor(${count}/10)*10)  This content is subject to limited support.                   

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

Max Custom Row Limit for Download from the UIKnowledge Drop

Last tested: Jan 22, 2019 If All Results is unavailable for a query, users can still select the Custom Limits option with a limit of up to 100,000 rows. If you know that your instance has been provisioned to be able to export more than 100,000 rows of unstreamed results, you can further increase that limit with the --max-unstreamed-limit startup option.The recommended max max unstreamed limit depends on the amount of RAM on the machine. Very roughly speaking, assume you need 16GB for a million rows. (Also roughly assume it's linear, thus 8GB for 500K, 32GB for 2 million rows) This content is subject to limited support.                   

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

GIT - Find differences between 2 branchesKnowledge Drop

Last tested: Jan 21, 2019 If you have access to the git repo on command line, you can use `git diff`. The syntax looks like this:git diff <branch 1>..<branch 2> git diff prod..other_branch This is useful when trying to find out what's different between your dev mode and production. This content is subject to limited support.                  

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

Download Looker Jar ErrorsKnowledge Drop

Last tested: Mar 19, 2020 Invalid RequestThis is a catch-all error message for a number of different issues on the download page. Here are some of the main reasons this error can come up:You are using the wrong license key You are using the license key for a Looker-hosted instance (only self-hosted licenses can download jar files) You are using the wrong version name when selecting Specific Version (a valid example is looker-7.4-latest.jar) You are trying to download a jar for an unsupported release (more info on supported releases here)You can find more information on the looker jar file download page here.ERROR 400: Bad RequestOne reason for this issue is the formatting for the wget command when using the API method for downloading the jar files. Following the example from the doc: wget -O- --header=Content-Type:application/json </code>--post-data='{"lic": "YOURLOOKERLICENSE", "email": "contact@email.com", "latest":"specific", </code>"specific":"looker-5.24-latest.jar"}'

951
mconigliaro
5 months 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