A Look at the Top 6 Visualization Tools for BigQuery

Let’s begin with the good news — there are lots of data visualization services on the market. There are free online services, paid offline services, services for mobile devices, services for desktops, services that allow for collaborative editing of reports, services that support a combination of different data sources — everything the marketing specialist (and the budget) could wish for. But which service will be the best to cope with your tasks?​

In our article, we consider the six most popular BigQuery reporting and visualization tools.​

To start working in Google Data Studio, log in to your Google account and go to https://datastudio.google.com.

1. Choose Start a new report on the homepage, either at the top left or the bottom right (as shown in the image above).
2. To load data from GBQ into Data Studio, click the Create Data Source button and choose Google –> BigQuery from the list of connectors. 

Note: A Google Cloud Platform account is necessary for working with data in GBQ.

When choosing a data source, decide on the project, data set, and table that should be connected with the report. Then the editor window will open where you can:

• change the period for data storage
• give access to third-party visualization tools
• edit fields in reports

You can read more about possibilities for processing data in Data Studio in the Data Studio Help Center.

Now you can visualize your data. The window for the reports editor will open as soon as you select a table for your report. The interface of the Data Studio editor is very simple to understand and use.

The editor supports eleven types of visualizations, including tables and reports, charts and graphs, dynamic ranks, and maps. There are also separate elements for report visualization:

• Date range
• Filters
• Data management
• URL of the built-in element

To add a visual element to a report, it’s enough to choose it from a drop-down menu and drag it to the workspace. Any element can be edited using the settings menu on the right. In this menu, there are two tabs where it’s possible to change the data shown and the style in which it's displayed.

Among the latest updates to the editor are a profound specification of graphs. For example, now you can pass from the display of the countries to the cities. But most importantly, the latest update brings an opportunity to combine different data sources in one chart.

Google has prepared not only infographics with a simple explanation of how this works but also a YouTube video.

A distinctive feature of Data Studio is the possibility to collaborate on reports. You can give access to a report using the standard the standard sharing dialog box that you see in Google Drive. Easy and simple!

If desired, you can create a template from any report. For this purpose, there’s an option in the menu to copy a report. To create a new report from this template, choose a new data source.

Advantages of Google Data Studio:

• Free
• Intuitive interface
• Collaborative editing of reports
• Page-by-page formatting
• Opportunity to use several data sources in one report
• In case of an error, it’s possible to use the version history to roll back to a previous version of a report

Disadvantages of Google Data Studio:

• Lack of extensive data preparation tools
• No report preview for mobile devices

Google Data Studio is extremely simple to use. Its free functionality, integration with Google products, and more than 150 connectors is enough to satisfy the requirements of small and medium-sized businesses.

Do you want to visualize reports in the form of tables, charts, and graphs in Google Sheets and do this based on data stored in Google BigQuery?

If so, the OWOX BI BigQuery Reports Add-on is all you need. It allows you to load data quickly, schedule reports, and send the results to a new table in GBQ.

You can get the add-on from the Chrome Web Store or directly in Google Sheets by selecting Add-ons → Get add-ons...:

Advantages of the OWOX BI BigQuery Reports Add-on:

• You don’t need to log in to Google BigQuery.
• Your tables won’t slow down and all calculations will take place in GBQ storage.
• You can just open the add-on and create a new report.

After Google Sheets has loaded the chosen table, you can start visualizing data. For example, you can add a chart:

To select graphs and charts, open the menu in the top right corner and select the first item. On the right, there will be a menu with settings for an element. It’s possible to change the type of the chart or to choose the range of data.

Note: Sheets shows recommendations for what types of charts should be used with a particular data set.

You can easily share reports with the help of standard Google access settings.

• Advantages of Google Sheets:
• Simple control of access to reports
• Collaborative editing of reports
• Compatible with Excel

Disadvantages of Google Sheets:

• Minimum set of visualizations
• Impossible to unite data sources

Undoubtedly, Google Sheets has become a cloud replacement for Excel. This tool is mobile and available everywhere there’s an internet connection. If you have no need for difficult data visualization, then this free option is definitely for you.

And for a solution to the problem of combining data from different data sources, there’s OWOX BI.

This tool for creating reports is specially developed for people who don't know SQL. Smart Data works with data from Google BigQuery; therefore, it’s possible to create visualization right after choosing a project and data set.

There are three ways to construct a report using Smart Data:

• Choose a report type from the gallery of popular templates
• Construct a report in the Report Builder, choosing parameters and metrics
• Make a direct request to the service in the search field

In the template gallery, reports are divided into thematic blocks. For example, reports on attribution, reports on CRM databases, reports on the ROPO effect.

It’s possible to use reports in several ways:

1. Export to Google Sheets
2. Save as a CSV file
3. Copy a SQL request to the clipboard

Easy exporting to Google Data Studio is also provided. For this purpose, there’s a separate button in the menu.

Advantages of OWOX BI Smart Data:

• Direct connection to data in Google BigQuery
• High level of data protection in line with PCI DSS, ISO 27001, and SOC 2.3 standards
• Detailed OWOX Help Center

Disadvantages of OWOX BI Smart Data:

• Limited set of combinations for creating reports
• Minimum number of items for visualization

The key feature of OWOX BI is a friendly interface built for nontechnical specialists. Knowledge of SQL is not required to build reports. The process is rather simple: enter a request and the system itself will pick suitable reports.

This tool from Microsoft follows the logic of divide and conquer. To work with Power BI, you’ll need to set up two versions of the product at once:

1. Power BI Desktop for creating reports
2. Power BI Service for monitoring and analyzing reports. Also, opportunities for working with data are limited.

Note: Power BI doesn’t support Linux or macOS.

The built-in Google BigQuery connector can be found only in the desktop version of Power BI. The second option is to use a separate connector from Simba drivers.

When using the connector, Power BI will request access to your Google BigQuery account, and after authenticating, it will be possible for the user to start loading data.

When loading data, it’s possible to choose between two connection parameters:

1. Import – Transfers a copy of the data; imports the chosen tables and columns with which Power BI Desktop will work.
2. DirectQuery – Creates a dynamic connection. Data is not copied or imported; the basic data source is requested.

Among the advantages of working with DirectQuery are the ability to work with up-to-date data and the lack of a 1 GB restriction on data sets.

Disadvantages of DirectQuery:

• Errors when too complicated query is requested in the editor
• All tables must come from one database
• No time-based logic for operations (processing of columns of date: day, month, year)

You can find more details about how to use DirectQuery in the official documentation.

After loading data, we can go to the reports editor.

Just like in Google Data Studio, the menu with settings for visual elements in Power BI is on the right. You can choose from several types of visualizations:

• Simple charts
• Stacked charts
• Clustered charts
• Maps, sensors, and funnels
• Tables and matrices
• R scripts and Python scripts

The list of fields on the basis of which you can build visualizations is also located on the right. If you aren’t sure which graph to use, just specify the fields you want to show in the report and Power BI will choose a suitable form of data visualization for you.

Note: If you add too many values, the service will report a mistake and will offer to correct it.

With Power BI, you can prepare data directly in the request editor interface (which you can access using the Change Requests button). You can perform the following actions on data:

• Clean
• Rename
• Delete
• Integrate

There are two ways to associate requests in Power BI:

1. Merge (association) — Useful if one or several columns need to be added to the table
2. Addition (adding) — Useful if a line of data needs to be added to the table

Unfortunately, to share a report you’ve created, you not only need two versions of the product (as we mentioned) but also a paid package of services called Power BI Pro.

Advantages of Power BI:

• Extensive opportunities for data preparation
• Applied Steps allows you to cancel steps in the request editor
• Report previews. It’s possible to see adaptation of reports for mobile devices.

Disadvantages of Power BI:

• Freemium model: possible to share reports directly only in the Pro version
• Slow loading of data from GBQ
• Not available for Linux or macOS

The full integration of Power BI with Microsoft products can be decisive in your choice of this service. However, if you don’t like Excel, you can find some difficulties with the interface and execution of operations. Besides, Power BI copes poorly with loading a large amount of data.

To start working with data in Tableau, select the Google BigQuery connector from the menu of servers.

In the window that opens, choose the project, a data set, and the table with data you want to visualize in your report.

In the lower menu, go to the first page of the report (Sheet 1). This will take you to the reports editor.

The elements available in the menu on the right will depend on the number of selected parameters and metrics on the left. In addition to standard types of visualization, you can find:

• Tree maps
• Scatter and packed bubble charts
• Gantt chart

Note: If you put the cursor over an element, Tableau will show which parameters and metrics are necessary for it to be displayed.

This service also allows for adding one more data source to the report from the menu. Thus, it’s possible to combine data from different databases.

Reports can be exported (as PowerPoint or PDF) and shared in Tableau Online or Tableau Server.

Advantages of Tableau:

• Access to video records and detailed instructions directly from the menu
• Ability to merge data sources
• Many opportunities for reports: it’s possible to create dashboards on the basis of pages of reports.

Disadvantages of Tableau:

• Frequent problems when working with difficult data models

Tableau differentiates itself from other services thanks to its simple interface and active community of users. One more plus is support for the R language; however, data needs to be prepared before working with this service.

As with Power BI, in the cloud version of QlikView, there’s no direct connector to Google BigQuery. 

For the desktop version of QlikView to be connected to GBQ, you need to set up the ODBC Connector Package.

Note: Access to GBQ is provided only to users with paid licenses for QlikView.

When you first start working with a report, you can choose only one type of visualization:

Then you need to define what fields will be presented in this graph or chart. After that, it’s possible either to add a second graph or chart or proceed to the report.

For more visualizations, you can choose from the tools menu:

• Quick chart wizard
• Time chart wizard
• Statistics chart wizard
• Box plot wizard

Created reports can be sent by email as attachments or be loaded into a repository on the QlikView server. Only authenticated users have access to these reports.

Advantages of QlikView:

• Interactive training and video records
• Built-in ETL functions (extract, transform, load)
• Fast data loading and processing

Disadvantages of QlikView:

• Impossible to connect data from GBQ without an additional connector
• Minimum number of items for visualization

The makers of QlikView, which is a corporate tool, also offer QlikSense, which is designed for individual users. QlikView sets itself apart thanks to its fast work with data and the possibility for collaborative development. But to use it successfully, users should have technical training.

How can you decide which visualization service is the most suitable for your reports? The following questions will help you choose the best option:

1. What problems are you going to solve with the help of reports?
2. Which of your employees will use the service?
3. What complexity of reports and graphs do you want to receive?
4. What are your technical requirements for a visualization service?

For a better understanding of this subject, we advise reading the article Data Visualization: Principles, Tools, and Useful Tricks.