BigQuery BI Engine lets you perform fast, low-latency analysis services andinteractive analytics with reports and dashboards backed byBigQuery.

This introductory tutorial is intended for data analysts andbusiness analysts who use the business intelligence (BI) tool Tableau Desktopto build reports and dashboards.

Objectives

In this tutorial, you complete the following tasks:

• Create a dataset and copy data.
• Create a BI reservation and add capacity using the Google Cloud console.
• Use Tableau Desktop to connect to a BigQuery table that'smanaged by BI Engine.
• Create dashboards using Tableau Desktop.

Costs

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

• BigQuery
• BI Engine

To generate a cost estimate based on your projected usage, use thepricing calculator.

When you finish the tasks that are described in this document, you can avoid continued billing by deleting the resources that you created. For more information, seeClean up.

Before you begin

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

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.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

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

If you're using an existing project for this guide,verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

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

If you're using an existing project for this guide,verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

1. Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

   Enable the API

   For new projects, the BigQuery API is automatically enabled.

Required roles

To get the permissions that you need to create a dataset, create a table, copy data, query data, and create a BI Engine reservation, ask your administrator to grant you the following IAM roles on the project:

• Run copy jobs and query jobs:BigQuery Job User (roles/bigquery.jobUser)
• Create a dataset, create a table, copy data into a table, and query a table:BigQuery Data Editor (roles/bigquery.dataEditor)
• Create a BI Engine reservation:BigQuery Resource Admin (roles/bigquery.resourceAdmin)

For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Additional permissions might be needed if you have are using a custom OAuthclient in Tableau Desktop to connect to BigQuery. For moreinformation, seeTroubleshooting Errors.

Create a BigQuery dataset

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

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, click your project.

4. In the details pane, clickmore_vertView actions, and then clickCreate dataset.

5. On theCreate dataset page, do the following:

   • ForDataset ID, enterbiengine_tutorial.

   • ForData location, chooseus (multiple regions in UnitedStates), themulti-regionlocation where public datasetsare stored.

   • For this tutorial, you can selectEnable table expiration, and thenspecify the number of days before the table expires.

     

6. Leave all of the other default settings in place and clickCreate dataset.

Create a table by copying data from a public dataset

This tutorial uses a dataset available through theGoogle Cloud Public Dataset Program. Public datasetsare datasets that BigQuery hosts for you to access and integrateinto your applications.

In this section, you create a table by copying data from theSan Francisco 311 service requests dataset. You can explore the dataset by using theGoogle 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 left pane, clickexploreExplorer:

   

3. In theExplorer pane, search for thesan_francisco_311 dataset.

4. Click the dataset, and then clickOverview> Tables.

5. Click the311_service_requests table.

6. In the toolbar, clickCopy.

   

7. In theCopy table dialog, in theDestination section, do thefollowing:

   • ForProject, clickBrowse, and then select your project.
   • ForDataset, selectbiengine_tutorial.

   • ForTable, enter311_service_requests_copy.

     

8. ClickCopy.

9. Optional: After the copy job is complete, verify the table contents by expandingPROJECT_NAME> biengine_tutorial andclicking311_service_requests_copy> Preview. ReplacePROJECT_NAME with name of your Google Cloud projectfor this tutorial.

Create your BI Engine reservation

1. In the Google Cloud console, underAdministration go to theBI Engine page.

   Go to the BI Engine page

   Note: If prompted to enableBigQuery Reservation API, clickEnable.

2. ClickaddCreate reservation.

3. On theCreate Reservation page, configure your BI Enginereservation:

   • In theProject list, verify your Google Cloud project.
   • In theLocation list, select a location. The location shouldmatch thelocation of the datasetsthat you're querying.

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

     

4. ClickNext.

5. In thePreferred Tables section, optionally specify tables foracceleration with BI Engine. To find table names, do thefollowing:

   1. In theTable Id field, type part of the name of the table that youwant 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 preferredtables are specified, all project queries are eligible for acceleration.

6. ClickNext.

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

8. If you accept the terms of agreement, clickCreate.

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

Connect to a dataset from Tableau Desktop

To connect to a dataset from Tableau Desktop, you need to take some steps inTableau Desktop and then some steps in BI Engine.

Steps to take in Tableau

1. StartTableau Desktop.
2. UnderConnect, selectGoogle BigQuery.
3. In the tab that opens, select the account that has theBigQuery data that you want to access.
4. If you're not already signed in, enter your email or phone, selectNext, and enter your password.
5. SelectAccept.

Tableau can now access your BigQuery data.

InTableau Desktop, on theData Source page:

1. From theBilling Project drop-down, select the billing projectwhere you created the reservation.
2. From theProject drop-down, select your project.
3. From theDataset drop-down, select the datasetbiengine_tutorial.
4. UnderTable, select the table311_service_requests_copy.

Creating a chart

Once you have added the data source to the report, the next step is to create avisualization.

Create a chart that displays the top complaints by neighborhood:

1. In the Google Cloud console, clickNew worksheet.
2. Set theDimension toComplaint Type.
3. Filter based on the dimension calledneighborhood.
4. UnderMeasures, selectNumber of Records.
5. Right-click on theNeighborhood filter and clickEdit Filter.
6. Add a filter to exclude null: selectNull.
7. ClickOK.

For more information, see theTableau documentation.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

To avoid incurring charges to your Google Cloud account for the resources usedin this quickstart, you can delete the project, delete theBI 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 anappspot.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 theManage resources page.

   Go to Manage resources

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

Deleting the reservation

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

To delete your reservation, follow these steps:

1. In the Google Cloud console, underAdministration go to theBI Engine page.

   Go to the BI Engine page

   Note: If prompted to enableBigQuery Reservation API, clickEnable.

2. In theReservations section, locate your reservation.

3. In theActions column, click themore_vert icon to the right of your reservation and chooseDelete.

4. In theDelete reservation? dialog, enterDelete and thenclickDELETE.

Troubleshooting errors

If you are using a custom OAuth configuration in Tableau Desktop to connect toBigQuery, some users might experience issues connecting to aTableau server and encounter the following error message:

the app is blocked

To resolve this error, verify that the user is assigned to a role that has alltherequired permissions to connect Tableau to BigQuery.If the problem persists, add the user to theOAuth Config Viewer role(roles/oauthconfig.viewer).

What's next

• For an overview of the BI Engine, seeIntroduction to BI Engine.