Quickstart using Looker Studio

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 more_vert 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

2. Click add 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

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.