Quickstart using Data Studio

BigQuery BI Engine is a fast, in-memory analysis service. By using BI Engine you can analyze data stored in BigQuery with sub-second query response time and with high concurrency.

BI Engine integrates with familiar Google tools like Google Data Studio to accelerate data exploration and analysis. With BI Engine, you can build rich, interactive dashboards and reports in Data Studio without compromising performance, scale, security, or data freshness.

Objectives

In this tutorial, you:

• Create a BI Engine capacity reservation by using the BigQuery Admin Console.
• Use Data Studio to connect to a BigQuery table managed by BI Engine.
• Create a Data Studio dashboard that queries your BI Engine-managed table.

Costs

This tutorial uses billable components of Google Cloud, including:

• 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.

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 Account.

   If you don't already have one, sign up for a new account.

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 the project selector page

3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

4. 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

Step one: Create a BigQuery dataset

The first step is to create a BI Engine dataset to store your BI Engine-managed table. To create your dataset:

1. Go to the BigQuery page in the Cloud Console.

   Go to the BigQuery page

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

3. On the right side, in the details panel, click Create dataset.

4. On the Create dataset page:

   • For Dataset ID, enter biengine_tutorial.

   • For Data location, choose United States (US). Currently, the public datasets are stored in the US multi-region location. For simplicity, you should place your dataset in the same location.

     

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

Step two: Create a table by copying data from a public dataset

This tutorial uses a dataset available through the Google Cloud Public Dataset Program. A public dataset is any dataset that is stored in BI Engine and made available to the general public. The public datasets are datasets that BI Engine hosts for you to access and integrate into your applications. Google pays for the storage of these datasets and provides public access to the data via a project.

About the dataset

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 Cloud Console.

Create your table

To create your table:

1. In the Cloud Console, open the SF 311 dataset.

   Go to the SF 311 dataset

2. In the navigation pane, expand san_francisco_311 and click the 311_service_requests table.

3. On the right side of the window, click Copy table.

4. In the Copy table dialog, in the Destination section:

   • For Project name, choose your project.
   • For Dataset name, verify biengine_tutorial is selected.

   • For Table name, enter 311_service_requests_copy.

     

   • Click Copy.

5. When the copy job is complete, you can verify the table contents by expanding [PROJECT] > biengine_tutorial and clicking 311_service_requests_copy > Preview.

Step three: Create your BI Engine reservation

1. In the BigQuery Admin Console, go to the BI Engine page.

   Go to the BigQuery Admin Console

2. Click Create reservation.

3. On the Create reservation page, for Step 1:

   • Verify your project name.
   • Choose your location. The location should match the location of the datasets you are querying.

   • Adjust the slider to the amount of memory capacity you're reserving. The following example sets the capacity to 2 GB. The current maximum is 100 GB.

     

4. Click Next.

5. For Step 2, review your reservation details and then click Next.

6. For Step 3, review the agreement and then click Create.

7. After confirming your reservation, the details are displayed on the Reservations page.

   

Step four: Create a data source connection in Data Studio

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

When you define your data source connection in Data 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 Google Data Studio report. In addition, the permissions applied to BigQuery datasets will apply to the reports, charts, and dashboards you create in Google Data Studio. When a Google Data 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. 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 Google Data Studio.

2. On the Reports page, in the Start a new report section, click the Blank 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 Google Data Studio access to your Google Cloud project.

7. In the Request for permission dialog, click Allow to give Google Data Studio the ability to view data in BigQuery. You may not receive this prompt if you previously used Google Data 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 Google Data 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 Data Studio the ability to view and manage files in Google Drive. You may not receive this prompt if you previously used Google Data Studio.

Step five: 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.

   

Cleaning up

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 this tutorial, 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 tutorials and quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the 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, you can avoid additional BI Engine costs by deleting your capacity reservation.

To delete your reservation:

1. In the BigQuery Admin Console, go to the BI Engine page.

   Go to the BigQuery Admin Console

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 Confirm reservation removal dialog, enter REMOVE and then click Proceed.

What's next

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