Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Looker Connect Training

Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
The Kitchen Table
Technical Tips & Tricks

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

1,744 Topics
374 Replies

When you subscribe we will email you when there is a new topic in this category

1744 Topics

Newest first

Recently active Most replies Most views

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

In a table calculation, how do I fill all of the null values of a column with the last non-null value of that column before the current row?Knowledge Drop

Last Tested: Jan 21, 2019 index(${calculation_1}, max(offset_list(if(is_null(${calculation_1}), null, row()), -1 * row() + 1, row()))) There are a few steps in this formula:Get the row number for each non-null row using the row() function Use offset_list to get a list of all of those row numbers from the first row to the current row Take the max of that list of values, which will be the row number of the last non-null row Use the index function to get the value of the field for that row number This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Does SQL Runner use cache?Knowledge Drop

Last Tested: Aug 30, 2018 No, it doesn't. While explores, looks, and dashboards may use cache, SQL Runner executes the query directly against the database.If it looks like SQL Runner is caching, check to make sure that it isn't the database doing that. Some databases such as Redshift may be doing result caching. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Is it possible to merge two Looks, instead of two explores?Knowledge Drop

Last Tested: Mar 15, 2021It is not currently possible to merge saved Looks directly, but there is a feature request here which you can vote on. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I have a filtered measure of type number?Knowledge Drop

Last tested: Sep 6, 2019 No. Cite Docs.ExplanationThe filter for a filtered measure has to be applied prior to the aggregation. For a number type measure, the aggregation has already been done in the other measures it is referencing in the sql parameter, so there's no way to apply the filter.For most use cases, you can just use filters in the other measures that your number type measure is referencing, per the doc. One exception to this is a max or min on a date field, where the aggregation must be done in the sql parameter.WorkaroundsUse CASE WHEN inside the measure to emulate filtering. The below example is a MAX of created_date which is filtering on the field status having the value complete :MAX(CASE WHEN status = 'complete' THEN created_date ELSE NULL END) Perform the filtering in other measures that are not of type number.measure: count_complete { type: count filters: { field: status value: "complete" } } measure: count_total { type: count } measure: percent_complete { type: numb

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why am I seeing Deploy Failed JGIT FetchCommand failed: unknown host?Knowledge Drop

Last Tested: Jul 3, 2018 There are a couple of possibilities:The Looker server can't resolve the name for the git service host. The Looker server can't connect to the git service host via ssh. Use Test Connection to get more information about which step in the connection the error is coming from. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How do I do a ROW_NUMBER OVER (PARTITION BY ...) with a table calc?Knowledge Drop

Last Tested: Jul 1, 2018 match(${products.brand},${products.brand})if(${partition}=offset(${partition},-1) , 1+row()-${partition} , 1 This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How do I change the order of my rank on a table calculation? (ascending, descending)Knowledge Drop

Last Tested: Jan 21, 2019 When you do a rank in table calculations it will show you the rank in descending order so the largest value of the field being ranked as number one. Like so:rank(${price}, ${price}) Price Price Rank $5 3 $28 1 $13 2 To see the rank in ascending order, you can simply make the numbers negative, so that they sort the opposite way:rank((-${price}, (-${price})) So you can see this:Price Price Rank $5 1 $28 3 $13 2 This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Drill to Dashboard - Dashboards in Drill MenusKnowledge Drop

Last Tested: Aug 30, 2017 Info about Dashboards in Drill Menus Chat Tip: In general we recommend leaving this feature off, because of performance implications. Keep in mind this feature ignores content access and so will show a user dashboards that they potentially lack access to. If a user tries to click on a dashboard they lack access to, they will see an error message telling them so.If it's not working correctly, check that:Listens are correctly applied Dashboard is not in a personal space (must be in shared spaces) - note: Even after you fix this it may take a little while (~30 mins) to start seeing the dashboards. You also must clear cache on the query. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Some Useful Looker Keyboard Shortcut TipsKnowledge Drop

Last tested: Aug 20, 2019 Some keyboard shortcuts, hidden functionality, and buttons you didn't know exist!Find the last query you ran: Click your account in the top right of the instance > History > click on any of those to re-open the query! Click the i in the field picker to open up an awesome info menu that saves you going into LookML. To sort by multiple columns, Shift + Click on the headers. On a chart with lots of series, Option + click the Series in the legend to select only that one! This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Extending Multiple ExploresKnowledge Drop

Last tested: Apr 3, 2019How does extending multiple explores work?Say we have:Explore A {}Explore B {}And we'd like to create an Explore C {} That extends explore A and B.Essentially extending an explore makes all of the base explores joins available in the extended explore. Therefore the joins must be relevant- i.e. if Explore C extends Explore A then Explore C must be joined to view a or whatever view Explore A is based on.Same goes with extending multiple explores. The entended explore must be joined to views that the base explores are based on. With the caveat of, if the extended Explore is based on the same view as one of the base explores then no join to that explores base view is necessary. The joins can either be directly in the extended explore or they can be in the base explores (see examples below).Here is an example of what an extended explore might look like:Explore A {}Explore B {}Explore C {extends: [A, B]view_name: Ajoin: B {}}This also works (indirect join):Explore A {

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I reference the 'day_of_week' timeframe in a custom field?Knowledge Drop

Last tested: Sep 6, 2019 No. The day_of_week timeframe yields an error (shown below) - use day_of_week_index instead. Invalid expression: The type of field "fact.day_of_week", date_day_of_week, is not currently supported This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Folding LookML (Collapsing code)Knowledge Drop

Last Tested: Jan 21, 2019 LookML can be folded with the little arrows next to the row number of a specific dimension or measure. It can also be done via the settings of the LookML file. The following link has detailed instructions on how to use folding.https://docs.looker.com/data-modeling/getting-started/lookml-validation#foldingOnly bracketed LookML blocks will be collapsible by default, but it is possible to make any block of code collapsible by using brackets inside comments, as described here. Example:# my long list of dimensions { dimension: user_id {description: "Unique ID for each user that has ordered" # ... (lots more LookML) ... sql: ${TABLE}.most_recent_purchase_at ;;}# } / my long list of dimensions This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How can I find the id of a LookML dashboard?Knowledge Drop

Last Tested: Feb 7, 2019The id of a LookML dashboard can be found in the URL like any other dashboard. The catch is, a LookML dashboard id is NOT an integer. It is model_name::dashboard_name. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

SAML Authentication Failed first time logging inKnowledge Drop

Last Tested: Oct 5, 2018 If group to role pairing and auth requires role is on, a user will not be able to authenticate to Looker for the first time unless they are in a SAML group and that group is listed under the group to role pairing in the Looker SAML setting. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How to filter merged resultsKnowledge Drop

Last Tested: Jan 21, 2019 Create filters by adding those filters in the source queries, either when creating and merging the queries, or by editing the queries from the Merged Results page. Note that you can’t add filter directly to the merged results. More information can be found here: Looker DocumentFor filters post-merge, we can use a yesno if() function in table calculation and hide 'no's from the visualization.You can also apply a dashboard-level filter to any of the source queries, as noted in step 6 of this doc. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How to specify the date range for dimension fill?Knowledge Drop

Last Tested: Jan 21, 2019 You can specify the date range for dimension fill by filtering on your date dimension for that range.Let's say you only have data until the end of 2016 but want the years 2016 and 2017 to appear in your visualization with nulls, dimension fill will only fill in dates until your last date by default. If you add in a filter to filter on something like before 2018, dimension fill will fill in dates through the end of 2017. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Dynamic dashboard filterKnowledge Drop

Last Tested: Sep 6, 2019 There isn’t currently a way to filter on a dashboard by clicking on a field in one of the visualizations. There is a feature request for this.There is a pretty nice workaround for this where we can achieve something very similar using a link.To do this, the first step is to add a filter onto the dashboard for the field that you would like to dynamically filter on. Give the default "any value".Next, add in a link parameter to the field in the LookML and use this as the link: /dashboard/<dashboard_id>?<dashboard_filter_name>={{ value }} This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How do I give a contractor or limited user access to only one folder or dashboard?Knowledge Drop

Last Tested: Jul 10, 2020 In order to view a dashboard, the user must have access to the folder that it's in. So first, you'd want to create a folder that contains only what you'd like the user to have access to. Next in order to view content on the dashboard, the user needs permissions to the models that the looks/tiles are built from. They can be given permission to the model by including that model in a model set that is in a role assigned to them. Additionally, the role's permission set must include the following permissions: access_data, see_looks, see_user_dashboards. Make sure they do not have permissions which would allow them to explore since that is a model specific permission and can expose the whole model to this user.Note that this user will probably still have access to most folders since by default, Looker allows the "All Users" group access to all User and shared folders. If we'd like to hide the folders themselves from the user as well, we'll need to do something simil

adina.katzLooker Staff

published in Technical Tips & Tricks

Dashboard PerformanceKnowledge Drop

Last Tested: Sep 6, 2019 Question: Why is my dashboard taking so long to load?Answer: When we run a dashboard, the query for each tile gets kicked off at the same time. Firstly, we may be hitting the per user query limit or the max connection. Let's say this dashboard has 20 tiles: we would hit the per user query limit which has a default of 15. This means that 15 of those tile's queries would run and the remaining 5 will be queued until a connection thread becomes available. Then as query results are returned. The next query in the queue will run. Similarly with the max connections: if we have max connections set to 50 and 45 user are each running 1 query at the time we run out dashboard - only 5 tile's will run and the queries for the remaining 15 will be queued until connections open up. Secondly, it could be that the query itself is taking a long time to run against the database. See How to cache a dashboard (Note for Sam and Molly - this is a Community Candidate Guru card) for inf

adina.katzLooker Staff

published in Technical Tips & Tricks

Trigger-based PDTs in productionKnowledge Drop

Last Tested: Mar 27, 2020 If a PDT is defined in production LookML and the PDT is persisted using the datagroup_trigger or sql_trigger_value parameter, the regenerator typically handles the creation of the PDT on the scratch schema.The regenerator is the process in Looker that manages the building of PDTs on the scratch schema. At a high level, the regenerator process works by checking the current value returned by the sql_trigger_value/sql_trigger of a PDT and comparing that value to the previous value returned for that PDT. If there has been a change in this value, the regenerator will rebuild the PDT on the scratch schema. More on the regenerator process here.There is one exception to trigger-based PDTs in production being managed by the regenerator -If the PDT is queried via an explore/look/dashboard and it has not yet been built (or rebuilt if the previous build is expired) by the regenerator, the explore query thread will handle the creation of this table. This content is subject

adina.katzLooker Staff

published in Technical Tips & Tricks

PDT Naming ConventionKnowledge Drop

Last Tested: Jan 31, 2022 PDT name structure:LR$[connection_key][hash]_[view_name]All PDT names begin with LR, short for Looker Read. The connection_key is a two letter string that is unique for the connection. The PDT hash is an encoding slug that follows the connection_key and precedes view_name in the SQL table name. (ex: if the PDT SQL table name is LR$ZLYI79XRAB6OU1633ET9D_CS_HEALTH_CALENDAR_DAILY, the connection_key is ZL and the hash is YI79XRAB6OU1633ET9DThe PDT hash encodes the following information:Connection Scratch Schema Instance Slug Underlying LookML View Name Datagroup name, if used SQL of the sql_trigger/sql_trigger_value, if used Underlying SQL of the PDT (including SQL of dependencies) Increment Key Creation TimestampSince the hash includes the creation timestamp, the PDT name changes every time it is rebuilt, with the exception of incremental PDTs. With incremental PDTs, the PDT name remains the same upon each incremental rebuild, unless there is a change to th

adina.katzLooker Staff

published in Technical Tips & Tricks

The PDT Build ProcessKnowledge Drop

Last Tested: Feb 13, 2020 Whether a PDT is built by the query thread or by the regenerator, the build process is the same for all types of persistence.Versions prior to 7.0:The regenerator/query thread first builds a table with a name that begins with LC, short for Looker Create.Once the LC table has been created successfully, if this is the first time the table is being built on the scratch schema, the regenerator/query thread will rename the table to a name that starts with LR, short for Looker Read. This LR table is the table that Looker will query from the UI.If this is not the first time the table is being built on the scratch schema, the regenerator/query thread will first drop the previous version of the LR table and then rename the LC table to LR.Versions 7.0+:An LC table is no longer created prior to the LR table.The regenerator/query thread builds one version of the PDT with a name that begins with LR. There is no longer a drop/rename process. Every new build of a PDT has a u

adina.katzLooker Staff

published in Technical Tips & Tricks

How to cache a dashboardKnowledge Drop

Last Tested: Apr 1, 2019 You have two options here.Instant DashboardsThe first option is to use instant dashboards. With instant dashboards, dashboards display the previous run’s data while new queries are run in the background, letting information appear quickly. This is a labs feature. More information here. LookML-level cachingThe second option is to persist the explores that the dashboard's tiles are built on. This can be done in the LookML using datagroups with the 'persist_with' parameter or using the 'persist_for' LookML parameter. 'persist_with/for' can be defined for the entire model, or for individual explores.Note: Instant dashboards need to be turned off for this method to work. Which to choose?The benefit to instant dashboards is that we can see old information while new information loads. Instant dashboards will always hit the database, so you can be sure you're looking at fresh data.The benefit to datagroups is tighter control over your caching given that you know your E

adina.katzLooker Staff

published in Technical Tips & Tricks

The PDT Regenerator ProcessKnowledge Drop

Last Tested: Nov 4, 2020 Purpose:The regenerator's purpose is to manage the building of PDTs on the scratch schema. Its major tasks are to check datagroup triggers, build new PDTs that have been pushed to production and rebuild existing production PDTs whose trigger values have changed.Number of Regenerator Threads:Every connection has one regenerator thread. Since the regenerator is only a single thread, it can only perform one operation at a time. This means that it can only check a single trigger or build/rebuild a single PDT at a time (unless parallel PDTs are enabled on that connection, then multiple PDTs can be built at a time).There is a maximum of 25 regenerator threads per instance. However, there is still only one thread per connection. This means that 25 PDTs could theoretically build simultaneously only if there were 25 different databases connected to the Looker instance. If there are more than 25 connections with PDTs enabled on an instance, multiple connections will shar

david.chiaramonteLooker Staff

published in Technical Tips & Tricks

How to Choose a Measure to Display DynamicallyKnowledge Drop

Last Tested: Jan 21, 2019 Let's say I have a dimension that I want to see multiple aggregations for one at a time!We can create a parameter with n number of allowed values depending on the number of measures you wish to display:From here, we can create a measure with liquid that listens to the parameter value declared above and then chooses the corresponding measure to display:You can then add the dimension and dynamic measure to an explore, save it to a dashboard, and then add a dashboard filter on that parameter:You should then see similar functionality to this:Congratulations! You've now made a dynamic measure that users can interact with! :)

1
...
58
59
60
61
62
63
64
...
70

Page 61 / 70

Badges

sbrylowskihas earned the badge Friendly Face
carriehas earned the badge Helpful
carriehas earned the badge Community Star
carriehas earned the badge Friendly Face
Dawidhas earned the badge Community Star

Show all badges

Powered by inSided

Terms of Service

Sign up

Already have an account? Login

Social Login

or

Username *

E-mail address *

Company

(Private)

Only you and moderators can see this information

*

Password *

I accept the Terms of Service.

If you’re an EEA-based consumer of digital content, services, or goods, and you’ve agreed to our Terms of Service, then EEA consumer laws provide you with a legal guarantee. Under this guarantee, we’re liable for any lack of conformity that you discover for the one-time supply of digital content or services at any time during the “continuous” supply of digital content or services.
Your national laws may provide an even longer guarantee. Your rights under these legal guarantees aren’t limited by any other commercial guarantees that we provide. If you want to make a guarantee claim or if you would like to delete your account data, please contact us lookerconnect-help@google.com.

loginBox.register.email_repeat

Login to the community

No account yet? Create an account

Social Login

or

Username or Email

Password

Remember me

Forgot password?

Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.

Username or e-mail

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

OK

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.

OK

© 2022 Google. All rights reserved.
Privacy | Terms | Community Guidelines