Choosing the Best BigQuery Reporting Tool for Your Team & Building BigQuery Dashboards
Which way BigQuery reporting?
Table of Contents:
Introduction
BigQuery, a part of Google Cloud Platform (GCP), is a comprehensive solution for data warehousing, analytics, and machine learning. It is fit for large-scale data analysis, enabling users to run petabyte-scale queries quickly. BigQuery uses a serverless architecture, eliminating the need for extensive infrastructure management, and allowing users to focus on data analysis rather than system maintenance. Choosing the right analytics solution on top of Bigquery can help an organization generate error-free insights. So, which is the best reporting tool to pair with your BigQuery infrastructure? Below are the 5 best options and features they have to offer.
Best BigQuery Reporting Tools
Tableau
Tableau is a powerful business intelligence tool that supports integration with BigQuery. After starting Tableau, you can find Tableau’s Google BigQuery connector under the “Connect” tab. The connector requires you to know your BigQuery email/phone, and password. Tableau supports two authentication options when connecting to BigQuery: Using OAuth or a Service Account (JSON) file. To sign in using OAuth, choose “Sign in using OAuth” under the Authentication drop-down button and click “ Sign In.” Tableau will then require you to enter your BigQuery password and allow it to access your BigQuery data. To sign in using the service account (JSON) file, select the “Sign in using Service Account” option under the Authentication drop-down button, search for the file, or enter its path location. Click “Sign In” and enter your BigQuery password. Tableau will prompt you to allow it to access your BigQuery data. After a successful connection to Google BigQuery, you can select your target data set and table.
Tableau Features
Tableau has the following features for its users:
- Integrations
Tableau supports native integration with PDFs, spreadsheets, files, SQL databases, and cloud data warehouses such as Google BigQuery. However, Tableau lacks connectors to NoSQL databases such as Apache Cassandra. Users must depend on JDBC or ODBC connectors to pull data from such sources.
Tableau users can connect to BigQuery without installing any driver. User authentication can be done using OAuth or a Service account (JSON) file. Once connected to Bigquery, you can select a project, data set, and table to work with. Tableau also allows you to create a custom query to connect to a specific query instead of the entire data source. You can also configure customization attributes to improve the performance of large result sets
2. Visualizations
Tableau provides beautiful and powerful visualizations to help you present your BigQuery data. Examples include maps, stories, charts, and dashboards. Tableau users can customize the visualizations to meet their unique needs.
3. Search-based analytics
Tableau comes with the Ask Data feature powered by Natural Language Processing to help users uncover insights from their data using a common language. You can ask questions about your BigQuery data in natural language and get instant answers in the form of visualizations without mastering any query language.
4. Cross-database joins
Tableau supports join operations across different data sources, a feature that can help you join your BigQuery data with data from other supported sources. It supports inner join, right outer join, left outer join, and full outer join operations. The data sources involved in join operation must have a common field.
5. Embedded Analytics
Tableau has an embedded analytics feature to enable users to embed their dashboards, visualizations, and analytical capabilities into their products of choice. It requires you to generate a simple HTML code and use it to embed your content into data products, custom web portals, and third-party applications.
6. Alerts/Anomaly Detection
Tableau users can configure conditions in their data to trigger notifications when met. These can be configured on views and dashboards, but not on story points. The alerts can be received within Tableau, on Slack, or by email.
7. Customer Support
Tableau is shipped with a knowledge base full of written materials to help users learn how to use the tool. The materials are grouped into various Tableau products such as Tableau Desktop, Tableau Server, and Tableau Cloud. You can type your question within the knowledge base and Tableau will suggest articles that can answer you as you type.
8. Pricing
Tableau has three pricing plans, Tableau Creator, Tableau Explorer, and Tableau Viewer.
The Tableau Creator plan has all the features needed for end-to-end analytics and it costs $75 per user/month, billed annually. The Tableau Explorer plan enables users to explore their data and answer questions with full self-service analytics. It costs $42 per user/month, billed annually. The Tableau Viewer plan enables users to view and interact with visualizations and it costs $15 per user//month, billed annually.
Benefits of Tableau
Tableau users enjoy the following benefits:
- Seamless integration with BigQuery
Tableau users can easily connect to Google BigQuery and pull their data for analysis. It doesn’t require users to download and install any driver.
2. User authentication
Tableau supports two authentication methods when connecting to Google BigQuery: OAuth and service account (JSON) file. This is good for the security of your BigQuery data.
3. Beautiful visualizations
Tableau users can generate beautiful and powerful visualizations to make sense of their BigQuery data.
4. Fit for non-technical users
Tableau has Natural Language Processing capabilities, allowing users to uncover insights from their data using natural language.
Limitations of Tableau
The following are the limitations of Tableau:
- Few integrations
Tableau doesn’t support native integration with some data sources such as NoSQL databases. You may encounter challenges in joining your BigQuery data with data from such sources.
2. Legacy architecture
Tableau uses a legacy architecture, a reflection of its founding DNA. Users must use Tableau Desktop to publish workbooks to the Tableau Server or Cloud. Although Tableau excels in exploratory data analysis, it requires the data to be structured and ready for analysis.
Knowi
Knowi is an all-in-one data analytics solution that enables data discovery, querying, aggregation, visualization, and reporting automation from Google BigQuery along with other structured and unstructured data sources. Knowi users can connect to BigQuery in two different ways: Directly through its UI or using its Cloud9Agent to securely pull data inside their network. Knowi provides two methods for establishing a connection to BigQuery via a UI-based approach: Connecting by OAuth and connecting by credentials file. To connect by OAuth, you must create a Google BigQuery data source in Knowi and choose OAuth as the authentication type. To connect via the credentials file, you should create a service account in the GCP console with added access permissions and download the credentials file. You can then create a Google BigQuery data source in Knowi and choose “Credentials File’’ as the authentication type. Knowi will then prompt you to upload the credentials JSON file. Knowi users can also choose between direct and non-direct data execution strategies. In direct execution, queries are executed directly on BigQuery, without any storage in between. In non-direct execution, query results are stored in Knowi’s Elastic Store. This has benefits like long-running queries, reduced load on the database, and others.
Knowi Features
Knowi has the following key features for its users:
- Data-as-a-Service
Knowi has a data-as-a-service feature that enables it to work with any data, anywhere. Knowi supports native integration with a wide range of SQL, NoSQL, files, and REST APIs to help users access data securely, and instantly. Knowi users can also source data from multiple sources by blending data from structured and unstructured sources.
Knowi users can connect to their Google Biguery database without installing any driver or moving their data. They can connect directly from Knowi’s UI or download and install its Cloud9Agent to securely pull data inside their network. When connecting from Knowi’s UI, authentication can be done via OAuth or by generating and uploading a
credentials JSON file. Once connected to BigQuery, you can run direct queries without any storage between Knowi and BigQuery or store the query results in Knowi’s Elastic Store to facilitate long-running queries and reduce the load on the database. You can query your BigQuery data using Knowi’s Visual Builder in a no-code environment by selecting, dragging, and dropping fields through a dropdown, or using its versatile Query Editor that supports multiple language modes and advanced editing capabilities such as BigQuery Query.
2. Visualizations
Knowi has a presentation layer sitting on top of its data-as-a-service layer with 40+ visualizations that you can use to present your Google BigQuery data. Users can customize the visualizations to get the desired look and feel. Knowi offers filters and drill-down capabilities to help users extract fine-grained details from their data. Users can also create custom visualizations using JavaScript/CSS to meet their unique needs.
3. Ask Questions of Your Data
Knowi has a search-based analytics feature powered by Natural Language Processing to help you understand your data by asking questions in plain English. You can type questions about your data in plain English within Knowi and get immediate answers in the form of charts and tables.
You can embed this feature into your external applications and enable users to ask data questions directly from these applications and get instant answers.
Knowi has also added this feature in Slack and Microsoft Teams to help users ask data questions directly from these apps and get immediate answers.
4. Multi-Data Source Joins
Knowi supports multi-source joins, enabling you to join your BigQuery data with relational, NoSQL, and APIs on the fly across data centers without moving data or building complex ETL steps. It supports inner join, right outer join, left outer join, full outer join, and loop join operations. The data sources involved must have a common field.
5. Embedded Analytics
Knowi has an embedded analytics feature to enable users to embed dashboards, visualizations, and analytical capabilities into external applications. You can also email reports or share dashboards to enable analytics reporting among offline users.
Users can choose simple URL-based embedding, secure URL embedding with encrypted request payload, or Single SignOn (SSO) API embedding that enables token exchange from your system users to map to Knowi with user rights and permissions.
6. Machine Learning
Knowi is shipped with built-in machine learning algorithms to enable users to combine hindsight and foresight and perform prescriptive and descriptive analytics on their data. You can easily integrate machine learning into your Google BigQuery analysis and workflows. You can use Knowi’s built-in algorithms or integrate your own.
7. Triggers, Alerts, and Actions
Knowi has an alert feature through which you can automate notifications and actions based on your Google BigQuery data. You can use it to monitor changes to your business data and send notifications via Slack, WebHook, or email or configure a webhook to initiate a process in a downstream application.
8. Customer Support
Knowi has released notes to guide users on how to complete various tasks on the platform. It also has a knowledge base with written documentation to help users learn how to use the tool. You can type your question within the knowledge base and get top article suggestions that can answer you as you type.
Knowi has a community section with sourced questions and answers that can help you. You can also contact its support team by submitting a form via its Zendesk-powered chat system and they will get back to you.
Benefits of Knowi
Knowi has the following benefits for its users:
- Effortless integration with BigQuery
Knowi users can effortlessly connect to Google BigQuery and access their data for analysis and visualization. Knowi doesn’t require users to download and install any driver to connect to BigQuery.
2. Data Security
Knowi supports two types of authentication for users connecting to Google BigQuery: OAuth or using a credentials JSON file. This is good for the security of your BigQuery data.
3. Reduced load on the database
Knowi supports non-direct queries, in which query results are stored in its Elastic Store. This can help reduce the load on your BigQuery database since you will not be hitting the database every time you need data.
4. Fit for non-technical users
Knowi has a search-based analytics feature to help non-technical users extract insights from their data by typing questions in plain English.
5. Supports multi-source joins
Knowi supports various join operations. Thus, you can create the largest possible dataset by joining your Google BigQuery data with data from other SQL, NoSQL, and REST API sources.
6. Customizable visualizations
Knowi users can edit their visualizations to improve their appearance. They can also create custom visualizations using CSS/JavaScript to meet their unique needs.
Limitations of Knowi
Knowi users encounter the following challenges:
- Not open source
Knowi is a commercial tool.
2. Out-of-the-box visualizations are not the “prettiest”
Knowi doesn’t have the “prettiest” out-of-the-box visualizations. However, it enables users to customize them using CSS/JavaScript.
3. Sophisticated user interface
Knowi business users have access to an intuitive user interface. However, its user interface for data engineers is complex and may take time to get used to.
Power BI
Power BI is a BI tool created by Microsoft that comes with a BigQuery connector. Power BI users can connect to Bigquery without downloading and installing any driver. The Power BI’s BigQuery connector can be found under the “Get Data” experience after starting Power BI. The connector lets you connect through an organizational account or service account sign-in. For Service Account Sign-in, the connector will require you to provide your service account email and your service account JSON key file contents. You must also provide the details of your Google account. Power BI also allows you to connect to BigQuery through advanced options, in which you’ll have to provide additional connection details. Once connected, a navigator window will display the data available on the server. You can run direct queries against your BigQuery data from Power BI or import the data into Power BI.
Power BI Features
Power BI has the following key features for its users:
- Integrations
Power BI has many software-as-a-service connectors to help users connect to apps, databases, and cloud data warehouses such as Google BigQuery.
Its BigQuery connector requires a Google or a Google service account to sign in to BigQuery. To sign in using a Google service account, you must provide your service account email and your service account JSON key file contents. You will also be prompted to provide the Google account to sign in. After a successful connection, you can run direct queries against BigQuery or import/load your data into Power BI for analysis and visualization.
2. Visualizations
Power BI users can access various visualization types to create reports and dashboards. Examples include doughnut charts, decomposition trees, and waterfall charts. Additional charts can be downloaded from Microsoft AppSource. Power BI users can create custom visualizations and share them with other Power BI users.
3. Ask Questions of Your Data
Power BI has a Q&A feature powered by Natural Language Processing to help you uncover insights from your data using natural language. You can type your question in natural language and Power BI will return answers in the form of visualizations. You can specify the type of visualization you desire in your question or let Q&A select the best visualization depending on the nature of your data.
4. Alerts/Anomaly Detection
You can configure Power BI to notify you when your data changes beyond certain limits. You can configure the frequency with which Power BI will check your data for the alert condition. You can receive the notifications within Power BI’s notification center or via email.
5. Customer Support
Power BI’s support is now part of Microsoft Fabric support. It has written documentation to help users learn how to use the BI tool. You can also contact the support team by creating a support request via Microsoft’s official website and the support team will get back to you. You can also post your question to other Power BI users on the Microsoft Fabric Community forum and they will answer you.
6. Pricing
Power BI has a free plan and two other priced plans, Power BI Pro and Power BI Premium.
The Power BI Pro plan gives users access to self-service analytics features, enabling them to publish and view reports and dashboards. It costs $10 per user/month. The Power BI Premium plan gives users access to enterprise-grade features such as more frequent refreshes, deployment pipelines, and larger model sizes. It costs $20 per user/month.
Benefits of Power BI
Power BI users have access to the following benefits:
- Seamless integration with BigQuery
Power BI offers a seamless integration with Google BigQuery. Users can easily connect to BigQuery without installing any driver.
2. Fit for non-technical users
Power BI supports Natural Language Processing capabilities through its Q&A feature. Non-technical users can uncover insights from their data using natural language.
3. Customizable visualizations
Power BI allows users to edit their visualizations to improve their appearance. They can also create custom visualizations to meet their specific needs.
Limitations of Power BI
The following are the limitations of Power BI:
- Limited integrations
Power BI doesn’t have connectors to all data sources. Users may have to create custom connectors to connect to some data sources.
2. Bulky user interface
Power BI’s user interface is bulky. Users may encounter challenges navigating from one component to another.
QlikView
QlikView is a business intelligence tool with a BigQuery connector. You can access the BigQuery connector by opening the QlikView ODBC connection dialog and choosing “Google BigQuery” from the list of drivers in the connectors list. The connector requires you to know the server’s name and the database to connect. You must also provide your BigQuery credentials. After connecting to your Google BigQuery database, QlikView lets you select data from the available tables and load it into your document. The data can be loaded using the Edit Script dialog.
QlikView Features
QlikView users have access to the following features:
- Integrations
QlikView users can use the hundreds of connectors to healthcare, finance, data analytics, CRM, and other applications provided by Qlik in its connector factory to connect to the data sources of choice. It supports native integration to cloud data warehouses such as Google BigQuery and Amazon Redshift. QlikView also supports integration with NoSQL data sources such as MongoDB, ElasticSearch, and Apache Cassandra. Qlik boasts of its ability to rapidly create new connectors to help users integrate data from sources of interest.
Qlik users can access the BigQuery connector by opening the Qlik ODBC connection dialog. The connector requires you to know the server and database names to connect to. You must also provide your BigQuery credentials.
Once connected to BigQuery, QlikView lets you load data into your document using the Edit Script dialog.
2. Visualizations
QlikView users have access to a wide variety of visualizations for presenting data. Examples include gauges, pie charts, bar charts, tables, and treemaps. Users can customize the visualizations even after adding them to their sheets. The visualizations are interactive, enabling users to select, search, drill down, and zoom to find specific answers.
3. Search and conversational analytics
QlikView users can use Qlik’s search and conversational analytics feature to extract insights from their data using natural language. Qlik has an AI assistant powered by Natural Language Processing and can understand 10 languages.
4. Embedded Analytics
QlikView users can embed their visualizations and analytics into external products and cloud applications such as JIRA and Salesforce. They can do this using the no-code and pro-code options provided by Qlik.
5. Alerts/Anomaly Detection
QlikView users can configure alerts to check their data in various applications. It can check the data on a scheduled basis or when an application reload completes. Users can receive the alert notifications via email or mobile.
6. Machine Learning
QlikView users can use Qlik’s AutoML feature to create machine learning experiments and train models. AutoML can identify patterns in your data and use them to make predictions. You can select and deploy the best-performing models by scoring and ranking.
7. Customer Support
QlikView users can find help on Qlik’s official website in the form of videos and written documentation. They can also post their questions on the Qlik community forum and get answers from other QlikView users.
8. Pricing
QlikView has three pricing plans namely Standard, Premium, and Enterprise.
The Standard plan helps small teams perform basic data analytics and it costs $825month. The Premium plan enables you to operationalize analytics across your organization with premium capabilities and it costs $2,700month. The Enterprise plan gives your organization maximum flexibility and scalability with enterprise capabilities. Its pricing is custom-based.
Benefits of QlikView
QlikView offers the following benefits for its users:
- Integrates with BigQuery
QlikView supports native integration with Google BigQuery without requiring users to install any third-party driver.
2. Fit for non-technical users
QlikView has Natural Language Processing capabilities, allowing non-technical users to understand their data using natural language.
3. Customizable visualizations
QlikView visualizations are customizable, enabling users to edit them to meet their desired look and feel.
4. Fit for advanced analytics
QlikView users can build machine learning models from their data for advanced analytics.
Limitations of QlikView
QlikView users face the following challenges:
- Integrations are not extensible
QlikView doesn’t support integration with all data sources. It doesn’t allow users to create custom connectors to data sources of choice, but they must wait for the Qlik team to build new connectors.
2. Over-dependence on Qlik
QlikView doesn’t stand as a BI tool on its own. It depends on Qlik to provide features such as data source integrations, machine learning, and others.
Looker
Looker is a cloud-based business intelligence platform with a BigQuery connector that enables you to access data from your BigQuery tables within Looker Studio. Looker Studio can be connected to a single BigQuery table or view, or a custom query. You can connect to your data by creating a new embedded data source or selecting an existing (reusable) data source. Data sources created while editing a report are embedded within the report. Data sources created within the home page are reusable, meaning they can be reused in different reports. You can connect to BigQuery by signing in to your Looker Studio account and clicking “Create”. Select “Report” to open the report editor and the “Add data to report” panel. Select the BigQuery connector when prompted to select a connector. Looker will prompt you to configure the connection to your dataset. This involves choosing a BigQuery project to connect to, dataset, table, or creating a custom query.
Looker Features
Looker has the following features for its users:
- Integrations
Looker supports integration with SQL-based databases such as MySQL and cloud data warehouses such as Google BigQuery. However, Looker doesn’t have connectors to NoSQL data sources. It requires users to move their data into a relational structure to query against.
Looker’s BigQuery connector allows you to connect to a single BigQuery table, view, or custom query. You can access the connector by signing in to Looker and clicking the “Create” button. The BigQuery connector can be found in the “Add data to report” panel. The connector will require you to configure the connection details to your target data source, after which it will load a table into your report canvas. You can also choose the custom query option to create an SQL query rather than connecting to a single table.
2. Visualizations
Looker has different visualizations that you can use to present your BigQuery data. Examples include bar, area, radar, and gauge charts. Users can customize the visualizations to change their appearance.
3. Analytics
Looker has Looker Blocks, which are pre-built data models for the common data sources and analytical patterns. They facilitate quick data analysis in Looker by helping users reuse the work done by others instead of starting from scratch. The blocks can be customized to meet specific needs.
4. Embedded Analytics
Looker comes with an embedded analytics solution called Looker Embedded that enables users to embed their dashboards, visualizations, and analytical capabilities into their applications. It requires you to generate an iframe and use it to embed content in HTML-formatted web pages, portals, and applications.
5. Alerts/Anomaly Detection
Looker users can configure alerts to receive notifications when their query results meet certain conditions. They can set the frequency with which Looker will check the data for the alert conditions. The notifications can be sent via email or Slack.
6. Customer Support
Looker users can get help from Looker Studio’s help page. It has help topics to help users learn how to use the BI platform. You can also type your question within the help page and get article suggestions that can answer you as you type.
Looker Studio also has a community forum where you can type your questions and get answers from other Looker users.
7. Pricing
Looker has divided its pricing plans into two: user pricing and platform pricing. User pricing is the cost of licensing individual users to access the Looker platform, and it depends on the type of user and their permissions within Looker. Platform pricing is the cost of running a Looker (Google Cloud Core) instance. There are three editions for Looker User pricing, Developer User, Standard User, and Viewer User, and their pricing is custom-based. There are also three Platform pricing plans, Standard, Enterprise, and Embed and they have a custom-based pricing approach.
Benefits of Looker
Looker users enjoy the following benefits:
- Seamless integration with BigQuery
Looker offers a seamless integration with BigQuery, allowing users to access their data without installing any driver.
2. Customizable visualizations
Looker enables users to customize their visualizations to meet their specific needs.
3. Multi-Cloud Friendly
Looker is a multi-cloud-friendly BI tool. This gives its users flexibility in choosing where to deploy it and the underlying databases.
Limitations of Looker
Looker comes with the following limitations:
- Lacks connectors to NoSQL data sources
Looker doesn’t come with connectors to NoSQL data sources. It requires users to move the data into a relational structure for it to query against.
2. It may require a learning curve
Looker users may have to master LookML, its proprietary, markup language, to perform some tasks on the platform. This may require a learning curve.
3. Limited US customer support
Looker’s acquisition by Google has seen the BI tool go through many changes, including scaling down its US customer support team. Looker has also confused its users with its naming (Looker Studio).
Final Thoughts
BigQuery is a comprehensive platform for data warehousing, analytics, and machine learning. It is a suitable platform for large-scale data analysis, enabling users to run petabyte-scale queries quickly. Choosing the right business intelligence tool to pair with your BigQuery infrastructure can help your team generate error-free insights from data.
Tableau, Knowi, Power BI, QlikView, and Looker are some of the best BigQuery reporting tools.
Tableau is a powerful data analytics tool that comes with a Google BigQuery connector. You can find the connector under the “Connect” tab. It requires you to provide your BigQuery email/phone and password. The connector supports authentication through an OAuth or service account (JSON) file.
Knowi is a unified data analytics platform that enables data discovery, visualization, analysis, and reporting automation from BigQuery. Knowi users can connect to BigQuery directly from its UI or use its Cloud9Agent to pull data inside their network. When connecting via Knowi’s UI, authentication can be done via OAuth or a credentials (JSON) file. Knowi users can run direct queries against BigQuery, without any storage in-between, or run non-direct queries by storing query results in Knowi’s Elastic Store to facilitate long-running queries and reduce the load on Bigquery.
Power BI is a BI tool created by Microsoft and it has a Google BigQuery connector. The connector allows you to connect to BigQuery via an organizational account or service account sign-in. It also requires you to have a Google account. Once connected to BigQuery, Power BI users can run direct queries against BigQuery or import the data into Power BI.
QlikView is a BI platform with a Google BigQuery connector. The connector requires you to provide the name of the server and the database to connect to, and your Google BigQuery credentials. Once connected, QlikView lets you load your BigQuery data into your document.
Looker is a cloud-based BI tool with a BigQuery connector that lets you access your BigQuery data within Looker Studio. You can connect Looker Studio to a single BigQuery table, view, or create a custom query. The connector will prompt you to configure the details of the connection, that is, choosing the target BigQuery project, dataset, table, or creating a custom query.
