Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Looker Connect Training

Cloud Certifications

Community
WelcomeGetting started136 News & announcements123 Who's hiring436 Release notes (archive)63 Lesson Comments15 Challenge10
Looker developmentLookML1773 SQL308
Exploring & curating dataExplores541 Dashboards & looks1519
Application developmentEmbedding Looker / Powered by Looker151 Open source projects53 Looker API368
AdministrationUser access & management66 General Looker administration440
Data science & analyticsGot a question?78 Share your best practices16
コミュニティフォーラム (Japanese)ニュースと告知49 ヘルプとサポート14 コラム69
The Kitchen TableTechnical Tips & Tricks1694 Common Account Questions14 Blog Archives347 DCL Mic Drops5 Video Tutorials 🎥1

Ask question / Create topic
Login

Community
The Kitchen Table
Technical Tips & Tricks

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

1,694 Topics
251 Replies

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

1694 Topics

Recently active

Most replies Most views Newest first

Department of Customer LoveModerator

published in Technical Tips & Tricks

Selected articles about Looker API & SDK use cases

We have gathered a list of community and help center articles covering some major API & SDK use cases. We hope this makes it easier for you to browse and navigate. The code examples are for demo purposes only. For any further questions on those use cases please post a comment under those articles directly. GeneralI Mastered Looker’s Interactive API Docs. What Now?Introducing the API Explorer: Interactive documentation for the Looker API & SDKsDefault API port on GCP and Azure (why can’t I connect to the API for instances hosted on Kubernetes)Quickstart: Looker API Ruby SDKWhy is my API call returning a 403 Forbidden error?Easily loading JSON to a dataframePostman & Looker (1)Postman & Looker (2)Is there a limit to the number of API requests you can send at one time?Migration from API 3.0 to 3.1: Change Log & Notes Data export and DeliveryRun Looks from Google Spreadsheetes Using Looker APICreating a Data Dictionary Using Looker’s APIGenerating a Data Dictionary in G

Department of Customer LoveModerator

published in Technical Tips & Tricks

Selected articles about LookML

Selected articles about LookML We have gathered some selected community and help center articles and organised them in different topics. Hope this makes it easier for you to search and navigate. General Why do we recommend many_to_one joins over one_to_many joins?How to add a new row of data with LookMLAdvanced LookML - Hub and Spoke modelingNested Data in BigQuery (Repeated Records) Cohort Analysis and Retention AnalysisAdvanced LookML - Cohort and Retention AnalysisCreating Dynamic TiersCalculating Cohort Size for Cohorting[Analytic Block] - Cohorts as Filters using Templated Filters[Analytic Block] Daily, Weekly, Monthly Active UsersCombining multiple transactions into a single event table[Analytic Block] - Customer Lifetime Value - Simple ApproachSet up Flexible Retention Analysis without Derived Tables[Analytics Block] Customer Retention and Lifetime Value - Survival Style[Analytic Block] Retention Analysis Period Over Period Analysis Methods for Period Over Period (PoP) Analysis

Department of Customer LoveModerator

published in Technical Tips & Tricks

Selected articles about SQL use cases in Looker

Selected articles about SQL use cases in Looker We have gathered a list of community and help center articles organised in different topics. Hope this makes it easier for you to search and navigate. TroubleshootingCommon SQL error troubleshooting tips in Looker Calendar tableHow to combine a retail calendar with retail sales dataGenerate Date Series/ Create a calendar view of future datesGenerating a sequence of numbers or datesHow to Set Multiple Week Definitions in Looker Conditional joinCASE in sql_on: Conditional Joins when field = value DatagroupPDT trigger value at a specific time and day of week, once a weekHow to trigger datagroup/PDT rebuilds X minutes past each hour between certain hours of the day Parsing dataParsing JSON data in LookerUsing Postgres and JSON Functions and Operators to Parse JSON FieldsDefining JSON Objects as LookML Dimensions Query performanceEXPLAIN functionA (Hitchhiker’s) Guide to Query Performance in Looker SQL in LookML fieldsCase when lookml vs. sqlc

published in Technical Tips & Tricks

Breaking News: Knowledge Drops are LIVE!

Democratizing information has always been a core tenet of Looker’s values. It’s how we all become better thinkers and better doers. This ethos isn’t just a philosophy or a catchy marketing phrase; it’s a practice - and it’s one we’ve always taken to heart. So in true Looker-fashion, we’re excited to announce: We are giving you access to hundreds of knowledge articles that were previously internal-only. These will be published to Community over the next 6 months for you to explore, learn, and discuss! Dubbed ‘Knowledge Drops’, these articles were originally created by Lookers, for Lookers. They document many of the insightful tips, tricks, and Looker how-to’s that we have collected over the years. They have been a go-to resource for Lookers whenever we have product questions or want to become even greater Looker experts ourselves. And now, we’re offering them as a resource for all! How does this all work? All of these articles now live within a new category: The Kitchen Table. Each

Department of Customer LoveModerator

published in Technical Tips & Tricks

Can you embed a Looker iframe in PowerPoint / Google Slides?Knowledge Drop

Last tested: Sep 8, 2020 Looking for a solution to export a dashboard of visualizations to a Google Slide deck on their template. Below were the options I was able to outline. Spoiler alert: none are ideal.(1) Python Script Overview Allows you to take a template slide deck and push viz images onto them Pros Most complete, detailed solution Works with Google Slides Very automatic solution once complete - would scale well Cons Requires significant dev time to create the solution Minor maintenance may be required to make changes over time (2) LiveSlides Overview Embed live visualizations into MS PPT presentation Pros Embed interactive visualizations into a PPT presentation Very easy to get set up Will update with live data with no work. No need to export images Cons Only available in MS PowerPoint Cannot be exported to PDF (3) Use a webhook tool like Zapier Overview Use a tool like Zapier to send a visualization from Looker and place it on slides Pr

Department of Customer LoveModerator

published in Technical Tips & Tricks

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentialsKnowledge Drop

Last tested: Jan 20, 2021If you're seeing this error, check to see if the table that is being queried is a google sheet that's stored in Drive.If that's the case, make sure that the Service Account for BQ that Looker is using has access to the Drive. This content is subject to limited support.

Department of Customer LoveModerator

published in Technical Tips & Tricks

Advanced Templated Filters

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @tig This page assumes you understand the Templated Filters and Parameters page in Looker Docs. Be sure to check out the parameter discussion on that page, because using a parameter field is a better approach for many use cases.The advanced templated filter applications discussed below are useful for a few use cases, such as making a derived table obey top-level filters for performance or aggregation reasons, creating dynamic filtered dimensions and measures, and creating dynamic WHERE clauses in queries. Referencing a Dimension Instead of a Filter in Templated Filter TagsIn this example, we have a filter field to accept user input:filter: my_filter_name { ... }This filter dynamically modifies the derived table's SQL, like so:{% condition my_filter_name %} ...Alternatively, a dimension filter can als

Department of Customer LoveModerator

published in Technical Tips & Tricks

Dynamic Rank in Visualization Using Parameter

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: Zachary AragosaThis article requires knowledge of Liquid variables — specifically, {% parameter parameter_name %}. Using a LookML parameter called parameter, you can let users choose the top N ranking to display in visualizations on a dashboard. For example, if a user wanted to see the top seven product categories by total sale price, they could enter that value in a filter and the results would update dynamically:The ProcessTo implement the above solution, we need to perform the following steps: Add a type: number dimension and parameter to the appropriate LookML view (typically the same view that contains the fields you would like to visualize into rankings). The parameter will surface as a front-end filter for users who want to specify the desired number for rank, and the dimension will reflect th

Department of Customer LoveModerator

published in Technical Tips & Tricks

Templated Filters and Derived Tables - Variable not found "parameter"

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @Zam Derived tables are a common use case for applying templated filters. This is because templated filters allow us to restrict tables to certain criteria prior to building them.This approach is particularly useful in columnar databases, as they have a single sort order. Thus, it can be more efficient to isolate a subset of data from a large table as a joined subquery, and transform the subset on the fly. With this method, we can avoid joining in the entire large table.A great example use case of templated filters in derived tables is performing a dynamic cohort analysis, as detailed in the Help Center article [Analytic Block] Creating Custom Cohort Analysis. Referencing Dynamic Derived Table Views CorrectlyIn Looker, Explores and joins must reference the view name that is defined in the underlying

Department of Customer LoveModerator

published in Technical Tips & Tricks

Aggregate Awareness using _in_query

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: Russel Garner You can define aggregate tables in your LookML projects, and Looker will use aggregate awareness logic to find the smallest, most efficient table available to run a query while still maintaining correctness. See the Aggregate Awareness documentation page for information.With the Liquid attribute _in_query, we can check which fields a user has selected in an Explore and make on-the-fly modifications to SQL. This opens a large number of potential use cases; this article focuses on a concept called "aggregate awareness," which can be used to streamline performance by reducing database resource consumption.Many Looker customers report on very large volumes of granular data at an event or transaction level. This is helpful for getting to the bottom of specific scenarios like examining individ

Department of Customer LoveModerator

published in Technical Tips & Tricks

Filtering a View Using a Field from Another View without Joining Views

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @Christel_Ilaka The ProblemLet's say that we have two views: Rental and Customer. The Rental view has more than 1.5B records, and each record has a customer_id. The Customer view has more than 200K records with the following columns:customer_id first_name last_nameWe want to filter the data in the Rental view by entering the name of the customer in the filter. However, the customers' names are stored in the Customer view and we don't want to join these two views because of the number of records in both views. A SolutionThis problem can be solved by using Liquid variables and the parameter parameter. In the Customer view, concatenate the ID, first name, and last name to create a new dimension called full_name_and_id: dimension: full_name_and_id { type: string sql: ${customer_id} || '-' || ${first_na

Department of Customer LoveModerator

published in Technical Tips & Tricks

Multiple Schemas with Shared Tables Can Use a Single View Accessed with Parameterized Table Names

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: Lindsey Meyer If you have multiple schemas with shared tables in your database, these shared tables can be represented by a single view file in Looker to prevent duplicate LookML.But if you have a different model for each schema, how do you tell the shared view files to point to the appropriate schema/table for each model?You do this using Liquid templates in the sql_table_name parameter. With Liquid, you can access the _model._name variable, which is the model name of the current Explore in the browser. Based on the model name, you can have sql_table_name return the appropriate schema.table like this:view: table_name { sql_table_name: {% if _model._name == 'model_a' %} schema_a.table_name {% else %} schema_b.table_name {% endif %} ;;}

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

Is there a limit to the number of looks/tiles I can have on a dashboard?Knowledge Drop

Last Tested: May 28, 2020No, but performance can be impaired with too many looks/tiles. We generally recommend a limit of around 25, which is documented here along with a warning in the product when you go above 25. This content is subject to limited support.

Department of Customer LoveModerator

published in Technical Tips & Tricks

A Cure For the one_to_many Blues

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @fabio Suppose you want to build an Explore that lets users start from a "one" table and then fan out into more detailed "many" records from multiple tables. In this situation, you can often feel constrained by the fact that, by default, these joins will conflict.For example, if you have an application where a user can order service and then separately make payments against their account, you will find that there is no direct relationship between orders and payments. So, you will want to let your Explore expose both orders and payments as child entities for a given user, but not with a specific relationship.In the Outer Join on False — or: How I learned to stop fanning-out and love the NULL Looker Community topic, we talk about this approach; however, that approach tries to handle a broader class of p

Department of Customer LoveModerator

published in Technical Tips & Tricks

Uploading Spreadsheet (Excel, Google Sheets) Data into Looker

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified. Author: Mark Stephens The ProblemAlthough Looker does not connect directly to an Excel spreadsheet, there are ways to upload data via a derived table.First, there is a third-party CSV to SQL Tool with which you can easily convert a CSV file into a SQL UNION ALL query, and generate a LookML derived table file to copy and paste into the appropriate project. However, if you have security concerns, there is second, more secure, method.The secondary method utilizes the CONCATENATE function to create a series of SELECT statements to ultimately create a SQL based derived table. This method works only if you have a limited number of columns and rows, such as monthly forecasting data, and is not very scalable for large spreadsheets. This method is also static and will not automatically update if the underlying spread

Department of Customer LoveModerator

published in Technical Tips & Tricks

Splitting Strings into Rows in the Absence of Table-Generating Functions

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.IntroductionMost SQL dialects don't support robust table-generating functions. There are a few common use cases for table-generating functions, such as creating a sequence of numbers or dates, un-nesting a JSON object or array into rows, or splitting a string on a delimiter into rows.In this article, we'll show an example for the last use case: Taking a string containing n distinct comma-separated values and splitting it into n unique rows.SetupSuppose we have a table of products containing id, name, and tags: productsid name tags 123 Scrunch Cloth Pants Set Pants, Throwback, 80s 1287 Le Suit Citrus Breeze Skirt Suit Skirts, Dresses, Business Casual 486 MaxStudio Plaid Romper Jumpsuits, Casual, Rompers Our goal is to un-nest the tags to create a table to map products to tags. We will assum

Department of Customer LoveModerator

published in Technical Tips & Tricks

Converting Shapefiles to TopoJSON

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Looker uses a format for creating map layers called TopoJSON. TopoJSON stores region data in a compact format appropriate for the web. But, what if we have region data in a shapefile instead of in TopoJSON format?You can easily create TopoJSON files from many common shapefile formats. This article demonstrates how to convert a shapefile to TopoJSON using a free online tool called Mapshaper 5. For a drawing tool that will let you create a TopoJSON file by hand, check out geojson.io 3.Note: For pre-built TopoJSON map layers of commonly used shapefiles, see the Looker Map Layers repository in GitHub. The WorkflowVisit Mapshaper 5, and drag your shape file into the window: You'll see a preview of the shape: You can use the info tool to inspect each region in the shapefile to see what properties it contains.

Department of Customer LoveModerator

published in Technical Tips & Tricks

Week-to-Date (WTD) Comparison

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @paola The ProblemWe want to compare the orders we have received this week, up to and including today, with the orders from previous weeks, up to and including the same day of the week. The SolutionWe can create a dimension of type: yesno, which will use only the days of the week equal to or before today. Then, we can filter on that dimension equal to yes to dynamically get only the days of the week we are interested in. ExampleIf today is Thursday, we could compare the orders we have had this week, from Monday through Thursday, with the orders from the previous week, from Monday through Thursday.The first step is to utilize Looker's day_of_week_index timeframe, which displays the days of the week as Monday=0, Tuesday=1, Wednesday=2, and so on.We can create an order date dimension_group, which looks

Department of Customer LoveModerator

published in Technical Tips & Tricks

How to count a non-primary key

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: Lindsey MeyerIf you would like to count (not count_distinct) a field that is not the primary key, you can do this by creating a measure of type: number and performing the count in the sql parameter:measure: count_states { type: number sql: COUNT(${state}) ;;}This measure will generate the following SQL:COUNT(users.state)Measures of type: count_distinct can make use of a filters field parameter, while measures of type: number cannot. In addition, while this does not apply to COUNT DISTINCT, other aggregation types — like SUM, AVERAGE, and COUNT — should be represented with specific measure types rather than number.These difference allow Looker to properly apply symmetric aggregates in order to avoid fanout values when exploring and using aggregations.

Department of Customer LoveModerator

published in Technical Tips & Tricks

Dynamic Rankings (Filtering Dimensions by Custom Rank)

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.This article is about dynamically filtering dimensions based on an aggregation. You can use the examples from this article to filter a dimension by its relative ranking over any metric. The most common use case is filtering pivoted data by revenue (or other aggregation), rather than an alphanumeric string, in order to see only the top N pivoted values.Dynamic Rankings in LookerFor example, if we want to look at the number of users in each country, but want to filter the countries by the top 10 in revenue, we might think to build a is_top_10 dimension like this in our Orders view:dimension: is_top_10 { type: yesno sql: exists( select * from ( select country from orders group by country order by sum(revenue) desc limit 10 ) top_10 where ${country} = to

Eric_HutchesonLooker Staff

posted in Technical Tips & Tricks

Using BigSearch in Looker to search across tables and columns

Google BigQuery recently released a new feature called BigSearch, which allows customers to rapidly find data points without having to scan all the text in their table or even know which column the data resides in. This functionality can be extremely useful when working with log data, or other unstructured/semi-structured datasets that require searching for specific strings and values (like IP addresses).This article focuses on how to bring that capability to your Looker dashboards and explores.BigSearch allows Looker users to search across all columns in a table for specific valuesHow to set it up1. Create the search filter parameterInside the lookml view file for the table that you’d like to search on, create a new parameter field, which will be used to accept the user inputted search values on a dashboard or explore.parameter: big_search_filter { suggestable: no type: unquoted } 2. Add `sql_always_where` to your Explore Next find the explore that you’d like enable the searchi

Department of Customer LoveModerator

published in Technical Tips & Tricks

4-4-5 Calendar Using LookML

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @Hugo_Selbie Many companies use a standardized 4-4-5 calendar for their accounting purposes. In a 4-4-5 calendar, each quarter is broken up into two 4-week periods and one 5-week period to make 13-week quarters. These quarters often start on the first Monday of that period, so the first day of the fiscal year will be the first Monday that occurs after January 1st.There are a couple of ways to access this type of calendar in Looker. Often, companies have a pre-generated calendar that can be joined into Explores in a model file.In cases where there is no pre-generated calendar with the appropriate denotations, we can create a 4-4-5 calendar using LookML date dimensions and some logic to assign the appropriate labels for a 4-4-5 calendar to existing date fields. The main advantage to this approach is tha

Department of Customer LoveModerator

published in Technical Tips & Tricks

Variable Reference Lines for Charts

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: Mark StephensWith Looker it is easy to add a straight horizontal reference line to a visualization. But what if you had variable monthly or quarterly targets, and you needed to show the varying targets? You can create a line whose height varies in different months or quarters. You can do this in LookML by creating a measure that hard-codes the correct values to show in that variable reference line.Examples Since we use the `sql` parameter, the code to create the variable reference lines may differ by the database dialect your project is using.The code to do this, written for Redshift dialect:measure: target_test { type: number sql: (max (case when to_char(${TABLE}.date, 'YYYY-MM') in ('2015-01','2015-02', '2015-03') then 12000 when to_char(${TABLE}.date, 'YYYY-MM') in ('2015-04','

Department of Customer LoveModerator

published in Technical Tips & Tricks

Week Ending Vs. Week Beginning

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @daniel_nelson_looker Looker starts the week on Monday. For example, if today is Thursday, April 11, 2019, today belongs to the week 04/08/2019. If it is Saturday, April 20, 2019, today belongs in the week 04/15/2019. However, some reports must display week-ending dates rather than week-beginning dates. How do you get Looker to display the ending dates for weeks? You can convert the dates to an end date on Sundays.ExampleWe start with the created dimension group. In the next dimension, we add 7 days to end the week on Sunday. This is how it looks in LookML: dimension_group: created { type: time timeframes: [time, hour_of_day, date, week, month, year] sql: ${TABLE}.created ;;}dimension: created_week_ending_sunday { type: date sql: DATEADD( day, 7, ${created_date});; datatype: date} The examp

Department of Customer LoveModerator

published in Technical Tips & Tricks

Using Liquid Split and Array Filters to Format Lists

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @lauren.boltz This post covers advanced subject matter and assumes good, pre-existing knowledge of Liquid and LookML. The Problem In data, objects often have lists of attributes associated with them. For example, a user's profile can contain multiple cities, states, or countries, depending on how often they have changed addresses. A common challenge is organizing and presenting lists of attributes in a user-friendly and digestible way. This article addresses how to render lists into easily readable formats and how to make them more interactive. To achieve this, we will use Liquid, a templating language developed by Shopify, in conjunction with the html and link LookML parameters. The Solution In our e-commerce data set, we have a field called "City List Raw." This is a type: string field that outputs

1
2
3
4
...
68

Page 1 / 68

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 & Conditions

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 & conditions

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

© 2021 Looker Data Sciences, Inc.
community@looker.com
Privacy | Terms | Cookies | Community Guidelines