Getting Started with Cube Cloud: Connecting a Headless BI Tool with Google BigQuery

I learned about the existence of Cube Cloud. It seems to be an open-source headless BI tool.

https://cube.dev/

First, I'll try it out. There seem to be two ways to use it:

1. Cube Cloud, which runs on a server hosted by Cube
2. Cube, which you host on your own server

This time, I will try Cube Cloud.

Features of Cube

While I'm still in the investigation stage, it seems to have the following advantages:

• Analytical data in Athena or BigQuery can be extracted with a consistent writing style by following Cube's model.
• Extracted data can be cached using Cube's mechanism.
• Extracted data can be used as an API from frontends and BI tools.

In situations where multiple analytical data sources are used, the impression is that queries differ by service and maintenance becomes difficult. I can understand the goal of trying to resolve that.

Cube: API-First Business Intelligence

Goal

Since I'm not very familiar with tools in this area, I'm not sure how much of an advantage Cube has over other BI tools. For now, I'll just try using it.

In this article, the goal is to use the BigQuery public dataset "COVID-19 Public Dataset" to create a graph in Cube Cloud. I'm pretty much just following this blog post, but I'll record the process here for my own reference.

https://cube.dev/blog/google-bigquery-public-datasets

Registering for Cube Cloud

You can register from this page. You can use either a GitHub or Google account.

Creating a Deployment

You will be taken to a screen like this. It seems you need to specify a cloud platform. Apparently, there is a feature to set it up within a VPC (which seems like an Enterprise plan feature), so that might be why.

You can name the deployment anything, but I'll go with something like test-covid19. I selected Google Cloud as the deployment destination. Unfortunately, it seems that the Japan region cannot be selected as a deployment destination. I chose Iowa.

Next, you will see a screen asking how you want to create your Cube.js project. This time, since I'm starting from scratch and trying it in the browser, I selected + Create.

For the database, select BigQuery.

Enter your Google Cloud Project ID, and you will be asked for a key file to access BigQuery.

It's best to create a dedicated service account for Cube Cloud on the Google Cloud side. At this time, the following roles are required:

• BigQuery Data Viewer
• BigQuery Job User

Create a service account in Google Cloud with an image like this, and generate a JSON key file.

After uploading the key file, click Apply. Once the connection is successful, you will be asked for the target dataset on the Generate Data Schema screen. However, since we are working with public data this time, select Skip & Deploy. Then, wait for a while.

Editing the Schema

Once the deployment is finished, you can edit the Schema. It seems that you can interact with BigQuery via the Cube.js server by preparing a JavaScript file and executing the cube() function there. First, click Enter Development Mode at the top of the screen to enter the editing mode. Next, create a Measures.js file under the schema/ directory. The content is as follows, quoted from the blog.

schema/Measures.js

cube(`Measures`, {
  sql: `
    SELECT *
    FROM \`bigquery-public-data.covid19_govt_response.oxford_policy_tracker\`
  `,

  measures: {
    confirmed_cases: {
      sql: `confirmed_cases`,
      type: `max`
    },

    cancelPublicEvents: {
      sql: `CAST(cancel_public_events AS NUMERIC)`,
      type: `max`
    },
  },

  dimensions: {
    country: {
      sql: `country_name`,
      type: `string`
    },

    date: {
      sql: `TIMESTAMP(${Measures}.date)`,
      type: `time`
    },
  },
});

To be honest, I don't fully understand the DSL yet, but for now, I'll follow the blog post. It should look something like this. Click Save All in the upper right corner of the screen to save it.

Trying it out in the Playground

Click Playground in the left pane. You can test the Measures.js you just created. You should see a screen that looks like a query selector, so please select the following options:

• Measures: Confirmed Cases
• Time: All time by Day
• Filters: Country equals Japan

The Daily cumulative positive cases in Japan should appear.

The cumulative number of infected cases as of September 8, 2021, is 1,617,099, which seems to roughly match the infection data from NHK.

Trying the basics of Cube Cloud

First, I tried using Cube Cloud in conjunction with BigQuery. I've only tested a small part of it. I'll continue my investigation. Specifically, I'm interested in the following points:

• What kind of data can be called via the API?
• How to handle authentication when calling the API from a browser or other servers?
• I want to try deploying and using it on Cloud Run instead of Cube Cloud.

References

https://cube.dev/blog/google-bigquery-public-datasets#how-to-define-a-data-schema