Analyze data with Looker Studio

You can use BigQuery to explore data with Looker Studio, aself-service business intelligence platform that lets you build andconsume data visualizations, dashboards, and reports. WithLooker Studio, you can connect to your BigQuerydata, create visualizations, and share your insights with others.

Looker Studio offers a premium version, Looker Studio Pro,which includes enhanced enterprise capabilities, including permissionsmanagement with Identity and Access Management, team workspaces for collaboration, a mobile app,and technical support.

You can use BigQuery BI Engine to improve report performance while reducingcompute costs. To learn about BI Engine, seeIntroduction to BI Engine.

These examples use Looker Studio to visualize data in theBigQueryaustin_bikeshare dataset. For more information about public data sets, seeBigQuery public datasets.

Explore query results

You can construct an arbitrary SQL query and visualize the data inLooker Studio. This is useful if you want to modify the data inBigQuery before working with it in Looker Studio,or if you only need a subset of the fields in the table. Dashboards are based ontemporary tables based on query results. Temporary tables are stored for up to24 hours.

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. Select yourbilling project.

3. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

4. In theExplorer pane, enterbikeshare_trips in thesearch field.

5. Go tobigquery-public-data> austin_bikeshare>bikeshare_trips.

6. Clickmore_vertView actions,and then clickQuery.

7. In the query editor, construct your query. For example:

   SELECT*FROM`bigquery-public-data.austin_bikeshare.bikeshare_trips`LIMIT1000;

8. Clickplay_circleRun.

9. In theQuery results section, clickOpen in>Looker Studio.

10. On theWelcome to Looker Studio page, clickGet Started if you agree to the Google Looker Studio andGoogle Terms of Service.

11. On theAuthorize Looker Studio access page, clickAuthorizeto authorize the connection if you agree to the terms of service, andthen select your marketing preferences. Only you can view data in yourreport unless you grant others permission to view the data.

    The report editor displays your query results asLooker Studio charts.

The following image shows some features of a Looker Studio report:

Legend:

1. Looker Studio logo and report name.
   • To go to theLooker Studio page, click thelogo.
   • To edit the report name, click the name.
2. Looker Studio toolbar. TheAdd a chart tool is highlighted.
3. Report title. To edit the text, click the field.
4. Table (selected). You can interact with a selectedchart by using the options in the chart header.
5. Bar chart (not selected).
6. Chart properties pane. For a selected chart,you can configure its data properties and appearanceon theSetup andStyle tabs.
7. Data pane. In this pane, you can access thefields and data sources to use in your report.
   • To add data to a chart, drag fields from theData pane onto the chart.
   • To create a chart, drag a field from theDatapane onto the canvas.
8. Save and share. Save this report so you can view,edit, and share it with others later. Before you savethe report, review the data source settings and thecredentials that the data sources use.

Users who are data source credential owners can click a resource to view itsjob statistics, result tables, and BI Engine details.

Interact with charts

Looker Studio charts are interactive. Now that your data isdisplayed in Looker Studio, here are some things to try:

• Scroll and page through the table.
• In theBar chart, hold the pointer over a bar to see details about thedata.
• Select a bar in the bar chart to cross-filter the table by thatdimension.

Add charts

Looker Studio supports many different visualization types. To addmore charts to the report, follow these steps:

1. In the toolbar, clickadd_chartAdd a chart.
2. Select the chart you want to add.
3. Click the canvas to add the chart to the report.
4. Use theChart properties pane to configure the chart.

For more information about adding charts to a report, seeAdd charts to your report.

Explore table schema

You can export table schema to see the metadata of your data inLooker Studio. This is useful if you don't want to modify the datain BigQuery before working with it inLooker Studio.

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. Select yourbilling project.

3. In the left pane, clickexploreExplorer:

   

4. In theExplorer pane, enterbigquery-public-data in theType to search field.

5. Go tobigquery-public-data> austin_bikeshare>bikeshare_trips.

6. In the toolbar, clickfile_uploadExport. If Export is not visible, selectmore_vertMore actions, and then clickExport.

7. ClickExplore with Looker Studio.

   

Share reports

You can share reports with others by sending them an email invitation to visitLooker Studio. You can invite specific people or Google Groups. Toshare more broadly, you can also create a link that lets anyone access yourLooker Studio reports.

Learn more about sharing reports.

Deleting your project prevents Looker Studio from querying the databecause the data source is associated with your project. If you don't want todelete your Google Cloud project, you can delete the Looker Studioreport and data source.

View BigQuery job details

When data source credentials are set to the current user, the user is calleddatasource credential owner. When viewed by a data source credential owner,most dashboard elements display a BigQuery icon. To navigatetoJob details in BigQuery, click the BigQueryicon.

View Looker Studio information schema details

You can track which Looker Studio reports and data sources areused by BigQuery by viewing theINFORMATION_SCHEMA.JOBS view.EveryLooker Studio job haslooker_studio_report_id andlooker_studio_datasource_id labels. Those IDs appear at the end of the Looker Studio URLs when opening a report or data source page.For example, a report with theURL ofhttps://lookerstudio.google.com/navigation/reporting/XXXX-YYY-ZZhas a report ID of "XXXX-YYY-ZZ".

The following examples show how to view reports and data sources:

View jobs report and data source URLs for Looker Studio BigQuery

To view the report and data source URL for each Looker StudioBigQuery job by running the following query:

-- Standard labels used by Looker Studio.DECLARErequestor_keySTRINGDEFAULT'requestor';DECLARErequestor_valueSTRINGDEFAULT'looker_studio';CREATETEMPFUNCTIONGetLabel(labelsANYTYPE,label_keySTRING)AS((SELECTl.valueFROMUNNEST(labels)lWHEREl.key=label_key));CREATETEMPFUNCTIONGetDatasourceUrl(labelsANYTYPE)AS(CONCAT("https://lookerstudio.google.com/datasources/",GetLabel(labels,'looker_studio_datasource_id')));CREATETEMPFUNCTIONGetReportUrl(labelsANYTYPE)AS(CONCAT("https://lookerstudio.google.com/reporting/",GetLabel(labels,'looker_studio_report_id')));SELECTjob_id,GetDatasourceUrl(labels)ASdatasource_url,GetReportUrl(labels)ASreport_url,FROM`region-us`.INFORMATION_SCHEMA.JOBSjobsWHEREcreation_time>TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL7DAY)ANDGetLabel(labels,requestor_key)=requestor_valueLIMIT100;

View jobs produced by using a report and data source

To view the jobs produced, you run the following query:

-- Specify report and data source id, which can be found in the end of Looker Studio URLs.DECLAREuser_report_idSTRINGDEFAULT'*report id here*';DECLAREuser_datasource_idSTRINGDEFAULT'*datasource id here*';-- Looker Studio labels for BigQuery jobs.DECLARErequestor_keySTRINGDEFAULT'requestor';DECLARErequestor_valueSTRINGDEFAULT'looker_studio';DECLAREdatasource_keySTRINGDEFAULT'looker_studio_datasource_id';DECLAREreport_keySTRINGDEFAULT'looker_studio_report_id';CREATETEMPFUNCTIONGetLabel(labelsANYTYPE,label_keySTRING)AS((SELECTl.valueFROMUNNEST(labels)lWHEREl.key=label_key));SELECTcreation_time,job_id,FROM`region-us`.INFORMATION_SCHEMA.JOBSjobsWHEREcreation_time>TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL7DAY)ANDGetLabel(labels,requestor_key)=requestor_valueANDGetLabel(labels,datasource_key)=user_datasource_idANDGetLabel(labels,report_key)=user_report_idORDERBY1LIMIT100;

What's next

• To learn more about reserving capacity for BI Engine, seeReserve BI Engine capacity.
• To learn more about writing queries for BigQuery, seeOverview of BigQuery analytics.This document explains tasks such as how to run queries or create user-definedfunctions (UDFs).
• To explore BigQuery syntax, seeIntroduction to SQL in BigQuery.In BigQuery, the preferred dialect for SQL queries is standardSQL. BigQuery's older SQL-like syntax is described inLegacy SQL functions and operators.