Google BI Engine Statistics Dashboard

Google BI Engine is now generally available, and is already used by a lot of DoiT customers. Customers use BI Engine with Looker, but also on other BI Tools such as Tableau. We at DoiT, use BI Engine for the Advanced Cloud Cost Analytics platform to deliver sub second queries to our customers.

Yet, when it comes to monitoring BI engine workloads, there is still some work to be done.

In the Cloud Monitoring Metric Explorer, you can see the Utilization of the BI engine reservation as well as the total Size of the BI engine reservation.

Yet, this does not give insights about how many queries have been accelerated via BI Engine and more importantly, why others did not get accelerated at all. There is also no dashboard within BI Engine or Cloud console to give you some statistics over what went well and what does not.

At the time of writing, this information can only be queried from the Big Query Information Schema, specifically from the JOBS_BY_PROJECT table or via the Command Line (bq show — format=prettyjson -j job_id).

However getting some valuable insights needs some deeper understanding of the data as well as how BI engine works under the hood.

Therefore we have created an easy to use Data Studio Dashboard to monitor BI Engine workloads.

The query we used in this dashboard can be found here. [3]

Getting started

Prerequisites

To create the dashboard and query the INFORMATION_SCHEMA tables a user must have access to the following INFORMATION_SCHEMA table:

INFORMATION_SCHEMA.JOBS_BY_PROJECT

Datasource

1.1 Copy this datasource
Log in to Data Studio and create a copy of the following data source. More information on copying data sources can be found here.

1.2 Set the correct GCP Billing project
Set this to the project, where you have made the BI Engine reservation

1.3 Change the data sources:
We used region-eu in our example, but you can change this to a region of your choice (where your BI Engine capacity is reserved). Use the following format to specify regionality for the project-id, region, and views in the INFORMATION_SCHEMA view:

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.VIEW

Dashboard

2.1 Copy the dashboard
Create a copy of the public dashboard. You will be asked to select a new Datasource, you have to select the one you copied in step 1. Click on create report and rename it as desired.

2.2 Change the dashboard
Once the report is copied and all the data is rendered, change any date pickers in the report pages to use the period you desire (ex: last week, last 14 days, last 28 days, etc).