Unlock the power of unified business intelligence with Google Cloud BigQuery and Amazon QuickSight

Amazon QuickSight is a cloud-native, serverless business intelligence (BI) service that lets you build visualizations, perform ad hoc analysis, and gain insights through machine learning (ML) capabilities such as anomaly detection, forecasting, and natural language querying. QuickSight utilizes its robust in-memory engine SPICE (Super-fast, Parallel, In-memory Calculation Engine) to rapidly perform advanced calculations and deliver visuals.

BigQuery is Google Cloud’s fully managed, petabyte-scale, cost-effective analytics data warehouse that lets you run analytics over vast amounts of data in near-real time. With BigQuery, there’s no infrastructure to set up or manage, letting you focus on finding meaningful insights and taking advantage of flexible choices across on-demand and capacity-based options.

In this post, we walk you through the permissions and connection details needed in BigQuery to bring BigQuery data into QuickSight through OAuth and create a simple dashboard.

Prerequisites

Make sure you have the following:

• An AWS account
• A QuickSight account with Enterprise Edition
• A Google Cloud account

Configure BigQuery permissions

This section is specifically for Google account administrators, who must apply the necessary permissions for their users to view data in QuickSight. Complete the following steps:

1. Open the Google Cloud console and in navigation pane, choose IAM & Admin.
   

The permissions page opens, which lists all permissions by principal (email address) or by role.

2. Choose Grant access.
3. Enter the email address of the principal to which you want to grant access in QuickSight. They will use these login credentials to access BigQuery from QuickSight. In this example, our user is quicksight.user@gmail.com.
4. Under Assign roles, assign the following roles:
   1. BigQuery Metadata Viewer – At a project level. This is needed to display all datasets and tables for dataset and table discovery in QuickSight.
   2. BigQuery Job User – At a project level.

   

5. In the navigation pane, choose BigQuery.
6. Choose the options menu for your dataset (for this post, QuickSight_Demo) and choose Share.
7. Choose Add principal.

This allows the user to view the data in QuickSight. Depending on the permissions, this can be at a project like the previous roles or at a dataset or table level.

8. For this post, we share the dataset QuickSight_Demo for the user quicksight.user@gmail.com and choose the role BigQuery Data Viewer.
   

Get connection details from BigQuery

You need the following information from BigQuery when initiating the connection from QuickSight:

• Google Cloud account email and password
• Project ID
• Dataset region

Complete the following steps to collect this information:

1. If you don’t already have your Google account email and password, contact your Google account administrator.
2. To collect your project ID, open the Google Cloud console and choose your project name.
3. In the pop-up window, copy the respective ID for your project.

Alternatively, you can reach out to your BigQuery administrator to get your project ID.

4. In the Google Cloud navigation pane, choose BigQuery.
   
5. Under Explorer, choose the dataset you want to bring into QuickSight.
6. In the dataset details, locate Data location to get the dataset region. In this example, we have selected QuickSight_Demo as the dataset and the data location is US, which is the dataset region.

Alternatively, you can reach out to your BigQuery administrator to get your dataset region.

Analyze your BigQuery data in QuickSight

To analyze your BigQuery data in QuickSight, you first need to create a BigQuery data source. Complete the following steps:

1. On the QuickSight console, choose Datasets in the navigation pane.
2. Choose New dataset.
   
3. Choose Google BigQuery as your data source.
   
4. For Data source name, enter a descriptive name (for this post, we name it BigQuery Demo).
5. Enter the project ID and region details that you collected earlier.
6. Choose Sign in.
   
7. When prompted, enter your Google account email and password.
8. Review your access details and choose Continue.

This gives permissions to QuickSight to view your BigQuery data and manage in QuickSight for the given project ID and dataset region.

This completes the creation of a BigQuery data source in Amazon QuickSight. The workflow automatically takes you to the following steps to create your dataset.

9. For Dataset, choose the dataset you want to use (QuickSight_Demo).
10. Select the table you want to use (for this post, Sample_All_Flights).
11. Choose Edit/Preview data or, if you want to use your own SQL statement, choose Use custom SQL.
    
12. On the data preparation page, choose Save & Publish, then choose Publish & Visualize.
    
13. In the analysis section, choose your visual type (for this post, we use a vertical stacked bar chart).
14. Configure your columns (for this post, we use fl_date, origin_city_name, and late_aircraft_delay as for X axis, Value, and Group/Color, respectively).

When we hover over the chart, we see Dallas/Fort Worth experienced 50% flight delays on May 15, 2015, based on our sample data.

15. To publish the dashboard, choose the Share icon.
    

Note that the data we used in this post is just a sample created for this demo and not a true representation of airport and flight data.

Conclusion

In this post, we walked you through the permissions and connection information needed in BigQuery to import data into QuickSight and build a simple dashboard.

If you haven’t already, your next step is to create a QuickSight account with Enterprise Edition. We only reviewed the basic QuickSight functionalities in this post. For additional discussions and help getting answers to advanced QuickSight topics, check out the QuickSight Community.  If you are not a BigQuery customer already, sign up in Google Cloud Console.

About the authors

Vignessh Baskaran is a Product Manager owning Data Connectivity domain in Amazon QuickSight. He has 7+ years of experience in developing large scale data and analytics solutions. Prior to this role, he worked as a Sr. Sales Analytics Lead in AWS building comprehensive BI solutions using QuickSight which were globally adopted across AWS Worldwide Specialist Sales teams.

Jobin George is a Staff Solutions Architect at Google with more than a decade of experience designing and implementing large scale Data and Analytics solutions. He provides technical guidance, design advice and thought leadership to some of the key Google customers and Data & Analytics partners.