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
  • 407 Replies

1744 Topics

jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Azure SAML: AADSTS50011: The reply url specified in the request does not match the reply urls configured for the applicationKnowledge Drop

Last tested: Aug 19, 2020 Every case of this we have documented is due to some attribute of the /samlcallback url they're inputting into Azure being incorrect. Specifically:Not using the callback url, eg input https://instance.looker.com instead of https://instance.looker.com/samlcallback There is a different host url in Admin/Settings than what is inputting into Azure (if for example, they have a vanity URL) This content is subject to limited support.                  

1170
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

How to dimension fill a string? Or how to have a dimension value that isn't in my datasetKnowledge Drop

Last tested: Mar 20, 2020 Easy, but more brittle way: LookML caseFor example, this code:dimension: status {type: stringcase: when: { sql:${TABLE}.status = "complete" ;; label:"complete" }when: { sql:${TABLE}.status = "pending" ;; label:"pending" }when: { sql:${TABLE}.status = "cancelled" ;; label:"cancelled" }when: { sql:${TABLE}.status = "other" ;; label:"other" }} Outputs this:Even though only complete, pending, and cancelled are actually in the dataset.Less brittle, more nuanced way: Derived Table + Join LogicCreate a derived table view with the desired strings like so:view: glossary {derived_table: {sql: select 'complete' as xunion allselect 'pending' as xunion allselect 'cancelled' as xunion allselect 'other' as x ;;} dimension: glos {sql: ${TABLE}.x} Then, use this glossary view as the base view for your explores:explore: my_explore {view_name: glossaryjoin: viewa {type: full_outersql_on: ${glossary.glos} = ${viewa.status} ;;}...}  This content is subject to limited support.     

1360
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

How do I get dates for this month up to yesterday?Knowledge Drop

Last tested: Nov 25, 2019 Create this in a table calc. It should result in yes/no values and you can filter out the nos.trunc_months(${date_column}) = trunc_months(now()) AND extract_days(now()) > extract_days(${date_column}) This content is subject to limited support.                  

2310
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

How do I hide one value label?Knowledge Drop

Last tested: Apr 24, 2020 Let's say you have two or more measures, and want value_labels in your chart for only one but not the other?Answer:Method1 (Pre-7.0): Input transparent in the value label colors field. You'll also need to input another color for the other measure, or else both measure labels will be invisible.Method2: Copy the blank character from this link and paste it into the 'Series' Value label Transparent shown here working on 7.20.41 This content is subject to limited support.                  

2170
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Why am I seeing kill_looker_support_sessions in logsKnowledge Drop

Last tested: Jan 24, 2020 This may seem disturbing, but is pretty innocuous.There is task that runs to see if an admin has disabled support access. This is logged as kill_looker_support_sessions and doesn't actually do anything unless support access has actually been disabled. If support access has been disabled, it will log Killing all looker auth sessions. Disabling looker auth access. This content is subject to limited support.                  

310
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

What happens if you delete the Looker jar, while it is running?Knowledge Drop

Last tested: Jun 26, 2020 If on Linux:In layman's terms, you're ok. In Linux, files aren't actually deleted if they're still in use. Keep a backup of the jar, just in case, but you should be fine.Per StackOverflow:a file isn't really deleted until all applications that have it opened close it. The file will be gone from the directory listing, but it still exists and can be read (and even written!) by any application with a valid file descriptor open.Per Micah:When you "rm looker.jar", the kernel is ultimately unlinking that filename from the disk block. The running looker process will have already mapped the file into memory and will still have a reference to it This content is subject to limited support.                  

180
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Can I still create users in Looker if I'm using LDAP/SAML?Knowledge Drop

Last tested: Jul 22, 2020 Yes!The user will be unable to login, however, unless they have the login_special_email permission, or are granted access on the LDAP/SAML side. This content is subject to limited support.                  

120
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Create random sampling with table calcs (tiers, Excel RANK function)Knowledge Drop

Last tested: Sep 27, 2019 Let's say you have an explore, and you want a table calc that spits out 1,2,3,4,5 in a pattern (ie, every 5th row will be 5, the row after 1, then 2, etc).You can use this as a way of emulating random sampling, and it's fairly trivial to do in Excel with the RANK() function.We can do this with by nesting some table calcs!if(contains(to_string(row()/5), ".2"), 1, if(contains(to_string(row()/5),".4"),2, if(contains(to_string(row()/5), ".6"),3,if(contains(to_string(row()/5), ".8"),4,5 )))) We can extend this out for as many (or fewer) 'tiers' the user wants in their table.At a high level, we can always predict the decimal output of a whole_number / x. 1/5 will always end in .2, 2/5 will always end in .4, 3/5 in .6, etc. This works with any combination of whole_number and x (1/3 = .33, 2/3 = .66, etc).The table calc just grabs the row number, divides by number of rows in the pattern, then searches for the decimal we know should be in the pattern at that position. 

4030
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Python-SDK: Why isn't the returned object in JSON?Knowledge Drop

Last tested: Jan 24, 2020 Use file.attribute to access attributes of the object (eg, file.url or file.id).Alternately, use the hidden attribute __dict__ to return the object in json format (eg, file.__dict__ ) This content is subject to limited support.                  

630
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Label dates by an increment (dates since x)Knowledge Drop

Last tested: Oct 10, 2019 CASEwhen ROUND(datediff / increment, 0) <= 1 then "label 1" else concat( "label 2", cast(round(datediff / increment, 0) as string))end  This content is subject to limited support.                  

120
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Why can't I download `content` as `format`?Knowledge Drop

Last tested: Oct 11, 2019 All content in Looker has slightly different allowed download formats. All downloads are contingent on having appropriate permissions. These are distinct from 'sending' and 'scheduling' content.Dashboards:PDF CSVLooks:Text CSV / Excel JSON HTML Markdown PNGExplores:Text CSV / Excel JSON HTML Markdown PNGMerged Queries:Can only be downloaded after saving to a dashboard. Must download the entire dashboard This content is subject to limited support.                  

690
jamesnestler
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

Why is the Update Dependency Button not showing up in the Main Button after changes in remote projectKnowledge Drop

Last tested: Jul 23, 2020 The ProblemAfter commits are made to a remote project, users ask why they don't get a prompt to update dependencies in the local project where the remote project was imported.A SolutionClick the button to bring in the remote project files. If this is the first remote dependency you’ve added to your project, updating dependencies will also prompt Looker to create a manifest lock file. Looker uses the manifest lock file to track the version of the remote project(s).If you specify a Git branch or a Git release tag in the ref subparameter, Looker will check for new commits whenever the Looker IDE refreshes, which happens when a Looker developer goes into Development Mode, performs a Git action in the IDE, or refreshes the browser.If there are new commits, Looker will display the Update Dependencies button in the IDE so you can bring in the latest remote project files. Once you have the latest files, you can validate your LookML to verify that all of your project’s

1860
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

Online sandboxes and handy testing tools for SQL databases, HTML, Javascript, CSS, ...Knowledge Drop

Last tested: May 1, 2020 Here is a list of some online Sandboxes and handy testing tools for SQL databases, HTML, Javascript, CSS, and several other programming languages1 RexTester Sandbox online for several programming languages online https://rextester.com/l/postgresql_online_compiler 2 CodeSandbox An Online IDE for Rapid Web Development https://codesandbox.io/index2 3 DB Fiddle UK Test DB2, MariaDB, Postgresql, SQLServer, SQLite, Oracle, MySQL in browser free https://dbfiddle.uk/ 4 SQL Fiddle A tool for easy online testing and sharing of database problems and their solutions. http://sqlfiddle.com/ 5 JS Fiddle New fiddle or a Boilerplate for HTML/Javascript/CSS test online https://jsfiddle.net/ 6 JS Bin HTML/Javascript/CSS test online https://jsbin.com/?html,output 7 W3schools W3Schools is a web developers site, with tutorials and references on web development languages https://www.w3schools.com/ 8 PG Exercises PostgreSQL Exercise

1170
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

"Error: Must query at least one dimension or measure" (Inline_Query API Call)Knowledge Drop

Last tested: Feb 17, 2020 Recorded Use Case:User running Looker V6.24.30 was trying to get pull some details about a specific dashboard via `run_inline_query` API call in their program but ran into errors in one of the cases. Receiving "Error: Must query at least one dimension or measure" as a response.API Call:Call Type: SDK Endpoint: run_inline_query(result_format, body)More: DocsSample Logic expected in development IDE of the client (Outside of Looker e.g. Python, Ruby, NodeJS...):# instantiate Look API clientqueryAPI = looker.QueryApi(client)body = {"model":"thelook","view":"inventory_items","fields":["category.name","inventory_items.days_in_inventory_tier","products.count"],"filters":{"category.name":"socks"},"sorts":["products.count desc 0"],"limit":"500","query_timezone":"America/Los_Angeles"}resp = queryAPI.run_inline_query("json",body) The EXPERIENCERequest Body: body = {"model": "dashboard_login_model,"view": "view_name_1","fields": ["view_name_2.user_count_red","view_name_2.

5350
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

How to set up a custom Gmail/Google mail SMTP settings (Extended Guide)Knowledge Drop

Last tested: Jul 12, 2020 The ProblemUsers often come looking to change from Looker’s email service to a custom mail service. Gmail is a common choice but users run into errors during setup because certain steps haven't been followed on the Gmail/Google account end.A broad categorisation would be Google accounts intended to be used for the custom mail service could:An Account with TWO-FACTOR AUTHENTICATION (2FA) enabled. An Account without 2FA enabled.A SolutionMethod 1: Using an App Passwordwith 2FA enabled Method 2: Allow Less Secure Apps with no 2FA enabledScreenshots have been included below to share with you as a guideMethod 1: App PasswordIf 2-step verification has been enabled on the account, you can generate an app password.Follow these steps:Browse to the Security Page, notice 2FA is enabled Or go straight to the “App passwords” page. Click “Select app” and choose the app you’re using. Click “Select device” and choose the device you’re using (In this case it would be the Looke

3640
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

Sorting Row-limited Table calculation based on few Pivot values (Workaround)Knowledge Drop

Last tested: Sep 15, 2020 The ProblemThere are some specific scenarios when you can’t sort a table calculation:Calculations that hit a row limit, as described here. Sorting a dimension or measure after you’ve already sorted by a table calculation, as described here. Sorting a table calculation that makes use of an offset, as described here. The issue with sorting on a table calculation when a limit is reachedWhen there is no limit preventing the Table Calculation sortA Solution that could work for some uses casesThe workaround involves the use of filtered measures to replace the pivoted dimension and associated measuresThis is the conversion to make use of filtered measures (I kept the sorting based on the dimension to show the values of the calculation are the same)The equivalent table based on a filtered measure with the desired sort available on the measure and matching the table calculation values & sorting  # mySQL Database used was used here# Adjust the sql logic to suit your

3510
ismail
Looker Staff
1 year ago
iqbal.sandhu
Looker Staff
iqbal.sandhuLooker Staff
published in Technical Tips & Tricks

Methods for case insensitive string searchesKnowledge Drop

Last tested: Mar 31, 2021 You may run into a situation where you need to search for a string inside of a table calculation or a custom field, which means that you need to use a contains() function to determine if a string is present in another field. But what if you have multiple entries in a field and they might be capitalized differently? Easy! We can use the following formula:contains(lower(${my_string_dimension}),"thing i'm searching for in lower case") This content is subject to limited support.                  

2530
iqbal.sandhu
Looker Staff
1 year ago
iqbal.sandhu
Looker Staff
iqbal.sandhuLooker Staff
published in Technical Tips & Tricks

How to add commas to a Numeric String value in a Table CalculationKnowledge Drop

Last tested: May 21, 2020 The ProblemAre you trying to turn this - "Goku Power Level (957829)" into this - "Goku Power Level (957,829)"? You've come to the right place! A SolutionThis can be accomplished in a Table Calculation by using a couple of string functions, namely the length and substring function. Let's examine the basic template we'll be following, in this case for a 6 digit number, and also assume that x = the name of the field you want to add commas to:concat(substring(${x},0,length(${x})-3), ",", substring(${x}, length(${x})-2, length(${x})))Why do we need so many functions?Let's examine the code by working from the outside in of our template:concat - From a high level, all we're doing is we're concatenating the numbers before the comma, to the ones after, i.e. concat(numbers before comma, ",", numbers after comma). substring - We're using substring to extract the numbers we want, and the definition shows the basic form. For the first substring function, we're going from t

5910
iqbal.sandhu
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: Incorrect number of bytes in Base64 decoded key. 32 bytes expected but 24 bytes provided.Knowledge Drop

Last tested: Nov 5, 2018 This error means that you're running with the new encryption and is using a locally backed key. The provided key doesn't match the format that we expect, in this particular case we detected the key was base64 decoded it and the base64 decoded output wasn't the proper number of bytes, which should be 32.A recommended way to generate keys is with the following:>openssl rand 32 -base64 > key_file This content is subject to limited support.                  

760
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: null (Service: AWSKMS; Status Code: 400; Error Code: InvalidCiphertextException;Knowledge Drop

Last tested: Nov 5, 2018 This error is indicative of Looker trying and failing to start with new GCM encryption.It means that this Looker is configured to use AWS Key Management Service (KMS) but KMS is unable to decrypt the stored key with the provided Customer Master Key (CMK).Ensure that the database Looker is trying to start against is from this instance and hasn't been migrated from another host with a different CMK without doing DR backup procedure. This content is subject to limited support.                  

2450
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

How to adjust system keepalivesKnowledge Drop

Last tested: Jul 4, 2018 In order to comply with our recommended settings for system TCP keepalives, you may need to adjust the values. In order to do so, run the following, you will need to have sudo permisisons.Open up the file where these settings changes live:sudo vim /etc/sysctl.confadd the line:net.ipv4.tcp_keepalive_time=200and save the file.Then in order for those changes to come into effect, run:sudo sysctl --system This content is subject to limited support.                  

120
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: Incorrect format for wrapped KeyKnowledge Drop

Last tested: Nov 5, 2018 This error is indicative that you could be using a local backing key for encryption but we are unable to decrypt the saved ciphertext in the database with the provided key. Verify that you are using the key they should be in the LKR_MASTER_KEY_FILE env variable and that this database was always using this key and hasn't been moved to a new host with a different key. This content is subject to limited support.                  

1560
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: Invalid AWS CMK AliasKnowledge Drop

Last tested: Nov 5, 2018 This error is indicative of this Looker trying and failing to start using the new GCM encryption. This means that a self-hosted or Looker hosted instance is trying to use AWS Key Management Service (KMS) for storing the encryption keys but the alias provided in the LKR_AWS_CMK environment variable is not known to KMS as being a valid key.If Looker hosted, contact our Looker support team to ensure the alias is being sourced correctly and the key exists. If your instance is self-hosted, ensure the alias exists in KMS and that the Looker instance has permission to use it and is able to access KMS. This content is subject to limited support.                  

240
ian
Looker Staff
1 year ago
H
Looker Staff
Hugo_SelbieLooker Staff
published in Technical Tips & Tricks

How do i add a custom filter to my create_query api callKnowledge Drop

Last tested: Aug 27, 2020 Something like this by amending your filter_expression parameter with the same syntax as you would put in your custom explore filter "filter_expression": "${order_items.id} > 5 AND ${users.id} < 5",  "id": 71,"view": "order_items","fields": ["orders.id","order_items.count"],"pivots": null,"fill_fields": null,"filters": nul,"filter_expression": "${order_items.id} > 5 AND ${users.id} < 5","sorts": ["order_items.count desc"] This content is subject to limited support.                  

730
H
Looker Staff
1 year ago
gavin.wims
Looker Staff
gavin.wimsLooker Staff
published in Technical Tips & Tricks

Creating hour / minute durations in visualisations with value_format [value format]Knowledge Drop

Last tested: Nov 25, 2019 If you want to represent durations in hours, minutes and seconds in a visualisation, you can do this by representing the duration as a fraction of a day, and applying [h]:mm:ss in the 'value_format' section of the visualisation settings.This can also be done with some HTML formatting in the LookML, here is an example:dimension_group: elapsed {type: durationsql_start: ${dep_raw} ;;sql_end: ${arr_raw} ;;timeframes: [second,minute,hour]} dimension: elaped_formatted {type: numbersql: ${seconds_elapsed} ;;html: {% assign h = {{value}} | divided_by: 3600 %}{% assign m = minutes_elapsed._value | modulo: 60 %}{% assign s = seconds_elapsed._value | modulo: 60 %}{{h}}h{{m}}m{{s}}s;;} This content is subject to limited support.                  

5620
gavin.wims
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