Analyze data with Looker Studio

You can use BigQuery to explore data with Looker Studio, a no-cost self-service business intelligence platform that lets you build and consume data visualizations, dashboards, and reports. With Looker Studio, you can connect to your BigQuery data, create visualizations, and share your insights with others.

Looker Studio queries with BI Engine acceleration enabled can use acceleration modes, window functions, materialized view smart tuning, and repeated fields. Looker Studio query results are cached like other BigQuery queries and can take advantage of query queues concurrency handling.

Explore BigQuery data in Looker Studio

You can explore data in BigQuery with Looker Studio using the following options:

• Query results pane
• Table schema pane
• Share Looker Studio reports

These examples use Looker Studio to visualize data in the BigQuery austin_bikeshare dataset. For more information about public data sets, see BigQuery public datasets.

Limitations

BigQuery BI Engine has limited support for the following features when integrated with Looker Studio:

• User-defined functions (UDF)
• ARRAY columns
• If legacy SQL is used or if the generated URL length is greater than 11,378 characters, then the Explore with Looker Studio feature is disabled
• Wildcard tables are not accelerated.
• Certain join types are not fully accelerated.
• Small tables with complex joins are not fully accelerated.

Explore query results

You can construct an arbitrary SQL query and visualize the data in Looker Studio. This is useful if you want to modify the data in BigQuery before working with it in Looker Studio, or if you only need a subset of the fields in the table.

Note: You can visualize a maximum of 5,000 rows of data in Looker Studio charts.

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

   Go to BigQuery

2. Select your billing project.

3. In the Explorer pane, enter bikeshare_trips in the Type to search field.

4. Go to bigquery-public-data > austin_bikeshare > bikeshare_trips.

5. Click View actions, and then click Query.

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

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

7. Click Run.

8. In the Query results section, click Explore data, and then click Explore with Looker Studio.

   

9. On the Welcome to Looker Studio page, click Get Started if you agree to the Google Looker Studio and Google Terms of Service.

10. On the Authorize Looker Studio access page, click Authorize to authorize the connection if you agree to the terms of service, and then select your marketing preferences. Only you can view data in your report unless you grant others permission to view the data.

    The report editor displays your query results as Looker Studio charts.

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

Legend:

1. Looker Studio logo and report name.
   • To go to the Looker Studio page, click the logo.
   • To edit the report name, click the name.
2. Looker Studio toolbar. The Add a chart tool is highlighted.
3. Report title. To edit the text, click the text box.
4. Table (selected). You can interact with a selected chart by using the options in the chart header.
5. Bar chart (not selected).
6. Chart properties pane. For a selected table, you can configure its data properities and appearance on the Setup and Style tabs.
7. Data pane. In this pane, you can access the fields and data sources to use in your report.
   • To add data to a chart, drag fields from the Data pane onto the chart.
   • To create a chart, drag a field from the Data pane onto the canvas.
8. Save and share. Save this report so you can view, edit, and share it with others later. Before you save the report, review the data source settings and the credentials that the data sources use.

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

Interact with charts

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

• Scroll and page through the table.
• In the Bar chart, hold the pointer over a bar to see details about the data.
• Select a bar in the bar chart to cross-filter the table by that dimension.

Add charts

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

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

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

Explore table schema

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

Note: BigQuery queries can return a maximum of 20 MB of data. If you explore very large table schemas, Looker Studio might truncate the results.

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

   Go to BigQuery

2. Select your billing project.

3. In the Explorer pane, enter bigquery-public-data in the Type to search field.

4. Go to bigquery-public-data > austin_bikeshare > bikeshare_trips.

5. In the toolbar, click Export. If Export is not visible, select More actions, and then click Export.

6. Click Explore with Looker Studio.

   

Share reports

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

Note: Before you can share a report created by the BigQuery export feature, you must first click Save and share.

To share a report with another person, follow these steps:

1. In the Looker Studio page header, click Share.
2. In the Sharing with others dialog, type the recipient's email address. You can enter multiple email addresses or Google Group addresses.
3. Specify whether recipients can view or edit the report.
4. Click Send.

Learn more about sharing reports.

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

Monitor Looker Studio

To monitor Looker Studio with BigQuery BI Engine acceleration, see Monitor BigQuery BI Engine. To monitor resource and jobs, see Monitor health, resource utilization, and jobs.

• Full BigQuery monitoring support, including INFORMATION_SCHEMA, execution graph, and Cloud Monitoring metrics.
• Looker Studio queries with BI Engine acceleration enabled can take advantage of acceleration modes.
• BI Engine support for previously unsupported use cases include the following:
  • Analytic functions
  • Larger table sizes
  • Larger partition limits
  • Materialized view smart tuning
  • Repeated fields
  • Authorized views
• Looker Studio query results are cached like other BigQuery queries.
• Reduced latency for pivot table charts.
• Query queues concurrency handling.
• Data source credential owners have one-click navigation to job statistics, results tables, and BI Engine details available.
• Looker Studio INFORMATION_SCHEMA details includes two labels, (report_id and datasource_id), to help you understand costs and usage.
• Per-model Looker Studio BI Engine metrics are no longer provided.
• Improved reliability.

Availability for BigQuery native integration in Looker Studio

BigQuery native integration in Looker Studio is being incrementally enabled for customers by targeting end users, projects, and Google Cloud regions. Your dashboards use native integration if any of the following are true:

• When hovering over dashboard elements in Looker Studio, data sources owners with native integration enabled see a BigQuery icon. To view the job that produced the dashboard information, you can click the BigQuery link.
• The INFORMATION_SCHEMA.JOBS view contains all of the Looker Studio dashboard issues associated with looker_studio_datasource_id and looker_studio_report_id labels.
• Your Cloud Logging logs include entries from bigquery.googleapis.com and protoPayload.serviceName="bigquerybiengine.googleapis.com".

View Looker Studio INFORMATION_SCHEMA details

You can track which Looker Studio reports and data sources are used by BigQuery by viewing the INFORMATION_SCHEMA.JOBS view. When BigQuery native integration is enabled, every Looker Studio query creates an entry with report_id and datasource_id labels. Those are ids that appear in the end of LookerStudio URLs when opening report or data source page. For example, report with URL https://lookerstudio.google.com/navigation/reporting/my-report-id-123 will have a report ID of "my-report-id-123".

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 Studio BigQuery job by running the following query:

-- Standard labels used by Looker Studio.
DECLARE requestor_key STRING DEFAULT 'requestor';
DECLARE requestor_value STRING DEFAULT 'looker_studio';

CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING)
AS (
  (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key)
);

CREATE TEMP FUNCTION GetDatasourceUrl(labels ANY TYPE)
AS (
  CONCAT("https://lookerstudio.google.com/datasources/", GetLabel(labels, 'looker_studio_datasource_id'))
);

CREATE TEMP FUNCTION GetReportUrl(labels ANY TYPE)
AS (
  CONCAT("https://lookerstudio.google.com/reporting/", GetLabel(labels, 'looker_studio_report_id'))
);

SELECT
  job_id,
  GetDatasourceUrl(labels) AS datasource_url,
  GetReportUrl(labels) AS report_url,
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS jobs
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND GetLabel(labels, requestor_key) = requestor_value
LIMIT
  100;

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.
DECLARE user_report_id STRING DEFAULT '*report id here*';
DECLARE user_datasource_id STRING DEFAULT '*datasource id here*';

-- Standard labels Looker Studio uses in native integration.
DECLARE requestor_key STRING DEFAULT 'requestor';
DECLARE requestor_value STRING DEFAULT 'looker_studio';
DECLARE datasource_key STRING DEFAULT 'looker_studio_datasource_id';
DECLARE report_key STRING DEFAULT 'looker_studio_report_id';

CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING)
AS (
  (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key)
);

SELECT
  creation_time,
  job_id,
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS jobs
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND GetLabel(labels, requestor_key) = requestor_value
  AND GetLabel(labels, datasource_key) = user_datasource_id
  AND GetLabel(labels, report_key) = user_report_id
ORDER BY 1
LIMIT 100;

What's next

• To learn more about reserving capacity for BI Engine, see Reserve BI Engine capacity.
• To learn more about writing queries for BigQuery, see Overview of BigQuery analytics. This document explains tasks such as how to run queries or create user-defined functions (UDFs).
• To explore BigQuery syntax, see Introduction to SQL in BigQuery. In BigQuery, the preferred dialect for SQL queries is standard SQL. BigQuery's older SQL-like syntax is described in Legacy SQL functions and operators.