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

V
Looker Staff
vishal.dharmadhikariLooker Staff
published in Technical Tips & Tricks

Are pbf tile formats supported in Looker when creating custom map layers?Knowledge Drop

Last tested: Nov 20, 2020Nope, we will actually need to use .mvt files. Please refer to this Community post on setting up the custom map layer. If you have .pbf files, you can use Tippecanoe to output mapbox vector tiles (.mvt) just as easily as it can do protobufs (.pbf). This content is subject to limited support.                   

610
V
Looker Staff
1 year ago
xin
Looker Staff
xinLooker Staff
published in Technical Tips & Tricks

How to trigger datagroup/PDT rebuilds X minutes past each hour between certain hours of the day?Knowledge Drop

Last tested: Mar 5, 2021 The below examples are created for Bigquery.If you just want to trigger the datagroup/PDT rebuilds 10 minutes past each hour:if current minute is equal to or greater than 10, return the current hour, if not return current hour -1.SELECT IF(EXTRACT(MINUTE FROM CURRENT_DATETIME())>=10, EXTRACT(HOUR FROM CURRENT_DATETIME()), EXTRACT(HOUR FROM CURRENT_DATETIME())-1) 10 minutes past each hour between 9 and 19:Similar logic like above, however be aware that we use 18 rather 19, the last rebuild should be 18:10, and after 18:10, it should always return 18 until the next business hours starts at 9.SELECT IF(((EXTRACT(HOUR FROM CURRENT_DATETIME()) BETWEEN 9 AND 18) AND(EXTRACT(MINUTE FROM CURRENT_DATETIME())>=10)), EXTRACT(HOUR FROM CURRENT_DATETIME()), if((EXTRACT(HOUR FROM CURRENT_DATETIME()) BETWEEN 9 AND 18), EXTRACT(HOUR FROM CURRENT_DATETIME())-1,18 ))  This content is subject to limited support.                   

1850
xin
Looker Staff
1 year ago
xin
Looker Staff
xinLooker Staff
published in Technical Tips & Tricks

Custom visualization error: Cross-Origin Read BlockingKnowledge Drop

Last tested: Mar 3, 2020 If you see "Cross-Origin Read Blocking" error for self-created custom visualizations, and the host url is https://raw.githubusercontent.com/....This is due to the fact that https://rawgit.com/ is no longer in service. You should migrate the js file into other services such as S3 bucket, GCS or https://raw.githack.com/ etc. This content is subject to limited support.                 

980
xin
Looker Staff
1 year ago
xin
Looker Staff
xinLooker Staff
published in Technical Tips & Tricks

How to control the comparison arrow in a Single Value viz?Knowledge Drop

Last tested: Aug 30, 2018 According to the doc, positive numbers will display an upward arrow and negative numbers will show a down arrow. You can flip this so that "Positive values are bad".Beyond that, you'll want to use table calculations to manipulate your values accordingly.For some use cases, we might want to compare two values, for example: Sales value this month vs last month, we want to use arrow to indicate if this is an increase or not, we can use a table calc to do this using if() function.if(${last_month}>${this_month},${this_month}*(-1),abs(${last_month})) This content is subject to limited support.                  

3310
xin
Looker Staff
1 year ago
xin
Looker Staff
xinLooker Staff
published in Technical Tips & Tricks

Can I create a use a derived table in the subquery rather in the CTE?Knowledge Drop

Last tested: Jan 21, 2019 Yes you can. Actually this will not be a derived table any more, but the query will be faster. So how do I do that?The trick is with `sql_table_name`!Instead of using a table name in the `sql_table_name`, write the full subquery there, then define all the dimensions and measures as usual. The generated sql will be a subquery. This content is subject to limited support.                  

4600
xin
Looker Staff
1 year ago
xin
Looker Staff
xinLooker Staff
published in Technical Tips & Tricks

Can I convert table calculation from dimension type to measure type?Knowledge Drop

Last tested: Jan 21, 2019 Sometimes when we create a table calculation, we reference a dimension as condition, and in fact we want to use it as a measure type.The easiest way to do is referencing a measure value in the formula:count(dimension_type)+measure_type*0  This content is subject to limited support.                 

4180
xin
Looker Staff
1 year ago
zlebowitz
Looker Staff
zlebowitzLooker Staff
published in Technical Tips & Tricks

Why do we recommend many_to_one joins over one_to_many joins?Knowledge Drop

Last tested: Aug 2, 2019Minimize joinsBecause in most scenarios, the most granular table usually has the most valuable information, and is therefore the most likely one to always be necessary in your queries! For example, for a B2B SaaS company, most of your sales involve opportunity data (because that's where # of deals and $ ACV live). If you therefore start your explore with the Opportunity table, and then join into other tables (which usually have many_to_one relationships), your queries will on average contain fewer joins than if you started your explore with a less used table, like accounts.Avoid Symmetric Aggregates when possibleLooker is less likely to unnecessarily use symmetric aggregates if you define your explore from the most granular table. Let’s say you have order_items, orders, and users. You could define your explore starting with order_items (most granular) or users (least granular)Starting with the most granular table:explore: order_items { join: orders {   relations

18730
zlebowitz
Looker Staff
1 year ago
Zam
ZamMember
published in Technical Tips & Tricks

How to migrate SAML accounts when an email domain changesKnowledge Drop

Last tested: Jul 6, 2020 SAML has a predefined notion of a ‘nameid’ in the saml response document. We use that as the unique id for looker users. If you are not changing that per user, then all is good.If you are changing that, then you'll have to use the API to add credentials_email to all of the existing users that have the email of the new domain. Then when you move to the new domain, select the "merge users by email" option and it should be okay.Note: A colleague made an extension to help with this:https://github.com/davidtamaki/admin_power_packPlease note this is not officially supported resource - if you have a migration situation, please reach out by  filling an Issue in the git repo to get some help through it! This content is subject to limited support.                  

2310
Zam
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Why do large HyperSQL internal dbs cause problems?Knowledge Drop

Last tested: Feb 4, 2019 HyperSQL is an in-memory database. That basically means it needs to be loaded into memory to do its thing (store configuration, users, and other metadata). More specifically, the instructions to create the database (DDL, i.e.: data definition language) are persisted to disk. At startup, the script loads this into main memory and constructs the database in-memory. On a busy instance this database can grow in size, which leads to performance issues, Java memory pressure, and extremely long startup times. A concrete example is if more than the recommended 60% of main memory is allocated to the Looker java process, then the internal backend might be pinched to acquire the amount of main memory it needs to do its thing.https://docs.looker.com/setup-and-management/on-prem-mgmt/migrating-to-mysql This content is subject to limited support.                 

830
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 is cache_only_miss in the system activity History explore?Knowledge Drop

Last tested:  May 14, 2020 Looker has a feature that will fetch results from the cache if they existed before the page has fully loaded. If the cache only check does not find any results, we’ll mark that as a cache_only_miss.If we do find things in the cache there will be a complete record with a results_source of cache.If there is a miss (cache only miss) the queries will be executed again (without the cache only requirement) and the queries will be run from the database. This content is subject to limited support.                 

930
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 there a maximum value you can set user query limits to?Knowledge Drop

Last tested: Jan 21, 2019 No, there isn't a max per user query limit value.However, while there is no "max" value, as best practices we wouldn't suggest setting it higher than the connection pool size. Ideally, less than the connection pool size if you want to equally allow all users to run queries. This content is subject to limited support.                 

2030
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 did my query fail and have status cancelled?Knowledge Drop

Last tested: Mar 27, 2019 There can be various reasons for this. At the most simple level:The user may have closed the browser page. The user may have put their computer to sleep or to away mode.If queries seem to be getting killed after a consistent amount of time (like every 60 seconds), there may be a timeout set somewhere. This Help Center article has more info for that case. This content is subject to limited support.                 

2050
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 I create view files from db views?Knowledge Drop

Last tested: Nov 24, 2020 Yes. However, you may have to do it SQL Runner because the generator won't find them in all cases. Specifically,In SQL Runner, run SELECT * FROM <table> Click the gear in the top right and choose "Add to Project".The generator looks at the database's information schema, which seldom contains view information. This is also why views many times don't show up on the left hand side of SQL Runner.Some dialects can find Views, however. Snowflake views, for example, do show up in SQL Runner and Create View from Table. If you aren’t able to create the view file or see the view files in SQL Runner but you are able to do so in Snowflake, check to see if their permissions in Looker are the same as those in Snowflake. In other words, try to view the view files as the Looker user in Snowflake. This content is subject to limited support.                 

10200
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 gets stored in the internal Looker application database?Knowledge Drop

Last tested: Mar 5, 2020 Essentially, everything about the Looker application! With one big exception - LookML is stored in a file directory and remote git repo.Metadata about users, permission sets, folders, content, schedules, etc. are all stored in the internal application database. This content is subject to limited support.                 

1270
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 timezone are the i__looker / System Activity timestamps in for the history explore?Knowledge Drop

Last Tested: May 22, 2019 At a high level: i__looker/System Activity timestamps are in System Timezone - the timezone for the server that Looker is running on. This is not tied to any DB timezone setting or any timezone settings in the Looker platform.Looker-Hosted InstancesThe i__looker/System Activity timestamps are UTC on all hosted LookersIf you host your own instancesIt’s possible that if you host your own instance, you may be in another system timezone.Chat Tip: The i__looker/System Activity timestamps will not change and are not dependent on Application Timezone, Database Timezone, or any other Instance Timezone setting. Solely the System Timezone. Chat Tip #2: Changing the System Timezone is not a trivial change and is not recommended. If an end user needs to change/shift individual timestamps, then they are better off using table calcs to adjust the time. for example: add_hours(-5, ${time})  This content is subject to limited support.                 

1350
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

Default API port on GCP and Azure (why can't I connect to the API for instances hosted on Kubernetes)Knowledge Drop

Last tested: Apr 18, 2021 Default API port on GCP/Azure hosted instances is 443 - it’s no longer accessed via hitting port 19999. A layer 7 load balancer will redirect it internally to that port. Note that AWS-hosted k8s (EKS) has port 19999 set up to forward to 443.K8s can only route requests by domain name and URL paths - NOT by port number.Make sure that when you are making API requests, that the url always begins with /api/3.1/*, especially for login. If you try to use /login for the API, you'll end up at the Looker UI login page, which won't work for API requests.This might come in handy when using tools that leveraging looker API's, such as gazer, henry, etc.You can double check by hitting the /alive endpoint on a given port to check:curl -i https://[instanceurl]:19999/alive curl -i https://[instanceurl]:443/alive  This content is subject to limited support.                 

940
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't see/access the company / markdown homepageKnowledge Drop

Last tested: Jul 6, 2020 When adding a Looker URL as homepage, admins need to make sure all your users have access. One reason your colleagues may not be able to see the homepage, especially when seeing a 403 error, is because they don't have model permissions to view the page you have linked.WorkaroundOnly users with the see_lookml permission will be able to view a markdown document using the URL from the IDE. However, if you select View Document from the little arrow dropdown on the markdown file in the IDE, it allows you to view the document outside of the IDE. This URL requires only the access_data permission to view, but users will still need a role with the access_data permission on at least one model in the project where the Markdown file lives. If admins do not want to give access_data to all users for a model in an existing project, they can use the following workaround:Create a new dummy project. Put the custom homepage markdown inside the project. Create a new dummy model in

1190
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 non-admins have certain admin privileges? System Activity, PDT panel, queries, logs, etc.Knowledge Drop

Last tested: Jan 21, 2019 Currently, the only admin privileges you can give to non-admins are:see_queriessee_logssee_userssudosee_schedulessee_pdtssee_datagroupsupdate_datagroupssee_system_activity (new as of 5.20)(from permissions list)It's not currently possible for non-admins to:manage authentication systems manage actions in the Action Hub create or edit users edit/test connections from the Admin panel  This content is subject to limited support.                 

340
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 doesn't my user COUNT() in History explore match total existing users? (System Activity)Knowledge Drop

Last tested: Nov 7, 2020 The History explore in System Activity is NOT the best place to see ALL users, looks, dashboards on the instance. The reason for this, is because History only tracks query events, so if a user has not done anything in 90 days, or a look or dashboard has not been queried in 90 days, it will not show up in History.For all of your content exploration needs, use the following explores:/explore/system__activity/look/explore/system__activity/dashboard/explore/system__activity/userFor more details on the History explore, see our documentation on the History Explore in System Activity. This content is subject to limited support.                 

460
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

When extending an explore, can I exclude an existing parameter like `always_filter` or `conditionally_filter` on the new extended explore?Knowledge Drop

Last tested: May 6, 2020 No, we currently cannot leave out a specific parameter on the extended explore. Check out LookML refinements. Extending a view or an Explore is ideal for scenarios where you want to have multiple versions of the view or Explore. But if your goal is simply to modify a view or an Explore without editing the LookML file that contains it, you may want to use a refinement instead.WorkaroundsOption 1: Use a wildcard value to override (filter parameters only)For some parameters (like always_filter), you can set its value:`input to anything, like a string wildcard "%". This would override the value in the base explore and only works with string type fieldexplore: extend_test {extends: [products_required]always_filter: {filters: [brand: "%"]}} explore: products_required {view_name: productsalways_filter: {filters: [brand: "Bag Girls"]}} Option 2: Create a new base explore1. Copy your current base explore (call this "A") into a new base explore (call this "B").2. Remove

5760
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 I use Table Calculations to show total number of days in a month?Knowledge Drop

Last tested: Feb 18, 2018 Yes, we can do that in Table Calculations.  Given a date dimension ${date}, this calc returns the number of days in the month of ${date}.extract_days(add_days(-1, date(extract_years(add_months(1, ${date})), extract_months(add_months(1, ${date})), 1))) This content is subject to limited support.                 

8380
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 I use/reference a `filter:` of type: number in a liquid statement?Knowledge Drop

Last tested: Jan 21, 2019 Yes, you can do this using the assign Liquid syntax. For example:filter: filter_value_to_pass {type: number} measure: my_measure {type: sumsql: ${TABLE}.field;;html: {% assign var=_filters['filter_value_to_pass'] | plus: 0 %}{% if value < var %}<div style="color: blue">{{ value }}</div>{% else %}<div style="color: orange">{{ value }}</div>{% endif %} ;;}Where | plus: 0 casts the var value from String to Integer. You could could also use | times: 1.…html: {% assign var=_filters['filter_value_to_pass'] | times: 1 %}...  This content is subject to limited support.                 

460
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

How to check the data type of a table's columns [Examples: MYSQL, POSTGRESQL, REDSHIFT, BIGQUERY]Knowledge Drop

Last tested: Jan 21, 2019 In SQL Runner, you can use these queries:MySQL:SELECTCOLUMN_NAME, DATA_TYPEFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA = 'db_name'ANDTABLE_NAME = 'table_name'ANDCOLUMN_NAME = 'column_name PostgreSQL:pg_typeof(col_name) Redshift:SELECT "column", typeFROM PG_TABLE_DEFWHERE tablename = 'table_name'AND "column" = 'column_name'BQ Legacy and Standard don't support these types of queries. But if we try and do an incorrect cast of a column in BQ, the error message will tell us the data type. 

307120
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 I click or double-click on a visualization legend series to show one specific series?Knowledge Drop

Last tested: Oct 14, 2020 YES! Use "Alt + click" on WIndows or "Option + click" on MacOS on visualization legend series to show one specific series on the visual This content is subject to limited support.                 

490
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 only sometimes need substitution operators?Knowledge Drop

Last tested: Jul 4, 2018 The difference is that ${foo} refers to the value in the field, whereas foo refers to the field itself. So when performing math or string operations, we'd use ${} like so: dimension: do_math {type: numbersql: ${foo} - ${bar} ;;}because we want the values of foo and bar to be pulled in and operated on. We do not use ${} when we are referencing the field itself, for example in drill_fields or order_by_field where we are not describing the relationship between the values, but just a relationship between the fields. So we do:measure: sum_something {type: sumsql: ${something} ;;# use ${} since we want the values to be summeddrill_fields: [foo, bar, baz]# no ${} since we don't care about the# values, only that these are the fields to use} This content is subject to limited support.                 

270
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