How to implement Drill Anywhere in React/ChartJS/RestBI

It's inevitable that your end users will want more depth from whatever reporting you give them in a web application.

Most people solve this by increasing the sheer number of reports they offer. This is a mostly futile effort. The more irrelevant visualizations you show, the harder it is for the user to find the one that actually answers their question.

Given the choice I always opt for interactivity over breadth. Start simple and let users dive in as needed.

In this spirit, I wanted to write an article about my favorite interactivity mechanism - what is typically called “drill anywhere", with a focus on a newer tool RestBI that makes the data manipulation component easier.

What is Drill Anywhere

Drill Anywhere is a powerful feature found in many business intelligence tools today. It allows you to click on a specific point within a visualization, and drill into that data element. Unlike a drill-down that follows a set hierarchical path through the data, drill anywhere allows you to choose from a large list of other columns, and pick any to dive into.

When set up properly, this can be done an almost unlimited amount of times. Allowing users to start at a very high level aggregate, say “sales by region”, and work their way all the way down to the individual transaction.

How it works

With the proper data model this feature can be surprisingly easy to implement. In this case “proper” generally means a relational data model. One or two central fact tables, surrounded by a series of supporting dimensions. While it is possible to implement this on flat tables, or other structures, you will get the most flexibility out of your data going with this approach.

A visualization that enables drill anywhere typically starts with one Dimensional variable (a grouping column like “Region” or “Store” or “Product ID”), and one metric (an aggregated number like “total sales”, “average profit”)

In pseudo-SQL this chart might be fed by the following query:

SELECT region, sum(sales) FROM sales_fact JOIN region_dim
GROUP BY region

When a user clicks on the chart, they are clicking on a specific region. We then present them with a list of other Dimensional variables:

State, Store, Category, Sub-Category, Product, SKU, etc

Whatever the user selects becomes the new grouping variable. The old grouping variable is added to the where clause.

For example, if the users were to select the “east” region, and Store, the SQL would become:

SELECT store, sum(sales) FROM sales_fact JOIN region_dim, store_dim
WHERE region = ‘east’
GROUP BY store

Let’s see it in action.

Libraries We’re Using

For this example we are using few specific tools:

React: Front-end framework
Chart.js: Charting library
RestBI: A light BI / analytic query layer
Material UI: UI components (drill anywhere popup)

The code below is intended to highlight the core elements of enabling Drill Anywhere. The full code can be found here.

Setting up the Data Model in RestBI

Most charting examples assume you got the data easily, and kind of ignore it in the instructions. In this case the data is the whole point, so it's worth highlighting how we got here.

If you have relatively structured data, RestBI is a great way to simplify it for analytics. Rather than write custom SQL and worry about tables/joins, we can query the data with a simple syntax (an ORM for BI?, idk.).

To use RestBI we need to first define our data model. This acts kind of like the metadata layer that exists in many BI tools, except it is all JSON so it lives in our code.

Here is a simple example:

import { Connection, DatabaseType, Table, ColumnDataType, Join, Model, Formula, ColumnType } from "restbi-sdk";

// Define the PostgreSQL connection. 
const PostgresConnection: Connection = {
    id: '1',
    name: 'Postgres',
    host: 'host.docker.internal',
    port: 5433,
    user: 'postgres',
    password: 'test',
    database: 'adventureworks',
    type: DatabaseType.POSTGRES,
};

// Define the Tables with display names
const salesOrderHeaderTable: Table = {
    id: '1',
    dbName: 'SalesOrderHeader',
    schema: 'sales',
    name: 'Sales Order Header',
    columns: [
        { id: '1', dbName: 'SalesOrderID', dataType: ColumnDataType.NUMBER, name: 'Sales Order ID', type: ColumnType.DIMENSION },
        { id: '2', dbName: 'OrderDate', dataType: ColumnDataType.DATE, name: 'Order Date' },
        { id: '3', dbName: 'CustomerID', dataType: ColumnDataType.NUMBER, name: 'Customer ID' },
        { id: '4', dbName: 'TotalDue', dataType: ColumnDataType.NUMBER, name: 'Total Due' }
    ]
};
// Additional tables (Customer, Product SalesOrderDetail, etc.)
const productTable: Table = { /* ... */ };
const customerTable: Table = { /* ... */ };
const salesOrderDetailTable: Table = { /* ... */ };
const productCategoryTable: Table = { /* ... */ };
const productSubcategoryTable: Table = { /* ... */ };
const invoiceTable: Table = { /* ... */ };

// Define a simplified join
const updatedJoins: Join[] = [
    {
        id: '1',
        table1: 'SalesOrderHeader',
        table2: 'Customer',
        clauses: [{
            column1: 'CustomerID',
            column2: 'CustomerID',
            operator: '='
        }]
    },
    // Additional joins go here
];

// Define formulas
const formulas: Formula[] = [
    { id: "1", name: 'Formula Bikes', expression: "SUM(CASE WHEN {{Category Name}} = 'Bikes' THEN {{Total Due}} ELSE NULL END)" },
    { id: "2", name: 'Average Sales', expression: "AVG({{Total Due}})" },
    { id: "3", name: 'Year', expression: "YEAR({{Order Date}})" },
];

// Define the updated Model
export const AdventureWorksModel: Model = {
    id: '1',
    name: 'AdventureWorksModel',
    displayName: 'Adventure Works Model',
    connection: PostgresConnection,
    tables: [
        salesOrderHeaderTable,
        productTable,
        customerTable,
        /* ... additional tables */
    ],
    joins: updatedJoins,
    formulas: formulas,
    filters: []
};

Setting Up the Chart

For the chart, we used a simple react ChartJS bar chart. To obtain the dataset, we construct a RestBI query that refers to the currently selected Dimension column, Metric column, and any active Filters (this will be populated as we begin to drill). This is set to execute each time the user selects a new column, or drills:

import React, { useState, useEffect } from "react";
import { Bar } from "react-chartjs-2";
import { RestBIClient, SQLResult, Model, Query, inferColumnType } from "restbi-sdk";
import { FormControl, InputLabel, MenuItem, Select } from "@mui/
material";
import {AdventureWorksModel} from './models'

//RestBI client
const client = new RestBIClient();
//Use the data model generated previously
const dataModel = AdventureWorksModel;

const DrillAnywhereChart = ({ client, dataModel}) => {
  const [chartData, setChartData] = useState<SQLResult | null>(null);
  const [selectedDimension, setSelectedDimension] = useState<string>("");
  const [selectedMetric, setSelectedMetric] = useState<string>("");
  const [drillAnywherePopupVisible, setDrillAnywherePopupVisible] = useState<boolean>(false);
  const [drillAnywhereValue, setDrillAnywhereValue] = useState<string>("");
  const [filters, setFilters] = useState<QueryFilter[]>([]);


 //If either of the columns are updated, re-run the query
  useEffect(() => {
    const query: Query = {
      columns: [selectedDimension, selectedMetric], //initially provided by user updated by drill
      filters: filters,
      limit: 25, //for display purposes we use top 25 and set to sort descending
      sortBy: { name: selectedMetric, direction: "DESC" },
    };

    client.executeQuery(query, dataModel).then((data) => setChartData(data));
  }, [selectedDimension, selectedMetric]);

  //ChartJS Bar Chart config options
  const options = {
      onClick: handleChartClick
      // other chart  config options
  }

  //Format RestBI result to populate chart
  const data = chartData
    ? {
        labels: chartData.rows.map((item) => item[selectedDimension]),
        datasets: [
          {
            label: selectedMetric,
            data: chartData.rows.map((item) => item[selectedMetric]),
            backgroundColor: "#53D9D9",
          },
        ],
      }
    : { labels: [], datasets: [] };

  return <Bar options={options} data={data} />;
};

export default DrillAnywhere;

Note: to keep it dataset agnostic we have created two state variables for the selectedDimension and selectedMetric. To make this even more dynamic we can let the user specify these values

Let the User Pick the Starting Chart Columns

The code below highlights how we can create a simple Material UI select menu, with a list of all of the columns (in this case Dimensions) that feeds nicely into the query defined above.

<FormControl variant="outlined" style={{ minWidth: 200, marginRight: 20 }}>
  <InputLabel>Dimension</InputLabel>
  <Select
    value={selectedDimension}
    onChange={(e) => {
       //update the chart so the dimensional value is the column the user selected
       setSelectedDimension(e.target.value as string)
    }}
    label="Dimension"
  >
    {dataModel && dataModel.tables //loop through the tables in our Model
        .flatMap((table: Table) =>
        //for each column in the table, check if its a dimension and add to list
        table.columns.map((column: Column) => {
            if (inferColumnType(column) === ColumnType.DIMENSION) {
                return (<MenuItem key={column.name} value={column.name}>
                    {column.name}
                </MenuItem>);
            }
            return null;
        })
    )}
  </Select>
</FormControl>

Handle the Chart Click event

To allow the user to drill down, we need to handle click events on the chart. We need to figure out what value the user clicked on, then trigger the popup to prompt the user to select the next dimension.

const handleChartClick = (e, elements) => {
  if (elements.length > 0) {
    const elementIndex = elements[0].index;
    //get the appropriate element from the chartData
    const label = chartData?.rows[elementIndex][selectedDimension];
    //set the drill value to be the data point the user clicked on
    setDrillAnywhereValue(label);
    //open the popup
    setDrillAnywherePopupVisible(true);
  }
};

Drill Anywhere Popup Menu

To prompt the user with a list of available columns, we can leverage the RestBI model definitions. In this example we are using all columns that are typically classified as a "Dimension" and placing those in a material UI select menu.

<DrillAnywherePopup>
...
<FormControl variant="outlined" style={{ minWidth: 200, marginTop: 20 }}>
  <InputLabel>Drill-Down Dimension</InputLabel>
  <Select
    value=""
    onChange={(e) => drillAnywhere(e.target.value as string)}
    label="Drill-Down Dimension"
    displayEmpty
  >
    {dataModel && dataModel.tables //for each table in the Model
      .flatMap((table) =>
        //for each column in the table, check if its a dimension and add to list
        table.columns.map((column) => {
          if (column.columnType === "DIMENSION") {
            return (
              <MenuItem key={column.name} value={column.name}>
                {column.name}
              </MenuItem>
            );
          }
          return null;
        })
      )}
  </Select>
</FormControl>
</DrillAnywherePopup>

Drill Anywhere Function

The final step is the drill anywhere function itself. All we have to do here is add a new filter value to our filters array (the column and value the user clicked on, and update the selected Dimension to be whatever column the user chose from our column list.

const drillAnywhere = (newDimension: string) => {
  //create new QueryFilter
  const newFilter = {
      column: selectedDimension,
      operator: "=",
      value: drillAnywhereValue,
  };
  const updatedFilters = [...filters, newFilter];
  //update chart dimension to be whatever was clicked
  setSelectedDimension(newDimension);
  //update filters on data query
  setFilters(updatedFilters);
};

Wrap-Up

So yea, with a non-massive amount code we are able to take what was previously a singular flat chart, and turn it into something far more flexible.

Id imagine everyone here is pretty familiar with MaterialUI, React, and ChartJS. Definitely go check them out if not. If this is your first time reading about RestBI, I'd encourage you to check it out as well, here is a link to the full drill anywhere example application if you want to see this in action.

Hopefully someone out there found this useful. I'd love to hear any thoughts readers might have.