DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

Trending

  • How To Use Geo-Partitioning to Comply With Data Regulations and Deliver Low Latency Globally
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Tactics and Strategies on Software Development: How To Reach Successful Software [Video]
  • File Upload Security and Malware Protection

Trending

  • How To Use Geo-Partitioning to Comply With Data Regulations and Deliver Low Latency Globally
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Tactics and Strategies on Software Development: How To Reach Successful Software [Video]
  • File Upload Security and Malware Protection
  1. DZone
  2. Data Engineering
  3. Databases
  4. Introducing a Drill Down Table API in Cube.js

Introducing a Drill Down Table API in Cube.js

Artyom Keydunov user avatar by
Artyom Keydunov
·
Jul. 14, 20 · Tutorial
Like (1)
Save
Tweet
Share
5.99K Views

Join the DZone community and get the full member experience.

Join For Free

Since the release of drill down support in version 0.19.23, you can build interfaces to let users dive deeper into visualizations and data tables. The common use case for this feature is to let users click on a spike on the chart to find out what caused it, or to inspect a particular step of the funnel — who has converted and who has not.

In this blog post, I'll show you how to define drill downs in the data schema and build an interface to let users explore the underlying chart's data. If you're just starting with Cube.js, I highly recommend beginning with this Cube.js 101 tutorial and then coming back here. Also, if you have any questions, don't hesitate to ask them in our Slack community.

You can check the online demo of the example here, and the source code is available on GitHub.

Drill down table demo

Let's start hacking! 

Defining a Drill Down in the Data Schema

Let's start by setting up a new project with Cube.js and configuring drill down support in the data schema. We'll use PostgresQL and our example e-commerce dataset for this tutorial. You can download and import it by running the following commands.

Shell
xxxxxxxxxx
1
 
1
$ curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql
2
$ createdb ecom
3
$ psql --dbname ecom -f ecom-dump.sql


Next, install the Cube.js CLI if you don't have it already, and create a new project.

Shell
xxxxxxxxxx
1
 
1
$ npm -g install cubejs-cli
2
$ cubejs create drill-downs -d postgres


Make sure you have the following credentials in the .env file.

Shell
xxxxxxxxxx
1
 
1
CUBEJS_API_SECRET=SECRET
2
CUBEJS_DB_TYPE=postgres
3
CUBEJS_DB_NAME=ecom


Now, we're ready to launch the Cube.js server and navigate to the playground running at http://localhost:4000.

Shell
 




xxxxxxxxxx
1


 
1
$ npm run dev



Once you're in the playground, navigate to the Schema tab. Then, select the orders and users tables and click Generate Schema, as in the screenshot below.

Cube.js playground


This will generate a basic data schema for users and orders tables, which already includes the drillMembers property on the count measure. The drillMembers property contains a list of dimensions that will be used to show the underlying data when drilling into that measure.

Let's take a closer look at the Orders cube and its count measure.

JavaScript
xxxxxxxxxx
1
 
1
measures: {
2
  count: {
3
    type: `count`,
4
    drillMembers: [id, createdAt]
5
  }
6
}


It already has the basic dimensions listed in the drillMembers property: id and createdAt. We can add additional dimensions to that list. We also can reference dimensions from joined cubes—in our case, from Users.

Let's add more dimensions to the drillMembers property.

JavaScript
xxxxxxxxxx
1
 
1
measures: {
2
  count: {
3
    type: `count`,
4
    drillMembers: [id, status, Users.firstName, Users.city]
5
  }
6
}


That's all we need in the data schema to build our drill down. On the frontend, we're going to make a bar chart to display orders over time. When a user clicks on the bar, our app will display the table inside the modal window, with details about the orders in that bar.

Building the Drill Down UI

We'll use Cube.js templates to generate a frontend app. Navigate to the Dashboard App tab and select the Material-UI React Dashboard. It will take several minutes to set up the Dashboard App and install all the dependencies inside the dashboard-app folder in your project.

Please note: although we use React in this example, you can build the same drill down in Vue.js, Angular, or Vanilla JS.

The first step is to render a bar chart. We're going to plot the count of orders over time, grouped by the status. Eventually, we want to let users click on a specific group and day to explore the underlying orders— e.g., orders created on June 20 and already shipped.

Let's create a dashboard-app/src/DrillDownExample.js file with the following content.

JavaScript
xxxxxxxxxx
1
58
 
1
import React, { useEffect, useState } from "react";
2
import { useCubeQuery } from "@cubejs-client/react";
3
import {
4
  BarChart,
5
  Bar,
6
  ResponsiveContainer,
7
  XAxis,
8
  YAxis,
9
  CartesianGrid,
10
  Tooltip as RechartsTooltip,
11
  Legend
12
} from "recharts";
13
14
const colors = ["#FF6492", "#141446", "#7A77FF"];
15
16
const query = {
17
  measures: ["Orders.count"],
18
  dimensions: ["Orders.status"],
19
  timeDimensions: [{
20
    dimension: "Orders.createdAt",
21
    granularity: "day",
22
    dateRange: "last 30 days"
23
  }]
24
};
25
26
const DrillDownExample = () => {
27
  const { resultSet } = useCubeQuery(query);
28
29
  if (!resultSet) {
30
    return <p>Loading...</p>;
31
  }
32
33
  return (
34
    <>
35
      <ResponsiveContainer width="100%" height={300}>
36
        <BarChart data={resultSet.chartPivot()}>
37
          <CartesianGrid strokeDasharray="3 3" ></CartesianGrid>
38
          <XAxis dataKey="x" ></XAxis>
39
          <YAxis ></YAxis>
40
          <RechartsTooltip ></RechartsTooltip>
41
          <Legend ></Legend>
42
          {resultSet.seriesNames().map(({ key }, index) => {
43
            return (
44
              <Bar
45
                key={key}
46
                dataKey={key}
47
                stackId="a"
48
                fill={colors[index]}
49
              ></Bar>
50
            );
51
          })}
52
        </BarChart>
53
      </ResponsiveContainer>
54
    </>
55
  );
56
}
57
58
export default DrillDownExample;


The code snippet above is pretty straightforward. First, we load data with the useCubeQuery hook and render it later with Recharts. Next, let's add some interactivity and let users click on the bars!

To be able to show the underlying data, we first need to figure out where the user clicked on the chart, and then construct a query to Cube.js to load that data. The user can click on any day in our bar chart and on any status of the order within that day. To describe that location, Cube.js uses two variables: xValues and yValues.

For example, the following values mean that the user wants to explore processing orders on June 6:

JavaScript
xxxxxxxxxx
1
 
1
{
2
  xValues: ["2020-06-06T00:00:00.000"],
3
  yValues: ["processing", "Orders.count"]
4
}


To generate a query that returns data for a drill down table, we need to use the ResultSet#drillDown() method. If we run it with the above values, like this:

JavaScript
xxxxxxxxxx
1
 
1
resultSet.drillDown(
2
  {
3
    xValues: ["2020-06-06T00:00:00.000"],
4
    yValues: ["processing", "Orders.count"]
5
  }
6
)


it will return the query, which has all the dimensions from the drillMembers property in the data schema, as well as all required filters to specifically load processing orders on June 6.

JSON
xxxxxxxxxx
1
31
 
1
{
2
  "measures": [],
3
  "dimensions": [
4
    "Orders.id",
5
    "Orders.status",
6
    "Users.firstName",
7
    "Users.city"
8
  ],
9
  "filters": [
10
    {
11
      "dimension": "Orders.count",
12
      "operator": "measureFilter"
13
    },
14
    {
15
      "member": "Orders.status",
16
      "operator": "equals",
17
      "values": [
18
        "processing"
19
       ]
20
    }
21
  ],
22
  "timeDimensions": [
23
    {
24
      "dimension": "Orders.createdAt",
25
      "dateRange": [
26
        "2020-06-06T00:00:00.000",
27
        "2020-06-06T23:59:59.999"
28
      ]
29
    }
30
  ]
31
}


Once we have the drill down query, we can use it to load data from the Cube.js API.

To get the values for xValues and yValues properties, we will use the ResultSet#chartPivot() and ResultSet#seriesNames() methods. chartPivot() returns xValues for every data row, and seriesNames() returns yValues per series. We're going to use these methods to pass xValues and yValues to the Recharts to make sure we have them in the onClick handler.

First, let's create a click handler, which will accept xValues and yValues, generate a drill down query, and store it in the state.

JavaScript
xxxxxxxxxx
1
13
 
1
const [drillDownQuery, setDrillDownQuery] = useState();
2
const handleBarClick = (event, yValues) => {
3
  if (event.xValues != null) {
4
    setDrillDownQuery(
5
      resultSet.drillDown(
6
        {
7
          xValues: event.xValues,
8
          yValues
9
        }
10
      )
11
    );
12
  }
13
};


Now we need to make sure we pass both xValues and yValues to the handleBarClick. Since we pass resultSet.chartPivot() to the Recharts <BarChart /> component as a data property, the xValues will be available as the property on the event object in the onClick callback. To pass yValues, we need to make the following changes:

diff
xxxxxxxxxx
1
12
 
1
-{resultSet.seriesNames().map(({ key }, index) => {
2
+{resultSet.seriesNames().map(({ key, yValues }, index) => {
3
  return (
4
    <Bar
5
      key={key}
6
      dataKey={key}
7
      stackId="a"
8
      fill={colors[index]}
9
+     onClick={event => handleBarClick(event, yValues)}
10
    />
11
  );
12
})}


Now, as we have drillDownQuery in the state, we can query it in our component.

JavaScript
x
 
1
const drillDownResponse = useCubeQuery(
2
  drillDownQuery,
3
  {
4
    skip: !drillDownQuery
5
  }
6
);


Later, you can use drillDownResponse to render the drill down data however you want. In our example, we use Material-UI Kit and render it as a table within the modal window.

Table rendered with MaterialUI


I hope you found this tutorial helpful for adding interactive drill downs to your application! You can check the online demo of the example here, and the source code is available on GitHub.

If you have any questions, please don't hesitate to reach out to me in Cube.js Slack community.

Database Drill API Data (computing)

Published at DZone with permission of Artyom Keydunov. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • How To Use Geo-Partitioning to Comply With Data Regulations and Deliver Low Latency Globally
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Tactics and Strategies on Software Development: How To Reach Successful Software [Video]
  • File Upload Security and Malware Protection

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: