Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

jon.allen
Looker Staff
jon.allenLooker Staff
published in Technical Tips & Tricks

How to get Looker to display carriage returnsKnowledge Drop

Last tested: Aug 28, 2019 You should be able to use liquid to replace line break characters with HTML line break tags... something like {{ value | replace "\n","<br /" }} .  Or better yet, you can use whitespace css. Just checked and it's allowed...  html:<div style="white-space:pre">{{value}}</div>;;   This content is subject to limited support.                   

2590
jon.allen
Looker Staff
1 year ago
J
Looker Staff
Judah_HamaouiLooker Staff
published in Technical Tips & Tricks

How do I count the number of models or views in a project?Knowledge Drop

Last tested: Nov 14, 2017 Open the JavaScript console on a project's page, with "Cmd + Option + I" (for Mac) or "Ctrl + Shift + I" (for PC) and navigate to the "console" tab. Then, paste in one of the following commands:Count the number of models:$("#project-sidebar .model-link[href$='model.lkml']")Count the number of views:$("#project-sidebar .model-link[href$='view.lkml']") The output will be a number in parens followed by an array of files, the number is the count of the model or view files. This content is subject to limited support.                  

720
J
Looker Staff
1 year ago
J
Looker Staff
Judah_HamaouiLooker Staff
published in Technical Tips & Tricks

What is included in system activity's query runtime?Knowledge Drop

Last tested: Dec 7, 2018 “Query” in Looker does not hold the same definition as a “query” does on a database. In Looker, a query includes construction of that query, time in queue, time in transit (to and from database), and database “query” time. This content is subject to limited support.                  

1200
J
Looker Staff
1 year ago
J
Looker Staff
Judah_HamaouiLooker Staff
published in Technical Tips & Tricks

PDT’s won't work with Heroku?Knowledge Drop

Last tested: Nov 16, 2017 Not exactly, but there are workarounds.The problem is that Heroku only offers a streaming replica Postgres DB that is a read-only slave. This is because of Heroku's "hot swap" architecture, which basically means that the DB can swap out and become the production DB under certain circumstances, and therefore we cannot write to it.WorkaroundsYou can use a forked database and use the Update Connection API endpoint to change the connection credentials programmatically, but that would be a unique approach. Here’s a doc for that:https://docs.looker.com/setup-and-management/database-config/postgresql-herokuHowever, the best option here is to setup an ETL pipeline to incrementally copy data from production Postgres to another database (Postgres, Redshift, BigQuery, etc...). This has the added benefit of creating a destination where you can load more than just your production app data (e.g. Facebook Ads, Event/Click Tracking Data, Email Campaign Data etc.) to create ev

230
J
Looker Staff
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Can we send an unlimited result with Schedule?Knowledge Drop

Last tested: Nov 23, 2020 Yes, we can. Schedules sent via webhook, S3, or SFTP can send an unlimited result set as long as the result set streams.Sam explained in this article. For more details, please search (command + F) "Scheduling and Sending" in this article. This content is subject to limited support.                   

430
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

undefined method `applicable_filters' for #Knowledge Drop

Last tested: Feb 18, 2021 Users will encounter this error message when they are in the dev mode and then tried to download LookML dashboard which has not deployed to the prod. Downloads to formats that use the renderer (PNG and PDF) will use results from Production Mode, so if you haven't deployed yet then you should see this error. (If your download is in another format, the download will show results from whichever mode — development or production — you are in) This content is subject to limited support.                   

1020
Kento_Tomine
Community Admin
1 year ago
kevin.dunn
Looker Staff
kevin.dunnLooker Staff
published in Technical Tips & Tricks

Unavailable Ports on Looker Hosted InstancesKnowledge Drop

Last tested: Jan 28, 2020 Here's a list of ports that Looker already listens on, and won't be available for other processes (e.g., SSH Tunnels)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program nametcp 0 0 0.0.0.0:19997 0.0.0.0:* LISTEN -tcp 0 0 0.0.0.0:19998 0.0.0.0:* LISTEN -tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN -tcp 0 0 127.0.0.1:5000 0.0.0.0:* LISTEN -tcp 0 0 127.0.0.1:5001 0.0.0.0:* LISTEN -tcp 0 0 0.0.0.0:9997 0.0.0.0:* LISTEN -tcp 0 0 127.0.0.1:6062 0.0.0.0:* LISTEN -tcp 0 0 0.0.0.0:9998 0.0.0.0:* LISTEN -tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN -tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN -tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -tcp6 0 0 :::19999 :::* LISTEN 25756/javatcp6 0 0 :::1551 :::* LISTEN 25756/javatcp6 0 0 :::9999 :::* LISTEN 25756/javatcp6 0 0 :::111 :::* LISTEN -tcp6 0 0 :::46677 :::* LISTEN 25756/javatcp6 0 0 :::9910 :::* LISTEN 25756/javatcp6 0 0 :::22 :::* LISTEN -  This content is subject to limited support.           

550
kevin.dunn
Looker Staff
1 year ago
kevin.dunn
Looker Staff
kevin.dunnLooker Staff
published in Technical Tips & Tricks

Why do I get a "Can't read map layer "file file_name.topojson" from project "project_name"."Knowledge Drop

Last tested: Dec 17, 2019 This is usually caused by not including the entire file path in the file parameter of a map_layer.For files that are in folders, the entire path needs to be included.map_layer: layer_name {file: "/file_path/file_name.topojson"format: topojsonproperty_key: key_value} For files that are not in folders, a forward slash still needs to be in front of the filename:map_layer: layer_name {file: "/file_name.topojson"format: topojsonproperty_key: key_value} This content is subject to limited support.                  

1340
kevin.dunn
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

How to migrate/copy schedules using APIKnowledge Drop

Updated July 2021 Challenge: I want to copy the schedule of dashboard 1 and assign to dashboard 2 using API/SDK Solution: Please find an example script here This content is subject to limited support.                 

440
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Can we use created_hour (and other dimension group timeframe) to drill to a dashboard?Knowledge Drop

Last tested: Sep 7, 2020 The answer is YES, but because the filter value in Looker only designs to display "is on the date" or "is in the year" so part of the value will be cut offThe workaround is to convert the dimension timeframe to string and use it as a filter instead This content is subject to limited support.                   

260
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

publish as db view in multiple projects / modelsKnowledge Drop

Last tested: Feb 11, 2021 What happens if you publish db view in multiple projects or models?It will create a db view for each model -- given that all model belongs to the same projectsIf a PDT is included in two models in two projects, it will only create one stable view for one project This content is subject to limited support.                   

150
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Why does it take a long time to remove access of a user from Shared Folder?Knowledge Drop

Last tested: Feb 26, 2021 To summarize: After the DELETE /api/internal/core/3.1/content_metadata_access/331, {}, Looker runs several queries as below (note: not all queries are listed here -- I am pasting some examples to illustrate the process):Step 1: Confirm the permission of the user performing the action:SELECT * FROM "USER" WHERE ("ID" = 70) LIMIT 1SELECT * FROM "SESSION" WHERE ("TOKEN" = '[FILTERED]') LIMIT 1 Step 2: Get the ID of Looks, Dashboards, Spaces, etc inside the Shared Folder:SELECT * FROM "LOOK" WHERE ("LOOK"."ID" IN (1, 11))SELECT * FROM "DASHBOARD" WHERE ("DASHBOARD"."ID" IN (1, 2, 3, 41, 52, 65, 91, 93, 97, 105, 109, 110))SELECT * FROM "CONTENT_METADATA" WHERE ("CONTENT_METADATA"."PARENT_ID" = 37)Step 3: Looker inserts the action into the "Event" and commit "transaction" to remove accessINSERT INTO "EVENT" ("NAME", "CATEGORY", "CREATED_AT", "USER_ID", "SUDO_USER_ID", "IS_LOOKER_EMPLOYEE", "IS_ADMIN", "IS_API_CALL") VALUES ('delete_content_metadata_access', 'content

120
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

What happens to a schedule of a user after the permission `send to email` is taken out from that user?Knowledge Drop

Last tested: Feb 22, 2021 Scenario: "Someone has the permission and sets up an alert, now the permission is taken away, and we want to know if the alert will be working".After the permission send_to_integration is removed from the user, the schedule will not be sent. There will be an error "User missing permission to send to specified destination." for that schedule job in Admin > Schedules History The schedule will not be stopped, it will be triggered to be sent out. However, every time it will show an error in Admin > Schedules HistoryThe reason for the behavior is that every time Looker is sending out a schedule, it will run both a query to a "Schedule" table inside the database to "trigger" a schedule, and then another query to the "User" table to check for permission.Examples from checking the schedule to move it to different stages{"duration":"0.000059","sql":"SELECT * FROM \"SCHEDULED_JOB_STAGE\" WHERE (\"SCHEDULED_JOB_STAGE\".\"SCHEDULED_JOB_ID\" = 12603)","t":"2021-02-22

640
Lan
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Make a fake dimension (hard code value) in SQL ServerKnowledge Drop

Last tested: Apr 2, 2020 In many cases, we want to make a fake dimension, so it could act as a variable for table calc.dimension: name { type: number sql: 1 ;; } This works perfectly in MySQL yet it will run into an issue with SQL Server, so we would need to twist the syntax a bitdimension: name { type: number sql: CASE WHEN id IS NOT NULL THEN 0.003300 ELSE 0 END ;; } This content is subject to limited support.                   

5180
Lan
Looker Staff
1 year ago
L
Looker Staff
leticia.esparzaLooker Staff
published in Technical Tips & Tricks

Will Lookerbot still be responsive if Heroku is not running?Knowledge Drop

Last tested: Nov 27, 2017No. The Heroku app needs to be running in order for Lookerbot to be responsive. Heroku can, however, be set up for continuous running, but this is something that needs to be addressed on Heroku's end. This content is subject to limited support.                  

60
L
Looker Staff
1 year ago
Lan
Looker Staff
LanLooker Staff
published in Technical Tips & Tricks

Can we do merged results on system activity queries?Knowledge Drop

Last tested: Dec 16, 2020Yes, but only people with appropriate permission could view the query. This content is subject to limited support.                   

210
Lan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

How do I turn a dimension of type string into a measure?Knowledge Drop

Last tested: Apr 6, 2020 In some cases, users want to turn their dimension of type string into a measure (to put into a pivot, for example).To turn a dimension of type string into a measure, we can create a measure of type string and put an aggregation into the sql parameter so that it doesn't throw the GROUP BY/ AGGREGATION error, like so:measure: gender {type: stringsql: max(${table}.gender);;}  This content is subject to limited support.                   

3200
mandy.chan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

Cannot use stable views in schemaKnowledge Drop

Last tested: Apr 14, 2020 This error shows up when we're using the publish_as_db_view parameter and the Looker database user doesn't have the permission to create views on the scratch schema.If the Looker database user doesn't have permission to create views, we will either need to grant them permission to create views or remove the publish_as_db_view (because when the new PDT is built, the old view is dropped and the new view with the stable name is placed onto the scratch schema).If this warning is seen when we're testing the connection in the Connections page, this warning can be ignored as long as long as we're not trying to use stable views. This content is subject to limited support.                   

530
mandy.chan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

Illegal circular project dependency referenceKnowledge Drop

Last tested: May 6, 2020 This error occurs when, for example, we have project import where Project A references a view file in Project B and Project B references a view file in Project A.Local dependencies should go unilateral so we'll hit this error if we're referencing both ways.The workaround is to make it one way - so if Project A is referencing view files from Project B, make copies of the view files from Project A in Project B. This content is subject to limited support.                  

1710
mandy.chan
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

Why do I see `No Results` after installing a Marketplace block?Knowledge Drop

Last tested: Sep 1, 2020 After a user has added a marketplace block and try to run the query, they may see No Results when querying.One thing to check is to see if the schema name is correct. To find the schema name, you can look under the connection listed in the Update Configurations modal and search for the specific dataset. When you run Select 10 against the table, it should append the schema to the table name and you'll be able to compare schema names. This content is subject to limited support.                   

130
mandy.chan
Looker Staff
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

Filling is not Allowed in Queries with Limit Exceeding 30000Knowledge Drop

Last tested: Jul 24, 2019 This is error is in reference to a scheduled job failure. Instead of receiving the schedule, the schedule owner receives an email that the scheduled job has failed with the error message:Error: Missing values for 'view_name.date_field' were not filled because filling is not allowed in queries with limit exceeding 30000 This is may be due to a pivoted date field with "Fill in Missing Dates" (default) enabled. Even if the displayed data is less than 200 rows and/or column limit is enabled, the schedule will still fail. By selecting "Remove Filled in Dates" from the dimension options, the schedule will send. This content is subject to limited support.                  

2080
margaret.abercrombie
Looker Staff
1 year ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

Stable PDT name not showing up with publish_as_db_viewKnowledge Drop

Last tested: Mar 23, 2020 Did you add the publish_as_db_view parameter after the table has been built and pushed to production?If so, the stable name will not show up.It'll only show up once the table has been rebuilt, so setting the publish_as_db_view_parameter as yes is not enough. You will either have to make a change to the sql code to get the regenerator to rebuild table or hit the Rebuilt Derived Table and Run button in the Explore.Creating the PDT with publish_as_db_view for the first time will allow for the stable view, but adding the publish_as_db_view onto a PDT that is already built won’t until the next rebuilt. This content is subject to limited support.                   

700
mandy.chan
Looker Staff
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

Convert Seconds to HoursKnowledge Drop

Last tested: Sep 11, 2019 The ProblemSome data is best displayed in smaller time units, such as seconds, but with aggregation of large datasets, the seconds value gets so large it is relatively meaningless. In that regards, it would be easier to display the data in another format.The SolutionWe can use the value format parameter as listed in this document, but to properly use the Excel style formatting for time we need to first divide our aggregation by the number of seconds within a day.https://community.looker.com/lookml-5/readable-times-from-seconds-1587On either the raw measure or on a duplicate for formatting purposesDivide your sql parameter by the number of seconds in a day (60sec/min *60min/hr *24hrs/day) = 86400.0 Adding the decimal creates a floating point that accommodates for the decimals. Add the value_format parameter to your field and use the appropriate level of detail. The most conservative option is [hh]:[mm]:ss If you only need to the minutes level: [mm]:ss Viola!

12500
margaret.abercrombie
Looker Staff
1 year ago
maxcorbin
Looker Staff
maxcorbinLooker Staff
published in Technical Tips & Tricks

Error: Misconfigured proxy in front of your Looker serverKnowledge Drop

Last tested: Mar 7, 2018 ErrorThere appears to be a misconfigured proxy in front of your Looker server. HTTP DELETE to Looker are failing.HTTP PATCH to Looker are failing.HTTP PUT to Looker are failing.HTTP POST to Looker are failing. Possible SolutionCheck to see if you have the ublock origin browser extension. This can cause problems with Looker. This content is subject to limited support.                   

330
maxcorbin
Looker Staff
1 year ago
maxcorbin
Looker Staff
maxcorbinLooker Staff
published in Technical Tips & Tricks

BIGQUERY (HowTo): How do you escape a table or field name in BigQuery?Knowledge Drop

Last tested: Jan 10, 2018 In BQ Standard SQL, you can escape names by enclosing it in backticks:SELECT `schema.table`.`field` AS table1.field FROM public.`schema.table` AS table1 In BQ Legacy SQL, you can escape names by enclosing it in brackets:SELECT [schema.table.field] AS table1.field FROM [project:schema.table] AS table1 In Legacy SQL, this is only relevant if you have dashes in the name, which would cause an error without the brackets.Source: BigQuery documentationChat tip: Escaping the table name this way in the sql_table_name parameter can resolve errors like Aliases referenced in the from clause must refer to preceding scans  This content is subject to limited support.                   

15190
maxcorbin
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