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

1744 Topics

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

Can you reference measures in custom filters?Knowledge Drop

Last tested: Jan 12, 2021 Nope. Our docs mention this here: https://docs.looker.com/exploring-data/filtering-and-limiting#custom-filters There is a feature request for this though, please comment! There is an easy workaround, using a LookML measure type: yesno like so:measure: testyn {type: yesnosql: ${count}<> value or ${users.count} <> 1;;}You can then filter on this yesno measure in the explore. This content is subject to limited support.                   

3880
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

Formatting large numbers as digit with magnitude (Mil, Bil, etc.)Knowledge Drop

Last tested: Jan 21, 2019 In the visualization menu, open the X or Y axis menu and in the field labeled <X/Y> format options, use the commas to truncate the value and use literal strings to notate magnitude.Each comma denotes three zeroes that will be omitted from the number.Use: 0,, "Mil"to turn "2,000,000" into "2 Mil"and 0, "k"to turn "2,000,000" into "2,000 k"You can also use this in LookML using value_format. You'll need to escape the quotes, though, since you have to put the value format string in quotes.value_format: "0,, \"Mil\""You can take advantage of conditional value formats to show differing symbols based on magnitude:[>=1000000]0.00,,\"M\";[>=1000]0.00,\"K\";0 This is documented in Help Center This content is subject to limited support.                  

17030
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

Rendering Images and hosting custom visualizations from GCP StorageKnowledge Drop

Last tested: Aug 16, 2019 When referencing files from Google Cloud Storage via html, always use storage.googleapis.com instead of storage.cloud.google.com, e.g. for html:dimension: image {type: stringsql: '1' ;;html: <img src="https://storage.googleapis.com/xyz/Static%20Images/a1.png" style="width:415px;" "height:60px"/> ;;} And for a custom visualisation:https://storage.googleapis.com/folder_name/vis/your_vis_name.js storage.cloud.google.com will work in queries, but will fail in any PDF/PNG rendering. You may see the following error message if you use the expanded URL:{"error":{"errors":[{"domain":"global","reason":"lockedDomainExpired","message":"Locked Domain Expired"}],"code":401,"message":"Locked Domain Expired"}} Or for a hosted custom vis image rendering (it will hang for minutes/hours with the following loop):2019-08-16 11:55:33.602 +0000 [INFO|02271|http] :: Request from <ip address> GET /api/internal/render/job/state/cdf341a5c8c53c2b6fc49a234415863a, {}2019-08-16

7270
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

Is the JavaScript that runs Custom Visualizations able to make http requests?Knowledge Drop

Last tested: Jan 21, 2019 It is not.Sandboxed iframes have an origin of `null` so any http request at all would violate the browser’s cross-origin request rules. This content is subject to limited support.                 

400
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

Custom Visualizations in LookMLKnowledge Drop

Last tested: Aug 13, 2020 Great news! Visualizations can now be defined in LookML!Example:manifest.lkmlvisualization: {id: "unique_id"label: "MY LABEL"url: "https://www.url.js"sri_hash: "my_sri_hash"file: "bundle.js"dependencies: ["https://my_dep.js", "https://more_dep.js"]} Key notes:Regardless of which project defines the visualization, it will be available globally on the instance The project must be deployed to production for the visualization to be available the id must be unique across all visualizations on the instance (validation will show errors otherwise) We can download the files from the custom visualization repos if you are trying to add a viz made my Looker. We just need to download the .js file from the repo and drag and drop it into the LookML IDE and set this as the "file" parameter If dependencies are required ,the developer would make that very clear. In most cases, the visualization's source files and dependencies have been bundled into a single distribution file me

7790
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

PDTs - Useful Facts and Troubleshooting PDT IssuesKnowledge Drop

Last tested: Mar 20, 2017 The BasicsA PDT stands for Persistent Derived Table. It is a Looker-specific feature that allows users to create a table in the scratch schema of their database based on SQL they have written in Looker. Our docs on this are really good! PDT DocumentationUseful FactsThe scratch schema needs to be configured in advance (see setup instructions). Use ${view_name.SQL_TABLE_NAME} to refer to a PDT in the SQL of a PDT (only replace view_name) - Docs Triggers are checked by default every 5 minutes when no triggered PDTs are building. This is configurable. A new PDT is created if you change the SQL in dev mode. See "why are there multiple copies of my PDT?" Dev mode PDTs have the persist_for: 24 hours property regardless of what persistence is set.Troubleshooting Common PDT IssuesMake sure the SQL for the PDT runs by itself. Check this by copy and pasting the SQL from the view file into SQL Runner and see if it runs on the proper connection.  Check the Admin > PDT p

3330
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

The PDT ReaperKnowledge Drop

Last tested: Dec 12, 2019 The reaper is the process in Looker that is responsible for dropping non-active PDTs from the scratch schema.Schedule:Unlike the regenerator, there is only one reaper thread total for all connections (the reaper thread operates on the master node, in cases of a cluster). The reaper operates at most every hour, with respect to the cron specified in the PDT Maintenance Schedule of the connection settings.The process:The reaper uses the active_derived_tables table in the internal database to determine which tables it should drop from the scratch schema. Before the reaper drops any tables, it will update the active_derived_tables table.Updating the active_derived_tables table:The reaper first acquires a list of all derived tables in use in production-built LookML and then deletes rows from the active_derived_tables table for any tables not in that list.Next, the reaper gathers a list of all tables that currently exist on the scratch schema and then deletes rows fr

5870
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 two connections on separate instances share a scratch schema/PDTs? aka Enemy ReaperKnowledge Drop

Last tested: May 11, 2020 TL/DR: Don't share the schema! Two instances should not share the same scratch schema because they can hit the "enemy reaper" scenario. PDTs CAN be shared using the publish_as_db_view feature (detailed below)The PDT Reaper cleans up (deletes) any tables that are expired. In the "enemy reaper" scenario, the reaper for one instance shares a connection registration key (the two letters after $ in the PDT name) and will delete all PDTs with the same reg key that are not listed in the "active_derived_table" internal db table. PDTs that were created by the other instance will not be here, so they will be deleted, and the other instance will do the same thing. Thus, all PDTs will constantly be being deleted. The most common way for instances to possibly have the same reg key would be sharing license keys.To share PDTs across instances, you should use publish_as_db_view. This way, one instance can create the PDT and be in charge of maintaining it, while the other one

1360
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 are examples of how to configure the lookerstart.cfg startup file?Knowledge Drop

Last tested: Jul 3, 2018 Docs  have a pretty simple exampleLOOKERARGS="--no-ssl --port 8080" Here's another example with more startup flags set:LOOKERARGS="--cipher-key-file key-20160226-220142.key -d looker-db --use-custom-jdbc-config --concurrent-render-jobs=4"  As seen more clearly in the second example, all of the necessary startup flags go within the LOOKERARGS=" " variable.The startup flags seen are documented here.  This content is subject to limited support.                 

1790
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

Update failed with `Timed out waiting for Looker to start`Knowledge Drop

Last tested: Jun 17, 2020 When jumping versions, Looker needs to update the internal db. Generally this takes less than 5 minutes, but sometimes this can take longer than starting the Looker process. If you try to start Looker while the internal db update is happening, the daemonizing process will time out in 6 minutes with the message Timed out waiting for Looker to start.This can be a legitimate message, but if you’re skipping several releases, it’s likely that the migrations are actually still running and you just need to give it some more time. If you see this message, sit tight and see if the update finishes and Looker starts up in the next ~15-20 minutes. We can confirm if the process is still running, even after the Time out message, with ps -f $(pgrep -f -- '-jar looker.jar')If you saw the time out message and stopped the Looker process to restart, you may have issues with failed internal db updates (including but not limited to missing columns, duplicate columns, missing table

610
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 would I get `504 gateway time-out nginx/1.12.21` errorKnowledge Drop

Last tested: Jan 21, 2019 For Looker-hosted instances: Your web browser talks to a load balancer which forwards the traffic onto nginx reverse proxy servers. Nginx then forwards the traffic to the appropriate looker. That error message means that nginx didn't get a response from Looker in a timely manner.In short, Looker was/is down. Logs should help us understand the root cause - if your Looker deployment is hosted by Looker, please open a Looker support request for assistance.  This content is subject to limited support.                 

5730
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

Custom Viz installation overviewKnowledge Drop

Last tested: Jan 26, 2021 There are two main ways you can install a custom visualization:Install an existing custom viz straight from marketplace Write your own custom viz from scratch and host the code either Outside of Looker, in a git repo or a CDN, or Within Looker, in the LookML Both of these approaches are overviewed here:INSTALLING A CUSTOM VIZ FROM MARKETPLACESteps to install a viz from marketplace:Search for the viz in marketplace and follow the installation instructions The viz will now be visible in Admin>Platform>Visualizations and will be available to use in exploresWhat happens when you install a viz from marketplace?In v. 6.24+ a project gets created on the instance (won't appear in the Dev drop down but you can find in “Manage LookML Projects”). The project will contain:-minified dist file (.js): allows for proper versioning and package management -configuration files (package.json, yarn.lock, .babelrc, webpack.config.js) -LICENSE: licensing -README: description

2700
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

Maximum Images in Comp Three Image CarouselKnowledge Drop

Last tested: May 15, 2020 For the Marketplace Image Carousel Visualization (that is not made by Looker), we can add up to 5,000 images. This is in line with how many rows we can have in the UI of an Explore, as each row will have an image.However, the "breadcrumbs", or dots at the bottom of the visualization that we can select to navigate around, cap at 15. Any more than that, and we will use left/right. So it may not be as efficient to use more than 15, but it is possible. This content is subject to limited support.                 

1250
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

Duplicate Views in Field Picker Using View_LabelKnowledge Drop

Last tested: Oct 21, 2020 Looker has an article about cool ways to use Liquid when labeling, but if you use Liquid in a view_label in one location, say for a field, but use a static string for view_label in another location, like the explore, you will see duplicate view labels in the field picker. ex: Liquid with view_label in view: Static view_label in explore:Result in Field Picker:​WHAT IS HAPPENINGThe line breaks that help make the Liquid easier to read actually add spaces into the view_label, effectively making the labels different. ex: From explore: "My Awesome Name" From view: "My Awesome Name " To resolve this, remove any spaces/characters between each the liquid tags where you are defining the view name that you are trying to match. ex: ​THE RESULTYour field picker only has one view label for your custom name! ​Note: I left spaces between the {% else %} and Gobbleydegook because I wasn't trying to match another view_label defined else where. If you have other Liquid conditions

1340
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

Result Cache vs Rendered CacheKnowledge Drop

Last tested: Sep 6, 2019 1. Result cache is the results that came from the db with minimal post processing (this includes dimension fill but does not include formatting, drill fields, etc.) Result cache keys are created by hashing the SQL that generated them and a few other things including details about the connection.2. Rendered cache is the fully post processed and serialized (e.g. JSONified) result which is ready to return to the client. The rendered cache key is created by hashing info about the LookML project digest, the connection details in the Admin - Connections panel, general instance settings, query request params (field, filters etc), relevant user attribute values for the user (if the query uses access filters, access grants, liquid, etc.), other user settings (locale if localization is used, user timezone if User-Specific Timezones is enabled, etc.), result format (e.g. different file types for downloads), Looker version, and dev/prod. Unlike the result cache key, the re

2670
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

Net::SFTP::StatusException (3, "permission denied") errorKnowledge Drop

Last tested: Dec 1, 2020 When you see the error Net::SFTP::StatusException (3, "permission denied") when setting up a sftp schedule it's usually caused by one of four things:A missing trailing slash at the end of the sftp connect url. This URL, for instance will not work:sftp://sftp.dev.looker.com/home/testaccount It will error with permission denied because sftp is trying to open a directory for writing. Adding a trailing slash opens a file for writing iside the target directory, which is what we want. So you should write:sftp://sftp.dev.looker.com/home/testaccount/The sftp user does not have write permissions to the destination directory. Test by creating a small text file in your local directory. Use command line sftp to connect to the destination server, e.g. sftp sftpuser@sftp.host.url. Once logged in try put filename. Type ls to verify that the file was transferred. If this errors or does not results in a file on the destination the sftp user doesn't have write permission. The de

9460
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): Accessing all datasets in a BigQuery ConnectionKnowledge Drop

Last tested: Jan 21, 2019When setting up a BigQuery connection in Looker you have to choose a dataset in a project to create the connection, however, all datasets in a project should be available regardless of what dataset you put in your connection. This content is subject to limited support.                   

790
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 does the Verify SSL Cert button do in the Connection panel?Knowledge Drop

Last tested: Jan 21, 2019 This checkbox allows you to specify if you want to require verification of the SSL certificate used by the connection. Unverified certificates will still ensure encrypted connections.Logic behind it:If the SSL certificate is not verified, a third party can’t eavesdrop on the communication, but we aren’t 100% certain that the server we are talking to is who they say they are. It is pretty common for companies to have servers with “self-signed” certificates inside their own infrastructure. These are usually safe to trust since their IT controls what servers are in their own infrastructure. If we have a ssh tunnel into their infrastructure it should be ok too. SSL is still important because otherwise someone who has access to the network might be able to read the packets as they go by....You should never trust a non-verified connection on the open internet, unless you are just using sample data or something else that is not important. This content is subject to l

1260
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 my NDT values different when compared to an equivalent explore query?Knowledge Drop

Last tested: Sep 6, 2019 One reason why your NDT rows and your corresponding explore query rows may not be matching up is that your NDT is not doing the same timezone conversion. What this means is that aggregations like count distinct that get dimensionalized in your NDT may not be the same as your original explore query values, in particular when the NDT is persistent.In order to make sure that generated SQL of your persistent NDT includes the desired timezone conversion, you need to add in a timezone parameter under the explore_source parameter of your generated NDT LookML. Here's an example of the timezone parameter in action:view: example_ndt {derived_table: {explore_source: base_explore {timezone: "query_timezone"column: time_field {}column: count_distinct_field {}}}dimension: time_field {type: date_dateconvert_tz: no}measure: count_distinct_field {type: sum}} Note: if you DO NOT include convert_tz:no in your time_field dimension, querying the time_field will result in a double t

700
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

persist_for hierarchyKnowledge Drop

Last tested: Jan 16, 2019 persist_for can be applied to PDTs, Models and Explores.There is a significant difference between PDT persist_for and the model/explore persist_for (caching). PDT persistence controls how long should the PDT live on the DB as a temporary table after it was first queried in the explore. Model and explore persist_for controls how long should the query previously run in Looker be kept in memory. It's simply results caching. The two are completely independent.When the persist_for parameter is set within both model and an explore, the explore setting will take precedence over the model setting. Check this Community article for more info​ This content is subject to limited support.                   

330
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

Securing looker-db.ymlKnowledge Drop

Last tested: Oct 1, 2019 When moving a previously self-hosted instance to a MySQL backend, users are instructed to create a looker-db.yml file that contains the MySQL database credentials: https://docs.looker.com/setup-and-management/on-prem-mgmt/migrating-to-mysql#create_a_database_credentials_fileThose who do not want the clear text password for this configuration in a file on disk may optionally set environment variables for "LOOKER_DB" to accomplish the same thing.You can set the environment variable "LOOKER_DB" to contain a list of key/values for each line in the looker-db.yml file.export LOOKER_DB="dialect=mysql&host=localhost&username=root&password=&database=looker&port=3306"We'd also recommend that they limit use of the MySQL user account created to the IP address used by your Looker serverIt is important to also consider that, given Looker's encryption scheme, all sensitive data in the database is encrypted at rest.  If someone were to steal the database cr

2000
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 do I have duplicate PDTs?Knowledge Drop

Last tested: Apr 4, 2020 PDTs can be shared by multiple models. When duplicates show up in the Admin PDT panel, the following could be the culprits:Datagroups used to persist the PDT are defined differently in different models. The models use a different connection. Defining some models to be case sensitive, while others are not. This might change the where clause in the PDT SQL.Duplicates also occur when we have include: *.view syntax across all of the models. To avoid this, one way is to create folders that hold the required views, and then including references to the folders, like like so: include: "/folder_name/*.view.lkml" This content is subject to limited support.                   

1040
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 aren't my new values for my embed parameters taking effect?Knowledge Drop

Last tested: May 28, 2020 If you’ve changed one of the embed parameters values in your script, in order for these new values to take effect, you would have to start a new embed session. If the user already has an active session it will just use that session instead of re-authenticating as the embed user with the new parameters. Creating a new session can be done by setting the "force_logout_login" parameter to be "true". 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 persist a derived table with a datagroup that's *not* included in the model the derived table is used in?Knowledge Drop

Last tested: Jan 21, 2019 No, you cannot. If a derived table is persisted by datagroup_trigger, that datagroup needs to be defined in every model that derived table is used in. If a datagroup isn't defined in a model that a derived table is used in, you'll get the following error:invalid datagroup_trigger {datagroup_name} declared for {derived_table_name} Since you cannot share identical datagroups across models, datagroups will be duplicated per explore/model if included in multiple models like this:NOTE: even though the derived table might be used in multiple models with the same datagroup defined, the derived table build should not be triggered multiple times. Instead, it should be built once and shared across the models. This content is subject to limited support.                   

3060
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

Multiple PDTs and a single Datagroup ObjectKnowledge Drop

Last tested: Jan 16, 2019 If a user has multiple PDTs (say for example 3 PDTs) and they want to set a Datagroup trigger that only triggers when all 3 tables are built, then that is possible if the 3 PDTs are set to listen to the same Datagroup, only then will the cache invalidate when all 3 + PDTs are built.If each PDT is set to to the same Datagroup, the steps will be: Rebuild all PDTs, invalidate cache, send schedules This content is subject to limited support.                   

410
Department of Customer Love
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