Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

Can I safely drop the connection_reg_r3 table?Knowledge Drop

Last tested: Nov 25, 2019Yes. If your connection test returns an issue with accessing the connection_reg_r3 table or your PDTs fail to build because the regenerator is unable to access the connection_reg_r3 table, you CAN safely drop this table.Dropping this table will not have any affects on existing PDTs. The connection_reg_r3 table will be recreated the next time it is needed (i.e. when running the connection test, when the regenerator runs). Any connections that were registered in the connection_reg_r3 table will have the same registration key in the new version of the table.One thing to keep in mind is if you are deleting this table because the looker user cannot access it, you may also have issues with the looker user accessing the schema itself/ PDTs on the schema. If this is the case, you will need to modify the looker users permissions as specified in our database configuration documentation. This content is subject to limited support.                  

1550
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

How to hide/filter certain values from visualizationKnowledge Drop

Last tested: Sep 21, 2020 Certain visualizations, i.e. line, scatterplot, area, have the "Plot Null Values" option in the visualization section. We can leverage this along with table calculations to exclude certain values from a visualization.We can do this by creating a table calculation that uses an if() function to assign "null" to values that we would like to exclude from the visualization and then toggling on the "Plot Null Values" setting.This is useful because if we were to simply filter on the field where we'd like to exclude a certain value, we would filter out all rows where that value resides. This method preserves the values in the columns in the row.doc available here This content is subject to limited support.                   

76670
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

Do I need to specify a 'type' for a field that has a 'case' in it?Knowledge Drop

Last tested: Jan 21, 2019The "case" parameter always returns a type "string". In other words, the generated SQL includes a cast to a string. Since the Looker default "type" is "string", there is no need to specify 'type: string' in a field that has a case. That being said, it is recommended that you specify 'type: string' for consistency of code. This content is subject to limited support.                  

100
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

Can I use prefix unicode/ prefix N notation in Looker?Knowledge Drop

Last tested: Jul 3, 2019 You can use prefix N notation in the sql parameter of a dimension or other sql_* parameter types however this could lead to issues with filtering on the dimension.For example, if you have a dimensions with sql: N'Hello' ;; and then filter on that dimension in an explore for is equal to 'Hello', Looker will not generate N'Hello' in the WHERE clause so you will see no results in some cases, depending on the database settings.The better thing to do in this case is to configure the SQL server with a Collation that supports the desired string literals, and then never use the N'' prefix. https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017 This content is subject to limited support.                 

490
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

What happens when I don't specify a sql parameter in a dimension?Knowledge Drop

Last tested: Oct 19, 2018If you don't specify a sql parameter in a dimension, Looker defaults to ${table}.dimension_name. So when you select the dimension in the explore, the generated sql will select table_name.dimension_name. This content is subject to limited support.                  

110
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

When to use persistence (PDT)Knowledge Drop

Last tested: Feb 21, 2020 At a high level, persisting a derived table is useful for reducing database load and increasing query speed - querying a table that exists in the database schema can be much faster than composing a table in a CTE or temporary table and then using it in a query.For example, suppose we want to report on customers by cohort based on when they made their first order. This query might be expensive in real time, but by using a PDT the query can be calculated once and reused.Additionally, the PDT functionality makes it very simple to test different indexing, distributions, and other options without needing a large amount of support from your DBA or ETL developers. When NOT to use persistence:There are several situations where a PDT might not be the best choice.When a derived table is being extended - an extended PDT will create a new copy in the scratch schema for each extension When templated filters or Liquid parameters are being used - there are potentially infin

3730
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

How to change scatterplot dot sizes based on measure valueKnowledge Drop

Last tested: Oct 8, 2018 Scatterplot has a "Size points by field" option in the Series section in the visualization settings. Choose the field that you're representing in the scatterplot and it will show with different dot sizes. This content is subject to limited support.                   

2380
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

How to Persist Derived Tables in LookerKnowledge Drop

Last tested: Jul 1, 2019 In order to persist tables in Looker, PDTs must be enabled for the connection and the scratch schema must be set up according to the database configuration instructions.Once the scratch schema has been set up and the connection allows for PDTs, derived tables can be persisted by adding one of the below LookML parameters to your derived_table definition.persist_for persist_for lets you set the maximum amount of time that a persistent derived table may be used before it is regenerated. Once the maximum amount of time has been reached, the PDT is dropped from the scratch schema. When a user runs a query that relies on a persist_for derived table, Looker checks the age of the table against persist_for. If the age is greater than the persist_for setting, the derived table is regenerated before the query is run. If the age is less than the persist_for setting, the existing derived table is used. sql_trigger_value sql_trigger_value lets you trigger the regeneration

2760
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

How to schedule based on a datagroupKnowledge Drop

Last tested: Sep 17, 2020 In order to schedule content with a datagroup, the datagroup will need to be defined explicitly in the model file or written in a .lookml file and included in the model file.​1) Using just sql_trigger: use this to force a cache reset and/or drop and rebuild a PDT at a specific time. 2) Using just max_cache_age: doing this will not cache a query unless you manually run the content, since nothing's automating the query-running process (see the Q&A below). Otherwise, use this to create a cache reset policy or policy at the model or explore level to override the default 1 hour cache that Looker uses.3) Using both in combination: the primary use case I have seen is to ensure a scheduled dashboard will run with fresh data. for example schedule a dashboard to run at 7:01am. Set a datagroup with a sql_trigger for 7:00am and max_cache_age for 10 hours. This will ensure that when that dashboard runs at 7:01am the cache will be reset and then anyone loading that dash

6580
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

How do totals for table calcs work?Knowledge Drop

Last tested: Sep 21, 2020 Totals for table calcs work by apply the table calc logic to the totals of the measures that are referenced in said table calc.In other words,For any fields referenced in the calculation, the totals are computed. (This will fail for dimensions because they are not included in the totals query.) The calculation is applied to the totals computed in step 1.For example, if we had a table calc that is ${measure1} + $[measure2} . The total of this table calc is ${measure1:total} + ${measure2:total} , rather than computing ${measure1} + $[measure2} for each row and then totaling them somehow. This content is subject to limited support.                 

290
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

What is a derived table?Knowledge Drop

Last tested: Jun 29, 2021 In SQL, a derived table is a subquery that is used in the FROM clause of a query.Example:SELECT * FROM (SELECT a, b, c FROM table) derived_table ;;In the above example, SELECT a, b, c FROM table is our derived table.In Looker, derived tables follow this same concept; however Looker does not generate a subquery in the FROM clause of the main query. For non-persisted derived tables, known as ephemeral tables, Looker generates a derived table using a common table expression (CTE) or a temporary table. CTEs are used for dialects that support them, like Redshift and Snowflake. For dialects that do not support CTEs, like MySQL Aurora and MariaDB, Looker creates a temporary table on the database for derived tables. These CTEs or temporary tables are then used in the FROM parameter of the main query. Example CTE:With derived_table AS (SELECT a, b, c, FROM table)SELECT * FROM derived_table ;;  Example Temp Table:CREATE TEMPORARY TABLE derived_tableSELECT a, b, c, FROM

6070
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

When updating an instance, will schedules that are scheduled to send while the instance is down send once it’s back up?Knowledge Drop

Last tested: Mar 19, 2019if they don’t start before the update, they will send out once it’s back up - if they are in process during the update, Looker will try to finish them after the update This content is subject to limited support.                   

80
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

What timeframe is the "Average Build Duration" of a PDT calculated on?Knowledge Drop

Last tested: Oct 24, 2019 We only keep data in PDT System Activity explore for 30 days. This means that the "Average Build Duration" only considers the most recent 30 days. This content is subject to limited support.                   

200
A
Looker Staff
1 year ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

When "New Account Notifications" is on, will I get notified when new embed users are created?Knowledge Drop

Last tested: Apr 22, 2019Nope! New Account Notifications are only sent for non-embed accounts. This content is subject to limited support.                   

130
A
Looker Staff
1 year ago
brecht
Former Looker
brechtFormer Looker
published in Technical Tips & Tricks

Can Looker use Stored Procedures?Knowledge Drop

Last tested: Jan 21, 2019 NoThere is a semi-workaround for this with the `create_process` parameter with sql_step subparameters. If your database dialect uses custom Data Definition Language (DDL) commands, you can use create_process to create PDTs, by defining a list of SQL statements that will be executed in the order listed. This content is subject to limited support.                   

6961
S
1 year ago
andrew.rose
Looker Staff
andrew.roseLooker Staff
published in Technical Tips & Tricks

What standard are Looker's timezones based on?Knowledge Drop

Last tested: Apr 2, 2019 We have a list of timezone settings Looker can use in this doc here: https://docs.looker.com/reference/embedding/timezones​But, where do these timezone names come from? Did we at Looker just make up all these names for different timezones?Nope! These are the names provided by the IANA Time Zone Database. Here's the link to the IANA website, that has downloadable lists of all these timezone names (if users want to access these names in a script they're writing, for example): https://www.iana.org/time-zones​This content is subject to limited support.                 

410
andrew.rose
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

What permission do I need to schedule based on a datagroup?Knowledge Drop

Last tested: Apr 7, 2020 Besides the general scheduling permissions, you need the develop permission on the model where the datagroup is defined AND see_datagroups. This means that only developers can schedule based on a datagroup.You do NOT need update_datagroups —this permissions is only for seeing datagroups in the Admin panel.Changes from Version 7.8+Non-develop permissioned users can select "Datagroup Update" when defining schedules. This content is subject to limited support.                  

480
chris.seymour
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

What happens to my `models-user-X` directory when I switch branches?Knowledge Drop

Last tested: Aug 28, 2018The models-user-X directory does NOT act as a "pointer" to a permanent local copy of the shared branch's files. Instead, when you create a new shared branch in Looker, a branch on the remote repo is created; this branch contains all of the commit history for the branch. Then when each other user enters the shared branch in dev, they fetch from the one, central remote repo, creating file copies from that repo in theirmodels-user-X directory (and deleting the files for the branch they switched away from). This content is subject to limited support.                   

30
chris.seymour
Looker Staff
1 year ago
bryce.heltzel
Looker Staff
bryce.heltzelLooker Staff
published in Technical Tips & Tricks

When using user-specific connections, is the cache still shared?Knowledge Drop

Last tested: Jan 22, 2019Just using user-specific connections does not disable the shared cache because if two users share the same username for the database, the shared cache will still be used. If the usernames vary though (which is the point of this feature), the shared cache will not be used This content is subject to limited support.                   

390
bryce.heltzel
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

Explore from Here matches what's on my dashboard tile, but the edit modal for the tile does not. Why?Knowledge Drop

Last tested: Apr 29, 2019This is most likely because there are dashboard-level filters being applied to the tile.  The explore from here will match exactly what's on the dashboard, including applied dashboard-level filters.  The edit modal for the tile will show only the query information saved on the tile itself, which means it will run the query without any dashboard-level filters applied.  This content is subject to limited support.                 

630
chris.seymour
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

Column is ambiguous SQL ErrorKnowledge Drop

Last tested: Sep 6, 2018 You may see an error that says something like Column 'id' in field list is ambiguous. This error means that there is a field name that is present in more than one table, so it needs to be scoped with the table name to avoid ambiguity: using orders.id instead of just id will resolve the issue.Proper LookML references should always avoid these errors, so if you are seeing this, you are most likely doing something like using just field_name instead of ${field_name} or ${TABLE}.field_name. Check out this help center article: https://help.looker.com/hc/en-us/articles/360001298768-How-to-reference-views-and-fields-in-LookML This content is subject to limited support.                 

12800
chris.seymour
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

Will my LookML references evaluate if the liquid condition that directs to those references isn't met?Knowledge Drop

Last tested: Mar 4, 2020 Yes, the LookML references will always evaluate, even if the SQL containing the reference doesn't end up in the final query due to an unmet liquid condition. Looker checks for all dependencies before applying liquid conditions, which causes LookML references to be evaluated even when they don't need to be.A common example is when in_query is used in a liquid condition. For example:{% if products._in_query %}${products.products_sku_revenue}{% else %}SUM(${order_revenue}){% endif %} In this situation, product._in_query will always evaluate to true. This is because the ${products.products_sku_revenue} LookML reference is evaluated before the liquid condition, and the LookML reference will automatically bring the products join into the query.Some other examples:Example 1 (unnecessarily pulls in my required_fields):measure: simple_avg {type: averagesql: ${my_field};;}measure: running_total_example {type: numbersql: avg(${simple_avg}) OVER (order by ${created_week} )

420
chris.seymour
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

Scientific notation with n significant figures in table calcKnowledge Drop

Last tested: Oct 25, 2018 You can replace n with the number of sig figs in this formula:concat(round(${view_name.field_name}/power(10, floor(log(${view_name.field_name}))), n-1), " x 10^", floor(log(${view_name.field_name}))) Example with n=2:concat(round(${orders.count}/power(10, floor(log(${orders.count}))), 1), " x 10^", floor(log(${orders.count})))  This content is subject to limited support.                   

560
chris.seymour
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

Can I host a custom visualization on a private github repo?Knowledge Drop

Last tested: Aug 28, 2020 Not if you want it to work consistently! Users who are signed into Github and have access to the private repo will be able to see the custom vis, but if they are not signed in to Github, the custom visualization will not work, and Looker will not tell them why. Furthermore, PDF and PNG downloads of the custom visualization will also fail, since Looker will not sign into your Github account to generate the file. More detail:At the time a browser is loading a Looker dashboard page, it will make network requests to fetch the custom visualization javascript source. You will need to host those files somewhere that can be reached by any browser that can load your dashboard, including the “Headless” browser that Looker uses internally to generate PDFs and PNGs. Since there’s no way to guarantee that everyone who visits your dashboard is already signed-in to github with access to the private repo, you will probably need to “publish” the custom visualization source cod

670
chris.seymour
Looker Staff
1 year ago
chris.seymour
Looker Staff
chris.seymourLooker Staff
published in Technical Tips & Tricks

Why is my API call returning a 403 Forbidden error?Knowledge Drop

Last tested: Dec 28, 2018 One possible reason is that the URL is missing the port number: https://mycompany.looker.com/api/3.0/login instead of https://mycompany.looker.com:19999/api/3.0/login . Leaving the port unspecified will use the default port of 443, which isn't used for API calls. Adding :19999 to the URL should resolve the issue.This can also happen when using an out of date version of the Ruby gem. Make sure to keep those gems updated. You can check at https://rubygems.org/gems/looker-sdkThis can also happen when you don't include the /api/<version number>/ portion of the URL. If they try to hit https://mycompany.looker.com:19999/login, they'll see a 403 response. This content is subject to limited support.                  

10690
chris.seymour
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