Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

User_attributes values in liquid if tagsKnowledge Drop

Last tested: Mar 15, 2021 Reviewing the Liquid variables definitions we can see that the syntax when referencing user attributes is _user_attributes['name_of_attribute']. When used in a liquid if tag evaluation we can use this variable directly. Below is an example of this in practice in a sql_always_where block. The literal example check's if the user's name user attribute is equal to value if so, it then it filters the specified field to be equal to the name user attribute value, otherwise no filtering occurs.sql_always_where:{% if _user_attributes['name'] == 'value' %}${view_name.field_name}={{_user_attributes['name']}}{% else %}1=1{% endif %} ;; This content is subject to limited support.                   

9590
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 to evaluate when a templated filter is not filtered i.e. 1=1Knowledge Drop

Author: Josh Lowin Last tested: Feb 25, 2021 The ProblemIf no value is selected when using a templated filter Looker will default to pass 1=1 in sql, so how can we evaluate this case to get a particular desired output?A SolutionWe can cast the liquid condition to a string and check if it evaluates to the string "1=1" and base the condition on that.IF(CAST({% condition filter_name %} sql_or_lookml_reference {% endcondition %} AS STRING) = "1=1",no_condition_output,{% condition filter_name %} sql_or_lookml_reference {% endcondition %}) This content is subject to limited support.                   

1430
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

Beginner's guide to distributed systems (Clustered/loadbalancing)Knowledge Drop

Last tested: Mar 27, 2019 Imagine that you have been tasked with carrying water from a well to the town. You have to meet the demands of the town with how much water to bring. We'll use this as an analogy to define our terms:High Concurrency: This is purely the amount of volume of water you can carry. You can either increase your concurrency by getting a bigger bucket of water (more powerful server: "vertical scaling") or getting multiple buckets of water (clustered servers: "horizontal scaling"). Both achieve the same end goal, but unfortunately you can't get an infinitely big bucket of water, the largest they sell is 10 gallon buckets. If you want to carry more water, you'll need to get more buckets at this point.High Availability: If you drop a bucket on the way, then there's a crack and all the water spills out. It doesn't matter how big your bucket is, if it has a hole, that water starts to leak. If you want to make sure you don't lose water, you need multiple buckets (a cluster),

170
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 you use AWS Roles to authenticate an S3 schedule?Knowledge Drop

Last tested: Sep 12, 2019No, here is the feature request for comment, please +1 and share your use case This content is subject to limited support.                   

120
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 you make a multi-column index for a PDT?Knowledge Drop

Last tested; Oct 17, 2017 Right now that's not possible natively.One thing you can do is concat two columns and use that as the index, but this will only be used if both of those columns are included in the query.indexes: [col1, col2] makes two separate indexes on the DT. This content is subject to limited support.                   

280
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

CSV downloads with foreign currency renders as £ in Excel ( Euros, British Pounds, etc.)Knowledge Drop

Last tested: Aug 17, 2019This is a bug in how excel handles CSV files. Try downloading the content as XSLX instead! This content is subject to limited support.                   

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

Using liquid to create a custom date rangeKnowledge Drop

Last tested: Jan 27, 2021 This works if a user would like to be able to select a date, and then a number of days they'd like to see before and after the selected date. For example, if I selected 10 and Mar. 14, the resulting date range would be Mar. 10 - Mar. 24.Here's the lookml:parameter: number_of_days_test {type: number} filter: date_selector_test {type: datesql: ${created_date} >= date_add({% date_start date_selector_test %}, interval -{% parameter number_of_days_test %} day)AND${created_date} <= date_add({% date_start date_selector_test %}, interval {% parameter number_of_days_test %} day) ;;} dimension_group: created {type: timetimeframes: [raw,time,date,week,month,quarter,year]sql: ${TABLE}.created_at ;;}  This content is subject to limited support.                   

8760
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

What to put in Domain Whitelist field when setting up User Attribute Git PasswordKnowledge Drop

Last tested: Jun 24, 2020 It's URLs to the github repository!For example: https://github.com/<user>/<project>You may want to use wildcard syntax so you can use multiple projects within the same org, for examplehttps://github.com/<org>/*This domain also needs to be publicly accessible (per this post) It is in the docs -https://docs.looker.com/admin-options/settings/user-attributes but it is a little hard to find...For Bitbucket, the URLs are structured like this:https://user@bitbucket.org/org/repo.gitYou can use this syntax:https://*bitbucket.org/org/* This content is subject to limited support.                   

1450
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

Render error: WebSocket connection is not alive. id: XXKnowledge Drop

Last tested: Jun 18, 2020 A user sees RendererNoRenderFinishedEventReceivedError  when downloading a PDFSo we go to the logs and see this:2020-06-12 11:45:52.426 -0500 [WARN|00872|render] :: Chromium console output: WebSocket connection is not alive. id: 33 2020-06-12 11:45:52.433 -0500 [INFO|00872|render] :: Chromium Render Failed: WebSocket connection is not alive. id: 33 What is a Websocket?Chromium is headless (it doesn't have a graphical display) and websockets are how Looker controls ChromiumIf the connection is not alive it's likely that Chromium crashed or became unresponsive. This often happens when Chromium does not have enough available memory to run properly. On the Looker host, this can be caused by too much memory being allocated to the Looker process and not enough left over for Chrome.If this happens often, the host will need more memory or Looker will need to be allocated less memory.If your instance is Looker-hosted in Kubernetes, the instance will need a higher memor

5960
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

What is an Instance Slug?Knowledge Drop

Last tested: May 1, 2020 It's an identifier for an instance. Instance slug is the combination ofstable_root, which is the path to the directory where Looker's JAR file is running mac_address of the server on which Looker is running ip_address of the server on which Looker is running The license_keyThough it is supposed to be stable, it is not unusual (for self-hosted instances) for the instance slug to change as server/hardware changes are made, so over time, a license can accumulate multiple instance slugs.How many instance slug should a cluster have?Q: Is the instance slug the same among all instances within a cluster?A: Yes, an "instance" can be clustered or single. A "node" is always single.Q: If i have two clusters with the same license key, is the instance slug unique for each cluster?A: Yes, it's supposed to be.In relationship to PDTs:With our recent changes to PDT naming / stable PDTs - is it still a bad idea to share a scratch dataset among multiple instances?The best practice

3722
S
1 month ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How do you respecify `mount_nfs` options for a clustered instance?Knowledge Drop

Last tested: Jan 21, 2019 In general, these are the steps:stop looker umount <shared-storage-dir> mount_nfs -o vers=4 nolock <shared-storage-dir> start lookerYou should probably make the changes in /etc/fstab so they're permanent. Then, when you go to remount, you can just do mount /lookershare to pick up the changes. This content is subject to limited support.                   

20
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

What happens when you clear cache & refresh on a clustered instance?Knowledge Drop

Last tested: Sep 19, 2017 First things first: See this post for information on clustered instance caching behavior.If you clear cache and refresh a query that happens to be cached on node A, Looker will make sure to clear cache on node B so that queries run on that node don't pull from cache. This content is subject to limited support.                   

350
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

SQL Pattern: How can I count the number of comma separated values in a string?Knowledge Drop

Last tested: Aug 16, 2017 This might not be a common request, but the method to do so is below:SELECT (LENGTH(Col2) - LENGTH(REPLACE(Col2,",","")) + 1) AS MyCol2CountFROM MyTable Basically, you replace all occurrences of , with an empty string "", then subtract its LENGTH from the LENGTH of the unadulterated string, which gives you the number of , characters. This doesn't tell you the number of comma separates values, however. If you have four comma separated values, there will be three commas. In order to get the number of comma separated values, simply add 1.An example in Impala syntax is below:Source: https://stackoverflow.com/questions/19915746/count-the-number-of-elements-in-a-comma-separated-string-in-oracle This content is subject to limited support.                   

145911
C
3 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Does Looker support HA (Highly Available) databases?Knowledge Drop

Last tested: Jan 21, 2019 First things first: High availability databases use an architecture that is designed to continue to function normally even when there are hardware or network failures within the system. They have emerged as an alternative to traditional relational databases, which are generally built to be deployed on a single server and rely on a master/replica architecture to provide availability.High availability databases are built to eliminate single points of failure and are optimized to ensure that the end user does not experience an interruption in service or a degradation in user experience when hardware or networks fail. This is often accomplished through a masterless architecture that uses clustering, in which multiple servers are grouped together. Because there is no master, any server within a cluster can respond to read or write requests. Data is then replicated across all servers in the cluster, providing system redundancy and minimizing the possibility of downt

1070
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

What to do if you try to calculate the distance between identical locations and get ACOS values that are out of the [-1,1] range?Knowledge Drop

Last tested: Sep 14, 2017 You can check for those coordinates that are identical, then add a small value to one of them, like so:dimension: location { type: location sql_latitude: case when ${TABLE}.a_lat = ${TABLE}.b_lat then ${TABLE}.a_lat + .00005 else ${TABLE}.a_lat end;; sql_longitude: case when ${TABLE}.a_lon = ${TABLE}.b_lon then ${TABLE}.a_lon + .00005 else ${TABLE}.a_lon end;; }  This avoids calculation between identical locations by making them un-identical.Another method is calculating the distance manually using this formula and incorporating CASE WHEN logic to convert ACOS values > 1 or < -1 to null, like so: dimension: distance { type: number value_format: "0.00\ \k\m" sql: 6371 *acos( case when abs(cos(radians(${latitude}))* cos(radians(${transaction_fact.latitude}))* cos(radians(${longitude}) - radians(${transaction_fact.longitude})) + sin(radians(${latitude})) * sin(radians(${transaction_fact.latitude}))) <= 1 then cos(radians(${latitude})) * cos(radians(${tra

1840
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

BIGQUERY (HowTo): BigQuery Cluster and Partition Best PracticesKnowledge Drop

Last tested: Sep 21, 2018 Default to INNER join If LEFT is required, denormalize, nest, or figure out a way to push filter to fact table Make sure that Looker isn't overriding your cluster ON clause Clustering pushes predicates, partitioning does notSee the below example for inner join vs left. In this case the table is clustered by a.cust_id. We don't filter on a.cust_id, as the user is interacting with the b.cust_name. A left join will still do a full table scan.NO FILTER - Query complete (21.453 sec elapsed, 42.04 GB processed)INNER - Query complete (3.783 sec elapsed, 1.78 GB processed)LEFT - Query complete (11.192 sec elapsed, 42.04 GB processed) SELECT  a.prod_skid,  COUNT(*)FROM  ls.POSDS_WKLY_FCT aLEFT JOIN  ls.POSDS_CUST_DIM_enhanced bON  a.cust_id = b.cust_idLEFT JOIN  ls.POSDS_PROD_DIM AS posds_prod_dimON  a.CUST_ID = posds_prod_dim.CUST_ID  AND a.PROD_SKID = posds_prod_dim.PROD_SKIDWHERE  b.cust_name = 'Demo Customer US - P&G Only'GROUP BY  1Another example. In this cas

3440
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

Scheduler PrioritiesKnowledge Drop

Last tested: May 10, 2019 There are three types of schedule threads that determine schedule order:Scheduler jobs - default of 10 at a time Renderer jobs - default of 2 at a time Unlimited scheduler jobs - default of 3 at a timeThe scheduler, unlimited scheduler and renderer all have their own queue that is first-in first out. However, since the scheduler will need to wait on the renderer to finish for certain jobs (since the render stage is part of the execution of the schedule) there can be situations where render jobs back up before all 10 scheduler jobs back up, causing schedules to execute slightly out of order depending on the format. Unlimited jobs will have to wait for the previous unlimited job to complete.Separately, any one-time "Send" or "Test" will be given a higher priority than regularly scheduled jobs and will get to skip the line for whatever queue they are in. Currently, there is no way to change priority. This content is subject to limited support.                  

990
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 are Out-of-Date PDTs Managed?Knowledge Drop

Last tested: May 22, 2018 Persistent Derived Tables are automatically reaped by Looker based on the following rules:If refresh is tied to a particular time (e.g. set via 'persist for'), then the PDT will be reaped from the database 1 hour after the persistence window expires.PDTs in Dev-Mode are treated as persist-for 24 hours until they are pushed to Production.If refresh is tied to a SQL Trigger Value, the PDT will continue to be built as long as it exists in a Production model. They will be reaped 1 hour after the associated Production model is deleted.Datagroups behave based on the sql_trigger within them. This content is subject to limited support.                  

871
M
6 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Can I schedule a dashboard for last day of the month? What happens if I schedule on the 31st for months without 31 days?Knowledge Drop

Last tested: Sep 17, 2019 If you schedule on the 31st, it will not run on months without 31 days. To get the last day of the month there are several workarounds:Use custom filters as in this Help Center article to force the Look to have no results except for on the last day of month (Does not work for dashboards). Use a Datagroup trigger instead, using SQL similar to the help center article.Feature request here for comment!: https://feedback.us.pendo.io/app/#/case/59784?section=requestsAn example of using the sql_trigger with a Datagroup to fire a schedule on the last day of the month would be: SELECT DATE_TRUNC('m',DATEADD(d,1,CURRENT_DATE))​ This content is subject to limited support.                   

1460
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

Why are some emails greyed out in the admin->users page?Knowledge Drop

Last tested: Apr 26, 2019 A greyed out *email credential* means one of two things:1. the user is disabled2. external auth is being used and the user does not have the login_special_email permissionAnything related to the external auth system would be shown using a credential of that system ie a SAML credential, etcExample:Looker is using email/passwordUser gets created, has email credentialLooker switches to external auth- option 1: user has login_special_email, email credential remains valid (black), user logs in using external auth, user now has two valid credentials, email credential and external auth credential (both are black)- option 2: user does not have login_special_email, email credential is no longer valid (greyed out), user must use external auth system, user logs in through external auth and gets an external auth credential (black), the email credential though used to merge by during the user's first external auth login remains greyed out due to lack of permission This con

660
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

Performance Diagnosis: Network UnreliabilityKnowledge Drop

Last tested: Nov 2, 2018 In order to use Looker, end users need to make requests from their client browser to the Looker server. The Looker server usually lives on another machine than the one end users are requesting from, so these requests need to traverse a network (or networks).A network is a collection of connected machines capable of transmitting data amongst themselves. If a network has a route to another network by means of a router and routing table, machines in one network can transmit data to and from machines in another network. The internet is just a massive conglomeration of networks.If I'm at work within my office's network and I'm requesting resources from a Looker server hosted in Looker's VPC, I'm traversing multiple networks. In other words, the packets transmitted to and from our respective machines need to jump through multiple networks on their journey. When troubleshooting network unreliability, it's usually helpful to determine:what network devices are between t

550
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

Does Looker have native currency conversion?Knowledge Drop

Last tested: Dec 5, 2019 The ProblemI have currency data in my database, for example the cost of items. I would like to convert this to different currencies when I explore the data in Looker.For example, maybe I have item prices saved in United States dollars ($) but I would like to see them in Japanese yen (¥).ExplanationLooker doesn't provide any currency conversion at runtime. This would be a feature request.WorkaroundFor a one-off request, you can hard-code the conversion into the LookML, as arithmetic in a number dimension.dimension: cost_yen {type: numbersql: ${cost_usd} * 108.134;;value_format: "JP¥ 0"} (The value_format was built by referencing these docs and the conversion was found with this website.)If you want something more dynamic and up-to-date, you'll need to build currency conversion into your ETL. That is, extract the current currency conversion into your database so that you can treat it as a dimension. This content is subject to limited support.                  

4910
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 remove LookML elements like `case` in an extending view?Knowledge Drop

Last tested: Mar 15, 2021 No. You can only add new LookML elements in an extending view but you cannot remove existing onesFor example, if we ave the following in our original/extended view:dimension: case_when_test {type: stringcase: {when: {sql: ${TABLE}.genre = "Drama" ;;label: "Case1"}when: {sql: ${TABLE}.genre = "Comedy" ;;label: "Case2"}when: {sql: ${TABLE}.genre = "Action" ;;label: "Case3"}else: "Other"}} And attempt the following in order to reduce the number of the LookML case whens in the extending view, this is not going to work - the dimension is still going to have all the case whens from the original view.dimension: case_when_test { type: string case: { when: { sql: ${TABLE}.genre = "Drama" ;; label: "Case1" } else: "Other" } } As a workaround, we would want to use SQL CASE WHEN in the sql parameter in both the original view and the extending view. This content is subject to limited support.                   

570
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

Troubleshooting Private Embedding + SAMLKnowledge Drop

Last tested: Oct 7, 2020 There are a number of errors and settings to be aware of when using SAML authentication in an iframe for private embedding. You can test things out using a simple online HTML editor, using the following iframe code:<iframe src="https://instance.looker.com/embed/dashboards/[id]" height="500" width="500"></iframe> 401: You are not authenticated to view this pageIf you are not already logged into Looker, you will get this page by default when you attempt to access the embed URL through the iframe instead of getting the Authenticate button. To avoid this error, simply add allow_login_screen=true to the URL:<iframe src="https://instance.looker.com/embed/dashboards/[id]?allow_login_screen=true" height="500" width="500"></iframe>Note that even if you are using the sso_bypass_login_page license feature to skip the login page, you will still need to add allow_login_screen=true to avoid an error. You have to allow the login screen before you can b

9451
LucianaPadua
8 months ago
Lesley_Rahbany
Community Admin
Lesley_RahbanyCommunity Admin
published in Common Account Questions

How do I get on an open Looker Beta?Knowledge Drop

In order to get on an open beta, go to go to the General Section on your Looker admin panel, and then to the Labs Page. There you can choose to enable the features that you desire. Not all users have the permissions to accomplish this, soyou may need your Looker admin or technical contact to turn this on for you. If you want to read more about it, check out our documentation on Betas!Questions on this process? Comment Below!

320
Lesley_Rahbany
Community Admin
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