How to build a BI dashboard using Google Data Studio and BigQuery

Building BI dashboards on GCP is easy with this simple solution.

For as long as business intelligence (BI) has been around, visualization tools have played an important role in helping analysts and decision-makers quickly get insights from data.

In our current era of big data analytics, that premise still holds. To provide an integrated platform for building BI dashboards on top of big data, GCP offers the combination of Google BigQuery, a cloud-native data warehouse that helps you analyze petabytes of data quickly, and Google Data Studio, a free tool that helps you quickly create beautiful reports. (Google partners such as Looker, Tableau and Zoomdata also provide paid visualization tools on top of BigQuery, but that’s a topic for a different time.)

In this post, you’ll learn how to build a BI dashboard with Data Studio as the front end, and powered by BigQuery on the back end (with some help from Google App Engine for added efficiency). It assumes some familiarity with those products; for more information, review the background docs (BigQuery concepts, Data Studio overview, App Engine concepts).

Imagine that you work for an online retailer that bases important decisions on customer interaction data in the form of large amounts (multiple TBs) of usage logs stored as date-partitioned tables in a BigQuery dataset called usage. To get business value out of that data as quickly as possible, you want to build a dashboard for analysts that will provide visualizations of trends and patterns in your data.

The good news is that a connector is available that lets you query data stored in BigQuery directly from Data Studio. Even though Data Studio caches the results, if the dashboard editor refreshes the dashboard or a user filters dashboard results that are not available in cache, another query will run. In our example, this situation could be expensive, as the usage dataset stores granular logs and thus will end up querying multiple TBs of data.

Fortunately, this solution works around that issue. Typically, a dashboard shows an aggregated view of usage —it doesn’t need details all the way to the level of an order ID for instance. So here, to reduce query costs, you’ll first aggregate usage logs into another dataset called reports. Then, you’ll query the reports dataset from the Data Studio dashboard. This way, when your dashboard is refreshed, the reporting dataset queries process less data. Plus, since usage logs from the past never change, you’ll only refresh new usage data into the reports dataset.

Next, you’ll learn how to build this solution step by step.

Using the BigQuery Web UI, create a new dataset called reports in the same project as the usage dataset. A separate reports dataset has a couple of benefits. It reduces the amount of data queried by the dashboard. And, cuts unnecessary access to your source datasets, by users who are only interested in aggregated data. Once you figure out what usage metrics you want to display on the dashboard, create the respective tables in the reporting dataset. Use the BigQuery Web UI to create the tables.

For e.g., we created reports.orders table, which stores aggregate orders by date and location.

After creating the dataset and table, run a one-time query that will get the aggregate usage from usage logs dataset and write it into the reports dataset table. For example, the query below will get total orders completed each day for the last 180 days by location.