Using Google BigQuery with JasperReports Server

Introduction

Google BigQuery was designed as a "cloud-native" data warehouse and was basically built to address the needs of data-driven organizations in a cloud-first world.

BigQuery is Google Cloud Platform’s serverless, highly scalable, and cost-effective cloud data warehouse where you can query and process vast amounts of data using the processing power of Google’s infrastructure. Being serverless means all your data operates on a cloud platform and this serverless architecture, allows users to scale the analytics automatically. It organizes data tables into units called datasets which are collections of “related” tables/views together with labels and descriptions.

As data is essential to every business and losing it would be a setback for the business. With BigQuery, your information is automatically replicated and stored, so you don’t lose it. BigQuery keeps a seven-day history of changes. This feature allows you to restore previous data and compare your data at different times. It’s a great way to keep track of your data and see changes.

Also, as there’s no infrastructure for customers to manage, they can focus on uncovering meaningful insights using familiar SQL without the need for a database administrator. It’s also economical because they pay only for the processing and storage they use.

Working with Google BigQuery

Accessing Google BigQuery

In order to access you need to open console.cloud.google.com – which leads to a GCP window being displayed and one must have a Google account for this.

Once the GCP dashboard is loaded, Use the Search tab to search for BigQuery and this would redirect you to the BigQuery query editor window as shown below:

Accessing Public datasets from Google Developer Console

Google Cloud Platform provides an existing ready-to-use repository of public datasets that can be used by the users.

A public dataset is any dataset that is stored in BigQuery and made available to the general public through the Google Cloud Public Dataset Program. The public datasets are datasets that BigQuery hosts for the user to access and integrate into applications.

Log into Google cloud platform
Navigate to the Google BigQuery Dashboard
Go to the left section of the window where you’ll find an ‘Add Data’ option – here, select ‘Explore public datasets’ as shown below:
User can view, copy the available dataset to the required project id which also be further reflected in the JasperReports server data source.
User can select the required datasets and once selected it will get added under the project name ‘bigquery-public-data’ in the main editor window which can then be further added to their own dataset and can be used in Jasperserver e.g COVID-19 Public Datasets.

Google BigQuery and JasperReports Server

Illustrative Story: Covid-19 Statistics

Example:

A Company related to the medical field wants to process a large amount of Covid-19 related public data. GBQ would be feeding aggregated, anonymized data for a broad set of health symptoms, signs, and conditions.

The data provides weekly time series for each region letting the company know the relative number of cases for each symptom e.g. “symptoms_chest_pain, symptoms_Cough, symptoms_Dizziness, symptoms_Hypertension, symptions_kidney_failure” etc.

The Company requires the data of the symptoms for specific regions and certain symptom threshold which would help it to make better and efficient decisions and enable it to adjust course amid the daily whiplash of COVID-19 challenges and to prepare more effectively for the future.

And as it’s not surprising that analytics which is widely recognized for its problem-solving and predictive prowess, has become an essential navigational tool.

JasperReports Server is an ideal solution, as its integration with the GBQ data allows the user to visualize the metrics, build custom reports and dashboards using AdHoc designers and viewers. Users can easily get valuable insights from their data using Dashboards and Interactive Reports, and at the same time, such reports will provide rich, experience with drill-down, filtering, animated charting, and much more.

How to visualize Google BigQuery Data using JasperReports Server

The JasperServer data source wizard uses the Google Developer console credentials that you provide to discover your Google Big Query databases. It then uses those credentials to properly configure access rules to maintain the connection between JasperReports Server and the data source

Create Google BigQuery Data source

Log into JasperReports Server as an administrator.
Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data Source from the context menu. Alternatively, you can select Create > Data Source from the main menu on any page and specify a folder location later.
In the Type field, select JDBC Data Source. The information on the page changes to reflect what's needed to define an GBQ data source.
Fill in the required fields:
1. Provide project ID as per the developer console
2. Provide the dataset ID which has the required table data
3. Provide a valid Google Service Account Email Address
4. Private Key: Provide the repository path for the downloaded private key for the above used Google Service Account.
  (Refer Appendix: Uploading the .json file to repository as secure file)
  Note: Currently the only supported private key format is .json
5. Both Username and Password fields are optional.
6. If the date-time values stored in your database do not indicate a time zone, set the Time Zone field. When in doubt, leave the default Time Zone value (Use database setting).
When date-time values are stored in a format other than local time zone offset relative to Greenwich Mean time (GMT), you must specify a time zone so that the server can properly convert date-time values read from the target database. Set the Time Zone field to the correct time zone for the data in the database.
When you've entered all the information, click Test Connection.
If your connection is successful, a message appears to the right of the button. Sometimes the process takes a few minutes.
When done, click Save. The Save dialog appears.
Enter a name for the data source and an optional description. The Resource ID is generated from the name you enter. If you haven't already specified a location, expand the folder tree and select the location for your data source.
Click Save in the dialog. The data source appears in the repository.

Creating Adhoc View based on the Covid-19 public dataset from GBQ

Navigate to Create -> Domain. Select the earlier created GBQ data source which has the dataset containing the Covid-19 data

Create domain via derived table by using the query

Select * from asabherwal_dataset.symptom_search_country_weekly where country_region_code in ('SG','GB','AU','IE','US','NZ')

Navigate to Create -> Adhoc and create Adhoc using the created domain. User can create an interactive pie charts to visualize the metrics for both total count across the hospitals in selected regions as well as see the country-wise distribution of symptoms as depicted below and can further use the adhoc to generate custom reports as well as dashboards.

Creating Dashboard with views

Using these views you can build some nice dashboards, for example

The visual metrics can further be used by the company in targeting support services to required zones, identifying potential disruptions and determining the effectiveness of crisis intervention strategies, to name a few.

Appendix: Uploading the .json file to repository as secure file

Log into JasperReports Server
Click View > Repository, expand the folder tree, and right-click a folder to select Add Resource > Data > File > Secure File
Select Type as Secure File
Select the path to the private key downloaded from Google Developer Console for your respective service account to be used.
Provide Name and Resource Id for the Secure file
Use the default location or provide any other save location.
Click Submit