Visualizing BigQuery Data with Tableau

Tableau is an interactive data visualization tool that can be used to create business intelligence dashboards. Much like most business intelligence tools, it can be used to pull and manipulate data from a number of sources. The difference is its dedication to help users create insightful data visualizations. Tableau’s drag-and-drop interface makes it easy for users to explore data via elegant charts. It also includes an in-memory engine in order to speed up calculations on extremely large data sets. In today’s tutorial, we will be using Tableau Desktop for visualizing BigQuery Data.

[box type=”note” align=”” class=”” width=””]This article is an excerpt from the book, Learning Google BigQuery, written by Thirukkumaran Haridass and Eric Brown. This book is a comprehensive guide to mastering Google BigQuery to get intelligent insights from your Big Data.[/box]

The following section explains how to use Tableau Desktop Edition to connect to BigQuery and get the data from BigQuery to create visuals:

1. After opening Tableau Desktop, select Google BigQuery under the Connect To a Server section on the left; then enter your login credentials for BigQuery:

2. At this point, all the tables in your dataset should be displayed on the left:

You can drag and drop the table you are interested in using to the middle section labeled Drop Tables Here. In this case, we want to query the Google Analytics BigQuery test data, so we will click where it says New Custom SQL and enter the following query in the dialog:

SELECT trafficsource.medium as Medium, COUNT(visitId) as Visits

FROM `google.com:analytics- bigquery.LondonCycleHelmet.ga_sessions_20130910` GROUP BY Medium

3. Now we can click on Update Now to view the first 10,000 rows of our data. We can also do some simple transformations on our columns, such as changing string values to dates and many others.
4. At the bottom, click on the tab titled Sheet 1 to enter the worksheet view. Tableau’s interface allows users to simply drag and drop dimensions and metrics from the left side of the report into the central part to create simple text charts, with a feel much like Excel’s pivot chart functionality. This makes Tableau easy to transition to for Excel users.
5. From the Dimensions section on the left-hand-side navigation, drag and drop the Medium dimension into the sheet section. Then drag the Visits metric in the Metric section on the left-hand-side navigation to the Text sub-section in the Marks section. This will create a simple text chart with data from the original query:

6. On the right, click on the button marked Show Me. This should bring up a screen with icons for each graph type that can be created in Tableau:

Tableau helps by shading graph types that are not available based on the data that is currently selected in the report. It will also make suggestions based on the data available. In this case, a bar chart has been preselected for us as our data is a text dimension and a numeric metric. Click on the bar chart. Once clicked, the default sideways bar chart will appear with the data we have selected.

7. Click on the Swap Rows and Columns in the icon bar at the top of the screen to flip the chart from horizontal to vertical:

Map charts in Tableau

One of Tableau’s strengths is its ease of use when creating a number of different types of charts. This is true when creating maps, especially because maps can be very painful to create using other tools. Here is the way to create a simple map in Tableau using BigQuery public data. The first few steps are the same as in the preceding example:

1. After opening Tableau Desktop, select Google BigQuery under the Connect To a Server section on the left; then enter your login credentials for BigQuery.
2. At this point, all the tables in your dataset should be displayed on the left-hand side.
3. Click where it says New Custom SQL and enter the following query in the dialog:

SELECT zipcode, SUM(population) AS population FROM `bigquery-public- data.census_bureau_usa.population_by_zip_2010` GROUP BY zipcode

ORDER BY population desc

This data is from the United States Census from 2010. The query returns all zip codes in USA, sorted by most populous to least populous.

4. At the bottom, click on the tab titled Sheet 1 to enter the worksheet view.
5. Double-click on the zipcode dimension on the dimensions section on the left navigation. Clicking on a dimension of zip codes (or any other formatted location dimension such as latitude/longitude, country names, state names, and so on) will automatically create a map in Tableau:

6. Drag the population metric from the metrics section on the left navigation and drop it on the color tab in the marks section:

The map will now show the most populous zip codes shaded darker than the less populous zip codes. The map chart also includes zoom features in order to make dealing with large maps easy.

7. In the top-left corner of the map, there is a magnifying glass icon. This icons has the map zoom features. Clicking on the arrow at the bottom of this icon opens more features. The icon with a rectangle and a magnifying glass is the selection tool (The first icon to the right of the arrow when hovering over arrow):

8. Click on this icon and then on the map to select a section of the map to be zoomed into:

This image is shown after zooming into the California area of the United States. The map now shows the areas of the state that are the most populous.

Create a word cloud in Tableau

Word clouds are great visualizations for finding words that are most referenced in books, publications, and social media. This section will cover creating a word cloud in Tableau using BigQuery public data.

The first few steps are the same as in the preceding example:

1. After opening Tableau Desktop, select Google BigQuery under the Connect To a Server section on the left; then enter your login credentials for BigQuery.
2. At this point, all the tables in your dataset should be displayed on the left.
3. Click where it says New Custom SQL and enter the following query in the dialog:

SELECT word, SUM(word_count) word_count

FROM `bigquery-public-data.samples.shakespeare` GROUP BY word

ORDER BY word_count desc

The dataset is from the works of William Shakespeare. The query returns a list of all words in his works, along with a count of the times each word appears in one of his works.

4. At the bottom, click on the tab titled Sheet 1 to enter the worksheet view.
5. In the dimensions section, drag and drop the word dimension into the text tab in the marks section.
6. In the dimensions section, drag and drop the word_count measure to the size tab in the marks section.
7. There will be two tabs used in the marks section. Right-click on the size tab labeled word and select Measure | Count:

This will create what is called a tree map. In this example, there are far too many words in the list to utilize the visualization.

8. Drag and drop the word_count measure from the measures section to the filters section. When prompted with How do you want to filter on word_count, select Sum and click on next..
9. Select At Least for your condition and type 2000 in the dialog. Click on OK. This will return only those words that have a word count of at least 2,000..
10. Use the dropdown in the marks card to select Text:

11. Drag and drop the word_count measure from the measures section to the color tab in the marks section. This will color each word based on the count for that word:

You should be left with a color-coded word cloud.

Other charts can now be created as individual worksheet tabs. Tabs can then be combined to make what Tableau calls a dashboard. The process of creating a dashboard here is a bit more cumbersome than creating a dashboard in Google Data Studio, but Tableau offers a great deal of more customization for its dashboards. This, coupled with all the other features it offers, makes Tableau a much more attractive option, especially for enterprise users.

We learnt various features of Tableau and how to use it for visualizing BigQuery data.To know about other third party tools for reporting and visualization purposes such as R and Google Data Studio, check out this book Learning Google BigQuery.

Read Next:

Tableau is the most powerful and secure end-to-end analytics platform – Interview Insights

Tableau 2018.1 brings new features to help organizations easily scale analytics

Getting started with Data Visualization in Tableau