Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,744 Topics
  • 373 Replies

1744 Topics

Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to generate LookML for all datasets for BigQuery connectionKnowledge Drop

Last tested: Apr 23, 2020 The ProblemLooker only generates one view which is specified in connection setting as dataset even we select "All tables".  A SolutionListing all datasets ( comma-separated) in schema section in create project. This content is subject to limited support.                  

370
Pieter_DeVries
Looker Staff
1 year ago
rebecca
Looker Staff
rebeccaLooker Staff
published in Technical Tips & Tricks

Troubleshooting a 401 "Unauthorized" error for API callsKnowledge Drop

Last tested: Oct 1, 2019 A 401 HTTP response indicates a problem with the authentication credentials used to make the API call. This is pretty broad, but here are some things you can check:That either the Client ID and Secret are correct, or the token is correct (the Client ID and Secret are used to get the token, but once you have the token, you don't need the ID and Secret) Whether the user making the call is an Admin, or has the required permissions to make the call That the URL doesn't end in a slash (for example, api/3.0/lookml_models works, but api/3.0/lookml_models/ does not) In Postman, if you make a call using http without the port number, you'll get a 401 error. You'll need to use https AND include the port number to avoid errors. For example: https://your_company.looker.com:19999/api/3.0/login If you are seeing a 401 on the API docs prior to logging in or making calls, it may be that you have "Require API login to see API docs" selected. You should still be able to log in f

14800
rebecca
Looker Staff
1 year ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

What happens to non-Looker tables that someone puts into the looker_scratch schema?Knowledge Drop

Last tested: Dec 18, 2017Looker eventually drops them if they start with lr$, lc$ or one of the legacy prefixes. This is how Looker identifies tables it manages.Other tables will presumably be ignored.  This content is subject to limited support.                  

120
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

How can I avoid a sql error when no value is selected for a parameter filter?Knowledge Drop

Last tested: May 29, 2020 Unlike templated filters, which will default to 1=1 when no filter value is selected, a parameter with no filter value selected will cause a sql error. If you need to use parameters instead of templated filters, and you want to avoid this error when no filter value is selected, we can add this to our sql:{% if view_name.parameter_name._is_filtered %} [parameter filter statement] {% else %} 1=1 {% endif %}  This content is subject to limited support.                  

630
quinn.morrison
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to create a User Activation Report?Knowledge Drop

Last tested: Nov 28, 2019 The ProblemTo look at three KPIs,1. Active Users (Activity within last 30 days)2. Retained Active Users3. Re-Activated Users (Activity within last 30 days, but no activity from 30 - 60 days, but activity from 90 days +)A SolutionIn order to do this, we will need some date manipulation and some windows functions, meaning we will use a derived table.Here is an idea of how to create this, using Standard SQL (Bigquery) Required data is User ID connected to an Activity Date (basically, what date they performed an activity on.) We will create a derived table like below, only needing to change the parts in [] with your data set. WITH daily_use AS (SELECT[MEMBER_ACCOUNT_ID] as user_id, cast(TIMESTAMP_TRUNC(timestamp_millis(TIMESTAMP_IN_HOUR),day) as date) as activity_dateFROM [DATABASE.TABLE]GROUP BY 1, 2)SELECTdaily_use.user_id, wd.date as date, CASE WHEN MIN( DATE_DIFF(wd.date, daily_use.activity_date, day) ) < 30 THEN "yes"ELSE "no" END as within_30, CASE WHEN M

520
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to create a First and Last date using Custom FieldsKnowledge Drop

Last tested: Jan 8, 2020 The ProblemSay you want to see a user's first and last query dates. This could be pretty difficult because it is in system_activity so you can't edit the lookml, and you can't easily min/max dates.A SolutionWe will use a combination of Custom Dimensions, Custom Measure and Table Calcs to achieve this.Here is a picture of what that looks like.Verify that you have Custom Fields enabled. Open an explore that has the data you are looking for. Set up the explore to have your Main Dimension (in this case we are using User ID). Everything else will be custom. Create the custom dimension, this will create a number that shows the difference in days between the date we are interested in and today.diff_days(${history.created_date},now()) Create the Custom Measures. 1. Day_diff_1 (this is the last_day) 2. Day_diff_2 (this is the first day) Same as above, just change measure type from min to max. Create the table calcs that show the dates. 1. Last_dateadd_days(${day_diff_1}

5580
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

Can I use whitespace in filters?Knowledge Drop

Last tested: Mar 3, 2020 Starting in 7.4, YES!Say you have this table with a combination of leading and trailing spaces:In the past, Looker would trim all trailing and leading spaces from filter values, even if those spaces carried meaning for the customer's dataset. Now, you can include whitespaces in filter values without Looker trimming them!Here are some examples:Matches advanced works too! The important thing to remember is the ^ character is needed to escape the whitespace character(s), like:Please NOTE: With great power comes great responsibility. Since Looker no longer trims trailing and leading whitespaces, it's possible to get unexpected results if you accidentally include a whitespace in your filter value. This content is subject to limited support.                  

1360
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

String-type User Attributes and % WildcardsKnowledge Drop

Last tested: May 11, 2020 Starting in Looker 7.8 (and backport patches for 7.4 and 7.6), "string" type User Attributes will no longer support the "%" wildcard character in group or user values. Any "%" characters in a "string" type User Attribute's group or user values will be treated as regular, non wildcard characters. Note, this may impact the results of queries using such User Attributes in filters (specifically access_filters), as the "%" character will go from "matches anything" to "equals '%'". If "%" wildcard characters are needed in string-based User Attributes, please use the "string (advanced)" type as our docs recommend. This content is subject to limited support.                  

4530
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

ERROR : Internal database configuration issueKnowledge Drop

Last tested: Nov 11, 2020 Starting in 7.20, Looker now validates the dialect and version of the internal database during startup. If something other than the default in-memory Hypersql or a Mysql v5.7.x database is used as the internal database, Looker logs an error message before performing any startup migrations. There are two possible error messages:"The Looker internal database is not the default in-memory HyperSQL database or a MySQL 5.7.x database" This is thrown when you are using a supported dialect (i.e. hypersql by default, or mysql via looker-db.yml) but not a supported version (ex: mysql v8.0.1). "{dialect name} is not a valid dialect option for the Looker internal database" This is thrown when you aren't using a supported dialect in looker-db.yml (ex: aurora, mariadb, etc.). Because Looker is developed and tested around hypersql and mysql v5.7.x databases, those are the only two supported configurations. Given our lack of testing beyond hypersql and mysql v5.7.x, any dev

700
Pieter_DeVries
Looker Staff
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Can I track slack actions in system activity?Knowledge Drop

Last tested: Jul 21, 2020 Yes! With the event attribute explore.Sample URL to share:/explore/system__activity/event_attribute?fields=event.created_time,user.name,event.name,event.category,event_attribute.name,event_attribute.value&f[event.name]=%25slack%25&sorts=event.created_time+desc&limit=500&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22event.name%22%3A%5B%7B%22type%22%3A%22contains%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22slack%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded This content is subject to limited support.                  

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

How can I use a filter to exclude dates?Knowledge Drop

Last tested: Oct 7, 2019 The date options for a filter don't have one for "is not on the day". There is a feature request for that on our feedback system: Be able to filter for not a certain date (exclude a date). We can use a custom filter with this syntax:${orders.created_date} != date(2019,05,03)another option is:NOT matches_filter(${orders.created_date}, '2019/05/09')and for dashboards or users who can't use a custom filter there is a workaround to use this filter declaration:filter: exclude_dates {description: "enter dates to exclude in the format yyyy-mm-dd"type: stringsql: NOT {%condition exclude_dates %} ${created_date} {% endcondition %} ;;} This content is subject to limited support.                  

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

Will value_format affect JSON results in the API?Knowledge Drop

Last tested: Oct 18, 2018 Yes!Call run_query or run_inline_query setting the apply_formatting param to true and this will pick up the value_format option from the lookML.  This content is subject to limited support.                  

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

Can I include a comment in a custom field?Knowledge Drop

Last tested: Nov 20, 2019 Yes! A comment line begins with a #, just like in a table calculation This content is subject to limited support.                  

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

How to find all models used by a dashboardKnowledge Drop

Last tested: Mar 26, 2020 The problem is that a user can't see the data on a dashboard and we don't know which models they need permissions for.We can use this query in system activity if we know the dashboard ID:/explore/system__activity/history?fields=history.real_dash_id,query.model,query.count&f[history.real_dash_id]=1845&sorts=history.real_dash_id+desc&limit=500&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22history.real_dash_id%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%221845%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded  This content is subject to limited support.                  

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

Are Amazon S3 bucket credentials encrypted when Looker stores them?Knowledge Drop

Last tested: Feb 4, 2021Yes! S3 creds are encrypted in the scheduled_plan_destination table. This content is subject to limited support.                  

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

Why can't I save a Look? The Save button is grayed outKnowledge Drop

Last tested: May 4, 2020 Does the Look title have a % character in it? When a Look containing a % character in its title (let's call it "% Look"), where if the % were interpreted as a wildcard, the Look's name would match an existing Look (let's call it "named Look"), the % Look cannot have edits to it saved, as the save button will not activate on the % Look unless the title is changed, or the name of the named Look is changed to not wildcard match the % Look.Edit the Look's title to not match another Look, such as removing the percent symbol, or adding additional characters to the Look's name. (as an example, updating "Waitlist Funnel (%)" to "Waitlist Funnel (percent)" or "Waitlist Funnel - (%)". This content is subject to limited support.                  

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

What happens if the master git branch is protected on the git side with 'require pull requests'?Knowledge Drop

Last tested: Dec 11, 2020If the 'include administrators' box is not also checked, then Looker developers are still allowed to deploy changes to production as usual.If the 'include administrators' box is also checked, then an error will occur if developers try to deploy changes to productionThe recommendation is to enable pull requests on the Looker side. This content is subject to limited support.                  

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

Why does my donut multiple chart tooltip say 'undefined' ?Knowledge Drop

Last tested: Mar 22, 2019 A donut multiple chart can be created from only measures (no dimensions). In this case, the tooltip will show 'undefined'.A simple workaround is to create a custom dimension or table calculation) with and empty string (" " or "") and add that to the explore. Then this empty string will show up in the tooltip instead of "undefined". This content is subject to limited support.                  

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

Does Instant Dashboards work in embed?Knowledge Drop

Last tested: Nov 16, 2020No, it doesn't. "Fast cached results" do not work in embed because of differences in drill links that aren’t considered in the cache key. This content is subject to limited support.                  

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

How do I create a custom filter for "today or 2 weeks ago?" to show only 2 dates in the explore for comparisonKnowledge Drop

Last tested: May 6, 2020 First we have to construct the date for 'today', and then use an OR with add_days on that 'today' date It ends up like this: ${history.created_date}=date(extract_years(now()),extract_months(now()),extract_days(now())) OR ${history.created_date}=add_days(-14,date(extract_years(now()),extract_months(now()),extract_days(now())))​ This content is subject to limited support.                  

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

Does system activity track changes made to user attribute values?Knowledge Drop

Last tested: Jan 12, 2021No, it does not. If a user is edited to update the value of a user attribute, there is somewhat generic record added to the 'event' table for the user, and also the user's group being updated, as it appears that all of the user's values are updated in the internal database when a user definition is saved. This content is subject to limited support.                  

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

why would a custom SMTP test button work but schedules fail?Knowledge Drop

Last tested: Sep 19, 2019 1) The message failed to be delivered to the SMTP serverThere have been a couple incidents in the past where using the "pretty" email domain User Name <user@domain.com>  instead of simply  `user@domain.com` has caused the "Test" button to work in the SMTP test page, but caused various SMTP errors when actually sending contentThe admin should try using just user@domain.com instead.2) The relay server or receiver of the email is blocking the email from going throughThere are some situations where the SMTP server itself or spam filters will block certain emails differently, so even though the test email may have gone through, the scheduled email with attachments and a different email body could be causing issues.In this case, you would need to look at your SMTP server's logs to see if the email got to the SMTP server, and if so if it was blocked/bounced or sent on to the email to the recipient. This content is subject to limited support.                  

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

Can I use Amazon Aurora as our backend database instead of MySQL?Knowledge Drop

Last tested: Oct 8, 2019 No, this isn't supported but you can share your use case with our product team via Pendo! This content is subject to limited support.                  

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

Can you use both link and html in a dimension?Knowledge Drop

Last tested: Jan 14, 2021 The goal is to format the text, like with color or underline, and also have a link parameter with multiple drilldown options.This works:dimension: status {type: stringhtml: <p style="color: black; background-color: lightblue; font-size:100%; text-align:center">{{ linked_value }}</p>;;link: {label: "testit"url: "https://lookerv720.dev.looker.com/looks/20"}sql: ${TABLE}.status ;; This content is subject to limited support.                  

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

How can I create a filter or SQL WHERE clause that a user can conditionally apply?Knowledge Drop

Last tested: Aug 16, 2019 If your are asking how you can code an SQL WHERE clause that a user can conditionally apply, we can do this with a combination of a yesno parameter and an sql_always_where on the explore.The use case is that it is a complex filter and you don't want the user to need to use custom filter syntax.Example: In this case, you want to have a filter that looks for aircraft model names that contain the manufacturer name. If the user selects 'yes' the filter will be applied to the WHERE clause. If they select 'no' or don't select the filter, the WHERE clause will have 1=1 (always true). parameter: turn_on_filter{type: yesno} explore: aircraft_models {sql_always_where: {% if aircraft_models.turn_on_filter._parameter_value == "true" %} ${model} LIKE '%'||${manufacturer}||'%'{% else %} 1=1{% endif %};;} This content is subject to limited support.                  

2740
Peggy_Beard
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