Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

noah.kawasaki
Looker Staff
noah.kawasakiLooker Staff
published in Technical Tips & Tricks

Google Sheets showing #ERROR! exporting data with lookerFetchDataKnowledge Drop

Last tested: Sep 17, 2018 If you can successfully load their data into Sheets with lookerFetchData on the same look, it may be due to them breaching limitations on Google's side.This goes over their quota restrictions. Notably the "Custom function runtime" being 30 seconds at the time of this card being created. This content is subject to limited support.                  

450
noah.kawasaki
Looker Staff
1 year ago
noah.kawasaki
Looker Staff
noah.kawasakiLooker Staff
published in Technical Tips & Tricks

Why is my reference line giving an incorrect number on a pivoted query?Knowledge Drop

Last tested: Sep 18, 2018 Reference lines calculate based on the first column visible in a visualization in the returned dataset. So a max, min, average, or median will be based on the data in one column only, and not on every data point for that row.You can do some table calculations to manipulate this behavior, but the exact functionality you'd want still might not be possible. (Ex: You want a stacked bar chart of pivoted data, so you make a table calc to horizontally total up values from each pivot for the sake of making the reference line work. However, now the total table calc will also show in he stacked bars.)Here is the request to the product idea related. This content is subject to limited support.                  

1010
noah.kawasaki
Looker Staff
1 year ago
noah.kawasaki
Looker Staff
noah.kawasakiLooker Staff
published in Technical Tips & Tricks

Variable not found "variable_name" in commented out liquidKnowledge Drop

Last tested: Oct 24, 2018 Liquid being commented out in SQL by -- will still be processed as valid liquid by Looker because of the order in which Looker processes a view file.To have liquid commented out and not used, comment it out in liquid syntax as well{% comment %} some liquid stuff {% endcomment %} This content is subject to limited support.                  

480
noah.kawasaki
Looker Staff
1 year ago
noah.kawasaki
Looker Staff
noah.kawasakiLooker Staff
published in Technical Tips & Tricks

Make an identical dashboard from different dataKnowledge Drop

Last tested: May 13, 2020 If you have multiple data sets with all identical schemas, and identical views and explores, you can create a LookML dashboard from one, copy it, and then change the respective view and explore name in the dashboard file.Remember to have these dashboards included in model files! This content is subject to limited support.                  

1730
noah.kawasaki
Looker Staff
1 year ago
nathaniel.thompson
Moderator
nathaniel.thompsonModerator
published in Technical Tips & Tricks

Determining the number of days in a filter rangeKnowledge Drop

Last tested: May 7, 2019 Sometimes users want to know the number of days that are in the date range filtered on in the Explore. This is particularly useful for performing calculations (in table calcs or in the sql parameter of a dimension or measure).To dynamically get this value, we can use the date_start and date_end liquid variables, which are described in more detail here in the liquid variables doc. We can use these variables to determine the filter values applied on the date dimension, and then use SQL to determine the number of days between the start and end of the filter.In this example, we're using MySQL which uses DATEDIFF, but this can be adapted to fit any specific SQL dialect.dimension: num_days {type: numbersql: DATEDIFF({% date_end created_date %}, {% date_start created_date %}) ;;} In this hypothetical example, we have a dimension group called created. We're pulling in the start and end values for the filter applied to this dimension, and setting our new num_days dimens

5900
nathaniel.thompson
Moderator
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

What is "create_trigger_missing" in the PDT Event LogKnowledge Drop

Last tested: Sep 28, 2020In the PDT Event Log, users will see a value "create_trigger_missing" and wonder what that means.When asql_trigger_value /sql_trigger PDT is being built for the first time, Looker doesn't have an "old trigger value" to which to compare the new result of the trigger value. This results in the "create_trigger_missing" value entered in the PDT Event Log. This content is subject to limited support.                  

330
margaret.abercrombie
Looker Staff
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

Multiple Measure Pie Chart with Table CalcsKnowledge Drop

Last tested: Sep 20, 2019 The ProblemIt can be useful to set-up your data to perform your comparisons with multiple measures, and, for business reasons, a pie visualization is preferred. This article is for who wants different measures to be the values on the pie chart.You may also be able to resolve this via PDT, but that would require using aggregates of measures as the values of a single dimension.Note: This works best for relatively small datasets.For a displaying multiple pie charts (one for each measure) comparing dimension values, see this article: How to display two measure in pie chart. The WorkaroundSet-up your data so that you have a dimension with at least as many unique values as measures you are trying to display.Add a table calculation to return the intended aggregation of each measure based on rows using nested if statements. ex: Add a second table calculation to return the desired value labels for the measure based on row (corresponding to the order used for "Desired V

2860
margaret.abercrombie
Looker Staff
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

Why Can't I Schedule System Activity ExploresKnowledge Drop

Last tested: Sep 28, 2020 The ProblemI gave my user permission to the see System Activity and they have permission to schedule Looks, so why can't they send System Activity data?A SolutionThe user has the see_system_activity permission, but the system_activity model set was not included in the user's model set.This role allows the user to see the System Activity model, but none of the permissions listed apply to the user's access to the System Activity model. Once the System Activity model is added to the model set for the role, the user should be able to schedule System Activity data! This content is subject to limited support.                  

190
margaret.abercrombie
Looker Staff
1 year ago
margaret.abercrombie
Looker Staff
margaret.abercrombieLooker Staff
published in Technical Tips & Tricks

Dynamic Reference Lines with Value LabelsKnowledge Drop

Last tested: Sep 20, 2019 The ProblemSome users want reference lines in their visualization that use metrics that are not in our standard drop-down options. They could always use a custom value, but that value is static, so if it is based on a calculation of the data, it doesn't quite suit their needs. This help center article explains how to create dynamic reference lines via table calculations, but if value labels are included, this can look a little messy. This post is how to address the "value label" problem.Note:This is an imperfect solution being that I have not yet figured out how to hide the null value on bar/column visualizations. This works best with charts where the reference value will be off the main visualization. Then you can use a dark color for the data and the reference value to mask the null value.Less ideal use case:Better use case: The WorkaroundAfter creating the table calculation for the dynamic reference line:Create a second table calculation that returns the va

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

Can I have a dimension in a Look but hide it from the Explore?Knowledge Drop

Last tested: Apr 21, 2020 Yes!What we can do is first save the Look with our desired dimension, and then once saved, go into the LookML and apply the hidden parameter to the dimension so that it no longer shows up in the Explore. This content is subject to limited support.                  

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

What happens to embed users if we restrict access to Looker to certain IP addresses?Knowledge Drop

Last tested: Sep 30, 2020 This will essentially be a problem.Since IP allowlist will limit incoming traffic from certain IP addresses and the VPN allows all users to come from only 1 IP address, if the embed users aren't using the VPN, they will get blocked by the allowlist rules.At the moment, the workaround is to have two instances, one for embedding and one for Looker users. This content is subject to limited support.                   

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

Why aren't my Looker servers closing the connections to my internal DB?Knowledge Drop

Last tested: Apr 15, 2020 You may notice that Looker servers aren't closing the connection to the internal DB, which causes the open connection count on the DB to rise continuously and hit the DB connection limit.This is intended behavior. We keep connections around and re-use them to avoid the overhead associated with creating a new connection so this is actually a performance enhancement.You'll need to make sure their DB can accept number of nodes in a cluster * ~100 number of connections, which is the most connections Looker will try to open.Not sure if 100 is the actual number, but 100 is a safe number to use. This content is subject to limited support.                  

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

Orphan Groups in LDAP settingsKnowledge Drop

Last tested: Jul 15, 2020 If you have LDAP set up and then delete your old LDAP/AD groups in your IdP, you will still see the old group listed in the Groups page. For example, Viewing Testing (Orphan 123456789) GroupIt should be handled in the same way as SAML shadow groups, where the shadow groups will still appear until users from the old group re-logs in and gets mapped to the new group, or 30 days have passed. This content is subject to limited support.                  

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

What is the difference between Most Recent Query Run At Date vs Most Recent Query DateKnowledge Drop

Last tested: Nov 17, 2020 Most Recent Query Run At Date is the most recent query run per query ID. Essentially, it finds the most recent date that the query was run.Most Recent Query Date takes the most recent date of the History ID.So for instance, if the Most Recent Query Run at Date says 2020-11-06 but the Most Recent Query Date says 2020-10-02, this suggests that the History ID of that query was created on 2020-10-02, but the query was ran most recently on 2020-11-06.Reminder: The query ID is a unique combination of SQL text, connection and other fields. It's created the first time a SQL query is ran on a connection. The query ID can be linked to many History IDs This content is subject to limited support.                  

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

How does Looker check for dependencies between PDTs?Knowledge Drop

Last tested: Apr 28, 2020Looker does this by parsing derived table sql, sql_step, etc, looking for the ${view.SQL_TABLE_NAME} as well as required derived tables in the query of each NDTs to collect dependencies and build a directed acrylic graph. This content is subject to limited support.                  

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

What happens when we have from and view_name together?Knowledge Drop

Last tested: Oct 21, 2019 When you use from and view_name together, from tells Looker what view to actually pull from and view_name can literally be anything. (per Paola)So for example, say we have something like this:explore: extension_hidden {  extension: required  view_name: any_name_that_i_want  join: products {    sql_on: ${products.id} = ${any_name_that_i_want.id} ;;    type: left_outer    relationship: one_to_one  }} explore: order_items_3 {  extends: [extension_hidden]  from: orders} The SQL generated in the UI will look like this:SELECT    any_name_that_i_want.id  AS `any_name_that_i_want.id`,products.brand  AS `products.brand`FROM demo_db.orders  AS any_name_that_i_wantLEFT JOIN demo_db.products  AS products ON products.id = any_name_that_i_want.id This content is subject to limited support.                  

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

There must be at least 750 MB of free space in the temp directoryKnowledge Drop

Last tested: Jul 29, 2020 Looker requires at least 10GB of free disk space and for reference, we provision 50GB root partitions for our EC2 legacy instances by default.If possible, run df -h (which will display the summary of available and used disk space usage of file system). It should show the filesystems that marks off partition of their data. We should typically look for the directory /home because that is the directory where Looker is uploaded.The output should look something like so: /dev/mapper/vgsystem-lvhome 2.0G 1.8G 264M 88% /hom This indicates that there is 264M free in the /home/ volume out of 2G. This means everything in the Looker user’s home directory (and any sub-directories from there, like the Looker one) only has 2 gigs to work with, which is not very much, so you will need to upsize in order to be able to run Looker. This content is subject to limited support.                  

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

Embed users' user attribute values are being reset in the Users PanelKnowledge Drop

Last tested: Apr 16, 2020 If this is happening, it is likely that the embed user's user attributes are being defined in the Users panel instead of the embed URL, which is incorrect.The user attribute values for embed users should be defined in the embed URL, not in the Users panel.If you want to manage the embed user user attributes from the Looker UI, you can assign user attributes through groups.The groups_id parameter in the embed URL will determine what group the user belongs in, and once that group_ids value is set, they can assign the appropriate attribute value to that group without having to change the URL. If this is happening, it is likely that the embed user's user attributes are being defined in the Users panel instead of the embed URL, which is incorrect.The user attribute values for embed users should be defined in the embed URL, not in the Users panel.If you want to manage the embed user user attributes from the Looker UI, you can assign user attributes through groups.Th

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

Why is there a delay in alert notifications?Knowledge Drop

Last tested: May 27, 2020 Let's say an alert is being triggered and met in the Alert panel, but the alert email that was supposed at, for example, 10AM is instead being sent out at 10:50AM, so there is a 50 minute delay.One likely reason for this is possibly a backed up alert or render queue. The rest of the instance is probably backed up with schedules/renders as well.Right now the default is 10 threads per instance for schedules and 3 for alerts as you can see on this documentation.You'll need to balance the resources and adjust as needed, so instead of scheduling the reports/ alerts at the same time, space them out evenly. This content is subject to limited support.                  

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

How to get filter to equal to "any value" when drillingKnowledge Drop

Last tested: Apr 13, 2020 A user has a Look with a default date filter that's not to 'any value'When they drill into it from a dashboard, they want to change the date filter to 'any value' instead of last 7 days.The filter will just be an equal sign, like so:url: "/looks/18?&f[orders.created_date]=&f[products.sku]... This content is subject to limited support.                  

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

Content validator showing "unknown field" for schedule filtersKnowledge Drop

Last tested: Jun 11, 2020 When your content validator is showing "unknown field" for a field, but the field still exists in the Look, from 7.8 Looker version, the content validator now shows errors for scheduled plan filters.The clock icon will indicate that the error is due to a bad filter in the schedule. If you click on the replace/remove button, you will also see that changing the filter will only affect the schedule for that Look. You will have to recreate a new schedule with the new filter set. This content is subject to limited support.                  

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

Why is liquid not working with fiscal timeframes?Knowledge Drop

Last tested: Aug 20, 2020 Check if you're using ._value vs ._rendered_value.Looker isn't able to output a date in the fiscal format without string formatting. For example, Looker will generate SQL to return 2020-12 and then on the front end, turn it into a string like so: FY2020-Q4. If we use ._rendered_value, it'll grab the value after front-end formatting. This content is subject to limited support.                  

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

Scheduling single column visualization via API endpointKnowledge Drop

Last tested: Oct 29, 2019 If we want to schedule a visualization with settings single column visualization, the API endpoint for single column visualization is "format":"inline_visualization"  This content is subject to limited support.                  

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

Can we monitor the number of API requests in System Activity?Knowledge Drop

Last tested: Apr 2, 2020 The API requests under System Activity > Explore pertain only to those that involve generated SQL.If the user wants to monitor requests that don't involve generating SQL, they may be able to go to event Explore instead, but the Event Explore gets truncated after 100K rows, so only the most recent events are shown. This content is subject to limited support.                  

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

Can lots of merge results cause high CPU usageKnowledge Drop

Last tested: Oct 28, 2020 Answer is yes!Merge results are performed in memory and not in SQL on the database. We store the results of all the source queries in memory and then merge those source queries in memory. The usage of this memory can cause the memory to be throttled (essentially meaning that memory is used up to its max).When the memory is throttled, it can trigger the garbage collector, and thus causes high CPU usage.​ This content is subject to limited support.                  

1510
mandy.chan
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