Quickstart using Looker Studio

---

BigQuery BI Engine lets you perform fast, low-latency analysis services and interactive analytics with reports and dashboards that are backed by BigQuery.

This introductory tutorial is intended for data analysts and business analysts who use Looker Studio to build reports and dashboards.

Objectives

In this tutorial, you:

• Create a BI Engine capacity reservation by using BigQuery in the Google Cloud console.
• Use Looker Studio to connect to a BigQuery table managed by BI Engine.
• Create a Looker Studio dashboard that queries your BI Engine-managed table.

Costs

In this document, you use the following billable components of Google Cloud:

• BI Engine: You incur costs for the reservation you create in BI Engine.
• BigQuery: You incur storage costs for the table you create in BigQuery.

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

For more information on BI Engine pricing, see the Pricing page.

For more information on BigQuery storage pricing, see Storage pricing in the BigQuery documentation.

Before you begin

Before you begin, ensure you have a project to use, that you have enabled billing for that project, and that you have enabled the BigQuery API.

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. The BigQuery API is automatically enabled in new projects. To activate the BigQuery API in an existing project, go to

   Enable the BigQuery API.

   Enable the API

Note: Looker Studio is not supported by Cloud Support. For more information, see the Looker Studio Help Center.

Create a BigQuery dataset

The first step is to create a BigQuery dataset to store your BI Engine-managed table. To create your dataset, follow these steps:

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

   Go to BigQuery

2. In the navigation panel, in the Explorer panel, click your project name.

3. In the details panel, click View actions, and then click Create dataset.

4. On the Create dataset page, do the following:

   • For Dataset ID, enter biengine_tutorial.

   • For Data location, choose us (multiple regions in United States), the multi-region location where public datasets are stored.

   • For this tutorial, you can select Enable table expiration, and then specify the number of days before the table expires.

     

5. Leave all of the other default settings in place and click Create dataset.

Create a table by copying data from a public dataset

This tutorial uses a dataset available through the Google Cloud Public Dataset Program. Public datasets are datasets that BigQuery hosts for you to access and integrate into your applications.

In this section, you create a table by copying data from the San Francisco 311 service requests dataset. You can explore the dataset by using the Google Cloud console.

Create your table

To create your table, follow these steps:

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

   Go to BigQuery

2. In the Explorer panel, search for san_francisco_311.

3. In the Explorer panel, expand san_francisco_311 and click the 311_service_requests table.

4. In the Explorer toolbar, click Copy.

   

5. In the Copy table dialog, in the Destination section, do the following:

   • For Project name, click Browse, and then select your project.
   • For Dataset name, select biengine_tutorial.

   • For Table name, enter 311_service_requests_copy.

     

6. Click Copy.

7. Optional: After the copy job is complete, verify the table contents by expanding PROJECT_NAME > biengine_tutorial and clicking 311_service_requests_copy > Preview. Replace PROJECT_NAME with name of your Google Cloud project for this tutorial.

Create your BI Engine reservation

1. In the Google Cloud console, under Administration go to the BI Engine page.

   Go to the BI Engine page

   Note: If prompted to enable BigQuery Reservation API, click Enable.

2. Click Create reservation.

3. On the Create Reservation page, configure your BI Engine reservation:

   • In the Project list, verify your Google Cloud project.
   • In the Location list, select a location. The location should match the location of the datasets that you're querying.

   • Adjust the GiB of Capacity slider to the amount of memory capacity that you're reserving. The following example sets the capacity to 2 GiB. The maximum is 250 GiB.

     

4. Click Next.

5. In the Preferred Tables section, optionally specify tables for acceleration with BI Engine. To find table names, do the following:

   1. In the Table Id field, type part of the name of the table that you want accelerated by BI Engine—for example, 311.

   2. From the list of suggested names, select your table names.

      Only specified tables are eligible for acceleration. If no preferred tables are specified, all project queries are eligible for acceleration.

6. Click Next.

7. In the Confirm and submit section, review the agreement.

8. If you accept the terms of agreement, click Create.

After you confirm your reservation, the details are displayed on the Reservations page.

Create a data source connection in Looker Studio

Before you create a report in Looker Studio, you must create a data source for the report. A report may contain one or more data sources. Looker Studio uses the BigQuery connector to connect to a BI Engine-managed BigQuery table.

When you define your data source connection in Looker Studio, BI Engine uses the table and columns you configure to determine what data to cache. BI Engine only caches the columns you add to your report.

Required permissions

You must have the appropriate permissions in order to add a BigQuery data source to a Looker Studio report. In addition, the permissions applied to BigQuery datasets will apply to the reports, charts, and dashboards you create in Looker Studio. When a Looker Studio report is shared, the report components are visible only to users who have appropriate permissions.

Running a query job that is used to populate a report requires bigquery.jobs.create permissions. In order for the query job to complete successfully, the user or group must have access to the dataset containing the tables referenced by the query. The minimum access level required is Can view, which maps to the bigquery.dataViewer role for that dataset.

Because you created the dataset used in this tutorial, you are granted Is owner access to the dataset which gives you complete control over it. As well, since you created the project used in this tutorial, you have Owner access at the project level. Owner access gives you the ability to run jobs in the project.

Permission details

You can set bigquery.jobs.create permissions at the project level by granting any of the following predefined IAM roles:

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Note: Most users such as data analysts and data scientists should be granted bigquery.user or bigquery.jobUser. The role bigquery.admin has full permissions across all datasets in a project.

If you grant a user or group the bigquery.user role at the project level, by default, no access is granted to any of the datasets, tables, or views in the project. bigquery.user gives users the ability to create their own datasets and to run query jobs against datasets they have been given access to. If you assign the bigquery.user or bigquery.jobUser role, you must also assign access controls to each dataset the user or group needs to access that wasn't created by the user.

When you assign access to a dataset, there are 3 options:

• Can view maps to the bigquery.dataViewer role for that dataset.
• Can edit maps to the bigquery.dataEditor role for that dataset.
• Is owner maps to the bigquery.dataOwner role for that dataset.

The minimum access required for a user to run a query is Can view.

For more information on IAM roles in BigQuery, see Access control in the BigQuery documentation.

For more information on securing datasets in BigQuery, see Controlling access to a dataset in the BigQuery documentation.

Creating your data source

To create your data source:

1. Open Looker Studio.

2. On the Reports page, in the Start a new report section, click the Blank Report template. This creates a new untitled report.

   

3. If prompted, complete the Marketing Preferences and the Account and Privacy settings and then click Save. You may need to click the Blank template again after saving your settings.

4. In the Add a data source window, click Create new data source.

   

5. In the Google Connectors section, hover over BigQuery and then click Select.

6. For Authorization, click Authorize. This allows Looker Studio access to your Google Cloud project.

7. In the Request for permission dialog, click Allow to give Looker Studio the ability to view data in BigQuery. You may not receive this prompt if you previously used Looker Studio.

8. Leave My Projects selected and in the Project pane, click the name of your project.

9. In the Dataset pane, click biengine_tutorial.

10. In the Table pane, click 311_service_requests_copy.

11. In the upper right corner of the window, click Connect. Once Looker Studio connects to the BigQuery data source, the table's fields are displayed. You can use this page to adjust the field properties or to create new calculated fields.

12. In the upper right corner, click Add to report.

13. When prompted, click Add to report.

14. In the Request for permission dialog, click Allow to give Looker Studio the ability to view and manage files in Google Drive. You may not receive this prompt if you previously used Looker Studio.

Creating a chart

Once you have added the data source to the report, the next step is to create a visualization. Begin by creating a bar chart. The bar chart you create displays the top complaints by neighborhood.

To create a bar chart that displays complaints by neighborhood:

1. (Optional) At the top of the page, click Untitled Report to change the report name. For example, type BI Engine tutorial.

2. After the report editor loads, click Insert > Bar chart.

3. Using the handles, expand the size of the chart.

4. On the Data tab, notice the value for Data Source is 311_service_requests_copy.

5. Because you are charting the number of requests by neighborhood, you need to set the Dimension to category and the Breakdown dimension to neighborhood. Click the default dimension (likely status), and in the list, choose category.

   

6. In the Available Fields list, click and drag neighborhood onto the Add dimension here box under Breakdown dimension.

   

Add a filter

Because the data includes a number of NULL values in the neighborhood column, you add a filter that excludes NULL values from the chart.

To add a filter:

1. On the Data tab, click Add a filter.

   

2. In the Create filter dialog:

   • For Name, enter Exclude nulls.
   • Verify Data source is set to 311_service_requests_copy.
   • Click Include and choose Exclude.
   • Click Select a field and choose neighborhood.

   • Click Select a condition and choose Is null.

     

   • Click Save.

3. After the filter is applied, your chart should look like the following.

   

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this quickstart, you can delete the project, delete the BI Engine reservation, or both.

Deleting the project

The easiest way to eliminate billing is to delete the project that you created for the tutorial.

To delete the project:

Caution: Deleting a project has the following effects:

• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

Deleting the reservation

Alternatively, if you intend to keep the project, then you can avoid additional BI Engine costs by deleting your capacity reservation.

To delete your reservation, follow these steps:

1. In the Google Cloud console, under Administration go to the BI Engine page.

   Go to the BI Engine page

   Note: If prompted to enable BigQuery Reservation API, click Enable.

2. In the Reservations section, locate your reservation.

3. In the Actions column, click the icon to the right of your reservation and choose Delete.

4. In the Delete reservation? dialog, enter Delete and then click DELETE.

What's next

• For an overview of BigQuery BI Engine, see Introduction to BigQuery BI Engine.
• Learn more about Looker Studio in the Help Center.
• Learn how to reserve capacity.
• Learn more about BigQuery BI Engine Pricing.