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

Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Who is the user Testy McTestFace and where did they come from?Knowledge Drop

Last tested: Mar 13, 2020 This user is the default option for Fabio's SSO Embed tool. https://fabio-looker.github.io/looker_sso_tool/This user can be created by anyone with the Embed secret found in admin/embed. Further Reading: Testy's Origin Story: https://www.nytimes.com/2016/03/22/world/europe/boaty-mcboatface-what-you-get-when-you-let-the-internet-decide.amp.html  This content is subject to limited support.                  

360
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Color specific Values in a seriesKnowledge Drop

Last tested: Jan 15, 2020 Want to create different colors on a single series of data based on a specific dimension value?Create table calculations that use if statements to satisfy a certain condition. EG: if(${value) = 'TRUE', ${measure}, null)If true, we show the measure, if else, we show null.Then, we hide the null values from the visualization by clicking the gear --> edit --> plot. (You may have to run the explore again for the (Plot null values toggle to show up).Table Calc 1 Table Calc 2Turn Plot Null Values to Off on VisualizationWhat the end explore looks like:  METHOD 2 [Custom Palette]Get the # of series in your chart Go to the edit panel and click the Custom Palette option which will allow you to specify the colors via a comma separated list. Each color at the position will link to the position in the series. So in this case, we made the 4th series blue, hence the 4th value on the list is the color  This content is subject to limited support.                  

2360
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

How to drill on a dimension to get the module to popup like a measure. (Measure Drilling on a Dimension)Knowledge Drop

Last tested: Mar 11, 2020 Drilling on a dimension will bring you to a new page, but if you want to stay on the same page or use visual drilling like you can with a drill on a measure, follow these steps.If you have a drill field added to a dimension.EG: dimension: height_high {type: numbersql: ${TABLE}.height_high ;;drill_fields: [breed_name,Breed_of_Dog,count]link: {label: "link url" url:"{{ height_high._link }}&sorts=count+desc" }} This will result in an output like this:These links bring you to a new page, but if you want to get a module to appear. You can use a dummy measure like this. measure: dummy {type: numbersql: 1=1 ;;drill_fields: [breed_name,Breed_of_Dog, count,height_high]}dimension: height_high {type: numbersql: ${TABLE}.height_high ;;link: {label: "DUMMY MEASURE" url:"{{ dummy._link }}&sorts=count+desc" }} The sql 1=1 always returns true, but in the dimension link parameter we reference the dummy measure's drill link via {{MEASURE._link}}.In the dimension you can

2210
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Currency Exchange Rate Block with Big QueryKnowledge Drop

Last tested: Nov 13, 2019 Looker Currency Exchange Block Instructions for Big Query​For more information click here.​Here are some steps on how to set up the Currency Exchange Rate Block with a Big Query connection.1. Go to GithubFork the datablocks-exchangerate repo​​​2. Delete Snowflake files in the forked repo.Click into the file and then click the trashcan. You can write a commit message if you would like.3. In Looker go to Manage LookML Project. Create a new project.​​Start with a blank project. Set up a GIT connection to the forked repo.​​Shift-click on the git button and select Pull from production to download files from repo. ​​4. Create a new connection to BQ with PDTs enabled following the steps listed in the instructions here:Double-check the connection by going to admin → connections → test connections.**** If you already have a BQ connection with PDTs enabled, skip this step. ****5. Change the connection name in the model file in the new project to the new connection or ex

5110
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Use Python SDK to convert All Dashboards to Dashboards-Next on an instanceKnowledge Drop

Last tested: Oct 22, 2020 Love dashboards-next, but hate having to convert legacy dashboards one folder at a time? This script will convert them all for you. You just need to use the Python SDK, import looker_sdkimport csvimport pandas as pd import timei=0list_of_dashboards = []dashboards_next_list = []legacy = []# from typing import cast, MutableSequence, Sequence####Initialize API/SDK for more info go here: https://pypi.org/project/looker-sdk/from looker_sdk import methods40, models40sdk = looker_sdk.init40() # or init40() for v4.0 APIme = sdk.me()list_de_dashboards = sdk.all_dashboards()while i < len(list_de_dashboards):if sdk.dashboard(dashboard_id=list_de_dashboards[i].id).preferred_viewer == "dashboards-next":dashboards_next_list.append(list_de_dashboards[i].id)else: legacy.append(list_de_dashboards[i].id)i = i + 1time.sleep(1.0)print(i)#print("list of dashboards-next")#print(dashboards_next_list)#print("list of LEGACY")#print(legacy)n = 0 while n < len(legacy):sdk.update_d

540
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Does Refreshing the Page Stop/Kill LookML Validation aka Linting?Knowledge Drop

Last tested: Jun 16, 2020 Answer: No To double check, open the network panel, click validate and refresh the page. The /lint internal api call should still return a 200. This content is subject to limited support.                  

150
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Hide Pivoted Series from VisualizationKnowledge Drop

Last tested: May 4, 2020 Let's say that you want to hide a specific series from a pivoted table. What do you do? One option is to mirror all the columns manually with a pivot_where and leave out the column you want to hide. The other option is to use some visualization workarounds.Go to the bottom of this post for a quick workaround how to hide series. In our example below we want to hide the series from the year 2019. This example shows the pivot_where workaround:For the quick workaround:- In the series panel, edit the label.- Go to this website and copy and empty space. http://emptycharacter.com/- Paste it in.- Click on the colour square and insert a white hex code. #FFF worksIn this example we want to hide the year 2019:​Goodbye 2019. This content is subject to limited support.                  

2830
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

What does Daemonize mean in the Looker ARGS?Knowledge Drop

Last tested: Oct 15, 2020  example flag --no-daemonize  TLDR: daemonize runs programs in the background.If you add the --no-daemonize flag, Looker will stop when the user kills the terminal session on the instance.daemonize runs a command as a Unix daemon. As defined in W. Richard Stevens’ 1990 book, UNIX Network Programming (Addison-Wesley, 1990), a daemon is “a process that executes ‘in the background’ (i.e., without an associated terminal or login shell) either waiting for some event to occur, or waiting to perform some specified task on a periodic basis.”Source This content is subject to limited support.                   

520
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Admin PermissionsKnowledge Drop

Last tested: Sep 29, 2020 What abilities do admins possess that developers with maximum permissions not have?This is not an exhaustive list, but some examples of abilities Admins have that others do not:Managing users create/edit db connections manage authentication methods, session settings, Admins also have access to all models and folders. This content is subject to limited support.                  

740
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

How to mass add users to a group from a CSV with Looker SDK!Knowledge Drop

Last tested: Apr 27, 2020 import looker_sdkimport csvimport exceptions####Initialize API/SDK for more info go here: https://pypi.org/project/looker-sdk/from looker_sdk import methods31, modelssdk = looker_sdk.init31() # or init40() for v4.0 APIme = sdk.me()#print(me)#### GO TO ADMIN --> GROUPS AND FIND THE GROUP ID YOU WANT TO ADD THE PEOPLE TO. ADD IT BELOWgroup = 28data = []i=0with open('~/file.csv') as f:reader = csv.reader(f, delimiter=' ')for row in reader:data.append(str(row[i])) ### loops through list and searches user### grabs user id and passes that through add user to group try:for email in data:for user in sdk.search_users(email=email):sdk.add_group_user(group_id=group, body=models.GroupIdForGroupUserInclusion(user_id= user.id))except KeyError:print('Key error \n')print(email)passexcept TypeError:print('Key error \n')print(email)passexcept IndexError:print('Index error \n')print(email)pass  This content is subject to limited support.                  

2700
Eric_Lyons
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Troubleshoot Action with Google Cloud FunctionKnowledge Drop

Last tested: Jun 25, 2020 ProblemThe user is trying to set up an action that update(write-back) data on BigQuery from Looks per this doc. However, they are getting this error: The form was not accepted.Go to the trigger page of the action in cloud function - the URL should be there. If you click this what does you see when that browser pops up. It might be that your action is not accessible by looker If you see this: Error: Forbidden Your client does not have permission to get URL /Looker_action_test from this serverSOLUTION:You might need to add the cloud functions invoke permission. You can test this by going to the cloud functions page, ticking the box beside their action and then info panel on the right hand side click to add members and select all_users, then the role cloud functions > cloud functions invoker This will make it public, but it will help us test if this is the issueIn case this is the issue you will either need to keep it public or manually grant the permissions s

3920
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

"Non-base queries in merged_queries must define at least one join_fields" - LookML Dashboard errorKnowledge Drop

Last tested: Jul 31, 2020 ProblemWe did the auto-convert from a UDD dashboard that has merged results to a LookML Dashboard. The UDD runs as expected. However, the LookML version is giving the error "Non-base queries in merged_queries must define at least one join_fields".SOLUTIONIf we go to the original user-defined dashboard and click "Explore From Here" on the merged results query, we might see a "Not Merged" warning in the source query from that tile. In this case we would need to fix that warning as the queries are not having a common key (join field) specified in.We could try to have a Join field explicitly mentioned… if it is not available, we can join on the row-number(like a table calc to get the row number) This content is subject to limited support.                  

520
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Percent of change on the last two pivoted columnsKnowledge Drop

Last tested: May 1, 2020 In case we have a date column that is pivoted and want to calculate the percentage of change only on the last two columns. The main issue is that the last two columns are going to be dynamic as the users can select any time range filter (last 3, 6 months and so on).In this example, the user is pivoting into Created Month column. So to get the last column we can usepivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))And combine it with the previous column getting to the whole calculation as:pivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))/ pivot_index(${pivotted_measure}, max(pivot_row(pivot_column()))-1) This content is subject to limited support.                  

1980
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Dashboard Run Count versus View Total from Content Usage exploreKnowledge Drop

Last tested: Jan 25, 2021 User might want to know what is considered for the View Total from the Popular Content tile in the Content Usage Dashboard and how it differs from the Dashboard Session generated. So, which one will be closer to get how many times a dashboard was loaded?We ran some testings and could notice that:The View Total count gets updated only when a page is accessed if you reload the page. For example, page refresh or if you go to the URL via any other means The Dashboard Run Count will consider any time the dashboard was run. For instance, if a dashboard's auto-refresh it would generate a new dashboard session and added to this run count. This content is subject to limited support.                  

3390
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

How to customise the value labels showing in a GraphKnowledge Drop

Last tested: May 25, 2020 The ProblemSometimes users want to be able to customize values being shown on a line graph. So if the date range is day over days, we want to set the values being shown to only show a value every 7 days for instance. For example, the below graph is very noisy:A Solution UI FILTERSSo, in case we want to show the value every 7 days from a specific Day, we could filter on the day of the Week (Monday, Tuesday...) we want to show the values. Note that we will need to under "Edit" leave off "Plot Null Values" OR remove the filled out dates from the Date dimension so the nulls will be removed. This suggestion will look like this:Table CalculationNow, in case we would like to display values every 7 days from today. We could use the following table calculation by applying the mod() function if(mod(diff_days(${order_items.created_date},now()), 7) = 0 , yes, no)   To explain here, we get the date difference between the date field and now(). Then the mod function will div

900
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Error: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more detailsKnowledge Drop

Last tested: Jan 26, 2021 We've seen this issue when a user is connecting to a specific table on BigQuery and it is based on a Google Sheet (made sure to share it with the Looker Service Account).SOLUTIONThe solution that worked as a charm is to check the schema detection on the connection to sheet and just set them all to string This content is subject to limited support.                  

2750
flaviagdo
Looker Staff
1 year ago
fernanda.melo
Looker Staff
fernanda.meloLooker Staff
published in Technical Tips & Tricks

Create connection API - generate Base64 encoded certificateKnowledge Drop

Last tested: Nov 11, 2019 When creating a new connection using the create_connection(body) API you need to pass the certificate body for server authentication.To create this string you can run in a terminal the following command line:cat yourjsonfile.json | base64ORcat yourp12file.p12 | base64Wrap the result in quotes and use that as the value for certificate.For BigQuery certificate (string): (Write-Only) Base64 encoded Certificate body for server authentication (when appropriate for dialect).,file_type (string): (Write-Only) Certificate keyfile type - .json or .p12,--> you don't give a filename to the API to upload, you have to put the content of the file into the API request as a Base64 encoded string value This content is subject to limited support.                  

370
fernanda.melo
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

System Activity - Which users accessed specific dashboards and clicked "Run" ?Knowledge Drop

Last tested: Oct 19, 2020 We can use the "history" explore to determine who has accessed a dashboard. We can also use a custom measure to count the number of times a dashboard was run by a particular user.In the "history" explore, select the following fields:Dashboard Title User Name Dashboard ID History Count History Approximate Web Usage in MinutesAdd these filters:Dashboard ID is equal to {insert dashboard ID} Dashboard Title is equal to {insert dashboard title}Create a custom measure for "Dashboard Run Count":Field to measure: "Dashboard Session" in the History view Measure type: Count Distinct​​​​​ This content is subject to limited support.                  

5920
evelyn.shen
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

Set password manually for a userKnowledge Drop

Last tested: Nov 12, 2020 Go to Admin > Users > Add Users Enter the users email address and/or username if no email address is available. Finish the rest of the setup to create this user. Go back to Admin > Users > click "Edit" next to the user that you just created Select "Send rest link" next to Password. You will see a link appear. Copy this link. Go to an *incognito* browser page and paste the password reset link. Edit/Confirm the password.If the user has already been created and we're skipping to step 3 as our first step, make sure that the user was NOT set up via Google Auth, SAML, LDAP, etc. (If so, any management of passwords will have to be done in those external providers, not in Looker. See Why is there no Password Reset/Edit button for a user? for more details.)  This content is subject to limited support.                  

5000
evelyn.shen
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

Refresh frequency of lookerFetchDataKnowledge Drop

Last tested: Sep 26, 2020 Please know that the method outlined in https://help.looker.com/hc/en-us/articles/360001288508-Exporting-Data-to-Google-Sheets is not supported by Looker Support. lookerFetchData is a custom function created by an internal user. It's best to recommend using the Google Sheets Action (https://help.looker.com/hc/en-us/articles/360044002214-Looker-Actions-Google-Sheets) instead. Per Zach: Google scripts by default only refresh when the sheet page refreshes. Any Google scripts would need another script or function in sheets to have it refresh at any other rate. This content is subject to limited support.                  

1000
evelyn.shen
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

Is it possible to configure Chromium to use a proxy?Knowledge Drop

Last tested: Aug 17, 2020 In a nutshell: it's rare but possible. There isn’t a way in Looker to configure a proxy for Chromium, but at least three customers have found a way to configure Chromium itself to use the proxy.The solution is to specify the proxy in the config file, like so:There is a config file at /etc/chromium-browser/customizations/ Set --proxy-server var ( Or you could add below inside config file ) CHROMIUM_FLAGS="--proxy-server="Proxy IP:Port""But wait! Chromium can be run locally and Chromium has to be run by the Looker user. Where/when/how would a proxy fit into this?The proxy fits into this because if the dashboard is loading content outside of Looker, like an image added to a text file on a dashboard, the Looker server needs to be able to communicate out to the public internet. If the Looker server has a proxy configured for outbound connections from the Looker server to the outside world, then this would come into play.When a render job is selected by the user or

430
evelyn.shen
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

PDT Logs: Action is "create regen requires"Knowledge Drop

Last tested: Aug 3, 2020This means that the PDT in question is required by another table, thus triggering the PDT, but the PDT is not currently built. This content is subject to limited support.                  

110
evelyn.shen
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Send Look to SFTP Server Via Python SDKKnowledge Drop

Last tested: Jun 18, 2020 If someone wants to send content to an SFTP server, but does not want to want to use the UI, they can use the API.import looker_sdkimport csvimport pysftp#SET SFTP CREDENTIALS HERE. IT IS RECOMMENDED TO SET THEM IN ANOTHER FILE AND IMPORT THEM SECURELYmyHostname = "DOMAIN"myUsername = "user"myPassword = "PASSWORD" ####Initialize API/SDK for more info go here: https://pypi.org/project/looker-sdk/#### IN THE SAME DIRECTORY CREATE AN INI FILE TO STORE API CREDENTIALS## INITIALIZE THE APIfrom looker_sdk import methods31, modelssdk = looker_sdk.init31() # or init40() for v4.0 APIme = sdk.me()### GET THE LOOK BY PASSING THE LOOK ID### WE ALSO WRITE THE FILE TO THE LOCAL DIRECTORY AS A CSVlook = sdk.run_look(look_id=12, result_format = "csv")file = open('read.csv', 'w') file.write(look)file.close() ### HERE WE SEND THE CSV TO THE SFTP SERVERwith pysftp.Connection(host=myHostname, username=myUsername, password=myPassword) as sftp:print("Connection succesfully stablish

550
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

What happened to the Clients Module in the Python looker_sdk? -- (ImportError: cannot import name 'client' from 'looker_sdk')Knowledge Drop

Last tested: Apr 2, 2020 In the older version of the looker_sdk, clients was one of the modules imported to run functions like the init function.)EG:from looker_sdk import client, models sdk = client.setup("../looker.ini") With the latest versions methods31 and methods40 are used instead dependent upon the version of the API you want to use 3.1 or 4.0.You will most likely see this error if you try to use client:ImportError: cannot import name 'client' from 'looker_sdk' 4.0 APIfrom looker_sdk import models, methods40sdk = looker_sdk.init40() # or init40() for v4.0 API# and away you gomy_user = sdk.me()print(my_user) 3.1 APIfrom looker_sdk import methods31, modelssdk = looker_sdk.init31() # and away you gomy_user = sdk.me()print(my_user) For docs go here: https://pypi.org/project/looker-sdk/OrHere: https://github.com/looker-open-source/sdk-codegen/tree/master/python#basic-usage  This content is subject to limited support.                  

3170
Eric_Lyons
Looker Staff
1 year ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

If you delete an embed user does his or her content disappear?Knowledge Drop

Last tested: Apr 2, 2020Yes, like a traditional user who is deleted, his or her content will be deleted, but if the content is placed into a shared folder, the content will still exist.  This content is subject to limited support.                  

230
Eric_Lyons
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