Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications
Previously internal product knowledge now available for you to explore!
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
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
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
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
Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 7.12, you can deploy any Git commit SHA, tag, or branch to production with Advanced Deploy Mode. This helps consolidate repositories in multi-environment developer workflows, where each environment points to a different version of a codebase. It also gives greater control to one or a few developers or administrators over the changes that are deployed to production.IntroductionThis article provides a comprehensive development framework for managing the promotion of LookML code and content across multiple Looker instances and is intended to aggregate various resources into a cohesive walkthrough, provide a deeper understanding of the advantages and disadvantages of having multiple Looker instances, and present a best practice framework for a development strategy. In This ArticleWhy Have Multiple Looker Instances? Release Management Is a Development Instance Necessary? Benefits and
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.
Last tested: Jul 30, 2019 If you see odd results in a drill's filter string, it may be that the strings content has a character that Looker is trying to escape. Characters like - Looker is trying to escape, so we need to do something to avoid that.To avoid this, you'll want to replace the character(s) that Looker is trying to escape using something like the REPLACE function. This way both the field and the filter results will have the proper character string for filtering. dimension: namesql: REPLACE(${TABLE}.name_field, "-", " ") This content is subject to limited support.
Last tested: Oct 14, 2020 Using Table Calculations, you can add a total column to a stacked bar chart. (Note: If your bar chart is not stacked, you can add a total column by simply checking the row totals box and pivoting on your dimension)METHOD 1: TOTAL COLUMN WITH BREAKDOWNStep 1: Configure your explore section so that it has one extra row than the number that you need to display. For example, in this graph we only want to display March - July, but we'll want to add 1 extra row so that we can display a total column at the end.Step 2: Create two table calculations. The first will create a column that displays the total of the series if it is the max row in the data set. The second will create a column that displays the word "Total" if it is the max row in the data set. Step 3: Hide your original dimension and measure from the visualization so that only the table calculations and the pivoted columns remain in the visualization.METHOD 2: TOTAL COLUMN WITHOUT BREAKDOWN (PRESERVES DR
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 6: Any Two Arbitrary Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw, time,
Looker will not be updating this content, nor guarantees that everything is up-to-date. You can create ad hoc custom groups for dimensions without using logical functions in Looker expressions or developing CASE WHEN logic in sql parameters or type: case fields when the Custom Fields Labs feature is enabled and you have permissions to create custom fields.You can also create ad hoc custom bins for numeric type dimensions without needing to use logical functions in Looker expressions or needing to develop type: tier LookML fields when the Custom Fields Labs feature is enabled and you have permissions to create custom fields.When you are building a chart or table off a high-cardinality dimension, you might want to lump all low-frequency dimensions into an Other bucket to avoid cluttering your output. Referencing the :total of a measure in a table calculation makes other bucketing possible without the need for any LookML coding.Starting off with a count of events on our Looker instance: T
Looker will not be updating this content, nor guarantees that everything is up-to-date. If you'd like to count the days between two dates, you could use the SQL DATEDIFF function, which simply counts the days between two points in time:Redshift:DATEDIFF('day', start, stop) AS daysPostgres:DATEPART('day', start - stop) AS daysMySQL:DATEDIFF(stop, start) AS daysSnowflake:DATEDIFF('day', start, stop) AS daysHowever, the functions above will count all the days between the two specified dates. What if you want to exclude weekend days (Saturday and Sunday)?One way of achieving this is to create a derived table with the necessary logic. But, there is a more straightforward way, using a single short query, which you can define in the sql parameter of a dimension or measure in LookML.Note:You will want to make sure your database will return a day of week index of 0 for Sunday and 6 for Saturday; this is important, or the calculations will be one or two days off. You will also want to make sure
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 8: Arbitrary Period and Many Previous Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post. Period-over-Period Method 8: Arbitrary Period and Many Previous Periods - Compare Any Period to Any Other Period This method allows users to choose the current period, the grouping for that period, which period to compare (pivot), and how many periods to compare. This is the most complex PoP method, but it is also the most flexible.Below is an example with order items in the current month-to-date, broken down by day and compared to last month:Below is an example with an arbitrary date range, broken down by day and compared to the same date range for the previous three years:Advantages and DisadvantagesAdvantages:The date range can be broken to a more granular level (e.g., months, days, weeks). You can be
Looker will not be updating this content, nor guarantees that everything is up-to-date. See the Methods for Period Over Period (PoP) Analysis in Looker Community post for more examples of period-over-period analyses in Looker.The BasicsPeriod-over-period analysis is a pattern of analysis that measures something in the present and compares it to the same measurement in a comparable period of time in the past. The goal is to adjust the slice of the past you are looking at, so the same amount of time has passed in the two periods you are comparing. How-To: Year-over-Year (YOY)You can do period-over-period analysis over any defined time period. Our example is going to use year-over-year, because this is a very common use case. This example can be adjusted for comparing other time frames, such as month-over-month.In year-over-year analysis, we're looking to compare the current year to date with last year up to the same date. But if we simply filter a query for the last two years, we are goi
Looker will not be updating this content, nor guarantees that everything is up-to-date. There are several reasons you may want to mask the data in a field from some or all users. Maybe you want to restrict the ability for certain users to view the data for a specific field. Or maybe you want all users to be able to see the field but would like to partially obfuscate the data in that field.In the latter case, say you want to mask credit card information* so that some characters in the card number are replaced with asterisks. You can do this right in your Looker model by concatenating a combination of LEFT, RPAD, LEN, and RIGHT SQL functions in your SQL parameter.For example, you can create a new dimension masked_credit_card_number for a 16-digit field credit_card_number (this one uses MYSQL syntax):dimension: masked_credit_card_number type: string sql: CONCAT(LEFT(${TABLE}.credit_card_number, 4), RPAD('X', LEN(${TABLE}.credit_card_number) - 8, 'X'), RIGHT(${TABLE}.credit_card
Looker will not be updating this content, nor guarantees that everything is up-to-date. We often want to use a saved look as a sort of template and then dynamically override specific filter values, such as when we embed Looker into another application.This post shows you how to do this.Overriding Filters The following example shows a Look where the default value for Orders Created At is saved as on the day 2017-11-01. We see this URL (replacing <instance_name.looker.com> with the URL to your company's instance):https://<instance_name.looker.com>/looks/:id: In this case, toggle=fil appears in the URL, because we have expanded the filters box to show the value. We can override the filter value by visiting a URL and changing it to this:https://<instance_name.looker.com>/looks/:id?f[orders.created_date]=7+days Looker automatically rewrites the URL into a unique query ID (qid) parameter representing this ad hoc query, just like on an Explore page. This is the format o
Looker will not be updating this content, nor guarantees that everything is up-to-date. It is common to compare things like the sales from a specific week of this year with the sales from the same week in previous years. Let's say you want to compare last week to the same week last year. There are two ways to accomplish this:Weeks Starting on MondayIf you are using Looker's default weeks starting on Monday, you can do this easily by creating a matches (advanced) filter on a date field (in the example below we use a week timeframe to show a count by week) with the following filter expression:1 week ago for 1 week, 53 weeks ago for 1 weekThis filter will output something like this:Note: We will need to turn off the dimension fill feature on the date field in the Explore to compare only the time periods that were filtered for. Otherwise, we may see NULLs in the result set for the count for filled-in dates.Weeks Not Starting on MondayIf you want to analyze weeks that start on a day other
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 7: Arbitrary Period and Directly Previous Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 5: Current Period and Any Arbitrary Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 4: Current Period and Many Previous Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 3: Current Period and Previous Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw, time,
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 2: Any Two Native Timeframes (with Liquid) highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,
Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 1: Any Two Native Timeframes highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw, time,
