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

1744 Topics

Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Can we do a "not in" SQL statement without a derived table?Knowledge Drop

Last tested: Mar 5, 2021We can do it with merged results! Create the main query and then merge to the query for the "not in" part. The second explore's fields will be null when it's "not in" so you can use a yesno table calculation to find those and remove the ones that aren't null. Keep in mind that this is limited to 5K results on either side of the merge. This content is subject to limited support.                  

290
Peggy_Beard
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Can two looks have the same (or duplicate) title?Knowledge Drop

Last tested: Jun 16, 2020Only if they are in different folders. If you try to create or rename a Look when there is already a Look in the folder with the same title you'll see an error message and you won't be able to save it This content is subject to limited support.                  

90
Peggy_Beard
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Why does System Activity give the error data exception: invalid character value for cast`?Knowledge Drop

Last tested: Nov 16, 2017 The history explore in System Activity includes both User-Defined and LookML dashboards. Because of this, when filtering on a dashboard id, the generated SQL will convert the ID to a VARCHAR.If you try to filter on a dashboard id within a custom filter, you might get the error message data exception: invalid character value for cast (if they are on a HyperSQL back-end). To get around this, change your custom filter to be:concat(${history.dashboard_id},"") = "58"This will cast the dashboard ID to a VARCHAR in the generated SQL and filter correctly.  This content is subject to limited support.                  

560
paola
Looker Staff
1 year ago
noahmac
Looker Staff
noahmacLooker Staff
published in Technical Tips & Tricks

Spoofing a Dashboard with Realistic Fake Data on the Front-EndKnowledge Drop

Last tested: Dec 20, 2019 In LookML Block development, it's common to develop a model without real data populating the underlying connection for various reasons. The database may be empty, sparse, or populated with fake data which leaves a lot to be desired; this allows for model development, but not "nice-looking" dashboards with "realistic" trends. Spoofing takes advantage of table calculations to create the fake data!Here is an example of a dashboard tile built off of a LookML model with sparse, fake data. The date range is skewed, there are many more series than this dimension would create in production, and the value range is 0 or 1.This is the same dashboard tile, after being spoofed. The date range is more relevant, we see a realistic distribution of "CLOSED" and "CANCELLED" orders, and a realistic trending of "OPEN" orders over recent weeks. This tile could pass as being modeled off of real data and better demonstrates what the tile, and dashboard, could look like to users.Meas

3790
noahmac
Looker Staff
1 year ago
noahmac
Looker Staff
noahmacLooker Staff
published in Technical Tips & Tricks

Why is my Marketplace visualization blocked by CORS?Knowledge Drop

Last tested: Sep 1, 2020 This appears as a console warning or error, notifying you that the requested visualization cannot be served due to a cross site origin mismatch. Sometimes, "mime type: html/text" is listed as the resource type. Updating the visualization to serve from our CDN resolves this.Marketplace visualizations are now served from the Marketplace CDN. Great! What does that mean for me?- faster load times- fewer API calls made to the internal server- minor changes such as bug fixes can be deployed to the visualization without updating from the Marketplace > Manage page- no more CORS/cookie violations in Incognito mode/some Chrome configurationsWhat if I want to continue to use the internal server to host my Marketplace visualizations?This method is not recommended. However, strict security policies will block open access to our CDN and may necessitate hosting on the internal server. If your instance does not meet these network requirements,• HTTPS port 443 should be open

630
noahmac
Looker Staff
1 year ago
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.                  

390
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.                  

910
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.                  

460
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.                  

1640
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

4990
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.                  

310
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

2790
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

9500
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.                  

470
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.                   

260
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.                  

370
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.                  

630
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.                  

670
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.                  

740
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.                  

320
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.                  

770
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

360
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.                  

190
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