Creating Custom Visualisation on Looker using D3.js

Let’s talk about D3.js — a superstar JavaScript library that transforms your data into live, interactive, and downright fun visualisations! 🌟 By integrating custom visualisation into Looker we can offer a variety of benefits for businesses. Looker provides a rich set of tools for data exploration and visualisation out of the box, D3.js can further enhance these capabilities based on your requirements.

1. Personalised Visualisations:

• With D3 you can design visualisations specifically tailored to your business needs or to highlight specific insights that standard charts might not capture effectively.

2. Interactive Dashboards:

• D3.js excels at creating interactive visualisations. By integrating it into Looker, businesses can have dashboards where users can interact with the data, drill down into specifics, or even have visualisations that change based on user input.

Prerequisites:

• Looker Instance
• Looker Account with Admin rights
• Basic Knowledge of JavaScript
• For this guide, we will be using this Repository

Prepare your Data:

• Head to Bigquery in your Cloud console
• Create a Dataset in Bigquery
• In the dataset you have created, create a table and name it employee using the avro file here

Create Service Account and Key:

• To set up connection between Looker and BigQuery, you need to create a service account and export your JSON key under IAM & Admin in your cloud console.
• Simply click on “Create new key”, once created, the JSON key will be automatically downloaded, please keep this key for later use.

Writing your D3.js code

Getting started with D3 with some of the video resources here

• D3 Tutorial — Basics
• D3 Full Guide

Testing your D3 visualisation on the Looker Custom Visualisation Builder. This platform showcases the rendered version of your visualisation code.

Refer to the code here. This visualisation is a grouped horizontal bar chart with the following characteristics:

• Bars represent employment statuses for different job levels: current headcount, new hires, and leavers.
• Bars are colour-coded
• Green for new hires
• Blue for headcount of existing employees
• Red for leavers
• The length of each bar is proportional to its count as a fraction of the sum of headcount, new hires, and leavers.
• Accompanying each bar is a label that indicates its percentage of the total.
• There are also categorical labels for the job levels and the categories New Hires, Headcount, and Leavers.

Creating Connection on Looker:

• Once your Bigquery table and Key is created we can create a connection to Looker. This connection is essential for querying, visualising data, and generating a LookML template.
• You need to select the admin panel in the top left corner and then click on connection.

Under the connection page, click on add connection.

To set up the connection:

• Enter your “Connection name”.
• For Dialect, choose “Google BigQuery Standard SQL”.
• Input your specific “Project ID”.
• Specify the dataset you previously created.
• Upload the key you generated and downloaded earlier.

Creating your Model:

• Select the develop panel in the top left corner and then click on Projects.

• Click on “New Project”.
• Enter your desired “Project Name”.
• Select “Generate Model from Database Schema”. This option will allow Looker to create a model and view based on your database connection.
• Select the “employee” connection that we set up previously.
• Choose “All Tables” to include every table available in the connection.
• Finally, click on “Create Project”.

Below is the sample of model and view created

• View Parameter: The view parameter, along with its underlying attributes, delineates a data table to be utilised in a Looker model.
• Model File: The model file designates a database connection and determines the collection of Explores that are associated with that connection. A model file elaborates the Explores and their interrelations with other views. An Explore serves as an initiation point for querying your data. In SQL terminology, an Explore equates to the FROM clause of a query. The Explores established in the model become visible to your users in Looker’s Explore menu when they navigate through Looker.
• You can check out Types of files in a LookML project to find out more

To integrate your custom visualisation into the project, you need to add in your custom visualisation written in javascript and a manifest file.

• You can get a template of the manifest file here.
• Change the project name to the name of your current project of your manifest.
• Define vis_label with the name that you want to assign to your custom visualisation. This will be the name displayed on the explore page in Looker when users are selecting visualisations.
• Allocate a unique vis_id to your custom visualization. This ID serves to differentiate your custom visualisation from others.
• Specify the file name of your custom visualisation script in the required field

• Select the Explore panel in the top left corner, search for your project and then click on your explore that you have declared in your model.

• Select the dimensions you want to display on your visualisation, for this example below we will need to adjust the sql statement because some of the data aggregations are done at the custom visualisation
• To adjust the sql statement click on Open in SQL Runner

• Modify the query as shown in the bottom panel of the image below
• Navigate to the selection dropdown and select the visualisation with the name you have declared as viz_label in your manifest file
• Click on run on the top right hand-side to run your query
• Tadaa, your custom visualisation has been successfully integrated in Looker

Congratulations on creating your custom visualisation and hosting on Looker

• Hosting your code directly within your Looker project is just one method to integrating your custom visualisations. Another approach is through adding a visualisation on your Admin Panel. You can refer to this documentation below.
• Official Documentation to add Visualisation on Admin Panel
• For more information on Looker and custom visualisation you can also check out the links below
• Community on Custom Viz Intallation
• Getting started with Custom Viz

Credits

Daniel Clarke — Solution Architect @ Google

Yeong Eun — Partner Engineer @ Google