Tableau is a business intelligence tool that helps you analyze, visualize, and manage data, which can be understood even by non-technical people. With the help of its intuitive interface, you can easily create and share dashboards, reports, and workbooks, through its drag-and-drop interface.
Google Cloud Platform’s BigQuery service allows you to create, manage, share and query data and offers data warehousing, analytics, and machine learning capabilities. With the help of BigQuery, you can analyze vast amounts of data and meet your big data needs with cloud-based parallel processing.
With the help of BigQuery and Tableau integration, you can develop high-quality visualizations and a couple of data exploration with your business intelligence tools. It will allow you to monitor changes in your data at the actual time and create high-quality dashboards that can assist you in analyzing and researching your data.
In this blog, we will be using Tableau’s BigQuery integration to query your data and gain a more comprehensive picture. We would also look into some key areas where you can utilize Tableau to discover insights and data trends while keeping the performance and scalability at an edge.
Getting started
Before getting started, you need to have the following prerequisites satisfied:
1. Access to a Tableau workspace on Desktop or on Web.
2. Administrator access to a BigQuery project.
You will also need to possess a working knowledge of the Google Cloud Platform and BigQuery. You can connect BigQuery to your Tableau workspace via two methods:
- Using Tableau’s Google BigQuery connector
- Using ODBC drivers to connect Tableau to Google BigQuery
Using Tableau’s Google BigQuery connector
Tableau provides a built-in Google BigQuery connector that allows you to query data from Google BigQuery and import it into Tableau. This method is essential when you need to achieve a real-time data feed, which necessitates a real-time report, and the dataset is large enough for long extraction. You can start by launching your Tableau workspace. We recommend using a Tableau server to reduce the load on your server and get access to data protection, management and administration.
On the “To a server” section, select the “Google BigQuery” connector. A default window would open where you will be prompted to enter your details. Sign in using the Google account, through which you can access your Google BigQuery data. Provide Tableau with access to your Google BigQuery data and click “Accept” before closing the browser.
After successfully connecting to your Google BigQuery, you will see a data source page in Tableau. If you have some data in your dataset, you can now import it into Tableau. Select the name option and assign the name of your dataset. We recommend using a different name for each dataset, making it easier to identify the dataset in Tableau.
Next, select the billing project from the dropdown list. This is the project that you have access to in Google BigQuery. Pick up a project that you have access to in Google BigQuery. This connection will allow Tableau to access the data in your Google BigQuery dataset. Next, select the desired dataset from the Dataset dropdown list. Finally, choose the BigQuery table from the table dropdown list. The Google BigQuery connector will automatically import the data into Tableau.
In the top right corner of the Tableau workspace, you will see a Connection type to choose between Live and Extract. The Live connection type will allow you to see the data in Tableau as it is being updated. The Extract connection type will let you see the data in Tableau after it has been extracted from Google BigQuery. While the option defaults to Live, click on the Extract button if you wish to select Extract.
Further, click on the Edit icon to configure how to store the data and how much data to extract. On completion, click the sheet tab, select the extract location, give a filename, and click Save.
To test the connection, you can use Custom SQL to write a query to narrow down the scope. This would prevent you from querying the entire dataset. From the left panel, click the New Custom SQL option and provide the query. Click OK to execute the query and see your data by clicking Update Now.
Using ODBC drivers to connect Tableau to Google BigQuery
You can also use the ODBC drivers to establish a connection between Tableau and your Google BigQuery. For this purpose, you would need to install the Magnitude Simba ODBC driver. This allows your BigQuery dataset to be integrated with any other data source. The official Google Cloud website offers drivers for various operating systems and additional relevant information.
Once installed, launch it to start the DSN process. Click on the `Add` button and select the Simba ODBC driver. You will be now required to complete this relevant information:
1. Data source: Enter the name of your desired data source.
2. Description: Enter a short description of the connection.
3. Confirmation Code: Enter the confirmation code you received while providing access to your BigQuery cluster.
4. Empty: Enter the Google Email address that you used to create your BigQuery cluster.
5. Keyfile path: Enter the path to your JSON or .p12 key file.
6. Project: Enter the name of your Google BigQuery billing project.
The rest of the options can be kept as default, while you can use the advanced option to configure the connection further. On completion, click on the Test button, and you will see the connection status.
On Tableau, you can now use the ODBC connection option to connect to your BigQuery cluster. In the ODBC connection dialogue, select your DSN and click on the `Connect` button. You can also set the desired schema and table for the connection, dragging and dropping on the canvas. You can now perform your analysis on the BigQuery data.
Recommended settings
To keep track of monitoring and optimising your queries and workbooks, you can use the Performance Recorder. It can track time to fulfil a request and help you identify slow queries. You can also optimise parameters with customisation attributes. Using context filters can also help you improve the accuracy of your queries’ accuracy while working with an extensive database.
If you are using Extract, you can look forward to avoiding using related tables or custom SQL. These operations should be ideally performed on a BigQuery view so that you don’t have to spend your resources on Tableau. It can be ideally reserved for processing and analytics. It is also recommended to avoid using custom SQLs in the Extract process.
In Live connection, it is recommended to make use of partitioned tables. This will help you avoid the query cost and help you further increase the performance, which is necessary for responsiveness. We also recommend not using custom SQLs, related views and complex views. To avoid them, materialise all your data during aggregation in a single table.
GCP’s newest update, BigQuery Studio (designed for big data management in organizations using AI, ML, and LLM) is an all-in-one analytics dashboard that streamlines end-to-end analytics workflows, from data ingestion and transformation to sophisticated predictive analysis.
Conclusion
If you don’t have access to Tableau, you can request a trial of the platform by filling out the form on Tableau. You can also up-skill your team on Tableau with these guided tutorials.
Check out our BigQuery pricing calculator to estimate your BigQuery charges.
