{% setvar launch_stage %}beta{% endsetvar %} {% setvar launch_type %}product{% endsetvar %} {% setvar launch_name %}BigQuery BI Engine{% endsetvar %} {% include_content_page "/docs/includes/___info_launch_stage_disclaimer" %}

Getting started 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 Platform, including:

  • BI Engine: During the beta period, you do not incur charges for using 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. Select or create a GCP project.

    Go to the Project selector page

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

    Learn how to enable billing

  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 web UI in the GCP Console.

    Go to the BigQuery web UI

  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.

      Create dataset page

  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 BigQuery web UI in the Google Cloud Platform Console.

Create your table

To create your table:

  1. Open the SF 311 dataset in the BigQuery web UI.

    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.

      Copy the SF 311 table

    • 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. Go to the BI Engine page in the BigQuery Admin Console.

    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 10 GB.

      BI Engine capacity location

  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.

    Confirmed reservation

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. When you create a data source for BI Engine, Google Data Studio uses the BigQuery connector.

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 permisssions

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

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:

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.

    Blank template

  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.

    Add 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 GCP 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.

    Choose the category dimension

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

    Add the neighborhood 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.

    Add a filter option

  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.

      Completed filter

    • Click Save.

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

    Completed bar chart

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:

  1. In the GCP Console, go to the Projects page.

    Go to the Projects page

  2. In the project list, select the project you want to delete and 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. Go to the BI Engine page in the BigQuery Admin Console.

    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

Was this page helpful? Let us know how we did:

Send feedback about...