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.
ObjectivesIn 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.
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.
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
In the Cloud Console, on the project selector page, select or create a Cloud project.
Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project.
- The BigQuery API is automatically enabled in new projects. To activate the BigQuery API in an existing project, go to Enable the BigQuery 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:
Go to the BigQuery web UI in the Cloud Console.
In the navigation panel, in the Resources section, click your project name.
On the right side, in the details panel, click Create dataset.
On the Create dataset page:
- For Dataset ID, enter
For Data location, choose United States (US). Currently, the public datasets are stored in the
USmulti-region location. For simplicity, you should place your dataset in the same location.
- For Dataset ID, enter
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
Create your table
To create your table:
Open the SF 311 dataset in the BigQuery web UI.
In the navigation pane, expand san_francisco_311 and click the 311_service_requests table.
On the right side of the window, click Copy table.
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
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
Go to the BI Engine page in the BigQuery Admin Console.
Click Create reservation.
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 10 GB.
For Step 2, review your reservation details and then click Next.
For Step 3, review the agreement and then click Create.
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.
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
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
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.
You can set
bigquery.jobs.create permissions at the project level by granting
any of the following predefined IAM roles:
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
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
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:
Open Google Data Studio.
On the Reports page, in the Start a new report section, click the Blank template. This creates a new untitled report.
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.
In the Add a data source window, click Create new data source.
In the Google Connectors section, hover over BigQuery and then click Select.
For Authorization, click Authorize. This allows Google Data Studio access to your Google Cloud project.
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.
Leave My Projects selected and in the Project pane, click the name of your project.
In the Dataset pane, click biengine_tutorial.
In the Table pane, click 311_service_requests_copy.
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.
In the upper right corner, click Add to report.
When prompted, click Add to report.
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:
(Optional) At the top of the page, click Untitled Report to change the report name. For example, type
BI Engine tutorial.
After the report editor loads, click Insert > Bar chart.
Using the handles, expand the size of the chart.
On the Data tab, notice the value for Data Source is
Because you are charting the number of requests by neighborhood, you need to set the Dimension to
categoryand the Breakdown dimension to
neighborhood. Click the default dimension (likely
status), and in the list, choose category.
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
you add a filter that excludes
NULL values from the chart.
To add a filter:
On the Data tab, click Add a filter.
In the Create filter dialog:
- For Name, enter Exclude nulls.
- Verify Data source is set to
- Click Include and choose Exclude.
- Click Select a field and choose neighborhood.
Click Select a condition and choose Is null.
After the filter is applied, your chart should look like the following.
Deleting the project
The easiest way to eliminate billing is to delete the project that you created for the tutorial.
To delete the project:
- In the Cloud Console, go to the Manage resources page.
- In the project list, select the project that you want to delete and then click Delete delete.
- 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:
Go to the BI Engine page in the BigQuery Admin Console.
In the Reservations section, locate your reservation.
In the Actions column, click the icon to the right of your reservation and choose Delete.
In the Confirm reservation removal dialog, enter REMOVE and then click Proceed.